python创建sqlite3 unicode error_转 python3中SQLLIT编码与解码之Unicode与bytes

#########sample##########

sqlite3.OperationalError: Could not decode to UTF-8 column 'logtype' with text

将

with connection.cursor() as c:

c.execute("select id,name from district_info where p_id=0")

provinces = c.fetchall()

调整为

con = sqlite3.connect('./db.sqlite3')

# con.text_factory = bytes

con.text_factory = lambda x: str(x, 'gbk', 'ignore')

cur = con.cursor()

# with connection.cursor() as c:

c=cur.execute("select id,name from district_info where p_id=0")

provinces = c.fetchall()

return JsonResponse(provinces, safe=False)

############################

https://docs.python.org/3/library/sqlite3.html?highlight=conn%20text_factory%20str

https://docs.python.org/3/library/sqlite3.html?highlight=conn%20text_factory%20str

https://blog.csdn.net/chb4715/article/details/79104299 ( python3中编码与解码之Unicode与bytes)

https://www.cnblogs.com/lightwind/p/4499193.html (重要，python3中SQLLIT编码与解码之Unicode与bytes)

写这篇文章，起源于要写一个脚本批量把CSV文件(文件采用GBK或utf-8编码)写入到sqlite数据库里。

Python版本：2.7.9

sqlite3模块提供了con = sqlite3.connect("D:\\text_factory.db3") 这样的方法来创建数据库(当文件不存在时，新建库)，数据库默认编码为UTF-8，支持使用特殊sql语句设置编码

PRAGMA encoding = "UTF-8"; PRAGMA encoding = "UTF-16"; PRAGMA encoding = "UTF-16le"; PRAGMA encoding = "UTF-16be";

认识text_factory属性，大家应该都是通过以下错误知晓的：

sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

大意是推荐你把字符串入库之前转成unicode string，你要用bytestring字节型字符串(如ascii ，gbk，utf-8)，需要加一条语句text_factory = str。

Python拥有两种字符串类型。标准字符串是单字节字符序列，允许包含二进制数据和嵌入的null字符。 Unicode 字符串是双字节字符序列，一个字符使用两个字节来保存，因此可以有最多65536种不同的unicode字符。尽管最新的Unicode标准支持最多100万个不同的字符，Python现在尚未支持这个最新的标准。

默认text_factory = unicode，原以为这unicode、str是函数指针，但貌似不是，是和

下面写了一段测试验证代码：

1 # -*- coding: utf-8 -*-

2 import sqlite3

3 '''

4 GBK UNIC UTF-8

5 B8A3 798F E7 A6 8F 福

6 D6DD 5DDE E5 B7 9E 州

7 '''

8

9 con = sqlite3.connect(":memory:")

10 # con = sqlite3.connect("D:\\text_factory1.db3")

11 # con.executescript('PRAGMA encoding = "UTF-16";')

12 cur = con.cursor()

13

14 a_text = "Fu Zhou"

15 gb_text = "\xB8\xA3\xD6\xDD"

16 utf8_text = "\xE7\xA6\x8F\xE5\xB7\x9E"

17 unicode_text= u"\u798F\u5DDE"

18

19 print 'Part 1: con.text_factory=str'

20 con.text_factory = str

21 print type(con.text_factory)

22 cur.execute("CREATE TABLE table1 (city);")

23 cur.execute("INSERT INTO table1 (city) VALUES (?);",(a_text,))

24 cur.execute("INSERT INTO table1 (city) VALUES (?);",(gb_text,))

25 cur.execute("INSERT INTO table1 (city) VALUES (?);",(utf8_text,)