A database is a file that is organized for storing data. Most databases are organized like a dictionary in the sense that they map from keys to values.
database software is designed to keep the inserting and accessing
of data very fast, even for large amounts of data. Database software maintains its
performance by building indexes as data is added to the database to allow the
computer to jump quickly to a particular entry
There are many different database systems which are used for a wide variety of purposes including: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and SQLite.
focus on SQLite in this book because it is a very common database and is
already built into Python. SQLite is designed to be embedded into other applications to provide database support within the application. For example, the Firefox browser also uses the SQLite database internally as do many other products.
Database Concept
- look like spreadsheet
Creating a database table
아래 코드는 하나의 데이터베이스 파일과 하나의 노래 정보를 담을 Tracks 라는 테이블을 생성한다. 이 테이블에는 두개의 column으로 이루어져있다.
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
conn.close()connect: connect operation makes a "connection" to the database stored in the file music.splite in the current directory. If the file does not exists, it will be created.
cursor: Once we have the cursor, we can begin to execute commands on the contents of the database using the execute() method.
Database commands are expressed in a special language that has been standardized
across many different database vendors to allow us to learn a single database
language. The database language is called Structured Query Language or SQL for
short.
import sqlite3
conn = sqlite3.connect('music.sqlite') # 데이터베이스 연결. 없으면 새로 생성
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks') # Track이라는 테이블 있으면 삭제. 프로그램의 일관성 유지
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
cur.execute('INSERT INTO Tracks (title, plays) VALUES (?,?)',('Thunderstruck', 20))
cur.execute('INSERT INTO Tracks (title, plays) VALUES (?,?)',('My way', 15))
conn.commit()
print('Tracks:')
cur.execute('SELECT title,plays FROM Tracks')
# After we execute the SELECT statement, the cursor is something we can loop through in a for statement.
for row in cur:
print(row)
cur.execute('DELETE FROM Tracks WHERE plays < 100')
conn.commit()
conn.close()
commit : force the data to be writtem to the database file
select : to retrieve the rows inserted from the table
효율을 위해 cursor는 SELECT 문을 실행했을 때 데이터베이스에서 모든 데이터를 읽어오지 않음. 그럼 언제 읽어오냐, for문을 통해 데이터를 loop through할 때!
At the very end of the program, we execute an SQL command to _DELETE _the
rows we have just created so we can run the program over and over. The DELETE
command shows the use of a _WHERE _clause that allows us to express a selection
criterion so that we can ask the database to apply the command to only the rows
that match the criterion.
Structured Query Language summary
CREATE TABLE Tracks (title TEXT, plays INTEGER)
INSERT INTO Tracks (title, plays) VALUES ('My Way', 15)
SELECT * FROM Tracks WHERE title = 'My Way'
SELECT title,plays FROM Tracks ORDER BY title
DELETE FROM Tracks WHERE title = 'My Way'
UPDATE Tracks SET plays = 16 WHERE title = 'My Way'
> These four basic SQL commands (INSERT, SELECT, UPDATE, and DELETE)
allow the four basic operations needed to create and maintain data.