replicate Maneira rápida e fácil de migrar o SQLite3 para o MySQL?



sqlite create table blob example (21)

Alguém sabe uma maneira rápida e fácil de migrar um banco de dados SQLite3 para o MySQL?


Answer #1

Essa solução simples funcionou para mim:

<?php
$sq = new SQLite3( 'sqlite3.db' );

$tables = $sq->query( 'SELECT name FROM sqlite_master WHERE type="table"' );

while ( $table = $tables->fetchArray() ) {
    $table = current( $table );
    $result = $sq->query( sprintf( 'SELECT * FROM %s', $table ) );

    if ( strpos( $table, 'sqlite' ) !== false )
        continue;

    printf( "-- %s\n", $table );
    while ( $row = $result->fetchArray( SQLITE3_ASSOC ) ) {
        $values = array_map( function( $value ) {
            return sprintf( "'%s'", mysql_real_escape_string( $value ) );
        }, array_values( $row ) );
        printf( "INSERT INTO `%s` VALUES( %s );\n", $table, implode( ', ', $values ) );
    }
}

Answer #2

Surpreso que ninguém mencionou isso até agora, mas há realmente uma ferramenta explicitamente para isso. Está em perl, SQL: Tradutor: http://sqlfairy.sourceforge.net/

Converte entre a maioria de qualquer forma de dados tabulares (diferentes formatos SQL, planilha do Excel) e até cria diagramas do seu esquema SQL.


Answer #3

Ha ... Eu gostaria de ter encontrado isso primeiro! Minha resposta foi para este post ... script para converter arquivo mysql dump sql em formato que pode ser importado para sqlite3 db

Combinar os dois seria exatamente o que eu precisava:

Quando o banco de dados sqlite3 for usado com o ruby, você pode querer mudar:

tinyint([0-9]*) 

para:

sed 's/ tinyint(1*) / boolean/g ' |
sed 's/ tinyint([0|2-9]*) / integer /g' |

infelizmente, isso só funciona porque, mesmo que você esteja inserindo 1's e 0s em um campo marcado como booleano, o sqlite3 armazena-os como 1s e 0s então você tem que passar por algo como:

Table.find(:all, :conditions => {:column => 1 }).each { |t| t.column = true }.each(&:save)
Table.find(:all, :conditions => {:column => 0 }).each { |t| t.column = false}.each(&:save)

mas foi útil ter o arquivo sql para procurar todos os booleanos.


Answer #4

Eu escrevi este script simples em Python3. Ele pode ser usado como uma classe incluída ou script independente chamado por meio de um shell de terminal. Por padrão, ele importa todos os inteiros como int(11) e strings como varchar(300) , mas tudo isso pode ser ajustado nos argumentos de construtor ou de script, respectivamente.

NOTA: Requer o MySQL Connector / Python 2.0.4 ou superior

Aqui está um link para a fonte no GitHub se você encontrar o código abaixo difícil de ler: https://github.com/techouse/sqlite3-to-mysql/blob/master/sqlite3mysql.py

#!/usr/bin/env python3

__author__ = "Klemen Tušar"
__email__ = "[email protected]"
__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()

Answer #5

Não há necessidade de qualquer script, comando, etc ...

você só precisa exportar seu banco de dados sqlite como um arquivo .csv e importá-lo no Mysql usando o phpmyadmin.

Eu usei e funcionou incrível ...


Answer #6

Este script é ok, exceto para este caso que, claro, eu conheci:

INSERT INTO "requestcomparison_stopword" VALUES(149,'f');
INSERT INTO "requestcomparison_stopword" VALUES(420,'t');

O script deve fornecer esta saída:

INSERT INTO requestcomparison_stopword VALUES(149,'f');
INSERT INTO requestcomparison_stopword VALUES(420,'t');

Mas dá em vez disso a saída:

INSERT INTO requestcomparison_stopword VALUES(1490;
INSERT INTO requestcomparison_stopword VALUES(4201;

com alguns estranhos caracteres não ascii em torno dos últimos 0 e 1.

Isso não apareceu mais quando eu comentei as seguintes linhas do código (43-46), mas outros problemas apareceram:


    line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
    line = line.replace('THIS_IS_FALSE', '0')

Este é apenas um caso especial, quando queremos adicionar um valor 'f' ou 't', mas eu não estou realmente confortável com expressões regulares, eu só queria identificar este caso para ser corrigido por alguém.

De qualquer forma, muito obrigado por esse roteiro útil !!!


Answer #7

fallino identificou corretamente a localização do erro no script. Eu tenho a solução. O problema é as seguintes linhas:

line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
line = line.replace('THIS_IS_FALSE', '0')

O padrão de substituição (segundo parâmetro) nas chamadas re.sub é uma string "regular", então, em vez de expandir para a primeira correspondência de expressão regular, ela se expande para um literal 0x01. Da mesma forma, \ 2 expande para 0x02. Por exemplo, uma linha contendo:, 't', 'f', seria substituída por: <0x01> 10 <0x02>
(Alterações de primeira substituição, 't', para <0x1> 1 <0x2> Segunda substituição muda <0x02> 'f' para <0x1> 0 <0x1>)

A correção é alterar as cadeias de substituição, adicionando um prefixo 'r' ou escapando o \ 1 e \ 2 na cadeia existente. Como a manipulação fácil de strings regexp é para o que as strings são cruas, aqui está a correção usando essas:

line = re.sub(r"([^'])'t'(.)", r"\1THIS_IS_TRUE\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", r"\1THIS_IS_FALSE\2", line)
line = line.replace('THIS_IS_FALSE', '0')

Answer #8

Eu peguei o script Python de https://.com/a/32243979/746459 (acima) e o consertei para lidar com nossos próprios esquemas sqlite. Houve alguns problemas para lidar.

Você pode encontrá-lo no controle de origem aqui: https://bitbucket.org/mjogltd/sqlite3mysql

Também está disponível a mesma coisa que uma imagem do Docker, aqui: https://hub.docker.com/r/mjog/sqlite3mysql/ - é totalmente utilizável mesmo sob uma área de trabalho do Windows.



Answer #10

Eu uso o carregador de dados para migrar quase todos os dados, ele ajuda-me a converter MSSQL para MYSQL, acesso MS para MSSQL, mysql, csv loader, foxpro e MSSQL para acesso MS, MYSQL, CSV, FoxPro etc. Na minha opinião, este é um melhor Ferramenta de Migração de Dados

Download grátis: http://www.dbload.com


Answer #11

Recentemente tive que migrar do MySQL para o JavaDB para um projeto em que nossa equipe está trabalhando. Eu encontrei uma biblioteca Java escrita pelo Apache chamada DdlUtils que tornou isso muito fácil. Ele fornece uma API que permite fazer o seguinte:

  1. Descubra o esquema de um banco de dados e exporte-o como um arquivo XML.
  2. Modifique um banco de dados com base nesse esquema.
  3. Importe registros de um banco de dados para outro, assumindo que eles tenham o mesmo esquema.

As ferramentas que acabamos não foram completamente automatizadas, mas funcionaram muito bem. Mesmo que seu aplicativo não esteja em Java, não deve ser muito difícil criar algumas ferramentas pequenas para fazer uma migração única. Acho que consegui puxar nossa migração com menos de 150 linhas de código.


Answer #12

Com base na solução do Jims: Rápida maneira fácil de migrar o SQLite3 para o MySQL?

sqlite3 your_sql3_database.db .dump | python ./dump.py > your_dump_name.sql
cat your_dump_name.sql | sed '1d' | mysql --user=your_mysql_user --default-character-set=utf8 your_mysql_db -p  

Isso funciona para mim. Eu uso sed apenas para lançar a primeira linha, que não é semelhante a mysql, mas você também pode modificar o script dump.py para jogar fora essa linha.


Answer #13

Todo mundo parece começar com alguns greps e perl expressões e você meio que consegue algo que funciona para o seu conjunto de dados em particular, mas você não tem idéia se ele importou os dados corretamente ou não. Estou seriamente surpresa por ninguém ter construído uma biblioteca sólida que possa converter entre os dois.

Aqui está uma lista de TODAS as diferenças na sintaxe SQL que eu conheço entre os dois formatos de arquivo: As linhas que começam com:

  • COMECE A TRANSACÇÃO
  • COMMIT
  • sqlite_sequence
  • CRIAR ÍNDICE ÚNICO

não são usados ​​no MySQL

  • SQLlite usa CREATE TABLE/INSERT INTO "table_name" e MySQL usa CREATE TABLE/INSERT INTO table_name
  • O MySQL não usa aspas dentro da definição do esquema
  • MySQL usa aspas simples para strings dentro das cláusulas INSERT INTO
  • SQLlite e MySQL têm diferentes maneiras de escapar strings dentro de cláusulas INSERT INTO
  • SQLlite usa 't' e 'f' para booleanos, o MySQL usa 1 e 0 (um regex simples para isso pode falhar quando você tem uma string como: 'eu faço, você não' dentro do seu INSERT INTO )
  • AUTOINCREMENT usa AUTOINCREMENT , o MySQL usa AUTO_INCREMENT

Aqui está um script perl hackeado muito básico que funciona para o meu conjunto de dados e verifica muitas outras dessas condições que outros scripts perl eu encontrei na web. Nu garante que ele irá trabalhar para seus dados, mas fique à vontade para modificar e postar de volta aqui.

#! /usr/bin/perl

while ($line = <>){
    if (($line !~  /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){

        if ($line =~ /CREATE TABLE \"([a-z_]*)\"(.*)/){
            $name = $1;
            $sub = $2;
            $sub =~ s/\"//g;
            $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n";
        }
        elsif ($line =~ /INSERT INTO \"([a-z_]*)\"(.*)/){
            $line = "INSERT INTO $1$2\n";
            $line =~ s/\"/\\\"/g;
            $line =~ s/\"/\'/g;
        }else{
            $line =~ s/\'\'/\\\'/g;
        }
        $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g;
        $line =~ s/THIS_IS_TRUE/1/g;
        $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g;
        $line =~ s/THIS_IS_FALSE/0/g;
        $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
        print $line;
    }
}

Answer #14

Aqui está uma lista de conversores (não atualizados desde 2011):

Um método alternativo que funcionaria bem, mas raramente é mencionado, é: use uma classe ORM que abstraia as diferenças específicas de banco de dados para você. por exemplo, você obtém essas informações em PHP ( RedBean ), Python (camada ORM do Django, Storm , SqlAlchemy ), Ruby on Rails ( ActiveRecord ), Cacau ( CoreData )

ou seja, você poderia fazer isso:

  1. Carregar dados do banco de dados de origem usando a classe ORM.
  2. Armazene dados na memória ou serialize para o disco.
  3. Armazene dados no banco de dados de destino usando a classe ORM.


Answer #16

Obter um despejo SQL

moose@pc08$ sqlite3 mySqliteDatabase.db .dump > myTemporarySQLFile.sql

Importar o despejo para o MySQL

Para pequenas importações:

moose@pc08$ mysql -u <username> -p
Enter password:
....
mysql> use somedb;
Database changed
mysql> source myTemporarySQLFile.sql;

ou

mysql -u root -p somedb < myTemporarySQLFile.sql

Isso solicitará uma senha. Por favor, note: Se você quiser digitar sua senha diretamente, você tem que fazê-lo sem espaço, diretamente após -p :

mysql -u root -pYOURPASS somedb < myTemporarySQLFile.sql

Para lixões maiores:

mysqlimport ou outras ferramentas de importação como o BigDump .

O BigDump oferece uma barra de progresso:


Answer #17

Se você está usando Python / Django é bem fácil:

crie dois bancos de dados em settings.py (como aqui https://docs.djangoproject.com/en/1.11/topics/db/multi-db/ )

então faça assim:

objlist = ModelObject.objects.using('sqlite').all()

for obj in objlist:
    obj.save(using='mysql')

Answer #18

Eu verifiquei cuidadosamente todas as respostas neste post, assim como as respostas em outro post relacionado Translating Perl to Python . No entanto, ninguém conseguiu resolver totalmente o meu problema.

Meu cenário é que eu preciso migrar um banco de dados do Trac do sqlite para o MySQL, e o banco de dados contém muito conteúdo wiki baseado em tecnologia. Portanto, dentro dos valores INSERT INTO , pode haver instruções SQL como CREATE TABLE e AUTOINCREMENT . Mas a substituição linha a linha poderia ter substituições erradas lá.

Por fim, escrevi minha própria ferramenta para esse propósito:

https://github.com/motherapp/sqlite_sql_parser

O uso é relativamente simples:

python parse_sqlite_sql.py export.sql

Dois arquivos seriam gerados: export.sql.schema.sql e export.sql.data.sql . Um para o esquema DB atualizado e outro para os dados atualizados do banco de dados.

Pode-se fazer modificações manuais adicionais no arquivo de esquema do banco de dados usando qualquer editor de texto, sem se preocupar em alterar o conteúdo.

Espero que possa ajudar os outros no futuro.


Answer #19

Provavelmente, a maneira mais rápida e rápida é usar o comando sqlite .dump, nesse caso, criar um dump do banco de dados de amostra.

sqlite3 sample.db .dump > dump.sql

Você pode então (em teoria) importar isso para o banco de dados mysql, neste caso o banco de dados de teste no servidor de banco de dados 127.0.0.1, usando o usuário root.

mysql -p -u root -h 127.0.0.1 test < dump.sql

Eu digo, em teoria, como existem algumas diferenças entre gramáticas.

Em transações sqlite começam

BEGIN TRANSACTION;
...
COMMIT;

MySQL usa apenas

BEGIN;
...
COMMIT;

Existem outros problemas similares (varchars e aspas duplas voltam à mente), mas nada que encontrar e substituir não poderia resolver.

Talvez você devesse perguntar por que está migrando, se o tamanho do banco de dados / desempenho for o problema, talvez reogine o esquema, se o sistema estiver migrando para um produto mais potente, talvez seja o momento ideal para planejar o futuro dos dados.


Answer #20

O script python funcionou após algumas modificações da seguinte forma:

# Remove "PRAGMA foreign_keys=OFF; from beginning of script
# Double quotes were not removed from INSERT INTO "BaselineInfo" table, check if removed from subsequent tables.  Regex needed A-Z added.
# Removed backticks from CREATE TABLE
# Added replace AUTOINCREMENT with AUTO_INCREMENT
# Removed replacement,
#line = line.replace('"', '`').replace("'", '`')

...

useless_es = [
    'BEGIN TRANSACTION',
    'COMMIT',
    'sqlite_sequence',
    'CREATE UNIQUE INDEX',
    'PRAGMA foreign_keys=OFF',
    ]

...

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('"', "'")

...






migration