How to create a db file in sqlite3 using a schema file from within python

Question

I have a schema file myschema.sql and a database file mydatabase.db using sqlite3 within python (specifically python 2.7) I would like to generate this schema in to my database.

I am aware that via the command line one can do the following

sqlite3 mydatabase.db < myschema.sql

and alternatively I can do the following but this may not work on all systems:

import os
os.popen("sqlite3 mydatabase.db < myschema.sql")

Which is not what I am after, since it may not work on some platforms.

I would also like to avoid something like:

import sqlite3
schema_str = open("myschema.sql","r").read()
connection = sqlite3.connect("mydatabase.db")
cur = connection.cursor()
list_of_insertions = map(lambda x: x.lstrip().replace("\n"," "),
                         schema.split(";"))
map(cur.execute, list_of_insertions)
connection.commit()

So parsing the file and creating the tables seperately is not what I would like either I am just looking for a python-sqlite equivalent of what can be done with the commandline tool.

What the command-line tool does is not much different from the last code. — CL.
– CL., Commented Sep 3, 2015 at 9:56
I see so the best alternative I have to do it in a platform independant sort of manner (and within python itself) is just by parsing and executing the individual create table statements? — Francisco Vargas
– Francisco Vargas, Commented Sep 3, 2015 at 10:04

bereal · Accepted Answer · 2015-09-03 10:11:41Z

11

There's a helper method executescript exactly for that purpose:

with open('myschema.sql') as fp:
    cur.executescript(fp.read())  # or con.executescript

answered Sep 3, 2015 at 10:11

bereal

34.7k8 gold badges65 silver badges111 bronze badges

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

Collectives™ on Stack Overflow

How to create a db file in sqlite3 using a schema file from within python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related