SQLite导入中文csv文件时的报错

LinberEight

已于 2022-11-07 08:29:23 修改

阅读量1.5k

点赞数 1

文章标签： sqlite 数据库

于 2022-11-01 22:27:08 首次发布

本文链接：https://blog.csdn.net/LinberEight/article/details/127640947

版权

先上结论：

出现INSERT failed: datatype mismatch：如果csv文件有中文，那么就不影响，大概是中文变问号的问题，如果用python来做，不会有问题。确定是这个情况的方法是：select * from 表名，如果出现中文为问号，则是。

出现如下报错

是多次导入同一csv文件的原因，无视即可。

在导入csv文件前在sqlite环境下输入pragma table_info(faqs_scores);并确认返回的数据类型不是全为TEXT（除非这是你期望的），否则输入delete from 表名以清空表中数据，然后检查models文件，0001_initial.py文件，然后重新导入。

事件：

python web开发基础教程（Django版微课版）第81页，即4.4.1~4.4.2。

scores.csv文件放在项目文件中与manage.py同级目录下，SQLite工具包配置环境变量（我直接把三个exe放到了python的Scrpts里）。报错如下

sqlite> .separator ','#实测可以不打引号或改为双引，或将csv文件中的","改为"|"就不用打这一行
sqlite> .import scores.csv faqs_scores
scores.csv:1: INSERT failed: datatype mismatch

我尝试了删除db.sqlite3文件，然后用[1]中情景一的方式重新迁移了数据库，然后重新导入csv文件，无效。

然后我用[2]中方式清空表并重新导入csv文件，无效。

使用select * from faqs_scores;查看表，发现导入成功但中文问号。

在python shell环境下查看表，中文正常显示。但修改字段时报错。

Python 3.10.8 (tags/v3.10.8:aaaf517, Oct 11 2022, 16:50:30) [MSC v.1933 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from faqs.models import scores
>>> scores.objects.get(kh='10110103')
<scores: scores object (1)>
>>> scores.objects.get(kh='10110103').yw+=10
Traceback (most recent call last):
  File "<console>", line 1, in <module>
TypeError: can only concatenate str (not "int") to str

查看表信息

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> pragma table_info(faqs_scores);
0|id|TEXT|0||0
1|kh|TEXT|0||0
2|xm|TEXT|0||0
3|yw|TEXT|0||0
4|sx|TEXT|0||0
5|bj|TEXT|0||0

但我的models文件如下

class scores(models.Model):
    kh=models.CharField(max_length=8)
    xm=models.CharField(max_length=8)
    yw=models.SmallIntegerField()
    sx=models.SmallIntegerField()
    bj=models.CharField(max_length=8)

查看0001_initial.py文件，正常

migrations.CreateModel(
            name='scores',
            fields=[
                ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('kh', models.CharField(max_length=8)),
                ('xm', models.CharField(max_length=8)),
                ('yw', models.SmallIntegerField()),
                ('sx', models.SmallIntegerField()),
                ('bj', models.CharField(max_length=8)),
            ],

于是重新迁移数据库

D:\Files\Program\Code\Python\django\Textbook\chapter4>python manage.py makemigrations
Migrations for 'faqs':
  faqs\migrations\0001_initial.py
    - Create model faqsdata
    - Create model scores

D:\Files\Program\Code\Python\django\Textbook\chapter4>python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, faqs, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying faqs.0001_initial... OK
  Applying sessions.0001_initial... OK

查看表

D:\Files\Program\Code\Python\django\Textbook\chapter4>sqlite3 db.sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> pragma table_info(faqs_scores);
0|id|integer|1||1
1|kh|varchar(8)|1||0
2|xm|varchar(8)|1||0
3|yw|smallint|1||0
4|sx|smallint|1||0
5|bj|varchar(8)|1||0

导入文件

sqlite> .separator ','
sqlite> .import scores.csv faqs_scores
scores.csv:1: INSERT failed: datatype mismatch

查看表，中文问号。

sqlite> select * from faqs_scores;
1,10110103,??,76,55,class1
2,10110114,???,99,33,class1
3,10110701,???,84,39,class1
4,10130101,??,70,35,class1
5,10130117,???,82,45,class1
6,10130303,??,89,27,class1
7,10130406,???,85,118,class1
8,10130412,??,93,75,class1
9,10130413,???,87,57,class1
10,10130423,???,50,0,class1
11,10130515,??,84,0,class1
12,10130528,??,97,95,class1
13,10130601,???,28,52,class1
14,10130701,??,86,40,class1
15,10130721,??,64,56,class1
16,10131029,???,84,51,class1
17,10131216,???,75,28,class1
18,10131322,??,82,31,class2
19,10131416,???,78,22,class2
20,10131726,??,83,52,class2
21,10150325,???,88,43,class2
22,10150525,??,56,72,class2
23,10150626,???,62,59,class2
24,10150711,???,57,68,class2
25,10150712,???,58,25,class2
26,10151103,??,70,47,class2
27,10151414,???,54,48,class2
28,10230102,??,83,36,class2
29,10230104,???,43,45,class2
30,10230113,???,91,50,class2
31,10230121,???,81,74,class2
32,10230126,???,87,0,class2
33,10230207,???,81,23,class2
34,10230303,??,71,72,class2
35,10230313,??,70,30,class2
36,10230321,???,89,41,class2
37,10230423,???,92,32,class2
38,10250704,??,68,61,class2
39,10250928,??,44,83,class3
40,10251020,???,73,77,class3
41,10310128,???,70,40,class3
42,10310313,???,92,89,class3
43,10310323,???,74,0,class3
44,10310325,??,85,50,class3
45,10330208,???,76,36,class3
46,10330209,???,100,28,class3
47,10330308,??,99,46,class3
48,10330509,???,70,54,class3
49,10330603,??,24,107,class3
50,10330712,??,89,33,class3
51,10330822,??,81,34,class3
52,10330824,????,67,29,class3
53,10350323,???,60,88,class3
54,10350409,??,74,65,class3
55,10350410,???,41,15,class3
56,10350503,???,70,66,class3
57,10350626,??,65,35,class3
58,10350719,???,48,37,class3
59,10351105,???,74,88,class3
60,10410404,???,95,57,class3
61,10430202,???,75,34,class3
62,10430216,??,25,45,class3

进入python shell环境，正常

D:\Files\Program\Code\Python\django\Textbook\chapter4>python manage.py shell
Python 3.10.8 (tags/v3.10.8:aaaf517, Oct 11 2022, 16:50:30) [MSC v.1933 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from faqs.models import scores
>>> print(type(scores.objects.get(kh='10110103')))
<class 'faqs.models.scores'>
>>> print(type(scores.objects.get(kh='10110103').yw))
<class 'int'>
>>> d=scores.objects.get(kh='10110103')
>>> d.yw+=10
>>> d.save()
>>> print(scores.objects.get(kh='10110103').yw)
86
>>> from django.db.models import F
>>> d=scores.objects.get(kh='10110103')
>>> d.yw=F('yw')+10
>>> d.save()
>>> print(scores.objects.get(kh='10110103').yw)
96

[1]:Django开发—如何重置migration_卓修武的博客-CSDN博客_django 重新migrate

[2]:sqlite3清空表内容 - 知乎

后记：啊，解决完问题之后大部分的过程都忘了，代码也没保存，bug还不能复现，所以就酱。

我csv文件是utf-8-bom的，也改成ANSI和utf-8试过了。

另外我电脑系统语言是英文，估计有点关系