sqlite3 api interacting with databases

Question

I have an issue when running this simple example from here:

I created the database

[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> .help
sqlite> CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
sqlite> .schema employee
CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
sqlite> INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
sqlite> INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
sqlite> INSERT into employee values ('Barney Rubble','Sales','Neighbor');
sqlite> INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
sqlite> .tables
employee
sqlite> SELECT Name FROM employee WHERE dept='Sales';
Barney Rubble
sqlite> SELECT * FROM employee;
Fred Flinstone|Quarry Worker|Rock Digger
Wilma Flinstone|Finance|Analyst
Barney Rubble|Sales|Neighbor
Betty Rubble|IT|Neighbor
sqlite> DELETE FROM employee WHERE dept='Sales';
sqlite> .output /tmp/bedrock.sql
sqlite> .dump
sqlite> .exit

Everything seemed allright and i exited the program, but after i enter again in sqlite3 program, the database was not there when i tried the .databases command (but it appeared prior to the exit), and i don't know how can i make modifications on it again.

UPDATE:

I managed to find this page on the official documentation website and it worked out perfectly which is fine for me. But i'm still bothered about modifying the database and updating stuff after i exit the sqlite3 application. Is there any way in which i can interact with a database of my choice ? add/remove tables after the creation of the database ?

being a bit more specific would help... which exact example did you try, there's lots of them there. please add to question — codeling
– codeling, Commented Nov 29, 2013 at 10:41
At least, place some cout to locate where segfault occurs... — LS_ᴅᴇᴠ
– LS_ᴅᴇᴠ, Commented Nov 29, 2013 at 11:14
Your last edit has made the question less clear. You can run CREATE TABLE statements at any time. — CL.
– CL., Commented Nov 29, 2013 at 12:25

andrew cooke · Accepted Answer · 2013-11-29 14:16:23Z

1

you sound very confused, and i think some of what you are reporting is because you are just in a muddle.

you can modify a database at any time (both the data it contains and the table structure). even after data have been added. even after you leave the sqlite3 program.

in your example above, there are two files. one is /tmp/bedrock.db. that is the database. using /tmp for a database is not a good idea as files in /tmp are deleted. anyway, you can re-connect to that database again by using sqlite3 /tmp/bedrock.db.

note that you must include the database when using the sqlite3 command. if you start sqlite3 without the database then the .databases command wil not show the database.

the other file is /tmp/bedrock.sql. that's just the sql you used to create the database. generally that is not useful, and generally you don't create it. the reason it was created in that example is because that example is updating from an old version of sqlite. but that is not something you need to worry about. if i were you, i would forget about this file altogether.

if it hasn't been deleted (because it is in /tmp) then you can connect to your database now, with sqlite3 /tmp/bedrock.db and continue to modify it.

answered Nov 29, 2013 at 14:16

andrew cooke

47.1k10 gold badges102 silver badges148 bronze badges

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

1 Comment

Edeph Over a year ago

Thank you i haven't used the /tmp folder i just used my own project folder and everything works as you said. Very well explained!

Collectives™ on Stack Overflow

sqlite3 api interacting with databases

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related