How do I execute an SQLite script from within python?

Question

My SQLite script works fine, when I type:

.read 'dummy.sql'

from within the SQLite shell.

However, the following Python code is not doing it properly. I'm getting a syntax error in line 5.

import sqlite3
db = sqlite3.connect('scheduling.db')
cursor=db.cursor()
a='''.read "scheduling.sql"'''
cursor.execute(a)
db.commit
db.close()

I know I'm doing something wrong with the quotes. How do I make this work?

Answer 1

The workaround I would recommend is to read the contents of the .sql file into a Python string variable, as you would read any other text file, and then call executescript. Unlike execute, executescript can execute many statements in one call. For example, it will work correctly if your .sql contains the following:

CREATE TABLE contacts (
 contact_id INTEGER PRIMARY KEY,
 first_name TEXT NOT NULL,
 last_name TEXT NOT NULL
);

INSERT INTO contacts (contact_id, first_name, last_name)
VALUES (1, 'John', 'Smith');

Here's the full Python snippet that you'll need:

import sqlite3

with open('scheduling.sql', 'r') as sql_file:
    sql_script = sql_file.read()

db = sqlite3.connect('scheduling.db')
cursor = db.cursor()
cursor.executescript(sql_script)
db.commit()
db.close()