操作SQLite3数据库

python3标准库中内置了SQLite3模块，可以支持SQLite3数据库的访问和相关数据库操作。

python操作SQLite3数据库的基本流程

1.导入相关库或模块。

2.使用connect()连接数据库并获取数据库连接对象

connect提供的方法：

方法说明
.cursor()创建一个游标对象
.commit()处理事务提交
.rollback()处理事务回滚
.close()关闭一个数据库连接

3.使用con.cursor()获取游标对象。

4.使用游标对象的方法(execute()、executemany()、fetchall()等)来操作数据库，实现插入、修改和删除操作，并查询获取显示相关的记录。

在python程序中，连接函数sqlite3.connect()有两个常用参数：

  • database：表示要访问的数据库名。
  • timeout：表示访问数据的超时设定。

5.使用close()关闭游标对象和数据库连接，数据库操作完成之后，必须关闭数据库连接，这样可以减轻数据库服务器压力。

使用sqlite3创建表

使用sqlite3模块的connect方法创建或打开数据库，需要指定数据库路径，不存在则会创建一个新的数据库。

1
con = sqlite3.connect('e:/sqlitedb/first.db')

例：使用sqlite3创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import sqlite3
# 创建连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
# 执行sql创建表，编写创建表的sql语句
sql = """create table t_person(
            pno INTEGER PRIMARY KEY autoincrement,
            panme VARCHAR NOT NULL,
            age INTEGER
            )"""

try:
    # 执行sql语句
    cur.execute(sql)
    print("创建表成功")
    
except Exception as e:
    print(e)
    print("创建失败")
    
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

使用sqlite3插入数据

调用游标对象的execute()方法执行插入的sql，使用executemany()执行多条sql语句，使用executemany()比循环使用execute()执行多条sql语句效率高。

例1：使用sqlite3插入一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import sqlite3

# 创建数据库连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
# 编写创建表的sql语句
sql = "insert into t_person(pname, age) values(?, ?)"   # ? 为传值占位符

try:
    # 执行sql语句
    cur.execute(sql, ("张三", 22))    # 值需要用元组类型括起来
    # 提交事务
    con.commit()
    print("插入数据成功")

except Exception as e:
    print(e)
    print("插入数据失败")
    # 事务回滚
    con.rollback()

finally:
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    con.close()

例2：使用sqlite3插入多条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import sqlite3

# 创建数据库连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
# 编写创建表的sql语句
sql = "insert into t_person(pname, age) values(?, ?)"   # ? 为传值占位符

try:
    # 执行sql语句
    # 执行插入多条数据sql语句
    cur.executemany(sql, [("李四", 21), ("小明", 24), ("无名", 25)])  # 多组值需要用列表类型括起来
    # 提交事务
    con.commit()
    print("插入多条数据成功")

except Exception as e:
    print(e)
    print("插入数据失败")
    # 事务回滚
    con.rollback()

finally:
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    con.close()

使用sqlite3查询数据

游标对象提供了fetchall()和fetchone()方法查询数据库数据，fetchall()方法获得所有数据，返回一个列表，fetchone()方法获取其中一个结果，返回一个元组。

例1：fetchall()查询所有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import sqlite3

# 创建数据库连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
sql = "select * from t_person"  # sql查询语句
try:
    # 执行sql语句
    cur.execute(sql)
    # 获取查询数据
    person_all = cur.fetchall()  # 返回一个列表
    # 遍历列表
    for p in person_all:
        print(p)
    print("查询数据成功")

except Exception as e:
    print(e)
    print("查询数据失败")
    # 事务回滚
    con.rollback()

finally:
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    con.close()

例2：fetchone()查询一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import sqlite3

# 创建数据库连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
sql = "select * from t_person"  # sql查询语句
try:
    # 执行sql语句
    cur.execute(sql)
    # 获取查询数据
    person_one = cur.fetchone()  # 返回一个元组
    print(person_one)
    print("查询数据成功")

except Exception as e:
    print(e)
    print("查询数据失败")
    # 事务回滚
    con.rollback()

finally:
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    con.close()

使用sqlite3修改或删除数据

例1：修改一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import sqlite3

# 创建数据库连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
# 编写删除数据的sql语句
sql = "update t_person set pname=? where pno=?"  # sql修改语句，pno为第几条数据传值占位符
try:
    # 执行sql语句
    cur.execute(sql, ("张宇", 1))
    # 提交事务
    con.commit()
    print("修改数据成功")

except Exception as e:
    print(e)
    print("修改数据失败")
    # 事务回滚
    con.rollback()

finally:
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    con.close()

例2：删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import sqlite3

# 创建数据库连接
con = sqlite3.connect("e:/sqlite3Demo/demo.db")
# 创建游标对象
cur = con.cursor()
# 编写删除数据的sql语句
sql = "delete from t_person where pno=?"  # 删除语句，pno为第几条数据传值占位符
try:
    # 执行sql语句
    cur.execute(sql, (1,))
    # 提交事务
    con.commit()
    print("删除数据成功")

except Exception as e:
    print(e)
    print("删除数据失败")
    # 事务回滚
    con.rollback()

finally:
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    con.close()

Python调用Pymysql库使用Mysql

python安装pymysql

1
pip install pymysql

操作Pymysql数据库创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306)
# 创建游标对象
cur = con.cursor()
# 编写创建表的sql语句
sql = """
        create table t_student(
        son int primary key auto_increment,
        sname varchar(30) not null,
        age int(2),
        score float(3, 1)
        )
    """
try:
    # 执行创建表的sql语句
    cur.execute(sql)
    print("创建表成功")
except Exception as e:
    print(e)
    print("创建失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

操作Pymysql数据库插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306)
# 创建游标对象
cur = con.cursor()
# 编写数据插入的sql语句
sql = """
        insert into t_student(sname, age, score) values(%s, %s, %s)
    """
try:
    # 执行插入一条数据的sql语句
    cur.execute(sql, ("小明", 23, 98.2))
    # 提交事务
    con.commit()
    print("插入数据成功")
except Exception as e:
    print(e)
    # 事务回滚
    con.rollback()
    print("插入数据失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

操作Pymysql数据库插入多条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306, charset = 'utf8')
# 创建游标对象
cur = con.cursor()
# 编写数据插入的sql语句
sql = """
        insert into t_student(sname, age, score) values(%s, %s, %s)
    """
args = [("张三", 23, 98.2), ("李四", 25, 99.4)] # 定义变量接收插入数据，用列表括起来
try:
    # 执行插入多条数据的sql语句
    cur.executemany(sql, args)
    # 提交事务
    con.commit()
    print("插入数据成功")
except Exception as e:
    print(e)
    # 事务回滚
    con.rollback()
    print("插入数据失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

操作Pymysql数据库查询所有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306, charset = 'utf8')
# 创建游标对象
cur = con.cursor()
# 编写数据查询的sql语句
sql = "select * from t_student where age>=22"   # 查询年龄大于等于22的数据
try:
    # 执行查询数据的sql语句
    cur.execute(sql)
    # 获取查询结果
    results = cur.fetchall()    # 查询所有多条数据方法
    for r in results:
        sno = r[0]
        sname = r[1]
        age = r[2]
        score = r[3]
        print("sno:", sno, "sname:", sname, "age:", age, "score:", score)
    print("查询数据成功")
except Exception as e:
    print(e)
    print("查询数据失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

操作Pymysql数据库查询一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306, charset = 'utf8')
# 创建游标对象
cur = con.cursor()
# 编写数据查询的sql语句
sql = "select * from t_student where age>=22"   # 查询年龄大于等于22的数据
try:
    # 执行查询数据的sql语句
    cur.execute(sql)
    # 获取查询结果
    results = cur.fetchone()    # 查询一条数据方法
    print("sno:", results[0], "sname:", results[1], "age:", results[2], "score:", results[3])
    print("查询数据成功")
except Exception as e:
    print(e)
    print("查询数据失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

操作Pymysql数据库修改数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306, charset = 'utf8')
# 创建游标对象
cur = con.cursor()
# 编写数据修改的sql语句
sql = "update t_student set sname=%s where sno=%s"
try:
    # 执行修改数据的sql语句
    cur.execute(sql, ("五方", 2))
    # 提交事务
    con.commit()
    print("修改数据成功")
except Exception as e:
    print(e)
    # 事务回滚
    con.rollback()
    print("修改数据失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()

操作Pymysql数据库删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 导入pymysql
import pymysql
# 创建数据库连接
con = pymysql.connect(host="localhost", user="root", password="QQ524900", database="python_db", port=3306, charset = 'utf8')
# 创建游标对象
cur = con.cursor()
# 编写数据删除的sql语句
sql = "delete from t_student where sname=%s"
try:
    # 执行删除数据的sql语句
    cur.execute(sql, ("五方"))
    # 提交事务
    con.commit()
    print("删除数据成功")
except Exception as e:
    print(e)
    # 事务回滚
    con.rollback()
    print("删除数据失败")
finally:
    # 关闭游标
    cur.close()
    # 关闭连接
    con.close()