V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
mw717if
V2EX  ›  Python

Python 同步数据库, mysql 报错 1064

  •  
  •   mw717if · 2020-02-05 18:16:58 +08:00 · 3765 次点击
    这是一个创建于 1764 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我在 flaskl 里写了一个脚本,通过执行 mysqldump 命令备份三个数据库 sql 文件,然后再用 pymysql 通过每行写入的方式转存到另一个库中。

    现在的问题是:我一共备份了三个文件,前两个顺利执行,第三个文件执行每行都会报错 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,不知道有没有关系。

    12 条回复    2020-03-24 20:09:34 +08:00
    wuwukai007
        1
    wuwukai007  
       2020-02-05 19:57:53 +08:00
    为什么要用 pymysql 导入呢,要做处理吗
    mw717if
        2
    mw717if  
    OP
       2020-02-05 21:35:52 +08:00
    @wuwukai007 创建 sql 文件、写入新库 这个过程写在 flask 的脚本里,所以就用 pymysql 处理文件中的 sql 语句了
    mw717if
        3
    mw717if  
    OP
       2020-02-05 21:36:47 +08:00
    @wuwukai007 主要为了之后做到 apscheduler 定时任务中
    wuwukai007
        4
    wuwukai007  
       2020-02-05 21:38:07 +08:00
    如果不做数据处理可以试下这个,一步到位,
    https://github.com/tosmart01/fastsql
    pip install fast_sql
    mw717if
        5
    mw717if  
    OP
       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
        6
    wuwukai007  
       2020-02-05 21:54:14 +08:00
    对,全表不占内存的,我做了分块写入本地,做了缓存。
    zpfhbyx
        7
    zpfhbyx  
       2020-02-05 22:00:48 +08:00
    pymysql? 插入的数据有特殊字符啊,转义一下咯
    mw717if
        8
    mw717if  
    OP
       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
        9
    wuwukai007  
       2020-02-06 16:40:57 +08:00
    你两个库字段一致吗
    wuwukai007
        10
    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
        11
    mw717if  
    OP
       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
        12
    wuwukai007  
       2020-03-24 20:09:34 +08:00
    @mw717if 不是报主键重复了吗,append='delete' 加一个参数 delete_sql = 'delete from xxx',新版本要升级,
    pip install fast_sql==1.2.19
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4406 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 04:07 · PVG 12:07 · LAX 20:07 · JFK 23:07
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.