table - web2py migrate from sqlite to mysql




Maneira rápida e fácil de migrar o SQLite3 para o MySQL? (18)

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:

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



Aqui está um script python, construído com a resposta de Shalmanese e alguma ajuda de Alex martelli no Translating Perl to Python

Estou fazendo o wiki da comunidade, então sinta-se à vontade para editar e refatorar, desde que não quebre a funcionalidade (ainda bem que podemos reverter) - É bem feio, mas funciona

use assim (assumindo que o script é chamado dump_for_mysql.py :

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

Que você pode importar para o mysql

note - você precisa adicionar manualmente as restrições de chaves estrangeiras, já que o sqlite não as suporta

aqui está o script:

#!/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 '');
    # would be converted to \'); which isn't appropriate
    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 "?(\w*)"?(.*)', 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)
    else:
        m = re.search('INSERT INTO "(\w*)"(.*)', line)
        if m:
            line = 'INSERT INTO %s%s\n' % m.groups()
            line = line.replace('"', r'\"')
            line = line.replace('"', "'")
    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')

    # Add auto_increment if it is 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 
        if line.find('DEFAULT') == -1:
            line = line.replace(r'"', r'`').replace(r"'", r'`')
        else:
            parts = line.split('DEFAULT')
            parts[0] = parts[0].replace(r'"', r'`').replace(r"'", r'`')
            line = 'DEFAULT'.join(parts)

    # 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)

    if re.match(r"AUTOINCREMENT", line):
        line = re.sub("AUTOINCREMENT", "AUTO_INCREMENT", line)

    print line,

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.

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 ) );
    }
}

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 !!!


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()

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.


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.


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.



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

...


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.



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;
    }
}

este software fora da caixa - funciona para mim. tente e deixe os outros saberem.

https://dbconvert.com/sqlite/mysql/

Além do que, além do mais:

Eu tive que fazer uma pequena alteração: de alguma forma, o auto_increment de um campo (um campo encontrado na mensagem de erro) não estava habilitado. Então, no phpmyadmin eu verifico a propriedade A_I desse campo e ele funciona completamente. Espero que ajude.

Dunn.


echo ".dump" | sqlite3 /tmp/db.sqlite > db.sql

atente para instruções CREATE


aptitude install sqlfairy libdbd-sqlite3-perl

sqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t MySQL --add-drop-table > mysql-ten-sq.sql
sqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t Dumper --use-same-auth > sqlite2mysql-dumper.pl
chmod +x sqlite2mysql-dumper.pl
./sqlite2mysql-dumper.pl --help
./sqlite2mysql-dumper.pl --add-truncate --mysql-loadfile > mysql-dump.sql
sed -e 's/LOAD DATA INFILE/LOAD DATA LOCAL INFILE/' -i mysql-dump.sql

echo 'drop database `ten-sq`' | mysql -p -u root
echo 'create database `ten-sq` charset utf8' | mysql -p -u root
mysql -p -u root -D ten-sq < mysql-ten-sq.sql
mysql -p -u root -D ten-sq < mysql-dump.sql




migration