Run multiline SQL script from file?

Question

I have the following SQL in the 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 save SQL as-is, as the file will be checked in the original control and will be more convenient and readable, as it is now. Can SQL span multiple rows like this, or do I need to put all this on one line?

Answer 1

I had exactly the same problem.

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

Converting eols to Unix style turned the script file into a format that sqlite3 understood.

Answer 2

I understand that this is not a direct answer to your question. As Brian mentions, this can be a dumb platform issue.

If you interact with SQLite through Python, you will probably avoid most of the platform-related issues and you will enjoy 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 3

A few lines are not a problem. There may be a platform issue because I can successfully run this example using SQLite3 3.6.22 on OS X 10.5.8.

Answer 4

Here's an example of bernie python to handle exceptions in a script instead of silent failure (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