Python 同步数据库， mysql 报错 1064

现在的问题是：我一共备份了三个文件，前两个顺利执行，第三个文件执行每行都会报错 1064 比如(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2016-3-12 增\n 0-否' at line 6")

但是我在 mysql 命令行下执行 source a.sql 就可以顺利写入

现在看好像唯一区别是报错的 sql 文件很大，500+M，不知道有没有关系。

SQL

MySQL

syntax

文件

12 条回复 • 2020-03-24 20:09:34 +08:00

wuwukai007

2020-02-05 19:57:53 +08:00

为什么要用 pymysql 导入呢，要做处理吗

mw717if

2020-02-05 21:35:52 +08:00

@wuwukai007 创建 sql 文件、写入新库这个过程写在 flask 的脚本里，所以就用 pymysql 处理文件中的 sql 语句了

mw717if

2020-02-05 21:36:47 +08:00

@wuwukai007 主要为了之后做到 apscheduler 定时任务中

wuwukai007

2020-02-05 21:38:07 +08:00

如果不做数据处理可以试下这个，一步到位，
https://github.com/tosmart01/fastsql
pip install fast_sql

mw717if

2020-02-05 21:49:03 +08:00

@wuwukai007

#1.在线迁移
sql = "select * from student where id <1000000"
con = create_engine("oracle+cx_oracle://wuwukai:test@localhost:1521/helowin")
to_db = create_engine("mysql+pymysql://root:123456@localhost:3306/aps_2")
res = fast_sql.to_sql(sql,from_db=con,to_db=to_db,if_exists='delete',
mode='rw',to_table='stu',delete_cache=True,show_progress=True,)

用这个语句对吗我理解的是直接"select * from table " 这样就相当于同步所有数据到新库了

wuwukai007

2020-02-05 21:54:14 +08:00

对，全表不占内存的，我做了分块写入本地，做了缓存。

zpfhbyx

2020-02-05 22:00:48 +08:00

pymysql? 插入的数据有特殊字符啊,转义一下咯

mw717if

2020-02-06 15:58:48 +08:00

@wuwukai007 大佬报错了
res = fast_sql.to_sql(sql, from_db=con, to_db=to_db, if_exists='delete', mode='rw', to_table=table,
delete_cache=True, show_progress=True, )

Traceback (most recent call last):

File "/home/v2ex/PycharmProjects/test_project/app/util/syncdb.py", line 27, in syncdb
delete_cache=True, show_progress=False, )
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/__init__.py", line 360, in to_sql
parse_dates=parse_dates, columns=columns,
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/fastsql/sql.py", line 308, in rsync_db
self.decision()
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/fastsql/sql.py", line 274, in decision
self.write_db()
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/utils/common.py", line 94, in hander
raise e
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/utils/common.py", line 91, in hander
fun(*args, **kwargs)
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/fastsql/sql.py", line 372, in write_db
self.insert_db()
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/fastsql/sql.py", line 394, in insert_db
raise e
File "/home/v2ex/.local/lib/python3.6/site-packages/fast_sql/fastsql/sql.py", line 391, in insert_db
db.executemany(sql, df.values.tolist())
File "/home/v2ex/.local/lib/python3.6/site-packages/DBUtils/SteadyDB.py", line 605, in tough_method
result = method(*args, **kwargs) # try to execute
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 197, in executemany
self._get_db().encoding)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 213, in _do_execute_many
v = values % escape(next(args), conn)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 122, in _escape_args
return tuple(conn.literal(arg) for arg in args)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 122, in <genexpr>
return tuple(conn.literal(arg) for arg in args)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/connections.py", line 467, in literal
return self.escape(obj, self.encoders)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/connections.py", line 460, in escape
return converters.escape_item(obj, self.charset, mapping=mapping)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/converters.py", line 27, in escape_item
val = encoder(val, mapping)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/converters.py", line 118, in escape_unicode
return u"'%s'" % _escape_unicode(value)
File "/home/v2ex/.local/lib/python3.6/site-packages/pymysql/converters.py", line 73, in _escape_unicode
return value.translate(_escape_table)
AttributeError: 'Timestamp' object has no attribute 'translate'

wuwukai007

2020-02-06 16:40:57 +08:00

你两个库字段一致吗

wuwukai007

2020-02-06 17:23:55 +08:00

我修复了，重新装一下
pip install fast_sql==1.2.9,
如果你用的镜像可能要这样
pip install --index-url https://pypi.org/simple fast_sql==1.2.9

mw717if

2020-03-24 18:01:27 +08:00

@wuwukai007 我配置了 if_exists='delete'，
fast_sql.to_sql(sql, from_db=con, to_db=to_db, if_exists='delete', mode='rw', to_table=table,
delete_cache=True, show_progress=True, )
为什么还会报错 pymysql.err.IntegrityError: (1062, "Duplicate entry '509298289133400064' for key 'PRIMARY'")

wuwukai007

2020-03-24 20:09:34 +08:00

@mw717if 不是报主键重复了吗，append='delete' 加一个参数 delete_sql = 'delete from xxx'，新版本要升级，
pip install fast_sql==1.2.19