Sqlite3 use instance of Python standard library _python

Python brings a lightweight relational database SQLite. This database uses the SQL language. SQLite as a back-end database, you can build a Web site with Python or create tools that have data storage requirements. SQLite also has a wide range of applications in other areas, such as HTML5 and mobile terminals. The sqlite3 in the Python standard library provides an interface to the database.

I will create a simple relational database that stores the categories and prices of books for a bookstore. There are two tables in the database: category is used to record categories, and book is used to record information for a particular document. A book is assigned to a category, so it has a foreign key (foreign key) that points to the primary key ID of the Catogory table.

Creating a Database

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

Copy 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 ()

The SQLite database is a file on a disk, such as the test.db above, so the entire database can be easily moved or replicated. Test.db does not exist at first, so SQLite will automatically create a new file.

Using 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 tables are created above to establish the abstract structure of the database. The following inserts data into the same database:

Copy 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 can also use Execute () to execute a complete SQL statement. The arguments in the SQL statement, using the "?" As a substitution symbol, and gives a specific value in the following argument. You cannot use Python's format string, such as "%s", because this usage is susceptible to SQL injection attacks.

I can also use the Executemany () method to perform multiple inserts, 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, Python returns a loop containing multiple records obtained by the query. You can read loops by using the Fetchone () and Fetchall () methods provided by Sqlite3:

Copy 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 ' order by sort '):
Print (ROW)

Update and delete

You can update a record, or delete a record:

Copy 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 Code code as follows:

C.execute (' DROP TABLE book ')

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

Summarize

Sqlite3 is just a SQLite interface. To use the SQLite database skillfully, you also need to learn more about relational databases.