Sqlite3 with Python - Query from external file

Question

I would like to create my DB by an external file like:

database = "../data/cm4payroll.db"
query = "../data/emdb.sql"
# Datenbankverbindung herstellen
self.connection = sqlite3.connect(self.database)
self.cursor = self.connection.cursor()
# Datenbank erstellen
self.cursor.execute(self.query)

Traceback:

    self.cursor.execute(self.query)
sqlite3.OperationalError: near ".": syntax error

Answer 1

You need to read the file contents, and pass it to cursor.executescript() instead:

self.connection = sqlite3.connect(self.database)
self.cursor = self.connection.cursor()
with open(self.query) as queryfile:
    self.cursor.executescript(queryfile.read())

Your error shows you were trying to execute the filename as a SQL statement; cursor.execute() can only handle actual SQL strings, not filenames.