CODEWITHSUNDEEP
sqldatabasesqlite
user1639431
user1639431

Reputation: 3833

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

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?

Upvotes: 32

Views: 69878

Answers (2)

ravdhaw
ravdhaw

Reputation: 46

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"

Upvotes: 1

mu is too short
mu is too short

Reputation: 434585

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.

Upvotes: 59

Related Questions