有人知道一个快速简单的方法来迁移SQLite3数据库到MySQL吗?
当前回答
如果你使用的是Python/Django，这很简单:
在settings.py中创建两个数据库(如https://docs.djangoproject.com/en/1.11/topics/db/multi-db/)
然后就这样做:
objlist = ModelObject.objects.using('sqlite').all()
for obj in objlist:
obj.save(using='mysql')
其他回答
最近，为了我们团队正在进行的一个项目，我不得不从MySQL迁移到JavaDB。我发现Apache编写的一个名为DdlUtils的Java库非常容易做到这一点。它提供了一个API，让你做以下事情:
发现数据库的模式并将其导出为XML文件。 根据此模式修改DB。 将记录从一个DB导入到另一个DB，假设它们具有相同的模式。
我们最终使用的工具并不是完全自动化的，但它们工作得很好。即使您的应用程序不是用Java编写的，使用一些小工具进行一次性迁移也不会太难。我认为我只用了不到150行代码就完成了迁移。
最简单的方法可能是使用sqlite .dump命令，在本例中创建示例数据库的转储。
sqlite3 sample.db .dump > dump.sql
然后(理论上)您可以使用root用户将其导入到mysql数据库中，在本例中是数据库服务器127.0.0.1上的测试数据库。
mysql -p -u root -h 127.0.0.1 test < dump.sql
我说在理论上，因为语法之间有一些差异。
在sqlite事务开始
BEGIN TRANSACTION;
...
COMMIT;
MySQL只使用
BEGIN;
...
COMMIT;
还有其他类似的问题(varchars和双引号会回到脑海中)，但没有查找和替换不能解决的问题。
也许你应该问问为什么要迁移，如果性能/数据库大小是问题，也许应该考虑重新规划模式，如果系统正在迁移到更强大的产品，这可能是计划数据未来的理想时机。
我用Python3编写了这个简单的脚本。它可以作为包含的类或通过终端shell调用的独立脚本使用。默认情况下，它将所有整数导入为int(11)，将字符串导入为varchar(300)，但所有这些都可以分别在构造函数或脚本参数中进行调整。
注意:它需要MySQL连接器/Python 2.0.4或更高版本
如果你觉得下面的代码很难阅读，这是GitHub上的源代码链接:https://github.com/techouse/sqlite3-to-mysql
#!/usr/bin/env python3
__author__ = "Klemen Tušar"
__email__ = "techouse@gmail.com"
__copyright__ = "GPL"
__version__ = "1.0.1"
__date__ = "2015-09-12"
__status__ = "Production"
import os.path, sqlite3, mysql.connector
from mysql.connector import errorcode
class SQLite3toMySQL:
"""
Use this class to transfer an SQLite 3 database to MySQL.
NOTE: Requires MySQL Connector/Python 2.0.4 or higher (https://dev.mysql.com/downloads/connector/python/)
"""
def __init__(self, **kwargs):
self._properties = kwargs
self._sqlite_file = self._properties.get('sqlite_file', None)
if not os.path.isfile(self._sqlite_file):
print('SQLite file does not exist!')
exit(1)
self._mysql_user = self._properties.get('mysql_user', None)
if self._mysql_user is None:
print('Please provide a MySQL user!')
exit(1)
self._mysql_password = self._properties.get('mysql_password', None)
if self._mysql_password is None:
print('Please provide a MySQL password')
exit(1)
self._mysql_database = self._properties.get('mysql_database', 'transfer')
self._mysql_host = self._properties.get('mysql_host', 'localhost')
self._mysql_integer_type = self._properties.get('mysql_integer_type', 'int(11)')
self._mysql_string_type = self._properties.get('mysql_string_type', 'varchar(300)')
self._sqlite = sqlite3.connect(self._sqlite_file)
self._sqlite.row_factory = sqlite3.Row
self._sqlite_cur = self._sqlite.cursor()
self._mysql = mysql.connector.connect(
user=self._mysql_user,
password=self._mysql_password,
host=self._mysql_host
)
self._mysql_cur = self._mysql.cursor(prepared=True)
try:
self._mysql.database = self._mysql_database
except mysql.connector.Error as err:
if err.errno == errorcode.ER_BAD_DB_ERROR:
self._create_database()
else:
print(err)
exit(1)
def _create_database(self):
try:
self._mysql_cur.execute("CREATE DATABASE IF NOT EXISTS `{}` DEFAULT CHARACTER SET 'utf8'".format(self._mysql_database))
self._mysql_cur.close()
self._mysql.commit()
self._mysql.database = self._mysql_database
self._mysql_cur = self._mysql.cursor(prepared=True)
except mysql.connector.Error as err:
print('_create_database failed creating databse {}: {}'.format(self._mysql_database, err))
exit(1)
def _create_table(self, table_name):
primary_key = ''
sql = 'CREATE TABLE IF NOT EXISTS `{}` ( '.format(table_name)
self._sqlite_cur.execute('PRAGMA table_info("{}")'.format(table_name))
for row in self._sqlite_cur.fetchall():
column = dict(row)
sql += ' `{name}` {type} {notnull} {auto_increment}, '.format(
name=column['name'],
type=self._mysql_string_type if column['type'].upper() == 'TEXT' else self._mysql_integer_type,
notnull='NOT NULL' if column['notnull'] else 'NULL',
auto_increment='AUTO_INCREMENT' if column['pk'] else ''
)
if column['pk']:
primary_key = column['name']
sql += ' PRIMARY KEY (`{}`) ) ENGINE = InnoDB CHARACTER SET utf8'.format(primary_key)
try:
self._mysql_cur.execute(sql)
self._mysql.commit()
except mysql.connector.Error as err:
print('_create_table failed creating table {}: {}'.format(table_name, err))
exit(1)
def transfer(self):
self._sqlite_cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
for row in self._sqlite_cur.fetchall():
table = dict(row)
# create the table
self._create_table(table['name'])
# populate it
print('Transferring table {}'.format(table['name']))
self._sqlite_cur.execute('SELECT * FROM "{}"'.format(table['name']))
columns = [column[0] for column in self._sqlite_cur.description]
try:
self._mysql_cur.executemany("INSERT IGNORE INTO `{table}` ({fields}) VALUES ({placeholders})".format(
table=table['name'],
fields=('`{}`, ' * len(columns)).rstrip(' ,').format(*columns),
placeholders=('%s, ' * len(columns)).rstrip(' ,')
), (tuple(data) for data in self._sqlite_cur.fetchall()))
self._mysql.commit()
except mysql.connector.Error as err:
print('_insert_table_data failed inserting data into table {}: {}'.format(table['name'], err))
exit(1)
print('Done!')
def main():
""" For use in standalone terminal form """
import sys, argparse
parser = argparse.ArgumentParser()
parser.add_argument('--sqlite-file', dest='sqlite_file', default=None, help='SQLite3 db file')
parser.add_argument('--mysql-user', dest='mysql_user', default=None, help='MySQL user')
parser.add_argument('--mysql-password', dest='mysql_password', default=None, help='MySQL password')
parser.add_argument('--mysql-database', dest='mysql_database', default=None, help='MySQL host')
parser.add_argument('--mysql-host', dest='mysql_host', default='localhost', help='MySQL host')
parser.add_argument('--mysql-integer-type', dest='mysql_integer_type', default='int(11)', help='MySQL default integer field type')
parser.add_argument('--mysql-string-type', dest='mysql_string_type', default='varchar(300)', help='MySQL default string field type')
args = parser.parse_args()
if len(sys.argv) == 1:
parser.print_help()
exit(1)
converter = SQLite3toMySQL(
sqlite_file=args.sqlite_file,
mysql_user=args.mysql_user,
mysql_password=args.mysql_password,
mysql_database=args.mysql_database,
mysql_host=args.mysql_host,
mysql_integer_type=args.mysql_integer_type,
mysql_string_type=args.mysql_string_type
)
converter.transfer()
if __name__ == '__main__':
main()
获取SQL转储
moose@pc08$ sqlite3 mySqliteDatabase.db .dump > myTemporarySQLFile.sql
导入转储到MySQL
小批量进口:
moose@pc08$ mysql -u <username> -p
Enter password:
....
mysql> use somedb;
Database changed
mysql> source myTemporarySQLFile.sql;
or
mysql -u root -p somedb < myTemporarySQLFile.sql
这将提示您输入密码。请注意:如果你想直接输入你的密码，你必须不带空格，直接在-p后输入:
mysql -u root -pYOURPASS somedb < myTemporarySQLFile.sql
对于较大的转储:
mysqlimport或其他导入工具，如BigDump。
BigDump给你一个进度条:
我刚刚经历了这个过程，在这个Q/ a中有很多非常好的帮助和信息，但我发现我必须将各种元素(加上来自其他Q/ a的一些元素)组合在一起，以获得一个有效的解决方案，以便成功迁移。
然而，即使在结合现有的答案后，我发现Python脚本并没有完全为我工作，因为它在INSERT中出现多个布尔值时不起作用。看看为什么会这样。
所以，我想把我合并后的答案贴在这里。当然，功劳归于那些在其他地方做出贡献的人。但我想回馈一些东西，并节省其他人随后的时间。
我将在下面发布脚本。但首先，这里是转换的说明…
我在OS X 10.7.5 Lion上运行该脚本。巨蟒开箱即用。
要从您现有的SQLite3数据库生成MySQL输入文件，在您自己的文件上运行脚本，如下所示:
Snips$ sqlite3 original_database.sqlite3 .dump | python ~/scripts/dump_for_mysql.py > dumped_data.sql
然后复制结果的dumped_sql。sql文件转移到运行Ubuntu 10.04.4 LTS的Linux盒子中，我的MySQL数据库将驻留在那里。
我在导入MySQL文件时遇到的另一个问题是一些unicode UTF-8字符(特别是单引号)没有正确导入，所以我必须在命令中添加一个开关来指定UTF-8。
将数据输入一个新的空MySQL数据库的结果命令如下:
Snips$ mysql -p -u root -h 127.0.0.1 test_import --default-character-set=utf8 < dumped_data.sql
让它煮熟，应该就这样了!不要忘记仔细检查你的数据，在之前和之后。
所以，正如OP所要求的，当你知道怎么做的时候，这是快速而简单的!: -)
顺便说一句，在我研究这个迁移之前，我不确定的一件事是created_at和updated_at字段值是否会被保留——对我来说，好消息是它们会被保留，所以我可以迁移现有的生产数据。
好运！
更新
Since making this switch, I've noticed a problem that I hadn't noticed before. In my Rails application, my text fields are defined as 'string', and this carries through to the database schema. The process outlined here results in these being defined as VARCHAR(255) in the MySQL database. This places a 255 character limit on these field sizes - and anything beyond this was silently truncated during the import. To support text length greater than 255, the MySQL schema would need to use 'TEXT' rather than VARCHAR(255), I believe. The process defined here does not include this conversion.
以下是对我的数据进行合并和修改后的Python脚本:
#!/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 `%(name)s`%(sub)s\n"
line = line % dict(name=name, sub=sub)
line = line.replace('AUTOINCREMENT','AUTO_INCREMENT')
line = line.replace('UNIQUE','')
line = line.replace('"','')
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 = 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 commands
# 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,
