Python Learning Note 21: Database Operations (SQLITE3)

Python comes with a lightweight relational database, SQLite. This database uses the SQL language.
As a back-end database, SQLite can be used with Python to build websites or to create tools with data storage requirements.
SQLite also has a wide range of applications in other fields, such as HTML5 and mobile. The sqlite3 in the Python standard library provides an interface to the database.


A database design I will create a simple relational database that stores the classification and price of books for a bookstore.
The database contains two tables: category is used for record classification, and book is used to record the information of a certain books.
A book belongs to a category, so there is a foreign key (foreign key), which points to the primary key ID of the Catogory table.

"category" ID int,sort int,name text "book" ID int,sort int,name text,price real,category int

Two create a database first create a database, and a table in the database.
After using the Connect () Link database, you can execute the SQL statement by locating the pointer cursor.

Import sqlite3# data.db is a db file in the working directory.conn = Sqlite3.connect ("data.db") C = conn.cursor () # Create T Ablesc.execute ("' Create TABLE category      (ID int primary KEY,        sort int,        name text) '") C.execute ("' Create TABLE book      (ID int. PRIMARY KEY,        sort int,        name text, price        real,        category int,       FOREIGN key ( Category) REFERENCES category (ID)) # Save The Changesconn.commit () # Close the connection with the Databaseconn.close ()

SQLite database is a disk file, such as the above data.db, so the entire database can be easily moved or copied.
Data.db does not exist at first, so SQLite will automatically create a new file.
With the Execute () command, I executed two SQL commands to create two tables in the database. After the creation is complete, save and disconnect the database.


Three insert data the database and table are created, and the abstract structure of the database is established. The following will insert the data in the same database:

Import Sqlite3conn = Sqlite3.connect ("data.db") C = conn.cursor () books = [(1, 1, ' Cook Recipe ', 3.12, 1),         (2, 3, ' Pytho  N Intro ', 17.5, 2),         (3, 2, ' OS Intro ', 13.6, 2),        ]# Execute "Insert" C.execute ("INSERT into category VALUES (1, 1, ' Kitchen ') # using the Placeholderc.execute ("INSERT into category VALUES (?,?,?)", [(2, 2, ' computer ')] # execute Mult Iple Commandsc.executemany (' INSERT into book VALUES (?,?,?,?,?) ', books) Conn.commit () Conn.close ()

Inserting data you can also use Execute () to execute a complete SQL statement.
The arguments in the SQL statement, using the "?" As an alternative symbol and give a specific value in the following arguments.
It is not possible to format strings in Python, such as "%s", because this usage is susceptible to SQL injection attacks.
I can also use the Executemany () method to perform multiple insertions, adding multiple records.
Each record is an element in the table, such as an element in the books table above.


Four queries after executing the query statement, Python returns a circulator containing multiple records obtained by the query.
You can also read the records using the Fetchone () and Fetchall () methods provided by Sqlite3:

Import sqlite3conn = Sqlite3.connect (' test.db ') c = conn.cursor () # Retrieve one recordc.execute (' SELECT name from category ORDER by sort ') print (C.fetchone ()) print (C.fetchone ()) # Retrieve all records as a listc.execute (' SELECT * from book WHERE Book.category=1 ') print (C.fetchall ()) # Iterate through the recordsfor row in C.execute (' SELECT name, price from book ORDER by sort '):    print (ROW)

Five Update and delete records

You can update a record, or delete a record:

Import Sqlite3conn = Sqlite3.connect ("test.db") C = conn.cursor () c.execute (' UPDATE book SET price=? Where id=? ', (1)) C.execute (' DELETE from book WHERE id=2 ') conn.commit () Conn.close ()

You can also delete the entire table directly:

C.execute (' DROP TABLE book ')

If you delete data.db, the entire database is deleted.

Python Learning Note 21: Database Operations (SQLITE3)