gpt4 book ai didi

mysql - AWS RDS中将sqlite3从Django迁移到mysql总是报语法错误

转载 作者:行者123 更新时间:2023-11-29 22:47:56 26 4
gpt4 key购买 nike

我正在将 django sqlite3 迁移到 AWS RDS 中的 mysql(我对 mysql 还很陌生)。我所做的是:

(1) 首先我将 sqlite3 转储到 sql 文件 sudo sqlite3 db.sqlite3 .dump > /home/ubuntu/temp.sql

(2) 然后我尝试了 sudo mysql -h MYRDSHOSTNAME -u MYUSERNAME -P 3306 -p mydb < temp.sql

(3)输入密码后,我得到了这个

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 'PRAGMA foreign_keys=OFF' at line 1

我尝试删除 temp.sql 中的第一行,但随后发生了相同的错误,并且它仍然指向第 1 行。

我也去查了mydb在 RDS 中以确保它确实存在。它就在那里(当然是空的)。 enter image description here

RDS上MySQL引擎的版本是5.6.22,django电脑上的MySQL引擎版本是5.6.19

有什么我忽略或做错的事情吗?非常感谢你们!

最佳答案

#!/usr/bin/env python

import re
import fileinput

def this_line_is_useless(line):
useless_es = [
'BEGIN TRANSACTION',
'COMMIT',
'sqlite_sequence',
'CREATE UNIQUE INDEX',
'PRAGMA foreign_keys=OFF'
]
for useless in useless_es:
if re.search(useless, line):
return True

def has_primary_key(line):
return bool(re.search(r'PRIMARY KEY', line))

searching_for_end = False
for line in fileinput.input():
if this_line_is_useless(line): continue

# this line was necessary because ''); was getting
# converted (inappropriately) to \');
if re.match(r".*, ''\);", line):
line = re.sub(r"''\);", r'``);', line)

if re.match(r'^CREATE TABLE.*', line):
searching_for_end = True

m = re.search('CREATE TABLE "?([A-Za-z_]*)"?(.*)', line)
if m:
name, sub = m.groups()
line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS %(nam e)s%(sub)s\n"
line = line % dict(name=name, sub=sub)
else:
m = re.search('INSERT INTO "([A-Za-z_]*)"(.*)', line)
if m:
line = 'INSERT INTO %s%s\n' % m.groups()
line = line.replace('"', r'\"')
line = line.replace('"', "'")
line = line.replace('AUTOINCREMENT','AUTO_INCREMENT')
#line = line.replace('UNIQUE ','')
line = line.replace('"','')
line = re.sub(r"(?<!')'t'(?=.)", r"1", line)
line = re.sub(r"(?<!')'f'(?=.)", r"0", line)

# Add auto_increment if it's not there since sqlite auto_increments ALL
# primary keys
if searching_for_end:
if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
# replace " and ' with ` because mysql doesn't like quotes in CREATE com mands

# And now we convert it back (see above)
if re.match(r".*, ``\);", line):
line = re.sub(r'``\);', r"'');", line)

if searching_for_end and re.match(r'.*\);', line):
searching_for_end = False

if re.match(r"CREATE INDEX", line):
line = re.sub('"', '`', line)

print line,

终于,我找到了答案。下面是我的 python 脚本,用于将 sqlite3 转储转换为 mysql。它实际上来自Quick easy way to migrate SQLite3 to MySQL?与我自己的修改,因为 UINIQUE 在我的情况下不应该再被删除(我猜是由于版本更新)。无论如何,我使用下面的命令转储 sqlite3 并将其导入到 mysql 端。完成。

sqlite3 sample.db .dump | python dump_for_mysql.py > dump.sql

关于mysql - AWS RDS中将sqlite3从Django迁移到mysql总是报语法错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29012586/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com