Quick easy way to migrate SQLite3 to MySQL

Question

Anyone know a quick easy way to migrate a SQLite3 database to MySQL

User · Answer

Everyone seems to starts off with a few greps and perl expressions and you sorta kinda get something that works for your particular dataset but you have no idea if it's imported the data correctly or not. I'm seriously surprised nobody's built a solid library that can convert between the two.

Here a list of ALL the differences in SQL syntax that I know about between the two file formats: The lines starting with:

BEGIN TRANSACTION
COMMIT
sqlite_sequence
CREATE UNIQUE INDEX

are not used in MySQL

SQLite uses CREATE TABLE/INSERT INTO "table_name" and MySQL uses CREATE TABLE/INSERT INTO table_name
MySQL doesn't use quotes inside the schema definition
MySQL uses single quotes for strings inside the INSERT INTO clauses
SQLite and MySQL have different ways of escaping strings inside INSERT INTO clauses
SQLite uses 't' and 'f' for booleans, MySQL uses 1 and 0 (a simple regex for this can fail when you have a string like: 'I do, you don't' inside your INSERT INTO)
SQLLite uses AUTOINCREMENT, MySQL uses AUTO_INCREMENT

Here is a very basic hacked up perl script which works for my dataset and checks for many more of these conditions that other perl scripts I found on the web. Nu guarantees that it will work for your data but feel free to modify and post back here.

#! /usr/bin/perl

while ($line = <>){
    if (($line !~  /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){
        
        if ($line =~ /CREATE TABLE \"([a-z_]*)\"(.*)/i){
            $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_]*)\"(.*)/i){
            $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;
    }
}

User · Answer

There is no need to any script command etc      you have to only export your sqlite database as a  csv file and then import it in Mysql using phpmyadmin   I used it and it worked amazing

User · Answer

If you are using Python Django it s pretty easy   create two databases in settings py  like here https   docs djangoproject com en 1 11 topics db multi-db    then just do like this   objlist   ModelObject objects using  sqlite   all    for obj in objlist      obj save using  mysql

User · Answer

Get a SQL dump  moose pc08  sqlite3 mySqliteDatabase db  dump  gt  myTemporarySQLFile sql   Import dump to MySQL  For small imports   moose pc08  mysql -u  lt username gt  -p Enter password       mysql gt  use somedb  Database changed mysql gt  source myTemporarySQLFile sql    or  mysql -u root -p somedb  lt  myTemporarySQLFile sql   This will prompt you for a password  Please note  If you want to enter your password directly  you have to do it WITHOUT space  directly after -p   mysql -u root -pYOURPASS somedb  lt  myTemporarySQLFile sql   For larger dumps   mysqlimport or other import tools like BigDump   BigDump gives you a progress bar

User · Answer

Probably the quick easiest way is using the sqlite  dump command  in this case create a dump of the sample database   sqlite3 sample db  dump  gt  dump sql   You can then  in theory  import this into the mysql database  in this case the test database on the database server 127 0 0 1  using user root   mysql -p -u root -h 127 0 0 1 test  lt  dump sql   I say in theory as there are a few differences between grammars   In sqlite transactions begin  BEGIN TRANSACTION      COMMIT    MySQL uses just   BEGIN      COMMIT    There are other similar problems  varchars and double quotes spring back to mind  but nothing find and replace couldn t fix   Perhaps you should ask why you are migrating  if performance  database size is the issue perhaps look at reoginising the schema  if the system is moving to a more powerful product this might be the ideal time to plan for the future of your data

User · Answer

echo   dump    sqlite3  tmp db sqlite  gt  db sql   watch out for CREATE statements

User · Answer

This script is ok except for this case that of course  I ve met     INSERT INTO  requestcomparison stopword  VALUES 149  f    INSERT INTO  requestcomparison stopword  VALUES 420  t      The script should give this output     INSERT INTO requestcomparison stopword VALUES 149  f    INSERT INTO requestcomparison stopword VALUES 420  t      But gives instead that output     INSERT INTO requestcomparison stopword VALUES 1490  INSERT INTO requestcomparison stopword VALUES 4201    with some strange non-ascii characters around the last 0 and 1   This didn t show up anymore when I commented the following lines of the code  43-46  but others problems appeared        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     This is just a special case  when we want to add a value being  f  or  t  but I m not really comfortable with regular expressions  I just wanted to spot this case to be corrected by someone   Anyway thanks a lot for that handy script

User · Answer

I recently had to migrate from MySQL to JavaDB for a project that our team is working on   I found a Java library written by Apache called DdlUtils that made this pretty easy   It provides an API that lets you do the following    Discover a database s schema and export it as an XML file   Modify a DB based upon this schema  Import records from one DB to another  assuming they have the same schema    The tools that we ended up with weren t completely automated  but they worked pretty well   Even if your application is not in Java  it shouldn t be too difficult to whip up a few small tools to do a one-time migration   I think I was able to pull of our migration with less than 150 lines of code

User · Answer

Here is a list of converters  not updated since 2011     https   www2 sqlite org cvstrac wiki p ConverterTools  or snapshot at archive org     An alternative method that would work nicely but is rarely mentioned is  use an ORM class that abstracts specific database differences away for you   e g  you get these in PHP  RedBean   Python  Django s ORM layer  Storm  SqlAlchemy   Ruby on Rails  ActiveRecord   Cocoa  CoreData   i e  you could do this    Load data from source database using the ORM class  Store data in memory or serialize to disk  Store data into destination database using the ORM class

User · Answer

I usually use the Export import tables feature of IntelliJ DataGrip       You can see the progress in the bottom right corner

User · Answer

This simple solution worked for me    lt  php  sq   new SQLite3   sqlite3 db       tables    sq- gt query   SELECT name FROM sqlite master WHERE type  table       while    table    tables- gt fetchArray            table   current   table         result    sq- gt query  sprintf   SELECT   FROM  s    table           if   strpos   table   sqlite        false           continue       printf   --  s n    table        while    row    result- gt 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

User · Answer

Here is a python script  built off of Shalmanese s answer and some help from Alex martelli over at Translating Perl to Python  I m making it community wiki  so please feel free to edit  and refactor as long as it doesn t break the functionality  thankfully we can just roll back  - It s pretty ugly but works  use like so  assuming the script is called dump for mysql py   sqlite3 sample db  dump   python dump for mysql py  gt  dump sql   Which you can then import into mysql  note - you need to add foreign key constrains manually since sqlite doesn t actually support them  here is the 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

User · Answer

Ha    I wish I had found this first  My response was to this post    script to convert mysql dump sql file into format that can be imported into sqlite3 db  Combining the two would be exactly what I needed     When the sqlite3 database is going to be used with ruby you may want to change   tinyint  0-9       to   sed  s  tinyint 1     boolean g     sed  s  tinyint  0 2-9      integer  g      alas  this only half works because even though you are inserting 1 s and 0 s into a field marked boolean  sqlite3 stores them as 1 s and 0 s so you have to go through and do something like   Table find  all   conditions   gt    column   gt  1    each    t  t column   true   each  amp  save  Table find  all   conditions   gt    column   gt  0    each    t  t column   false  each  amp  save    but it was helpful to have the sql file to look at to find all the booleans

User · Answer

Based on Jims s solution  Quick easy way to migrate SQLite3 to MySQL   sqlite3 your sql3 database db  dump   python   dump py  gt  your dump name sql cat your dump name sql   sed  1d    mysql --user your mysql user --default-character-set utf8 your mysql db -p     This works for me  I use sed just to throw the first line  which is not mysql-like  but you might as well modify dump py script to throw this line away

User · Answer

http   sqlfairy sourceforge net  http   search cpan org dist SQL-Translator    aptitude install sqlfairy libdbd-sqlite3-perl  sqlt -f DBI --dsn dbi SQLite     open-tran ten-sq db -t MySQL --add-drop-table  gt  mysql-ten-sq sql sqlt -f DBI --dsn dbi SQLite     open-tran ten-sq db -t Dumper --use-same-auth  gt  sqlite2mysql-dumper pl chmod  x sqlite2mysql-dumper pl   sqlite2mysql-dumper pl --help   sqlite2mysql-dumper pl --add-truncate --mysql-loadfile  gt  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  lt  mysql-ten-sq sql mysql -p -u root -D ten-sq  lt  mysql-dump sql

User · Answer

I ve just gone through this process  and there s a lot of very good help and information in this Q A  but I found I had to pull together various elements  plus some from other Q As  to get a working solution in order to successfully migrate   However  even after combining the existing answers  I found that the Python script did not fully work for me as it did not work where there were multiple boolean occurrences in an INSERT  See here why that was the case   So  I thought I d post up my merged answer here   Credit goes to those that have contributed elsewhere  of course   But I wanted to give something back  and save others time that follow   I ll post the script below   But firstly  here s the instructions for a conversion     I ran the script on OS X 10 7 5 Lion   Python worked out of the box   To generate the MySQL input file from your existing SQLite3 database  run the script on your own files as follows   Snips  sqlite3 original database sqlite3  dump   python   scripts dump for mysql py  gt  dumped data sql   I then copied the resulting dumped sql sql file over to a Linux box running Ubuntu 10 04 4 LTS where my MySQL database was to reside   Another issue I had when importing the MySQL file was that some unicode UTF-8 characters  specifically single quotes  were not being imported correctly  so I had to add a switch to the command to specify UTF-8   The resulting command to input the data into a spanking new empty MySQL database is as follows   Snips  mysql -p -u root -h 127 0 0 1 test import --default-character-set utf8  lt  dumped data sql   Let it cook  and that should be it   Don t forget to scrutinise your data  before and after   So  as the OP requested  it s quick and easy  when you know how   -   As an aside  one thing I wasn t sure about before I looked into this migration  was whether created at and updated at field values would be preserved - the good news for me is that they are  so I could migrate my existing production data   Good luck   UPDATE  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     Here s the merged and revised Python script that worked for my data      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    lt     t         r 1   line              line   re sub r    lt     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

User · Answer

I wrote this simple script in Python3  It can be used as an included class or standalone script invoked via a terminal shell  By default it imports all integers as int 11 and strings as varchar 300   but all that can be adjusted in the constructor or script arguments respectively   NOTE  It requires MySQL Connector Python 2 0 4 or higher  Here s a link to the source on GitHub if you find the code below hard to read  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

[mysql] Quick easy way to migrate SQLite3 to MySQL?

Examples related to mysql

Examples related to sqlite

Examples related to migration