SQLite是移动端常用的地理数据库文件格式，Python是语法简洁清晰，又有Esri的ArcPy加持，在处理地理数据中具有很好的应用空间。用Python处理SQLite数据库是一个很有实用价值的课题，本次尝试以批量数据库合并为契机，整理该部分的相关基础知识。
知识要点如下：
- A.对SQLite数据库的操作属于Python标准库，直接引用sqlite3即可；
- B.使用Python对数据库中Data的操控，分为几步：
a)Sqlite3的connect方法连接到数据库文件；
b)在连接上创建一个cursor游标；
c)利用游标的execute方法实现sql语句；
d)若是返回数据的sql语句，需要接下来用游标的fetchone/fetchall方法拿到数据；
e)对数据的update不会直接生效，需要在连接上用commit方法提交；
f)最后关闭连接。 - C.合并过程的数据文件控制，需要利用到os标准库中的walk方法，实现数据遍历；
- D.合并过程对数据库内部结构的控制，一方面需要用到sqlite数据库内建表sqlite_master，它是一个系统表，它包含数据库中所有表、视图、索引和触发器的信息，这里最需要用到该表内type字段中为table的信息，它可以返回了全部table的基本信息，进一步通过PRAGMA table_info(catalog) 得到具体表格的字段信息。
#coding=utf-8
import sys,sqlite3,os
db3_paths=r"C:\测试数据数据" #待处理db3文件所在目录
db3_path=unicode(db3_paths,'utf-8')
including_subs = True#是否处理子文件
db3_extension_name='db3'#配合待合并的sqlite3数据库扩展名
n=0
def append_data(con_current_data,con_des_data,tables_current_data):
#把当前数据库的链接、目标数据库的链接、当前数据库的table列表同时传入
print '\n'+ db3list[i] + ' is Beginning !'
m=0
cur_current_data=con_current_data.cursor()
cur_des_data=con_des_data.cursor()
while m<len(tables_current_data):
if str(tables_current_data[m])[3:-3]!="sqlite_sequence":
sql="select * from "+str(tables_current_data[m])[3:-3]
cur_current_data.execute(sql)
temp_data_list=cur_current_data.fetchall()
temp_sql=""#用来存储插入记录信息，一次只能插入一个一维数组
n=0
if len(temp_data_list)>0:
while n<len(temp_data_list[0]):#注意此处求取的长度是指二维数组有多少列，有它来决定？的个数
temp_sql+="?,"
n+=1
temp_sql="("+temp_sql[0:-1]+")"#对循环后形成的"？,"列阵做修饰，去除尾部逗号，并加上括号来适配sql语句
cur_des_data.executemany("insert into "+str(tables_current_data[m])[3:-3]+ " values " + temp_sql,temp_data_list)
con_des_data.commit()
print '\n'+db3list[i]+"-----"+str(tables_current_data[m])+" Finished!"
m+=1
else: m+=1
print '\n'+ db3list[i]+" All Tables Finished!"
def sql_modify(table_info_unmod):
k=0
table_info_modified="("
#建立新表的execute中的sql语句为 CREATE TABLE XX (字段1 类型 ,字段2 类型)
#table_info_modified在这里将被构建成为(字段1 类型 ,字段2 类型)内容
#PRAGMA table_info返回的信息中，这里只需要使用字段名和字段类型
#这两个信息存在于第二和第三位
while k<len(table_info_unmod):
table_info_modified = table_info_modified+table_info_unmod[k][1]+" "+table_info_unmod[k][2]+","
k+=1
table_info_modified=table_info_modified[0:-1]+")" #最后去掉尾部多余的逗号，并加上括号
return (table_info_modified)
def compare_tables(tables_cur_db3,tables_des_db3,con_current_db3,con_des_db3):
j=0
while j<len(tables_cur_db3):
if (not tables_cur_db3[j] in tables_des_db3) and (str(tables_cur_db3[j])[3:-3]!='sqlite_sequence'):
con_current_db3_cursor=con_current_db3.cursor()
con_current_db3_cursor.execute("PRAGMA table_info ("+str(tables_cur_db3[j])[3:-3]+")")
#PRAGMA table_info方法可以获取表格的字段信息，但返回值为一个列表不能直接作为sql语句
table_info_modified=sql_modify(con_current_db3_cursor.fetchall())
#sql_modify函数实现PRAGMA table_info得到的列表信息转换为合格的sql信息
con_des_db3_cursor=con_des_db3.cursor()
new_table_sql="create table "+ str(tables_cur_db3[j])[3:-3] + table_info_modified
#配合返回后的table_info_modified，构成了完整的用于建立工作表的sql语句
con_des_db3_cursor.execute(new_table_sql)#新建table的操作貌似可以不用commit
j+=1
else: j+=1
con_des_db3_cursor=con_des_db3.cursor()
con_des_db3_cursor.execute(sql_inqury_tables)#更新目标数据库的table列表
tables_des = con_des_db3_cursor.fetchall()
con_des_db3.commit()
global db3list#便于自定义函数中对数据库列表信息的引用和输出
db3list=[]
for dir,dirs,files in os.walk(db3_path):
#由于os.walk会遍历包含子文件夹在内的全部文件
#采用os.listdir可只罗列当前目录的文件
#这里采用os.walk加辅助控制的办法
n=n+1#n为1的时候 正在处理首层目录，记录下其层级L
if n==1:
L=len(dir.split("\\"))
for file in files:
try:
if file.split(".")[1]==db3_extension_name:
db3list.append(os.path.join(dir,file))
except:
print "Got Exception"
#至此，得到了所有扩展名为db3的数据库文件的绝对路径
#严格地讲，只依靠扩展名来确定是否为sqlite3数据库具有不稳定性
#但是暂时不知道有没有内置方法可以测试数据文件是否为合格数据库
global i#配合db3list便于自定义函数中对数据库列表信息的引用和输出
i=0
con_des=sqlite3.connect(db3_path+u'\\'+u'合并后.db')
global tables_des,sql_inqury_tables
#便于自定义函数中对数据库列表信息的引用和输出
tables_des=[]
sql_inqury_tables="SELECT name FROM sqlite_master WHERE type='table'"
#接下来开始合并，新建一个数据库作为目标数据库
#之所以没有选择第一个待合并的数据库作为模板
#是因为数据库结构中可能存在的主键等问题，详见文后总结
#通过tables_des记录合并后的目标数据库内表名称，以便后续比较
while i<len(db3list) and ((len(db3list[i].split("\\"))==L+1)or(including_subs)):
#判别条件包括了【是否到达db3数据路径列表尾端，以及是否包含子文件夹内的db3两个，后者又分为了逻辑或连接的
#层级是否为主文件夹层级+1 或者 是否要求遍历全部文件夹两个条件
#由于采用了while循环，一旦出现子目录的文件，循环即终止
con_current=sqlite3.connect(db3list[i])
cur_current=con_current.cursor()
cur_current.execute(sql_inqury_tables)
tables_current=cur_current.fetchall()
cur_des=con_des.cursor()
cur_des.execute(sql_inqury_tables)
tables_des=cur_des.fetchall()
#这里有一个前提假设：不同数据库文件的相同名称table具有相同的结构，避免了逐个字段判断和对表结构的调整
compare_tables(tables_current,tables_des,con_current,con_des)
#经过compare_tables函数后，目标数据库的表格已经大于等于当前待合并的数据库了
#接下来逐个将表的信息录入目标数据库即可，因此再构建一个append_data函数
append_data(con_current,con_des,tables_current)
con_current.close()
i+=1
con_des.close()
print "---------All Data Finished!--------"
补充说明—本次数据库合并，设定了一些用以简化合并过程的限制条件：
①不同数据库文件中，相同名称的table具有相同的数据结构；
②忽略了【主键】、【外键】等数据库特性
③更没有涉及sqlite3数据库的高级概念和特性
reference article：
http://blog.sina.com.cn/s/blog_3f6643ee0102wv96.html
网友评论