问题描述
我一直在尝试弄清楚如何将数据导入到 sqlite 表中,我可以做到这一点,但我似乎遇到了日期条目的错误问题.
Ive been trying to sort out how to import data into a sqlite table, I can do this but I seem to have an error issue with the date entries.
这是演示错误的可重现代码
Here is reproducible code that demonstrates the error
.txt 文件
1,2019-08-24
2,2019-08-24
和 .py 文件
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import sqlite3
import importlib
import subprocess
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db'
db = SQLAlchemy(app)
class MyTable(db.Model):
__tablename__ = 'myTable'
id = db.Column(db.Integer, primary_key=True)
date_created = db.Column(db.DateTime)
db.create_all()
p = subprocess.Popen(["sqlite3", "mydatabase.db"], stdout=subprocess.PIPE, stdin=subprocess.PIPE)
p.communicate(b"""
INSERT INTO mytable (id, date_created);
.separator ","
.import repro.txt mytable """)
rows = MyTable.query.all()
for row in rows:
mytable_update = MyTable.query.get_or_404(row.id)
mytable_update.date_created = datetime.strptime(mytable_update.date_created, "%Y-%m-%d").date()
db.session.commit()
给出错误
ValueError: Couldn't parse datetime string: '2019-08-24 '
或者我得到的完整错误信息是
or the full error message I get is
(env) (base) Benjamats-Air:helloPython benjamattesjaroen$ python repro.py
/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future. Set it to True or False to suppress this warning.
'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
Error: near line 2: near ";": syntax error
Traceback (most recent call last):
File "repro.py", line 26, in <module>
rows = MyTable.query.all()
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3178, in all
return list(self)
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 105, in instances
util.raise_from_cause(err)
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
raise value
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 85, in instances
rows = [proc(row) for row in fetch]
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 85, in <listcomp>
rows = [proc(row) for row in fetch]
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 572, in _instance
populators,
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 693, in _populate_full
dict_[key] = getter(row)
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 107, in __getitem__
return processor(self._row[index])
File "/Users/benjamattesjaroen/helloPython/env/lib/python3.7/site-packages/sqlalchemy/processors.py", line 43, in process
"'%s'" % (type_.__name__, value)
ValueError: Couldn't parse datetime string: '2019-08-24 '
推荐答案
.txt
文件似乎包含 Date
类型的条目,而不是 Datetime代码>.您可以将以下行的数据转换为 sqlalchemy 指定的
Datetime
类型:日期 - 日期时间描述.
The .txt
file seems to contain entries of type Date
, and not Datetime
. You could convert the data for the following rows into type Datetime
as specified by sqlalchemy: Date - datetime description.
如果该列中的所有数据都包含 '2019-08-24'
或 'YYYY-MM-DD'
格式,那么您可以安全地更改数据库列输入Column(Date)
.
If all of your data in that column contains '2019-08-24'
or 'YYYY-MM-DD'
format then you can safely change your database column type to Column(Date)
.
这篇关于解析日期时间字符串的python sqlite错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!