Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 773
user1639431 Avatar asked Dec 22 '12 19:12

user1639431


People also ask

How do I start SQLite from command line?

Start the sqlite3 program by typing "sqlite3" at the command prompt, optionally followed by the name the file that holds the SQLite database (or ZIP archive). If the named file does not exist, a new database file with the given name will be created automatically.

How do I create a DB file?

Create a blank databaseOn the File tab, click New, and then click Blank Database. Type a file name in the File Name box. To change the location of the file from the default, click Browse for a location to put your database (next to the File Name box), browse to the new location, and then click OK. Click Create.


2 Answers

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.

like image 117
mu is too short Avatar answered Sep 26 '22 09:09

mu is too short


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"

like image 25
ravdhaw Avatar answered Sep 24 '22 09:09

ravdhaw