How to use SQLite database in Python

SQL (Structured Query Language) is a general database query language. SQL has data definition, data operation and data control functions and can complete all the work of the database. When using SQL language, you only need to tell the computer "what to do" without telling it "how to do it".

There are two ways to use SQL language. One is to use it interactively in command mode directly; the other is to be embedded into main languages ​​such as C/C and Python.

Preliminary knowledge

Creation and connection of sqlite database

Creation and connection of sqlite database are divided into three steps:

(1) Import module

import sqlite3
#或者：
from sqlite3 import dbapi2       #导入sqlite3模块的dbapi2接口模块

(2) Use the connect method to create a database

connection=sqlite3.connect(filename) 
#filename为数据库文件名，如果该文件存在则打开该数据库，如果不存在则创建一个新的数据库文件。 
#该方法返回一个数据库连接对象

(3) Close the connection object

connection.close() 
#关闭连接，更新数据库文件

SQL statement to create a data table

The table is the storage relationship in the database A collection of data. A database usually contains multiple tables, such as student table, class table, teacher table, etc. Tables are related through foreign keys.

In SQL, the syntax structure of using the create statement to create a table is as follows:

create table 表名(字段1,…,字段n)

For example, create a mytb table:

create table if not exists mytb( xm char, cj real, kc text )

The table name is mytb; IF NOT EXISTS means if the database If the mytb data table does not exist, create the table; if the data table already exists, do nothing;

xm char, cj real, kc text means that the data table has 3 fields, xm ( name) is a string type, cj(grade) is a float type, and kc(course) is a text string.

SQLite3 supports data types:

null (value = null), integer (integer), real (floating point number), text (string text), blob (binary data block).

execute() method

In Python we can use the execute method to execute a SQL statement

conn.execute('create table if not exists mytb( xm char, cj real, kc text )')

conn is the connection object. The parameter in the execute() method is a SQL statement. The type is string

Insert record

(1) The SQL statement to insert record

The syntax format is as follows:

insert into 表名 [字段名] values [常量]

For example:

insert into Persons values ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

(2) Use execute() to execute the SQL statement

cur.exceute(sql语句)

(3) Submit the transaction

conn.commit() #提交事务，将数据写入文件，保存到磁盘中。

Query the SQL statement

Query the expression that satisfies the condition from the "table" The "target column"

SELECT 目标列 FROM 表 [WHERE 条件表达式]

For example, query the names and ages of students under 20 years old:

select sname age from student where age<20

For example, query all records in the table:

select * from student

fetchall( )

Return multiple records (rows), if there is no result, return empty ()

sqlite_master table

Every SQLite database has a table called sqlite_master, The table is automatically created.

sqlite_master is a special table that stores meta-information of the database, such as table, index, view, and trigger. Related information can be queried through select.

select name,sql from sqlite_master where type='table'

This statement is used to query the name of the data table in the database, and the SQL statement to create the table

Update records

SQL statement to update records:

UPDATE 表名 SET 列名=表达式… [WHERE 条件]

When the "condition" is established, change the value of a column to "expression". For example:

update student set cj=90 where xh="001"

You can change the score of student No. 001 to 90

Delete record

DROP TABLE and DELETE statements:

(1) Delete all records in the data table

DELETE FROM <表名>

For example, delete the student table

delete from student

(2) Delete records

DELETE FROM <表名> WHERE <条件>

For example, delete the record whose middle school number #Example exercises

Level 1: Create and connect database files

The task of this level: Create and connect the mytest.db database in the current directory.

Code analysis

delete from student where xh='001'

Level 2: Create a data table

The task of this level: create or open a data table example.

Code analysis

DROP TABLE 表名

Level 3: Insert records

The task of this level: Create a sqlite3 database file mytest.db, and then create a data table mytb.db, go to Insert three rows of records into the table.

Code Analysis

drop table student

Level 4: Query Records

The task of this level: Design a program to query all records in the data table mytb in the existing database file myfile.db , and query the data table structure.

Code Analysis

def return_values():
#***********Begin**********#
    #（1）导入内置sqlite3模块
    import sqlite3
    #（2）创建conn连接对象（在当前路径下建立mytest.db数据库）
    conn = sqlite3.connect("mytest.db")
    #（3）关闭连接
    conn.close()
#***********End**********#

Level 5: Update and Delete Records

The task of this level: Update and delete records in the sqlite database

Code Analysis

#（1）导入sqlite3模块
import sqlite3
#（2）创建conn连接对象，建立mytest.db数据库
conn = sqlite3.connect("mytest.db")
#（3）定义sql语句，创建mytb数据表，表中有三个字段xm、cj、kc,其数据类型分别为char、real、text
sql_demo = "create table if not exists mytb( xm char , cj real , kc text )"
#（4）执行sql语句
conn.execute(sql_demo)
#（5）关闭连接
conn.close()

Level 6: Comprehensive operation of book database

The tasks of this level: create the database mybook.db in SQLite; create the data table mytb in the database; define in the table: isbn (text ), book title (text), price (real) and other fields, and insert records.

Code Analysis

#（1）导入sqlite3模块
import sqlite3
#（2）创建数据库文件mytest.db
conn = sqlite3.connect("mytest.db")
#（3）定义一个游标对象
cur = conn.cursor()
#（4）定义创建数据表SQL语句
sql_create = "create table if not exists mytb(xm char,cj real,kc text)"
sql_insert_by = "insert into mytb values ('宝玉',85,'计算机')"
sql_insert_dy = "insert into mytb values ('黛玉',90,'计算机')"
sql_insert_bc = "insert into mytb values ('宝钗',80,'数据库')"
#（5）执行SQL语句，创建数据表mytb
conn.execute(sql_create)
#（6）依次插入3条记录，内容分别为：('宝玉',85,'计算机')、('黛玉',92,'计算机')、('宝钗',80,'数据库')
cur.execute(sql_insert_by)
cur.execute(sql_insert_dy)
cur.execute(sql_insert_bc)
#（7）提交事务
conn.commit()
#（8）关闭连接
cur.close()
conn.close()

The above is the detailed content of How to use SQLite database in Python. For more information, please follow other related articles on the PHP Chinese website!