#查询
#---------------------------------------------------------------------
#'''
import sqlite3
import json
conn=sqlite3.connect('D:\sqlite3db.db')
c = conn.cursor()
sql = "select ID,name from company order by id limit 10 offset 0"
c.execute(sql) #执行sql语句
#r=c.fetchall()
jsonData = []

#for row in r:
# result={}
# result['ID']=row[0]
# result['NAME']=row[1]
# jsonData.append(result)

for row in c:
print(row)
print(list(row))

result={}
result['ID']=row[0]
result['NAME']=row[1]
jsonData.append(result)

str_json = json.dumps(jsonData,indent=3, ensure_ascii=False)
#ensure_ascii=False 输出中文 ，不加会输出中文的ascii码
#indent=3 格式化json时的缩进量

conn.close() #关闭数据库连接
print(str_json)
print('finish!')
#'''
#-------------------------------------------------------------------------
#异常处理
'''
try:
i=8
j=1
print(i/j)
except Exception as e:
print('error',e)
else:
print('ok')
finally:
print('finally!')
'''
#---------------------------------------------------------------------------
#!/usr/bin/python
#插入速度内存库 40w/s 1亿 250s 文件库380s
'''
import sqlite3
import datetime

print(datetime.datetime.now())
try:
#conn = sqlite3.connect(':memory:')#内存库
conn = sqlite3.connect('mytest.db')#文件库
print ("Opened database successfully")
c=conn.cursor()
c.execute('CREATE TABLE COMPANY \
(ID INT PRIMARY KEY NOT NULL, \
NAME TEXT NOT NULL);')
print('table create successfully')

for i in range(1,1000,1):
c.execute('insert into COMPANY(ID,NAME) \
VALUES(?,?);',(i,'xx'))

except Exception as e:
print(e)
else:
print('ok')
finally:
conn.commit()
c.execute('select count(1) from COMPANY')
print(c.fetchone())
conn.close()
print(datetime.datetime.now())
'''
#-------------------------------------------------------------------------------