Office of Digital Humanities

Exporting a MySQL database for import into SQLite

We can export the structure and data of an existing MySQL database by creating a series of SQL commands in a file and using the target system, in this case SQLite, to run the commands in a text file. Do the following:

1. Open the database in PHPMyAdmin.

2. Click the Export tab

   




3. Export Section (on left) - Select SQL





4. Options Section (on right)

5. SQL compatibility mode: choose ANSI

1. Structure Section

2. Uncheck all but "Add IF NOT EXISTS"

3. (Data Section - If you only want to export the database structure, uncheck this. If you want to export both the structure and the data, check this.)




6. Click Go. A file called databasename.sql will be created and downloaded to your Downloads folder.

7. Because there are slight differences in the CREATE TABLE command between MySQL and SQLite, we'll have to fix the commands somewhat:

8. Open the file and make the following find and replace substitutions:

1. Replace the phrase "int(N) NOT NULL auto_increment" with "primary key autoincrement"

2. For each table, delete the line that reads "PRIMARY KEY (column name here), including the preceding comma.

   Compare the following sample SQL statements:

Here is a CREATE TABLE statement as exported from MySQL.

This modified CREATE TABLE statement will work successfully in SQLite.

9. Open a terminal window.

10. At prompt, type:

11. sqlite3 /path/to/database/databasename.sqlite

For example:

sqlite3 /Users/devin/Desktop/acmeco.sqlite

12. This will open the sqlite command line program and simultaneously open the database file. If the database file doesn't exist, it will create it.

13. At prompt, type:

    .read /path/to/database/exportfilename.sql

14. Note errors, if any, and modify source .sql file to eliminate errors.

15. When done with sqlite, type Control+D to exit.

This procedure will create the database structure. You would then insert the data exactly as we did earlier with the MySQL databases.