How to create a database in sqlite3 from command line

Question

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&gt; .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'

Don't spam tags. As asked, it is not realted to any of the two different programming languages C or C++. — too honest for this site
– too honest for this site, Commented Jul 26, 2017 at 20:24
Where did you get that sqlite3> garbage from? What is the actual problem you're trying to solve? — CL.
– CL., Commented Jul 27, 2017 at 9:33
I am trying to create a databae and use it in c, linux program — chinmay
– chinmay, Commented Jul 27, 2017 at 9:51

CL. · Accepted Answer · 2017-07-30 12:14:01Z

2

When you are in the normal command-line shell of your OS, you can run the sqlite3 program to start the SQLite command-line shell. This is the only time you would ever enter "sqlite3". When you are inside the SQLite command-line shell and want to re-start sqlite3, you have to quite it first with .quit.

The command-line shell prints "sqlite>" to show that you are in that shell, and that it expects SQL commands (or special dot commands). That web page shows "sqlite>" just because it is on the screen; never enter this by hand.

That web page did the HTML encoding wrong; " is actually ", and > is actually >.

answered Jul 30, 2017 at 12:14

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mklement0 · Accepted Answer · 2021-09-09 18:17:46Z

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

Collectives™ on Stack Overflow

How to create a database in sqlite3 from command line

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related