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?
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()
You cannot. The program sqlite3 can be seen as splitted in 2 parts:
.read is kind of a meta command: the parser opens the file and read lines from it. AFAIK, nothing in the sqlite3 library can emulate that parser part, so you would have to the line parsing into SQL statements by hand, and then execute the SQL statements one at a time.
Try this. you can read query from file using the 'open' function - this will replace
.read
functionality; SQL scripts are text files with query. and then run read_sql_query.
import sqlite3
import pandas as pd
sqlite_file = 'scheduling.db'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()
f = open('scheduling.sql','r')
sql = f.read()
print pd.read_sql_query(sql, conn)
.readis a command implemented by the sqlite3 shell program, not the sqlite3 library. You can't execute it.avariable like this:a="SELECT * FROM desired-table ORDER BY id DESC LIMIT 1"