Humanities Home page   Office of Digital Humanities
Back     BYU LiveCode Lessons Gateway

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:

    A create table statement as exported from MySQL

    Here is a CREATE TABLE statement as exported from MySQL.

    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

  12. For example:

    sqlite3 /Users/devin/Desktop/acmeco.sqlite

  13. 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.

  14. At prompt, type:

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

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

  16. 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.

Back     BYU LiveCode Lessons Gateway
Maintained by Devin Asay.
Copyright © 2005 Brigham Young University.
This page last updated on February 14, 2014 16:02:03.