Question

如何在sqlite3中插入中文字符

Answer 1

第一种，设置text_factory = str

#-*-encoding:utf-8-*-

import sqlite3

def create_tables(dbname):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute('''create table userinfo(name text, email text)''')
conn.commit()
cursor.close()
conn.close()

def drop_tables(dbname):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute('''drop table userinfo''')
conn.commit()
cursor.close()
conn.close()

def insert():
users = ('腾讯qq', 'qq@example.com')
conn = sqlite3.connect(dbname)
conn.text_factory = str ##!!!
cursor = conn.cursor()
cursor.execute("insert into userinfo(name, email) values(?, ?)", users)
conn.commit()
cursor.close()
conn.close()

def select(text):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
print "select name from userinfo where email='%s'" % text
for row in cursor.execute("select name from userinfo where email= ? ", (text,)):
print row[0]

if __name__ == '__main__':
dbname = 'test.db'
try:
drop_tables(dbname)
except:
pass
create_tables(dbname)
insert()
select("qq@example.com")
drop_tables(dbname)

第二种，把插入的数据转换为unicode：
#-*-encoding:utf-8-*-

import sqlite3

def create_tables(dbname):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute('''create table userinfo(name text, email text)''')
conn.commit()
cursor.close()
conn.close()

def drop_tables(dbname):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute('''drop table userinfo''')
conn.commit()
cursor.close()
conn.close()

def insert():
users = ('腾讯qq'.decode('utf8'), 'qq@example.com')
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute("insert into userinfo(name, email) values(?, ?)", users)
conn.commit()
cursor.close()
conn.close()

def select(text):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
print "select name from userinfo where email='%s'" % text
for row in cursor.execute("select name from userinfo where email= ? ", (text,)):
print row[0].encode('utf8')

if __name__ == '__main__':
dbname = 'test.db'
try:
drop_tables(dbname)
except:
pass
create_tables(dbname)
insert()
select("qq@example.com")
drop_tables(dbname)