Examples of sqlite3 use of the Python standard library

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.

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.

Create a database

I'll start by creating the database, as well as the tables in the database. After connecting to the database using connect (), I can execute the SQL command by locating the pointer cursor:

Copy the Code code as follows:

# by Vamei
Import Sqlite3
# test.db is a file in the working directory.
conn = Sqlite3.connect ("test.db")
c = Conn.cursor ()
# Create tables
C.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 changes
Conn.commit ()
# Close the connection with the database
Conn.close ()

SQLite database is a disk file, such as the above test.db, so the entire database can be easily moved or copied. Test.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.

Inserting data

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

Copy the Code code as follows:

# by Vamei
Import Sqlite3
conn = Sqlite3.connect ("test.db")
c = Conn.cursor ()
Books = [(1, 1, ' Cook Recipe ', 3.12, 1),
(2, 3, ' Python Intro ', 17.5, 2),
(3, 2, ' OS Intro ', 13.6, 2),
]
# Execute "INSERT"
C.execute ("INSERT into category VALUES (1, 1, ' kitchen ')")
# using the placeholder
C.execute ("INSERT into category VALUES (?,?,?)", [(2, 2, ' computer ')])
# Execute multiple commands
C.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.

Inquire

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 the Sqlite3:

Copy the Code code as follows:

# by Vamei
Import Sqlite3
conn = Sqlite3.connect (' test.db ')
c = Conn.cursor ()
# Retrieve one record
C.execute (' SELECT name from category ORDER by sort ')
Print (C.fetchone ())
Print (C.fetchone ())
# retrieve all records as a list
C.execute (' SELECT * from book WHERE Book.category=1 ')
Print (C.fetchall ())
# Iterate through the records
For row in C.execute (' SELECT name ', "Price from book ORDER by Sort '):
Print (ROW)

Update and delete

You can update a record, or delete a record:

Copy the Code code as follows:

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

You can also delete the entire table directly:

Copy the Code code as follows:

C.execute (' DROP TABLE book ')

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

Summarize

Sqlite3 is just an interface for SQLite. To be proficient in using SQLite databases, you need to learn more about relational databases.

