Chapter12——使用python操作SQLite3数据库

第十二章——数据库应用开发

初识

import sqlite3 # 导入数据库

# 连接数据库
conn = sqlite3.connect('example.db') 
# 创建一个cursor对象
c = conn.cursor()  
# 创建表，只需要创建一次，多次则报错
c.execute('''CREATE TABLE stocks(data text, trans text, symbol text, qty real, price real)''')  
# 插入一条记录
c.execute("INSERT INTO stocks VALUES('2021-11-17','BUY','RHAT',100,35.15)")  
# excute返回一个元组，可迭代
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)
# 提交当前事务，保存数据，否则修改不生效
conn.commit() 
# 关闭数据库
conn.close()

增删改查

创建

import sqlite3

conn = sqlite3.connect("exercise.db") # 没有该数据库时会自己创建，多次创建报错
cur = conn.cursor()
sql = '''
        CREATE TABLE student_info
        (
            name text,
            age  int,
            id  int,
            address char(50),
            score real
        );
      '''
cur.execute(sql)
conn.commit()
conn.close()

增加

增加一条

import sqlite3

conn = sqlite3.connect("exercise.db")
cur = conn.cursor()
sql = '''
        INSERT INTO student_info
        (name,age,id,address,score)
        VALUES
        ("学生2", 20, 201212670, "重庆市南岸区",90)
      '''
cur.execute(sql)
conn.commit()
conn.close()

增加多条

创建好的序列

import sqlite3

conn = sqlite3.connect("exercise.db")
cur = conn.cursor()
persons = [('ABC', 21, 201212634, "北京市朝阳区", 95),
           ('CLS', 21, 201212340, "长沙市雨花区", 90),
           ('SXA', 23, 202283194, "郴州市北湖区", 98)]
cur.executemany("INSERT INTO student_info VALUES(?,?,?,?,?)", persons)
conn.commit()
conn.close()

迭代器

import sqlite3
import string


# 使用yield语句的函数可以用来创建生成器对象
def char_generator():
    for c in string.ascii_lowercase:
        yield c,


# a = IterChar()
# for i in a:
#     print(i)
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE characters(c)")
# 创建迭代器对象
theIter = char_generator()
# 插入记录，每次插入一个小写英文字母
cur.executemany("INSERT INTO characters(c) VALUES(?)", theIter)
# 读取并显示所有记录
cur.execute("SELECT c FROM characters")
print(cur.fetchall())

import sqlite3


# 自定义迭代器,按顺序生成小写字母
class IterChar:
    def __init__(self):
        self.count = ord('a')

    def __iter__(self):
        return self

    def __next__(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count  = 1
        return chr(self.count - 1)


# a = IterChar()
# for i in a:
#     print(i)
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE characters(c)")
# 创建迭代器对象
theIter = IterChar()
# 插入记录，每次插入一个小写英文字母
cur.executemany("INSERT INTO characters(c) VALUES(?)", theIter)
# 读取并显示所有记录
cur.execute("SELECT c FROM characters")
print(cur.fetchall())

删除

print('I just delete', cur.execute("DELETE FROM student_info WHERE name='ABC' OR name='CLS' OR name='SXA'").rowcount, 'rows')

查找

cur.execute("SELECT * FROM student_info WHERE address='重庆市南岸区' ORDER BY age ")
print(cur.fetchall())

占位符

使用问号作为占位符

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute('CREATE TABLE example(name, age)')
cur.execute('INSERT INTO example VALUES(?,?) ', ['ALICE', 30])
# n = ['ALICE', 30]
# cur.execute('INSERT INTO example VALUES(?,?) ', n)
cur.executemany("INSERT INTO example VALUES(?,?)", [('BOB', 29), ('CINDY', 30)])
cur.execute("SELECT * FROM example")
print(cur.fetchall())

问号占位后，传入一个包含占位符对应元素的列表。如VALUES(?,?)对应’ALICE’和30，则列表为[‘ALICE’, 30]

使用变量名称作为占位符

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute('CREATE TABLE example(name, age)')
name_var = 'ALICE'
age_var = 30
cur.execute("INSERT INTO example VALUES(:name_var, :age_var)", {'name_var': name_var, 'age_var': age_var})
cur.execute("SELECT * FROM example")
print(cur.fetchall())

注意变量名称占位时，是”:value“的形式。

变量名称占位后，传入一个字典，解释占位符对应的变量。

sqlite3.Row类

一般可以通过以下方法获得表里的数据

1.cur为sqlite3.Cursor对象，通过cur.execute()执行SELECT语句可以作为迭代器输出数据。

age_var = 20
cur.execute("SELECT * FROM student_info WHERE age=:age_var", {'age_var': age_var}) 
for row in cur:
    print(row)

2.当执行完cur.execute()后，cur为一个迭代器，此时cur.fetchone()相当于一个next方法返回一个元组，cur.fetchall()返回一个包含所有元组的列表。

age_var = 20
cur.execute("SELECT * FROM student_info WHERE age=:age_var", {'age_var': age_var}) 
row = cur.fetchone()
print(row)

3.使用sqlite3.Row类后，cur为一个迭代器，此时cur.fetchone返回的是更高级的sqlite3.Row而不是元组。

import sqlite3

conn = sqlite3.connect(':memory:')
conn.row_factory = sqlite3.Row # 使用更高级的sqlite3.Row类，而不是python的元组
c = conn.cursor()
c.execute('''CREATE TABLE stocks(data text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES('2021-11-17','BUY','RHAT',100,35.15)")
c.execute("SELECT * FROM stocks")
r = c.fetchone()
print(type(r)) # <class 'sqlite3.Row'>
print(tuple(r)) # ('2021-11-17', 'BUY', 'RHAT', 100.0, 35.15)
print(r[2]) # RHAT
print(r.keys()) # ['data', 'trans', 'symbol', 'qty', 'price']
print(r['qty']) # 100.0
for field in r: # 可迭代
    print(field)

相较于元组，sqlite3.Row支持：

• 使用keys（）输出表的键
• 按key输出数据，如r[‘qty’]

第十二章就学到这了

这篇好文章是转载于：学新通技术网