CODEWITHSUNDEEP
csqlite
Edeph
Edeph

Reputation: 772

sqlite3 api interacting with databases

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

SQLite example

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 ?

Upvotes: 1

Views: 244

Answers (1)

andrew cooke
andrew cooke

Reputation: 46882

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.

Upvotes: 1

Related Questions