数据库
数据库学习笔记
关系数据库类型
Oracle,SQL Server,DB2,Sybase,MySQL,PostgreSQL,SQlite,mongoDB
使用python操作SQLite数据库
优点
1、已内嵌在Python中，使用时需要导入sqlite3。
2、使用c语言开发，支持大多数SQL标准，不支持外键限制。
3、支持原子的、一致的、独立和持久的事务。
4、通过数据库级上的独占性和共享锁定来实现独立事务，当多个线程和进程同一时间访问同一数据库时，只有一个可以写入数据。
5、支持140TB的数据库，每个数据库完全存储在单个磁盘文件中，以B+数据结构的形式存储，一个数据库就是一个文件，通过复制即可实现备份。
常用的SQLite可视化管理工具
www.sqlabs.com==>SQLiteManager
SQLite Database Browser
访问和操作SQLite数据
需要首先导入sqlite3模块，然后创建一个与数据库关联的Connection对象：
import sqlite3 #导入模块 conn = sqlite3.connect('example.db') #连接数据库 # 连接到SQLite数据库 # 数据库文件是example.db # 如果文件不存在，会自动在当前目录创建:
成功创建Connection对象以后，再创建一个Cursor对象，并且调用Cursor对象的execute()方法来执行SQL语句创建数据表以及查询、插入、修改或删除数据库中的数据：
c = conn.cursor() # 创建表， c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''') # 插入一条记录 c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY', 'RHAT', 100, 35.14)") # 提交当前事务，保存数据 conn.commit() # 关闭数据库连接 conn.close()
如果需要查询表中内容，那么重新创建Connection对象和Cursor对象之后，可以使用下面的代码来查询。
for row in c.execute('SELECT * FROM stocks ORDER BY price'): print(row)
connect(database[, timeout, isolation_level, detect_types, factory]) ：连接数据库文件，也可以连接":memory:"在内存中创建数据库。
sqlite3.Connection.execute()：执行SQL语句
sqlite3.Connection.cursor()：返回游标对象
sqlite3.Connection.commit()：提交事务
sqlite3.Connection.rollback()：回滚事务
sqlite3.Connection.close()：关闭连接
connection对象
在sqlite3连接中创建并调用自定义函数
import sqlite3 import hashlib # 自定义Python函数 def md5sum(t): return hashlib.md5(t).hexdigest() # 在内存中创建临时数据库 conn = sqlite3.connect(":memory:") # 创建可在SQL调用的函数，其中第二个参数表示函数的参数个数 conn.create_function("md5", 1, md5sum) cur = conn.cursor() # 在SQL语句中调用自定义函数 cur.execute("select md5(?)", ["中国山东烟台".encode()]) print(cur.fetchone()[0])
row对象
创建并插入数据
conn = sqlite3.connect("D:\\test.db") c = conn.cursor() c.execute('''CREATE TABLE stocks(date text, trans text, symbol text, qty real, price real)''') c.execute("""INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)""") conn.commit() c.close()
使用fetchone（）方法读取数据
conn.row_factory = sqlite3.Row c = conn.cursor() 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)
数据库的应用
1、上篇博客的CSV文件存在数据库
代码
import sqlite3 import openpyxl lists=sqlite3.connect('best college.db') c=lists.cursor() c.execute('''CREATE TABLE rankg("序号","排名","学校名称","省市","总分","生源质量","培养结果","科研规模","科研质量","顶尖成果","顶尖人才","科技服务","产学研合作","成果转化" )''') listinsheet=openpyxl.load_workbook(r'university.xlsx') datainlist=listinsheet.active #获取excel文件当前表格 data_truck=('''INSERT INTO rankg("序号","排名","学校名称","省市","总分","生源质量","培养结果","科研规模","科研质量","顶尖成果","顶尖人才","科技服务","产学研合作","成果转化") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)''') for row in datainlist.iter_rows(min_row=2,max_col=14,max_row=datainlist.max_row): #使excel各行数据成为迭代器 cargo=[cell.value for cell in row] #敲黑板！！使每行中单元格成为迭代器 c.execute(data_truck,cargo) #敲黑板！写入一行数据到数据库中表rankf for row in c.execute('SELECT * FROM rankg ORDER BY "序号"'): print(row) lists.commit() lists.close()
运行结果（部分截图）
2、并查询我们学校的排名
代码
import sqlite3 import openpyxl lists=sqlite3.connect('best college.db') c=lists.cursor() c.execute('''CREATE TABLE rankh("序号","排名","学校名称","省市","总分","生源质量","培养结果","科研规模","科研质量","顶尖成果","顶尖人才","科技服务","产学研合作","成果转化" )''') listinsheet=openpyxl.load_workbook(r'university.xlsx') datainlist=listinsheet.active #获取excel文件当前表格 data_truck=('''INSERT INTO rankh("序号","排名","学校名称","省市","总分","生源质量","培养结果","科研规模","科研质量","顶尖成果","顶尖人才","科技服务","产学研合作","成果转化") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)''') for row in datainlist.iter_rows(min_row=2,max_col=14,max_row=datainlist.max_row): #使excel各行数据成为迭代器 cargo=[cell.value for cell in row] #敲黑板！！使每行中单元格成为迭代器 c.execute(data_truck,cargo) #敲黑板！写入一行数据到数据库中表rankh c.execute('SELECT * FROM rankh WHERE "学校名称"="广州大学"')#由于我们学校没有上榜就查询了广州大学的排名 r = c.fetchall() print(r) lists.commit() lists.close()
结果
3、将广东省的学校进行排名
代码
import sqlite3 import openpyxl lists=sqlite3.connect('best college.db') c=lists.cursor() c.execute('''CREATE TABLE rank9("序号","排名","学校名称","省市","总分","生源质量","培养结果","科研规模","科研质量","顶尖成果","顶尖人才","科技服务","产学研合作","成果转化" )''') listinsheet=openpyxl.load_workbook(r'university.xlsx') datainlist=listinsheet.active #获取excel文件当前表格 data_truck=('''INSERT INTO rank9("序号","排名","学校名称","省市","总分","生源质量","培养结果","科研规模","科研质量","顶尖成果","顶尖人才","科技服务","产学研合作","成果转化") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)''') for row in datainlist.iter_rows(min_row=2,max_col=14,max_row=datainlist.max_row): #使excel各行数据成为迭代器 cargo=[cell.value for cell in row] #敲黑板！！使每行中单元格成为迭代器 c.execute(data_truck,cargo) #敲黑板！写入一行数据到数据库中表rank9 c.execute('SELECT * FROM rank9 WHERE "省市"="广东省" ORDER BY "生源质量"')#由于2016的数据中没有“社会声誉”，因此用“生源质量”来排序 r = c.fetchall() print(r) lists.commit() lists.close()
结果显示
