How to create a database in sqlite3 from command line

user_8372328 0
Viewed 2176

From the command line sqlite3 gives output:

SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

tried to quit after that using command sqlite> sqlite3> .quit

it does not quit but gives output sqlite> sqlite3> .quit ...>

Then I come back to command prompt with ctrl+D

Then to create Database I entered command sqlite3 TheftSiren.db

sqlite3 TheftSiren.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite>

I used ctrl+D again to come to command prompt

To see if db i created I tried command

sqlite3> .databases
[1] 2601
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
gt: error: neither tool nor script specified; option -help lists possible tools

[1]+  Stopped                 sqlite3
.databases: command not found

it also failed

can someone tell what i the right way of handling these?

I also tried

'sqlite> sqlite3 TheftSiren.db
   ...> CREATE TABLE DB (
   ...> imei CHAR(50) PRIMARY KEY NOT NULL,
   ...> mobile_num CHAR(50) NOT NULL,
   ...> passwd     CHAR(50) NOT NULL, 
   ...> id         INT      NOT NULL
   ...> );
Error: near "sqlite3": syntax error'

and

'sqlite> sqlite TheftSiren.db
   ...> CREATE TABLE DB (
   ...> imei CHAR(50) PRIMARY KEY NOT NULL,
   ...> mobile_num CHAR(50) NOT NULL,
   ...> passwd     CHAR(50) NOT NULL, 
   ...> id         INT      NOT NULL
   ...> );
Error: near "sqlite": syntax error'
sqlite linux
2 Answers

CL.'s helpful answer explains the problems with your attempts well and provides helpful background information.

Based on the question's generic title, let me answer the question I thought was being asked when Google brought me here:

How do I use the sqlite3 CLI to programmatically create a database?

sqlite3 isn't just an interactive shell, it also accepts a series of statements via stdin.

The following examples create a sample.db database file with a sample table, sample_table, with two sample records:

  • Note: The commands below use bash syntax, but work analogously in other shells.
# The commands to submit - note that each statement must be ";"-terminated.
cmds='
create table sample_table (Name string, Age int); 
insert into sample_table (Name, Age) values ("JDoe", 42), ("JRoe", 43);
'

# Pipe the commands to `sqlite3` while also passing the database file path.
echo "$cmds" | sqlite3 ./sample.db

Note:

  • If ./samble.db already exists, the commands will modify that existing database; if a table sample_table already exists, you'll get an error.

  • The series of statements may also include:

    • .<cmd> commands such as .headers on (run .help from an interactive sqlite3 session to see them all) - note that these must be on their own line each.
    • Comment lines, which must start with # as the very first character.

To query the resulting table programmatically via the CLI:

Note:

  • This example uses a here-document to provide the stdin input to sqlite3, but you could use the same pipeline-based approach shown above.

  • Note the need to put the .headers on command (which turns on including column names in query results) on its own line.

sqlite3 ./sample.db <<'EOF'
.headers on
select * from sample_table;
EOF

The above yields:

Name|Age
JDoe|42
JRoe|43
user_453750
Related