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.
First, connect to the SQLite database by creating a Connection object. Second, create a Cursor object by calling the cursor method of the Connection object. Third, execute an INSERT statement. If you want to pass arguments to the INSERT statement, you use the question mark (?) as the placeholder for each argument.
To create a database, first, you have to create a Connection object that represents the database using the connect() function of the sqlite3 module. For example, the following Python program creates a new database file pythonsqlite.db in the c:sqlitedb folder.
In SQLite, sqlite3 command is used to create a new SQLite database. You do not need to have any special privilege to create a database. Following is the basic syntax of sqlite3 command to create a database: −
You will use SQLite .quit command to come out of the sqlite prompt as follows − You can use .dump dot command to export complete database in a text file using the following SQLite command at the command prompt.
To create a database, first, you have to create a Connection object that represents the database using the connect () function of the sqlite3 module. For example, the following Python program creates a new database file pythonsqlite.db in the c:sqlitedb folder.
There's a helper method executescript
exactly for that purpose:
with open('myschema.sql') as fp:
cur.executescript(fp.read()) # or con.executescript
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With