Python standard library 14 database (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 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. 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:

# By Vameiimport sqlite3# test.db is a file in the working directory.conn = sqlite3.connect("test.db")c = conn.cursor()# create tablesc.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()

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:

# By Vameiimport sqlite3conn = 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 placeholderc.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])# execute multiple commandsc.executemany('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:

# By Vameiimport 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)

Update and delete

You can update or delete a record:

# By Vameiconn = 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 directly delete the entire table:

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.