第十二章——数据库应用开发
import sqlite3
conn = sqlite3.connect('example.db')
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)")
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
def char_generator():
for c in string.ascii_lowercase:
yield c,
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)
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])
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“的形式。
变量名称占位后，传入一个字典，解释占位符对应的变量。
一般可以通过以下方法获得表里的数据
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
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))
print(tuple(r))
print(r[2])
print(r.keys())
print(r['qty'])
for field in r:
print(field)
相较于元组，sqlite3.Row支持：
- 使用keys（）输出表的键
- 按key输出数据，如r[‘qty’]
Original: https://blog.csdn.net/Raysth/article/details/121551093
Author: Raysth
Title: Chapter12——使用python操作SQLite3数据库
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/816217/
转载文章受原作者版权保护。转载请注明原作者出处！