Sqlite3 instance of Python standard library, pythonsqlite3

Sqlite3 instance of Python standard library, pythonsqlite3

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 create tools with data storage needs. SQLite is also widely used in other fields, such as HTML5 and mobile terminals. Sqlite3 In the Python Standard Library provides interfaces for this database.

I will create a simple relational database to classify and store books for a bookstore. The database contains two tables: category is used to record the category, and book is used to record the information of a book. A book belongs to a certain category. Therefore, the book has a foreign key pointing to the primary key id of the catogory table.

Create a database

First, create a database and tables in the database. After connecting to the database using connect (), I can locate the pointer cursor and execute the SQL command:

Copy codeThe Code is 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.exe cute (''' create table category
(Id int primary key, sort int, name text )''')
C.exe cute (''' 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, as shown in test. db above. Therefore, the entire database can be conveniently moved or copied. Test. db does not exist at the beginning, 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 creation, save and disconnect the database.

Insert data

The database and table are created above, and the abstract structure of the database is established. Insert data in the same database as follows:

Copy codeThe Code is as follows:
# By Vamei
Import sqlite3
Conn = sqlite3.connect ("test. db ")
C = conn. cursor ()
Books = [(1, 1, 'cook repository', 3.12, 1 ),
(2, 3, 'python ino', 17.5, 2 ),
(3, 2, 'OS intro', 13.6, 2 ),
]
# Execute "INSERT"
C.exe cute ("insert into category VALUES (1, 1, 'kitchen ')")
# Using the placeholder
C.exe cute ("insert into category VALUES (?, ?, ?) ", [(2, 2, 'computer ')])
# Execute multiple commands
C.exe cute.pdf ('insert INTO book VALUES (?, ?, ?, ?, ?) ', Books)
Conn. commit ()
Conn. close ()

You can also use execute () to insert data to execute the complete SQL statement. For parameters in SQL statements, use "? "As an alternative symbol, and the specific value is given in the following parameters. The format string of Python, such as "% s", cannot be used here because it is vulnerable to SQL injection attacks.

I can also use the executeplugin () method to execute multiple inserts and add multiple records. Each record is an element in the table.

Query

After a query statement is executed, Python returns a iterator containing multiple records obtained from the query. You can also use the fetchone () and fetchall () methods provided by sqlite3 to read records cyclically:

Copy codeThe Code is as follows:
# By Vamei
Import sqlite3
Conn = sqlite3.connect ('test. db ')
C = conn. cursor ()
# Retrieve one record
C.exe cute ('select name FROM category order by sort ')
Print (c. fetchone ())
Print (c. fetchone ())
# Retrieve all records as a list
C.exe cute ('select * FROM book WHERE book. category = 1 ')
Print (c. fetchall ())
# Iterate through the records
For row in c.exe cute ('select name, price FROM book order by sort '):
Print (row)

Update and delete

You can update or delete a record:

Copy codeThe Code is as follows:
# By Vamei
Conn = sqlite3.connect ("test. db ")
C = conn. cursor ()
C.exe cute ('Update book SET price =? WHERE id =? ', (1000, 1 ))
C.exe cute ('delete FROM book WHERE id = 2 ')
Conn. commit ()
Conn. close ()

You can also directly Delete the entire table:

Copy codeThe Code is as follows:
C.exe cute ('drop TABLE Book ')

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

Summary

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