SQLite - Run multi-line SQL script from file?

Question

I have the following SQL in a file, user.sql:

CREATE TABLE user
(
  user_id INTEGER PRIMARY KEY,
  username varchar(255),
  password varchar(255)
);

However, when the following command is executed:

sqlite3 my.db < user.sql 

The following error is generated:

Error: near line 1: near ")": syntax error

I would prefer to keep the SQL as-is, as the file will be checked into source control and will be more maintainable and readable as it is now. Can the SQL span multiple lines like this, or do I need to put it all on the same line?

Answer 1

I realize that this is not a direct answer to your question. As Brian mentions, this could be a silly platform issue.

If you interface with SQLite through Python, you will probably avoid most platform-specific issues and you get to have fun things like datetime columns :-)

Something like this should work fine:

import sqlite3

qry = open('create_table_user.sql', 'r').read()
conn = sqlite3.connect('/path/to/db')
c = conn.cursor()
c.execute(qry)
conn.commit()
c.close()
conn.close()

Answer 2

I had exactly the same problem.

Then I noticed, my editor (Notepad++) reports Macintosh format for end of lines.

Converting eols into Unix style turned the script file into format, which sqlite3 understood.

Answer 3

Multiple lines aren't a problem. There might be a platform issue, because I am able to run this example successfully using SQLite3 3.6.22 on OS X 10.5.8.

Answer 4

Here is bernie's python example upgraded to handle exceptions in the script instead of silently failing (Windows 7, ActiveState Python 3.x)

import sqlite3
import os
import os.path
import ctypes

databaseFile = '.\\SomeDB.db'
sqlFile = '.\\SomeScripts.sql'

# Delete the old table
if os.path.isfile(databaseFile):
    os.remove(databaseFile)

# Create the tables
qry = open(sqlFile, 'r').read()
sqlite3.complete_statement(qry)
conn = sqlite3.connect(databaseFile)
cursor = conn.cursor()
try:
    cursor.executescript(qry)
except Exception as e:
    MessageBoxW = ctypes.windll.user32.MessageBoxW
    errorMessage = databaseFile + ': ' + str(e)
    MessageBoxW(None, errorMessage, 'Error', 0)
    cursor.close()
    raise