How can I create a SQLite3 database file using a SQL command file?

Question

I have a file which contains some SQL commands, something that looks like this:

CREATE DATABASE IF NOT EXISTS `db_name`;
USE `db_name`;

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(10) unsigned NOT NULL,
  `f_name` varchar(50) DEFAULT NULL,
  `l_name` varchar(50) DEFAULT NULL,
  `company_name` varchar(200) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `customers` (`id`, `f_name`, `l_name`, `company_name`, `email`, `phone`) VALUES
    ...
    ...
    ...

I'd like to use these commands to create an SQLite3 database file in order to use it easily with Python.

How do I do that on Ubuntu?

mu is too short · Accepted Answer · 2012-12-22 19:29:26Z

60

That isn't quite an SQL file, that contains a bunch of MySQL-specific stuff some of which SQLite will accept and some it won't. We'll start at the top.

You don't need create database or use with SQLite. If you want to create a database just name it when you run sqlite3 from the command line:

$ sqlite3 db_name.sqlt < your_sql.sql

If db_name.sqlt exists then it will be used, if it doesn't exist then it will be created. So create database and use are implied by how you run sqlite3. You might need to use a different extension depending on what Python wants to see.

The backticks for quoting are a MySQLism, double quotes are the standard quoting mechanism for identifiers. Lucky for you, SQLite will accept them so you can leave them alone.

SQLite won't know what int(10) unsigned means, you'll have to remove the unsigned before SQLite will accept it. SQLite also won't know what ENGINE=InnoDB DEFAULT CHARSET=utf8 means so you'll have to remove that as well.

You'll probably run into other things that MySQL is happy with but SQLite is not. You'll have to try it and fix it and try it and fix it until it works. Or try to find a tool that can translate between databases for you, I always do these sorts of things by hand or using one-off scripts so I don't know of any tools that can help you.

answered Dec 22, 2012 at 19:29

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1639431 Over a year ago

Followed your instructions, getting the following: Error: near line 1: near "INSERT": syntax error. Funny thing, there is no "INSERT" near line 1...

user1639431 Over a year ago

And say I want to use MySQL instead of Sqlite3. How do I execute these commands? @mu-is-too-short

mu is too short Over a year ago

What is the whole error message? Just because it complains about line one doesn't mean that it is line one of the file. If you wanted to use MySQL you'd feed that stuff to mysql.

user1639431 Over a year ago

I'm running this command: $ cat commands.sql | sqlite3 db_name.db this is after applying the changes that you mentioned. The error message is exactly what I'm getting, no more, no less. @mu-is-too-short

mu is too short Over a year ago

I can't help you if I don't know what the INSERT looks like.

|

StayOnTarget · Accepted Answer · 2016-07-20 11:46:51Z

1

Basically above commands are for mysql or other database (most of these have to be tweaked in order to work with sqlite. Sqlite stores database in the form of file. Basically when you start sqlite it will create a file (if not present). You can create or open a database by typing

sqlite3 db

on command line. This statement create or open database named "db"

edited Jul 20, 2016 at 11:46

StayOnTarget

13.4k11 gold badges66 silver badges117 bronze badges

answered Dec 22, 2012 at 19:36

ravdhaw

463 bronze badges

Collectives™ on Stack Overflow

How can I create a SQLite3 database file using a SQL command file?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related