Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 224
Francisco Vargas Avatar asked Sep 03 '15 09:09

Francisco Vargas


People also ask

How do I populate a SQLite database in Python?

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.

How do I create a SQLite database in Python?

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.

What is SQLite3 command in SQLite?

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: −

How to export complete database in a text file using SQLite?

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.

How do I create a SQL database in Python?

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.


1 Answers

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

with open('myschema.sql') as fp:
    cur.executescript(fp.read())  # or con.executescript 
like image 114
bereal Avatar answered Nov 15 '22 00:11

bereal