Importing sqlite3 to MySQL in a semi non painful way.

The example I will give below should work for most data. I needed to import dashboards for graphite in sqlite3 format to MySQL which is our now standard backend. This is the rough steps I used.

Get that file from /opt/graphite/storage/graphite.db then dump it like it is 1982:

sqlite3 graphite.db
sqlite> .output graphite.sql
sqlite> .dump dashboard_dashboard

Grab that wonderful python script. I am copy/pasting it here just in case in disappears.

#! /usr/bin/env python

import sys

def main():
    print "SET sql_mode='NO_BACKSLASH_ESCAPES';"
    lines =
    for line in lines:

def processLine(line):
    if (
        line.startswith("PRAGMA") or
        line.startswith("BEGIN TRANSACTION;") or
        line.startswith("COMMIT;") or
        line.startswith("DELETE FROM sqlite_sequence;") or
        line.startswith("INSERT INTO \"sqlite_sequence\"")
    line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")
    line = line.replace("DEFAULT 't'", "DEFAULT '1'")
    line = line.replace("DEFAULT 'f'", "DEFAULT '0'")
    line = line.replace(",'t'", ",'1'")
    line = line.replace(",'f'", ",'0'")
    in_string = False
    newLine = ''
    for c in line:
        if not in_string:
            if c == "'":
                in_string = True
            elif c == '"':
                newLine = newLine + '`'
        elif c == "'":
            in_string = False
        newLine = newLine + c
    print newLine

if __name__ == "__main__":

Rename it something like then execute the following:

cat graphite.sql | python > graphite-mysql.sql

I would advise you add a few statements at the beginning and the end like:

USE graphite; 

All you need to do now is to dump it back to MySQL like this:

mysql < graphite-mysql.sql

You should now have a full DB of dashboards in MySQL format.