<p>Anyone know a quick easy way to migrate a SQLite3 database to MySQL?</p>

[{'answer_id': 19278, 'author': 'Tim Frey', 'author_id': 1471, 'author_profile': 'https://Stackoverflow.com/users/1471', 'pm_score': 3, 'selected': False, 'text': '<p>I recently had to migrate from MySQL to JavaDB for a project that our team is working on.  I found a <a href="http://db.apache.org/ddlutils/" rel="noreferrer">Java library written by Apache called DdlUtils</a> that made this pretty easy.  It provides an API that lets you do the following:</p>\n\n<ol>\n<li>Discover a database\'s schema and export it as an XML file. </li>\n<li>Modify a DB based upon this schema.</li>\n<li>Import records from one DB to another, assuming they have the same schema.</li>\n</ol>\n\n<p>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.</p>\n'}, {'answer_id': 25860, 'author': 'Richard Gourlay', 'author_id': 2674, 'author_profile': 'https://Stackoverflow.com/users/2674', 'pm_score': 4, 'selected': False, 'text': "<p>Probably the quick easiest way is using the sqlite .dump command, in this case create a dump of the sample database.</p>\n\n<pre><code>sqlite3 sample.db .dump &gt; dump.sql\n</code></pre>\n\n<p>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.</p>\n\n<pre><code>mysql -p -u root -h 127.0.0.1 test &lt; dump.sql\n</code></pre>\n\n<p>I say in theory as there are a few differences between grammars.</p>\n\n<p>In sqlite transactions begin</p>\n\n<pre><code>BEGIN TRANSACTION;\n...\nCOMMIT;\n</code></pre>\n\n<p>MySQL uses just </p>\n\n<pre><code>BEGIN;\n...\nCOMMIT;\n</code></pre>\n\n<p>There are other similar problems (varchars and double quotes spring back to mind) but nothing find and replace couldn't fix.</p>\n\n<p>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.</p>\n"}, {'answer_id': 87531, 'author': 'Shalmanese', 'author_id': 14559, 'author_profile': 'https://Stackoverflow.com/users/14559', 'pm_score': 7, 'selected': False, 'text': "<p>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.</p>\n<p>Here a list of ALL the differences in SQL syntax that I know about between the two file formats:\nThe lines starting with:</p>\n<ul>\n<li>BEGIN TRANSACTION</li>\n<li>COMMIT</li>\n<li>sqlite_sequence</li>\n<li>CREATE UNIQUE INDEX</li>\n</ul>\n<p>are not used in MySQL</p>\n<ul>\n<li>SQLite uses <code>CREATE TABLE/INSERT INTO &quot;table_name&quot;</code> and MySQL uses <code>CREATE TABLE/INSERT INTO table_name</code></li>\n<li>MySQL doesn't use quotes inside the schema definition</li>\n<li>MySQL uses single quotes for strings inside the <code>INSERT INTO</code> clauses</li>\n<li>SQLite and MySQL have different ways of escaping strings inside <code>INSERT INTO</code> clauses</li>\n<li>SQLite uses <code>'t'</code> and <code>'f'</code> for booleans, MySQL uses <code>1</code> and <code>0</code> (a simple regex for this can fail when you have a string like: 'I do, you don't' inside your <code>INSERT INTO</code>)</li>\n<li>SQLLite uses <code>AUTOINCREMENT</code>, MySQL uses <code>AUTO_INCREMENT</code></li>\n</ul>\n<p>Here is a very basic hacked up perl script which works for <em>my</em> 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.</p>\n<pre><code>#! /usr/bin/perl\n\nwhile ($line = &lt;&gt;){\n    if (($line !~  /BEGIN TRANSACTION/) &amp;&amp; ($line !~ /COMMIT/) &amp;&amp; ($line !~ /sqlite_sequence/) &amp;&amp; ($line !~ /CREATE UNIQUE INDEX/)){\n        \n        if ($line =~ /CREATE TABLE \\&quot;([a-z_]*)\\&quot;(.*)/i){\n            $name = $1;\n            $sub = $2;\n            $sub =~ s/\\&quot;//g;\n            $line = &quot;DROP TABLE IF EXISTS $name;\\nCREATE TABLE IF NOT EXISTS $name$sub\\n&quot;;\n        }\n        elsif ($line =~ /INSERT INTO \\&quot;([a-z_]*)\\&quot;(.*)/i){\n            $line = &quot;INSERT INTO $1$2\\n&quot;;\n            $line =~ s/\\&quot;/\\\\\\&quot;/g;\n            $line =~ s/\\&quot;/\\'/g;\n        }else{\n            $line =~ s/\\'\\'/\\\\\\'/g;\n        }\n        $line =~ s/([^\\\\'])\\'t\\'(.)/$1THIS_IS_TRUE$2/g;\n        $line =~ s/THIS_IS_TRUE/1/g;\n        $line =~ s/([^\\\\'])\\'f\\'(.)/$1THIS_IS_FALSE$2/g;\n        $line =~ s/THIS_IS_FALSE/0/g;\n        $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;\n        print $line;\n    }\n}\n</code></pre>\n"}, {'answer_id': 1067365, 'author': 'Jiaaro', 'author_id': 2908, 'author_profile': 'https://Stackoverflow.com/users/2908', 'pm_score': 6, 'selected': False, 'text': '<p>Here is a python script, built off of Shalmanese\'s answer and some help from Alex martelli over at <a href="https://stackoverflow.com/questions/1067060/perl-to-python">Translating Perl to Python</a></p>\n\n<p>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</p>\n\n<p>use like so (assuming the script is called <code>dump_for_mysql.py</code>:</p>\n\n<pre><code>sqlite3 sample.db .dump | python dump_for_mysql.py &gt; dump.sql\n</code></pre>\n\n<p>Which you can then import into mysql</p>\n\n<p>note - you need to add foreign key constrains manually since sqlite doesn\'t actually support them</p>\n\n<p>here is the script:</p>\n\n<pre><code>#!/usr/bin/env python\n\nimport re\nimport fileinput\n\ndef this_line_is_useless(line):\n    useless_es = [\n        \'BEGIN TRANSACTION\',\n        \'COMMIT\',\n        \'sqlite_sequence\',\n        \'CREATE UNIQUE INDEX\',\n        \'PRAGMA foreign_keys=OFF\',\n    ]\n    for useless in useless_es:\n        if re.search(useless, line):\n            return True\n\ndef has_primary_key(line):\n    return bool(re.search(r\'PRIMARY KEY\', line))\n\nsearching_for_end = False\nfor line in fileinput.input():\n    if this_line_is_useless(line):\n        continue\n\n    # this line was necessary because \'\');\n    # would be converted to \\\'); which isn\'t appropriate\n    if re.match(r".*, \'\'\\);", line):\n        line = re.sub(r"\'\'\\);", r\'``);\', line)\n\n    if re.match(r\'^CREATE TABLE.*\', line):\n        searching_for_end = True\n\n    m = re.search(\'CREATE TABLE "?(\\w*)"?(.*)\', line)\n    if m:\n        name, sub = m.groups()\n        line = "DROP TABLE IF EXISTS %(name)s;\\nCREATE TABLE IF NOT EXISTS `%(name)s`%(sub)s\\n"\n        line = line % dict(name=name, sub=sub)\n    else:\n        m = re.search(\'INSERT INTO "(\\w*)"(.*)\', line)\n        if m:\n            line = \'INSERT INTO %s%s\\n\' % m.groups()\n            line = line.replace(\'"\', r\'\\"\')\n            line = line.replace(\'"\', "\'")\n    line = re.sub(r"([^\'])\'t\'(.)", "\\1THIS_IS_TRUE\\2", line)\n    line = line.replace(\'THIS_IS_TRUE\', \'1\')\n    line = re.sub(r"([^\'])\'f\'(.)", "\\1THIS_IS_FALSE\\2", line)\n    line = line.replace(\'THIS_IS_FALSE\', \'0\')\n\n    # Add auto_increment if it is not there since sqlite auto_increments ALL\n    # primary keys\n    if searching_for_end:\n        if re.search(r"integer(?:\\s+\\w+)*\\s*PRIMARY KEY(?:\\s+\\w+)*\\s*,", line):\n            line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")\n        # replace " and \' with ` because mysql doesn\'t like quotes in CREATE commands \n        if line.find(\'DEFAULT\') == -1:\n            line = line.replace(r\'"\', r\'`\').replace(r"\'", r\'`\')\n        else:\n            parts = line.split(\'DEFAULT\')\n            parts[0] = parts[0].replace(r\'"\', r\'`\').replace(r"\'", r\'`\')\n            line = \'DEFAULT\'.join(parts)\n\n    # And now we convert it back (see above)\n    if re.match(r".*, ``\\);", line):\n        line = re.sub(r\'``\\);\', r"\'\');", line)\n\n    if searching_for_end and re.match(r\'.*\\);\', line):\n        searching_for_end = False\n\n    if re.match(r"CREATE INDEX", line):\n        line = re.sub(\'"\', \'`\', line)\n\n    if re.match(r"AUTOINCREMENT", line):\n        line = re.sub("AUTOINCREMENT", "AUTO_INCREMENT", line)\n\n    print line,\n</code></pre>\n'}, {'answer_id': 1498558, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>This script is ok except for this case that of course, I\'ve met :</p>\n\n<pre>\nINSERT INTO "requestcomparison_stopword" VALUES(149,\'f\');\nINSERT INTO "requestcomparison_stopword" VALUES(420,\'t\');\n</pre>\n\n<p>The script should give this output :</p>\n\n<pre>\nINSERT INTO requestcomparison_stopword VALUES(149,\'f\');\nINSERT INTO requestcomparison_stopword VALUES(420,\'t\');\n</pre>\n\n<p>But gives instead that output :</p>\n\n<pre>\nINSERT INTO requestcomparison_stopword VALUES(1490;\nINSERT INTO requestcomparison_stopword VALUES(4201;\n</pre>\n\n<p>with some strange non-ascii characters around the last 0 and 1.</p>\n\n<p>This didn\'t show up anymore when I commented the following lines of the code (43-46) but others problems appeared:</p>\n\n<p><pre><code>\n    line = re.sub(r"([^\'])\'t\'(.)", "\\1THIS_IS_TRUE\\2", line)\n    line = line.replace(\'THIS_IS_TRUE\', \'1\')\n    line = re.sub(r"([^\'])\'f\'(.)", "\\1THIS_IS_FALSE\\2", line)\n    line = line.replace(\'THIS_IS_FALSE\', \'0\')\n</pre></code></p>\n\n<p>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.</p>\n\n<p>Anyway thanks a lot for that handy script !!!</p>\n'}, {'answer_id': 2543241, 'author': 'daicoden', 'author_id': 266456, 'author_profile': 'https://Stackoverflow.com/users/266456', 'pm_score': 1, 'selected': False, 'text': '<p>Ha... I wish I had found this first! My response was to this post... <a href="https://stackoverflow.com/questions/489277/script-to-convert-mysql-dump-sql-file-into-format-that-can-be-imported-into-sqlit">script to convert mysql dump sql file into format that can be imported into sqlite3 db</a></p>\n\n<p>Combining the two would be exactly what I needed:</p>\n\n<hr>\n\n<p>When the sqlite3 database is going to be used with ruby you may want to change:</p>\n\n<pre><code>tinyint([0-9]*) \n</code></pre>\n\n<p>to:</p>\n\n<pre><code>sed \'s/ tinyint(1*) / boolean/g \' |\nsed \'s/ tinyint([0|2-9]*) / integer /g\' |\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>Table.find(:all, :conditions =&gt; {:column =&gt; 1 }).each { |t| t.column = true }.each(&amp;:save)\nTable.find(:all, :conditions =&gt; {:column =&gt; 0 }).each { |t| t.column = false}.each(&amp;:save)\n</code></pre>\n\n<p>but it was helpful to have the sql file to look at to find all the booleans.</p>\n'}, {'answer_id': 6000355, 'author': 'David d C e Freitas', 'author_id': 40961, 'author_profile': 'https://Stackoverflow.com/users/40961', 'pm_score': 7, 'selected': True, 'text': '<p>Here is a list of converters (not updated since 2011):</p>\n\n<ul>\n<li><a href="https://www2.sqlite.org/cvstrac/wiki?p=ConverterTools" rel="noreferrer">https://www2.sqlite.org/cvstrac/wiki?p=ConverterTools</a> (or <a href="https://web.archive.org/web/20160716191418/https://www.sqlite.org/cvstrac/wiki?p=ConverterTools" rel="noreferrer">snapshot at archive.org</a>)</li>\n</ul>\n\n<p><hr/>\nAn 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 (<a href="http://www.redbeanphp.com" rel="noreferrer">RedBean</a>), Python (Django\'s ORM layer, <a href="https://storm.canonical.com/" rel="noreferrer">Storm</a>, <a href="http://www.sqlalchemy.org" rel="noreferrer">SqlAlchemy</a>), Ruby on Rails (<a href="http://ar.rubyonrails.org/" rel="noreferrer">ActiveRecord</a>), Cocoa (<a href="http://developer.apple.com/technologies/mac/data-management.html" rel="noreferrer">CoreData</a>)</p>\n\n<p>i.e. you could do this:</p>\n\n<ol>\n<li>Load data from source database using the ORM class.</li>\n<li>Store data in memory or serialize to disk.</li>\n<li>Store data into destination database using the ORM class.</li>\n</ol>\n'}, {'answer_id': 6410922, 'author': 'alekwisnia', 'author_id': 268662, 'author_profile': 'https://Stackoverflow.com/users/268662', 'pm_score': 2, 'selected': False, 'text': '<p>Based on Jims\'s solution:\n<a href="https://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql/1067365#1067365">Quick easy way to migrate SQLite3 to MySQL?</a></p>\n\n<pre><code>sqlite3 your_sql3_database.db .dump | python ./dump.py &gt; your_dump_name.sql\ncat your_dump_name.sql | sed \'1d\' | mysql --user=your_mysql_user --default-character-set=utf8 your_mysql_db -p  \n</code></pre>\n\n<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.</p>\n'}, {'answer_id': 7747146, 'author': 'Dashamir Hoxha', 'author_id': 992353, 'author_profile': 'https://Stackoverflow.com/users/992353', 'pm_score': 3, 'selected': False, 'text': '<ul>\n<li><a href="http://sqlfairy.sourceforge.net/" rel="noreferrer">http://sqlfairy.sourceforge.net/</a></li>\n<li><a href="http://search.cpan.org/dist/SQL-Translator/" rel="noreferrer">http://search.cpan.org/dist/SQL-Translator/</a></li>\n</ul>\n\n<pre class="lang-bsh prettyprint-override"><code>aptitude install sqlfairy libdbd-sqlite3-perl\n\nsqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t MySQL --add-drop-table &gt; mysql-ten-sq.sql\nsqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t Dumper --use-same-auth &gt; sqlite2mysql-dumper.pl\nchmod +x sqlite2mysql-dumper.pl\n./sqlite2mysql-dumper.pl --help\n./sqlite2mysql-dumper.pl --add-truncate --mysql-loadfile &gt; mysql-dump.sql\nsed -e \'s/LOAD DATA INFILE/LOAD DATA LOCAL INFILE/\' -i mysql-dump.sql\n\necho \'drop database `ten-sq`\' | mysql -p -u root\necho \'create database `ten-sq` charset utf8\' | mysql -p -u root\nmysql -p -u root -D ten-sq &lt; mysql-ten-sq.sql\nmysql -p -u root -D ten-sq &lt; mysql-dump.sql\n</code></pre>\n'}, {'answer_id': 10067099, 'author': 'mgribov', 'author_id': 1320864, 'author_profile': 'https://Stackoverflow.com/users/1320864', 'pm_score': -1, 'selected': False, 'text': '<pre><code>echo ".dump" | sqlite3 /tmp/db.sqlite &gt; db.sql\n</code></pre>\n\n<p>watch out for CREATE statements</p>\n'}, {'answer_id': 13365275, 'author': 'Snips', 'author_id': 451544, 'author_profile': 'https://Stackoverflow.com/users/451544', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>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 <a href="https://stackoverflow.com/questions/13363473/re-sub-not-replacing-all-occurrences/13363620#13363620">here</a> why that was the case.</p>\n\n<p>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.</p>\n\n<p>I\'ll post the script below.  But firstly, here\'s the instructions for a conversion...</p>\n\n<p>I ran the script on OS X 10.7.5 Lion.  Python worked out of the box.</p>\n\n<p>To generate the MySQL input file from your existing SQLite3 database, run the script on your own files as follows,</p>\n\n<pre><code>Snips$ sqlite3 original_database.sqlite3 .dump | python ~/scripts/dump_for_mysql.py &gt; dumped_data.sql\n</code></pre>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>The resulting command to input the data into a spanking new empty MySQL database is as follows:</p>\n\n<pre><code>Snips$ mysql -p -u root -h 127.0.0.1 test_import --default-character-set=utf8 &lt; dumped_data.sql\n</code></pre>\n\n<p>Let it cook, and that should be it!  Don\'t forget to scrutinise your data, before and after.</p>\n\n<p>So, as the OP requested, it\'s quick and easy, when you know how! :-)</p>\n\n<p>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.</p>\n\n<p>Good luck!</p>\n\n<p><em>UPDATE</em></p>\n\n<p>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.</p>\n\n<hr>\n\n<p>Here\'s the merged and revised Python script that worked for my data:</p>\n\n<pre><code>#!/usr/bin/env python\n\nimport re\nimport fileinput\n\ndef this_line_is_useless(line):\n    useless_es = [\n        \'BEGIN TRANSACTION\',\n        \'COMMIT\',\n        \'sqlite_sequence\',\n        \'CREATE UNIQUE INDEX\',        \n        \'PRAGMA foreign_keys=OFF\'\n        ]\n    for useless in useless_es:\n        if re.search(useless, line):\n            return True\n\ndef has_primary_key(line):\n    return bool(re.search(r\'PRIMARY KEY\', line))\n\nsearching_for_end = False\nfor line in fileinput.input():\n    if this_line_is_useless(line): continue\n\n    # this line was necessary because \'\'); was getting\n    # converted (inappropriately) to \\\');\n    if re.match(r".*, \'\'\\);", line):\n        line = re.sub(r"\'\'\\);", r\'``);\', line)\n\n    if re.match(r\'^CREATE TABLE.*\', line):\n        searching_for_end = True\n\n    m = re.search(\'CREATE TABLE "?([A-Za-z_]*)"?(.*)\', line)\n    if m:\n        name, sub = m.groups()\n        line = "DROP TABLE IF EXISTS %(name)s;\\nCREATE TABLE IF NOT EXISTS `%(name)s`%(sub)s\\n"\n        line = line % dict(name=name, sub=sub)\n        line = line.replace(\'AUTOINCREMENT\',\'AUTO_INCREMENT\')\n        line = line.replace(\'UNIQUE\',\'\')\n        line = line.replace(\'"\',\'\')\n    else:\n        m = re.search(\'INSERT INTO "([A-Za-z_]*)"(.*)\', line)\n        if m:\n            line = \'INSERT INTO %s%s\\n\' % m.groups()\n            line = line.replace(\'"\', r\'\\"\')\n            line = line.replace(\'"\', "\'")\n            line = re.sub(r"(?&lt;!\')\'t\'(?=.)", r"1", line)\n            line = re.sub(r"(?&lt;!\')\'f\'(?=.)", r"0", line)\n\n    # Add auto_increment if it\'s not there since sqlite auto_increments ALL\n    # primary keys\n    if searching_for_end:\n        if re.search(r"integer(?:\\s+\\w+)*\\s*PRIMARY KEY(?:\\s+\\w+)*\\s*,", line):\n            line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")\n        # replace " and \' with ` because mysql doesn\'t like quotes in CREATE commands\n\n    # And now we convert it back (see above)\n    if re.match(r".*, ``\\);", line):\n        line = re.sub(r\'``\\);\', r"\'\');", line)\n\n    if searching_for_end and re.match(r\'.*\\);\', line):\n        searching_for_end = False\n\n    if re.match(r"CREATE INDEX", line):\n        line = re.sub(\'"\', \'`\', line)\n\n    print line,\n</code></pre>\n'}, {'answer_id': 17009384, 'author': 'Martin Thoma', 'author_id': 562769, 'author_profile': 'https://Stackoverflow.com/users/562769', 'pm_score': 3, 'selected': False, 'text': '<h1>Get a SQL dump</h1>\n\n<pre><code>moose@pc08$ sqlite3 mySqliteDatabase.db .dump &gt; myTemporarySQLFile.sql\n</code></pre>\n\n<h1>Import dump to MySQL</h1>\n\n<h2>For small imports:</h2>\n\n<pre><code>moose@pc08$ mysql -u &lt;username&gt; -p\nEnter password:\n....\nmysql&gt; use somedb;\nDatabase changed\nmysql&gt; source myTemporarySQLFile.sql;\n</code></pre>\n\n<p>or</p>\n\n<pre><code>mysql -u root -p somedb &lt; myTemporarySQLFile.sql\n</code></pre>\n\n<p>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 <code>-p</code>:</p>\n\n<pre><code>mysql -u root -pYOURPASS somedb &lt; myTemporarySQLFile.sql\n</code></pre>\n\n<h2>For larger dumps:</h2>\n\n<p>mysqlimport or other import tools like <a href="http://www.ozerov.de/bigdump/" rel="noreferrer">BigDump</a>.</p>\n\n<p>BigDump gives you a progress bar:</p>\n\n<p><img src="https://i.stack.imgur.com/TuDhM.png" alt="enter image description here"></p>\n'}, {'answer_id': 32243979, 'author': 'Klemen Tusar', 'author_id': 1040452, 'author_profile': 'https://Stackoverflow.com/users/1040452', 'pm_score': 3, 'selected': False, 'text': '<p>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 <code>int(11)</code>and strings as <code>varchar(300)</code>, but all that can be adjusted in the constructor or script arguments respectively.</p>\n\n<p><strong>NOTE:</strong> It requires MySQL Connector/Python 2.0.4 or higher</p>\n\n<p>Here\'s a link to the source on GitHub if you find the code below hard to read: <a href="https://github.com/techouse/sqlite3-to-mysql" rel="noreferrer">https://github.com/techouse/sqlite3-to-mysql</a></p>\n\n<pre class="lang-py prettyprint-override"><code>#!/usr/bin/env python3\n\n__author__ = "Klemen Tušar"\n__email__ = "techouse@gmail.com"\n__copyright__ = "GPL"\n__version__ = "1.0.1"\n__date__ = "2015-09-12"\n__status__ = "Production"\n\nimport os.path, sqlite3, mysql.connector\nfrom mysql.connector import errorcode\n\n\nclass SQLite3toMySQL:\n    """\n    Use this class to transfer an SQLite 3 database to MySQL.\n\n    NOTE: Requires MySQL Connector/Python 2.0.4 or higher (https://dev.mysql.com/downloads/connector/python/)\n    """\n    def __init__(self, **kwargs):\n        self._properties = kwargs\n        self._sqlite_file = self._properties.get(\'sqlite_file\', None)\n        if not os.path.isfile(self._sqlite_file):\n            print(\'SQLite file does not exist!\')\n            exit(1)\n        self._mysql_user = self._properties.get(\'mysql_user\', None)\n        if self._mysql_user is None:\n            print(\'Please provide a MySQL user!\')\n            exit(1)\n        self._mysql_password = self._properties.get(\'mysql_password\', None)\n        if self._mysql_password is None:\n            print(\'Please provide a MySQL password\')\n            exit(1)\n        self._mysql_database = self._properties.get(\'mysql_database\', \'transfer\')\n        self._mysql_host = self._properties.get(\'mysql_host\', \'localhost\')\n\n        self._mysql_integer_type = self._properties.get(\'mysql_integer_type\', \'int(11)\')\n        self._mysql_string_type = self._properties.get(\'mysql_string_type\', \'varchar(300)\')\n\n        self._sqlite = sqlite3.connect(self._sqlite_file)\n        self._sqlite.row_factory = sqlite3.Row\n        self._sqlite_cur = self._sqlite.cursor()\n\n        self._mysql = mysql.connector.connect(\n            user=self._mysql_user,\n            password=self._mysql_password,\n            host=self._mysql_host\n        )\n        self._mysql_cur = self._mysql.cursor(prepared=True)\n        try:\n            self._mysql.database = self._mysql_database\n        except mysql.connector.Error as err:\n            if err.errno == errorcode.ER_BAD_DB_ERROR:\n                self._create_database()\n            else:\n                print(err)\n                exit(1)\n\n    def _create_database(self):\n        try:\n            self._mysql_cur.execute("CREATE DATABASE IF NOT EXISTS `{}` DEFAULT CHARACTER SET \'utf8\'".format(self._mysql_database))\n            self._mysql_cur.close()\n            self._mysql.commit()\n            self._mysql.database = self._mysql_database\n            self._mysql_cur = self._mysql.cursor(prepared=True)\n        except mysql.connector.Error as err:\n            print(\'_create_database failed creating databse {}: {}\'.format(self._mysql_database, err))\n            exit(1)\n\n    def _create_table(self, table_name):\n        primary_key = \'\'\n        sql = \'CREATE TABLE IF NOT EXISTS `{}` ( \'.format(table_name)\n        self._sqlite_cur.execute(\'PRAGMA table_info("{}")\'.format(table_name))\n        for row in self._sqlite_cur.fetchall():\n            column = dict(row)\n            sql += \' `{name}` {type} {notnull} {auto_increment}, \'.format(\n                name=column[\'name\'],\n                type=self._mysql_string_type if column[\'type\'].upper() == \'TEXT\' else self._mysql_integer_type,\n                notnull=\'NOT NULL\' if column[\'notnull\'] else \'NULL\',\n                auto_increment=\'AUTO_INCREMENT\' if column[\'pk\'] else \'\'\n            )\n            if column[\'pk\']:\n                primary_key = column[\'name\']\n        sql += \' PRIMARY KEY (`{}`) ) ENGINE = InnoDB CHARACTER SET utf8\'.format(primary_key)\n        try:\n            self._mysql_cur.execute(sql)\n            self._mysql.commit()\n        except mysql.connector.Error as err:\n            print(\'_create_table failed creating table {}: {}\'.format(table_name, err))\n            exit(1)\n\n    def transfer(self):\n        self._sqlite_cur.execute("SELECT name FROM sqlite_master WHERE type=\'table\' AND name NOT LIKE \'sqlite_%\'")\n        for row in self._sqlite_cur.fetchall():\n            table = dict(row)\n            # create the table\n            self._create_table(table[\'name\'])\n            # populate it\n            print(\'Transferring table {}\'.format(table[\'name\']))\n            self._sqlite_cur.execute(\'SELECT * FROM "{}"\'.format(table[\'name\']))\n            columns = [column[0] for column in self._sqlite_cur.description]\n            try:\n                self._mysql_cur.executemany("INSERT IGNORE INTO `{table}` ({fields}) VALUES ({placeholders})".format(\n                    table=table[\'name\'],\n                    fields=(\'`{}`, \' * len(columns)).rstrip(\' ,\').format(*columns),\n                    placeholders=(\'%s, \' * len(columns)).rstrip(\' ,\')\n                ), (tuple(data) for data in self._sqlite_cur.fetchall()))\n                self._mysql.commit()\n            except mysql.connector.Error as err:\n                print(\'_insert_table_data failed inserting data into table {}: {}\'.format(table[\'name\'], err))\n                exit(1)\n        print(\'Done!\')\n\n\ndef main():\n    """ For use in standalone terminal form """\n    import sys, argparse\n    parser = argparse.ArgumentParser()\n    parser.add_argument(\'--sqlite-file\', dest=\'sqlite_file\', default=None, help=\'SQLite3 db file\')\n    parser.add_argument(\'--mysql-user\', dest=\'mysql_user\', default=None, help=\'MySQL user\')\n    parser.add_argument(\'--mysql-password\', dest=\'mysql_password\', default=None, help=\'MySQL password\')\n    parser.add_argument(\'--mysql-database\', dest=\'mysql_database\', default=None, help=\'MySQL host\')\n    parser.add_argument(\'--mysql-host\', dest=\'mysql_host\', default=\'localhost\', help=\'MySQL host\')\n    parser.add_argument(\'--mysql-integer-type\', dest=\'mysql_integer_type\', default=\'int(11)\', help=\'MySQL default integer field type\')\n    parser.add_argument(\'--mysql-string-type\', dest=\'mysql_string_type\', default=\'varchar(300)\', help=\'MySQL default string field type\')\n    args = parser.parse_args()\n\n    if len(sys.argv) == 1:\n        parser.print_help()\n        exit(1)\n\n    converter = SQLite3toMySQL(\n        sqlite_file=args.sqlite_file,\n        mysql_user=args.mysql_user,\n        mysql_password=args.mysql_password,\n        mysql_database=args.mysql_database,\n        mysql_host=args.mysql_host,\n        mysql_integer_type=args.mysql_integer_type,\n        mysql_string_type=args.mysql_string_type\n    )\n    converter.transfer()\n\nif __name__ == \'__main__\':\n    main()\n</code></pre>\n'}, {'answer_id': 33796909, 'author': 'soulseekah', 'author_id': 482864, 'author_profile': 'https://Stackoverflow.com/users/482864', 'pm_score': 0, 'selected': False, 'text': '<p>This simple solution worked for me:</p>\n\n<pre><code>&lt;?php\n$sq = new SQLite3( \'sqlite3.db\' );\n\n$tables = $sq-&gt;query( \'SELECT name FROM sqlite_master WHERE type="table"\' );\n\nwhile ( $table = $tables-&gt;fetchArray() ) {\n    $table = current( $table );\n    $result = $sq-&gt;query( sprintf( \'SELECT * FROM %s\', $table ) );\n\n    if ( strpos( $table, \'sqlite\' ) !== false )\n        continue;\n\n    printf( "-- %s\\n", $table );\n    while ( $row = $result-&gt;fetchArray( SQLITE3_ASSOC ) ) {\n        $values = array_map( function( $value ) {\n            return sprintf( "\'%s\'", mysql_real_escape_string( $value ) );\n        }, array_values( $row ) );\n        printf( "INSERT INTO `%s` VALUES( %s );\\n", $table, implode( \', \', $values ) );\n    }\n}\n</code></pre>\n'}, {'answer_id': 38414534, 'author': 'NavidIvanian', 'author_id': 5162480, 'author_profile': 'https://Stackoverflow.com/users/5162480', 'pm_score': 2, 'selected': False, 'text': '<p>There is no need to any script,command,etc... </p>\n\n<p>you have to only export your sqlite database as a <code>.csv</code> file and then import it in Mysql using phpmyadmin.</p>\n\n<p>I used it and it worked amazing... </p>\n'}, {'answer_id': 45062019, 'author': 'Mihkorz', 'author_id': 1908693, 'author_profile': 'https://Stackoverflow.com/users/1908693', 'pm_score': 4, 'selected': False, 'text': '<p>If you are using Python/Django it\'s pretty easy:</p>\n\n<p>create two databases in settings.py (like here <a href="https://docs.djangoproject.com/en/1.11/topics/db/multi-db/" rel="noreferrer">https://docs.djangoproject.com/en/1.11/topics/db/multi-db/</a>)</p>\n\n<p>then just do like this:</p>\n\n<pre><code>objlist = ModelObject.objects.using(\'sqlite\').all()\n\nfor obj in objlist:\n    obj.save(using=\'mysql\')\n</code></pre>\n'}, {'answer_id': 52840128, 'author': 'Vincent Sit', 'author_id': 1644934, 'author_profile': 'https://Stackoverflow.com/users/1644934', 'pm_score': 5, 'selected': False, 'text': '<p>I usually use the <a href="https://blog.jetbrains.com/datagrip/2017/03/22/datagrip-2017-1-released/#import" rel="noreferrer">Export/import tables</a> feature of <a href="https://www.jetbrains.com/datagrip/" rel="noreferrer">IntelliJ DataGrip</a>.</p>\n\n<p><img src="https://i.stack.imgur.com/XT3f8.png" alt="step 1">\n<img src="https://i.stack.imgur.com/eikBz.png" alt="step 2">\n<img src="https://i.stack.imgur.com/NbKoZ.png" alt="step 3"></p>\n\n<p>You can see the progress in the bottom right corner.</p>\n\n<p>[<img src="https://i.stack.imgur.com/WDAio.png" alt="enter image description here">]</p>\n'}]
['https://Stackoverflow.com/questions/18671', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/534/']