python2中在sqlite3中插入中文
1 # -*- coding: utf-8 -*- 2 import sqlite3 3 4 conn = sqlite3.connect('SWC_Perf_Info.db') 5 cur = conn.cursor() 6 7 test_sql1 = "INSERT INTO test (name) VALUES ('我爱中国')" #gbk编码，存入数据库的时候显示的是乱码 8 cur.execute(test_sql1) 9 conn.commit()
10 11 cursor = cur.execute("SELECT name from test") 12 for row in cursor: 13 print(row[0]) #因为读取的是GBK编码内容，所以可以直接显示 14
15 16 test_sql2 = "INSERT INTO test (name) VALUES ('我爱中国')".decode('GBK') #unicode编码 17 cur.execute(test_sql2) 18 conn.commit() 19
20 cursor = cur.execute("SELECT name from test") 21 for row in cursor: 22 print(row[0].decode('utf-8')) #即使用的是Unicode写入的数据库，内部还是使用的UTF-8编码存入，所以需要使用UTF-8解码 23 print(row[0].decode('utf-8').encode('GBK')) #输出的时候可以使用Unicode输出也可以使用本地的GBK输出 24
25 test_sql3 = test_sql2.encode('utf-8') #utf-8编码 26 cur.execute(test_sql3) 27 conn.commit() 28
29 cursor = cur.execute("SELECT name from test") 30 for row in cursor: 31 print(row[0].decode('utf-8')) 32 print(row[0].decode('utf-8').encode('GBK'))
1 select_sql1 = "SELECT name from test where name = '我爱中国'" #以GBK格式查询 2 cursor = cur.execute(select_sql1) 3 for row in cursor: 4 print(row[0].decode('utf-8')) 5 print(row[0].decode('utf-8').encode('GBK'))
#查询不到结果
6 7 select_sql2 = "SELECT name from test where name = '我爱中国'".decode('GBK') #以Unicode格式查询 8 cursor = cur.execute(select_sql2) 9 for row in cursor: 10 print(row[0].decode('utf-8')) 11 print(row[0].decode('utf-8').encode('GBK'))
12 13 select_sql3 = "SELECT name from test where name = '我爱中国'".decode('GBK').encode('utf-8') #以utf-8格式查询 14 cursor = cur.execute(select_sql3) 15 for row in cursor: 16 print(row[0].decode('utf-8')) 17 print(row[0].decode('utf-8').encode('GBK'))
总结下来写入的时候可以使用Unicode或者UTF-8编码的字符写入数据库
读取输出的时候使用UTF-8编码或者GBK编码的字符输出
查询的语句也必须是Unicode或者utf-8格式才能查询到结果
UTF-8是可以支持直接的读取或者输出理想编码格式
