How to Create a SQLite3 Database File Using a SQL Command File

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

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.

Create sqlite3 database from .sql file

Your exports is taken from a MySQL database hence the MyISAM. This will work for you

CREATE TABLE vpVideo (
  ID int(11) NOT NULL,
  userID int(11) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  createDate bigint(20) DEFAULT NULL,
  vidExists varchar(1) DEFAULT 'N',
  PRIMARY KEY (ID)
);

AUTOINCREMENT is done automatically with the definition of your primary key

Imports between different databases only work if it is done with the SQL-92 standard. All vendors of databases extended that standard, so that SQL-92 is only a subset of the entire sqlite syntax (as it is for others like oracle, db2, mysql)

Creating sqlite3 database from .sql file in Windows 8

Just keep following the tutorial by adding the init_db method and running the following python script:

# all the imports
import sqlite3
from flask import Flask
from contextlib import closing

# configuration
DATABASE = './flaskr.db'
DEBUG = True

# create our little application :)
app = Flask(__name__)
app.config.from_object(__name__)

def init_db():
    with closing(connect_db()) as db:
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()

def connect_db():
    return sqlite3.connect(app.config['DATABASE'])

if __name__ == '__main__':
    init_db()
    #app.run()

to make it simple, database file flaskr.db will be created in the current directory and schema.sql is supposed to be there too ...

How do I import .sql files into SQLite 3?

From a sqlite prompt:

sqlite> .read db.sql

Or:

cat db.sql | sqlite3 database.db

Also, your SQL is invalid - you need ; on the end of your statements:

create table server(name varchar(50),ipaddress varchar(15),id init);
create table client(name varchar(50),ipaddress varchar(15),id init);

How do I execute sqlite3 file into a database in mac terminal?

The command

sqlite3 data.db < c291.sql

must be run from the OS shell, i.e., from the $ prompt.

The commands

.open data.db
.read c291.sql

must be run from within the SQLite shell, i.e., from the sqlite> prompt.

Create empty sqlite db from command line

I don't think there is a way to do that in just one statement.

In Linux I would workaround it this way:

sqlite3 aFile.db "create table aTable(field1 int); drop table aTable;"

This will automatically create the needed file with the table in it and then drop it leaving the database file without tables. That's the closest thing I know.

Anyway, I think most editors will even accept an empty file too. Give that a try.

Execute SQLite script

There are many ways to do this, one way is:

sqlite3 auction.db

Followed by:

sqlite> .read create.sql

In general, the SQLite project has really fantastic documentation! I know we often reach for Google before the docs, but in SQLite's case, the docs really are technical writing at its best. It's clean, clear, and concise.

---

Related Topics