Python standard library 14 database (sqlite3)

Python comes with a lightweight relational database SQLite. This database uses SQL language. As a back-end database, SQLite can be used with Python to build websites or create tools that require data storage. SQLite is also widely used in other fields, such as HTML5 and mobile terminals. sqlite3 in the Python standard library provides an interface to this database.

I will create a simple relational database to store book categories and prices for a bookstore. The database contains two tables: category is used to record classification, and book is used to record information about a certain book. A book belongs to a certain category, so book has a foreign key that points to the primary key id of the catogory table.

Create the database

I will first create the database and the tables in the database. After using connect() to connect to the database, I can execute the SQL command by positioning the cursor:

# 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’s database is a file on disk, such as test.db above, so the entire database can be easily moved or copy. test.db does not exist to begin with, 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 from the database.

Insert data

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

# 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. For parameters in SQL statements, use "?" as a replacement symbol, and give specific values ​​in subsequent parameters. Python format strings such as "%s" cannot be used here because this usage is vulnerable to SQL injection attacks.

I can also use the executemany() method to perform multiple insertions and add multiple records. Each record is an element in the table, such as the elements in the books table above.

Query

After executing the query statement, Python will return a looper containing multiple records obtained by the query. You can read in a loop, or you can use the fetchone() and fetchall() methods provided by sqlite3 to read records:

# 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:

# 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 it directly Entire table:

c.execute('DROP TABLE book')

If test.db is deleted, the entire database will be deleted.

Summary

sqlite3 is just an interface to SQLite. If you want to use SQLite database proficiently, you need to learn more knowledge about relational databases.