Python comes with a lightweight relational database called SQLite, which uses the SQL language as a back-end database that can be used with Python to build websites or create tools that require data storage.SQLite has a wide range of applications in other areas, such as HTML5 and mobile.The Python Standard Library's sqlite3 provides an interface to this database.
I will create a simple relational database to store the categories and prices of books for a bookstore. The database contains two tables: category for recording the categories and book for recording information about a particular 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.
1. Create database
I'll start by creating the database, and the tables in it. After connecting to the database using connect(), I can then locate the pointer cursor, theExecuting SQL Commands：
# By Vamei
import sqlite3
# is a file in the working directory.
conn = ("")
c = ()
# create tables
('''CREATE TABLE category
(id int primary key, sort int, name text)''')
('''CREATE TABLE book
(id int primary key,
sort int,
name text,
price real,
category int,
FOREIGN KEY (category) REFERENCES category(id))''')
# save the changes
()
# close the connection with the database
()
SQLite's database is a file on disk, as above, so the entire database can be easily moved or copied. It doesn't exist at first, so SQLite will automatically create a new file.
utilizationexecute() command, I executed two SQL commands to create two tables in the database. Once created, save and disconnect from the database.
2. Insert data
The database and tables were created above, establishing the abstract structure of the database. The following willInserting data in the same database：
# By Vamei
import sqlite3
conn = ("")
c = ()
books = [(1, 1, 'Cook Recipe', 3.12, 1),
(2, 3, 'Python Intro', 17.5, 2),
(3, 2, 'OS Intro', 13.6, 2),
]
# execute "INSERT"
("INSERT INTO category VALUES (1, 1, 'kitchen')")
# using the placeholder
("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])
# execute multiple commands
('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)
()
()
Inserting data can also be done using execute() to execute the full SQL statement. parameters in the SQL statement, use "?" in the SQL statement, use "?" as an alternative symbol and give the specific value in the parameter that follows. It is not possible to use Python formatting strings such as "%s", as this usage is vulnerable to SQL injection attacks.
I could use one.executemany()method to perform multiple inserts to add multiple records. Each record is an element of a table, such as the elements in the BOOKS table above.
3. Enquiry
After executing the query statement, Python returns a looper containing multiple records obtained by the query. You loop through the reads, or you canReading records using the fetchone() and fetchall() methods provided by sqlite3：
# By Vamei
import sqlite3
conn = ('')
c = ()
# retrieve one record
('SELECT name FROM category ORDER BY sort')
print(())
print(())
# retrieve all records as a list
('SELECT * FROM book WHERE =1')
print(())
# iterate through the records
for row in ('SELECT name, price FROM book ORDER BY sort'):
print(row)
4. Updates and deletions
You can update a record thatOr delete the record:
# By Vamei
conn = ("")
c = ()
('UPDATE book SET price=? WHERE id=?',(1000, 1))
('DELETE FROM book WHERE id=2')
()
()
You can also just delete the whole table:
('DROP TABLE book')
If deleted, then the entire database is deleted.
Summary:
sqlite3 is just an interface to SQLite. You need to learn more about relational databases to become proficient with SQLite databases.
to this article on the Python standard library of the database sqlite3 article is introduced to this, more related Python standard library sqlite3 content, please search for my previous articles or continue to browse the following related articles I hope you will support me in the future more!