I start up sqlite3 version 3.7.7, unix 11.4.2 using this command:
sqlite3 auction.db
where auction.db has not already been created.
sqlite> auction.db < create.sql;
gives me this error: near "auction": syntax error
How can I run the script?
Add a comment
|
6 Answers
You want to feed the create.sql into sqlite3 from the shell, not from inside SQLite itself:
$ sqlite3 auction.db < create.sql
SQLite's version of SQL doesn't understand < for files, your shell does.
4 Comments
Alan
Unfortunately, not all shells understand
< as input redirection. (E.g., PowerShell.)
mu is too short
@Alan PowerShell has some input redirection mechanism, no? And if not there's always bitops's approach.
Chris Becke
@muistooshort see my comment to bitops. The sqlite dot commands work in the sql statement parameter so you can .read the file you want without resorting to input redirection.
TWiStErRob
@ChrisBecke they are different solutions.
< will exit the SQLite prompt immediately and return the error code to the shell. .read file.sql will leave the prompt up and -init file.sql will always return 0, so < is the best for scripting. Also it's cross-platform unlike .read which doesn't support Windows paths.There are many ways to do this, one way is:
sqlite3 auction.db
Followed by:
sqlite> .read create.sql
In general, the SQLite project has really fantastic documentation! I know we often reach for Google before the docs, but in SQLite's case, the docs really are technical writing at its best. It's clean, clear, and concise.
3 Comments
Cris Luengo
For use in scritps, you can run the
.read command directly from the sqlite3 command: sqlite3 autction.db '.read create.sql'.
PeterCo
For Windows CMD line it is important to use doublequotes for the
".read script.sql" part. With single quotes you get an Error: in prepare, unrecognized token: "'.read"In order to execute simple queries and return to my shell script, I think this works well:
$ sqlite3 example.db 'SELECT * FROM some_table;'
4 Comments
Colonel Thirty Two
This is effectively mu is too short's answer.
remeika
@ColonelThirtyTwo Yes, this is very close to mu is too short's answer. The reason I added an additional answer was to demonstrate a method to quickly execute an inline command, rather than taking the additional step of creating an SQL file to store the command in.
Christopher Shroba
@remeika I think the more idiomatic way to execute an inline command would be
sqlite3 example.db 'SELECT * FROM some_table;', rather than piping an echo
Rafael Almeida
Also, (after the edit), it demonstrates that
sqlite3 reads commands to execute not only from stdin, but also from the last argument.For those using PowerShell
PS C:\> Get-Content create.sql -Raw | sqlite3 auction.db
Comments
sqlite3 -init create.sql auction.db .quit
When I enter the command man sqlite3, I see under "OPTIONS":
-init file
Read and execute commands from file , which can contain a mix of SQL statements and meta-commands.
and under "SQLITE META-COMMANDS":
.quit Exit this program
See also: Command Line Shell For SQLite: 3. Special commands to sqlite3 (dot-commands).
Also, when I enter the command sqlite3 -help, I see under "OPTIONS":
-init FILENAME read/process named file
Comments
If you are using the windows CMD you can use this command to create a database using sqlite3
C:\sqlite3.exe DBNAME.db ".read DBSCRIPT.sql"
If you haven't a database with that name sqlite3 will create one, and if you already have one, it will run it anyways but with the "TABLENAME already exists" error, I think you can also use this command to change an already existing database (but im not sure)
