Convert mysql to sqlite

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Convert mysql to sqlite

Scott Doctor
I have a database with a few tables and about 140MB of data in
it that exists as a MySQL database. Using MySQL workbench I can
export the data, but it dumps as a SQL dump with its flavor of
sql. I want to convert this database into a sqlite database.
Nothing fancy in it. No triggers, procedures, or foreign keys.
Tables are normal structure with a pk field and some text and
integer fields in each table.

Anyone know of a utility to directly convert from MySQL to sqlite?


--

-------------------------
Scott Doctor
[hidden email]
-------------------------

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convert mysql to sqlite

Eduardo Morras-2
On Sat, 10 Sep 2016 14:24:48 -0700
Scott Doctor <[hidden email]> wrote:

> I have a database with a few tables and about 140MB of data in
> it that exists as a MySQL database. Using MySQL workbench I can
> export the data, but it dumps as a SQL dump with its flavor of
> sql. I want to convert this database into a sqlite database.
> Nothing fancy in it. No triggers, procedures, or foreign keys.
> Tables are normal structure with a pk field and some text and
> integer fields in each table.
>
> Anyone know of a utility to directly convert from MySQL to sqlite?
>

You can export as csv and import them in sqlite.

> -------------------------
> Scott Doctor
> [hidden email]
> -------------------------

---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convert mysql to sqlite

Simon Slavin-3
In reply to this post by Scott Doctor

On 10 Sep 2016, at 10:24pm, Scott Doctor <[hidden email]> wrote:

> Anyone know of a utility to directly convert from MySQL to sqlite?

What syntax does the SQL dump use that the SQLite command-line shell doesn't like ?

I generally do this stuff using find/replace in a text processor (TextEdit on the Mac, NOTEPAD on Windows).


Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convert mysql to sqlite

Chris Locke
In reply to this post by Scott Doctor
What OS are you using?  There is a freeware utility here for Windows:
http://sqlite2009pro.azurewebsites.net/


Thanks,
Chris

On Sat, Sep 10, 2016 at 10:24 PM, Scott Doctor <[hidden email]>
wrote:

> I have a database with a few tables and about 140MB of data in it that
> exists as a MySQL database. Using MySQL workbench I can export the data,
> but it dumps as a SQL dump with its flavor of sql. I want to convert this
> database into a sqlite database. Nothing fancy in it. No triggers,
> procedures, or foreign keys. Tables are normal structure with a pk field
> and some text and integer fields in each table.
>
> Anyone know of a utility to directly convert from MySQL to sqlite?
>
>
> --
>
> -------------------------
> Scott Doctor
> [hidden email]
> -------------------------
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convert mysql to sqlite

Scott Doctor
In reply to this post by Simon Slavin-3
The database evolved over the past ten years with many
modifications over time. I decided to just do it the hard way.
Created the schema from scratch in sqlite, exported each table
as csv from mysql (22 of them). Mysql workbench only exports to
csv at the table level, not at the database level. Then I
imported the csv into the new sqlite database. Everything seems
to have transferred properly that way and all the garbage from
years gone by is scrubbed.

-------------------------
Scott Doctor
[hidden email]
-------------------------

On 9/10/2016 14:39, Simon Slavin wrote:

> On 10 Sep 2016, at 10:24pm, Scott Doctor <[hidden email]> wrote:
>
>> Anyone know of a utility to directly convert from MySQL to sqlite?
> What syntax does the SQL dump use that the SQLite command-line shell doesn't like ?
>
> I generally do this stuff using find/replace in a text processor (TextEdit on the Mac, NOTEPAD on Windows).
>
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convert MySQL to sqlite

RichardR
In reply to this post by Scott Doctor
Funny, I was just doing the same thing.

I have hacked together some Python code to convert the MySQL exports from the Workbench into SQLite format for import.

It's not pretty, and is a work in progress, but you are welcome to it.

        Uncheck everything from the advanced tab and select Dump Structure and Data.  This will not convert triggers and only converts a few functions but is easy enough to manipulate to do what you need.
        https://drive.google.com/open?id=0B_3LcLJUnNA4MVozYWtUaGZFbUk

        Once converted run: SQLITE3 OuputName.sqlite ".log log.txt" ".read InputFromConversion.sql"

Hope it helps.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Scott Doctor
Sent: Saturday, September 10, 2016 4:25 PM
To: [hidden email]
Subject: [sqlite] Convert mysql to sqlite

I have a database with a few tables and about 140MB of data in it that exists as a MySQL database. Using MySQL workbench I can export the data, but it dumps as a SQL dump with its flavor of sql. I want to convert this database into a sqlite database.
Nothing fancy in it. No triggers, procedures, or foreign keys.
Tables are normal structure with a pk field and some text and integer fields in each table.

Anyone know of a utility to directly convert from MySQL to sqlite?


--

-------------------------
Scott Doctor
[hidden email]
-------------------------

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users