Python operation SQLite database

SQLite is a lightweight database that occupies very low memory, usually only a few hundred K of memory is enough. It treats the entire database, including definition tables, indexes, and data itself, as a single cross-platform [file storage] (https://cloud.tencent.com/product/cfs?from=10680) in the host, and supports Python, Java, C# and other languages. The current version has Developed to SQLite3.

There is a built-in SQLite module in Python, which does not require any configuration and can be directly imported and used. The following briefly introduces the operation of SQLite database in Python.

Operating SQLite is similar to operating mysql database, you need to perform the following steps:

Import sqlite3
Create connection object
Create cursor object
Execute SQL statement
Close cursor
Close the connection

To ensure that the opened Connection object and Cursor object are closed correctly, otherwise resource leakage may occur.
The SQL syntax of SQLite is basically the same as that of mysql.

Create database

After execution, a db file will be generated in the current directory.

import sqlite3
# Create connection object
con=sqlite3.connect('test.db')
# Create cursor object
cur=con.cursor()
# Execute SQL
cur.execute('CREATE TABLE `students` (id int(10) PRIMARY KEY,name varchar(20),no int(20))')
# Close cursor
cur.close()
# Close the connection
con.close()

New data

import sqlite3
# Create connection object
con=sqlite3.connect('test.db')
# Create cursor object
cur=con.cursor()
# Add a single piece of data
# cur.execute('insert into students (id,name,no) values(1,'xiaoming',1001)')
# Add more data, use executemany()Method to perform multiple inserts
data=[(1,'Xiao Ming',1002),(2,'King Two',1002),(3,'Zhang San',1003)]
cur.executemany('insert into students (id,name,no) values(?,?,?)',data)
# Close cursor
cur.close()
# Commit transaction
con.commit()
# Close the connection
con.close()

delete data

import sqlite3
# Create connection object
con=sqlite3.connect('test.db')
# Create cursor object
cur=con.cursor()
# Execute SQL
cur.execute('delete from students where id =3')
# Commit transaction
con.commit()
# Close cursor
cur.close()
# Close the connection
con.close()

change the data

import sqlite3
# Create connection object
con=sqlite3.connect('test.db')
# Create cursor object
cur=con.cursor()
# Execute SQL
cur.execute('update students set name = "Zhang San" where id = 3')
# Commit transaction
con.commit()
# Close cursor
cur.close()
# Close the connection
con.close()

Query data

Three ways to query data:

fetchone(): Get a record in the query result set.

fetchmany(size): Get records of the specified data.

fetchall(): Get all records in the result set.

import sqlite3
# Create connection object
con=sqlite3.connect('test.db')
# Create cursor object
cur=con.cursor()
# Execute SQL
cur.execute('select * from students')
# Two records in the query result set
# print(cur.fetchmany(2))
# Query all records in the result set
print(cur.fetchall())
# Close cursor
cur.close()
# Close the connection
con.close()

View SQLite database through Pycharm

1. Open the Database component

2. Associate SQLite database

The SQLite driver needs to be installed for the first connection, just download it directly.