CODEWITHSUNDEEP
pythonsqlite
node_analyser
node_analyser

Reputation: 1612

Why am I not able to see table contents from sqlite3 interface?

I wrote a simple basic program to create a database and insert one row in a table. Here is how the program looks like. 

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute('''CREATE TABLE COMPANY
   (ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL);''')

print "Table created successfully";

curs = conn.cursor()

curs.execute("INSERT INTO COMPANY (ID,NAME,AGE) \
  VALUES (1, 'Paul', 32)");

cursor = conn.execute("SELECT id, name, age from COMPANY")
for row in cursor:
    print "ID = ", row[0]
    print "NAME = ", row[1]
    print "AGE = ", row[2], "\n"
conn.close()

The above program successfully creates a database(test.db) and a table(COMPANY). It even displays the data. 

Problem :

The tuple is getting properly inserted in the table but when I try to open the table content from the sqlit3 interface, I get blank output.

root@root> sqlite3 test.db 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
COMPANY
sqlite> select * from COMPANY;
sqlite>

Why is it so that I get the result if I run "SELECT" query through script but I get no output from sqlite3 interface ?? Am I missing something ?

P.S - Script is written in python. Database is SQLite(version 3.8.2)

Any help is highly appreciated

Upvotes: 0

Views: 1955

Answers (2)

monkut
monkut

Reputation: 43840

You seem to have forgotten to commit.

# Save (commit) the changes
conn.commit()

You can also use the connection in a context manager, committing the results once the block exits:

with conn:
   conn.execute("INSERT INTO COMPANY (ID,NAME,AGE) VALUES (1, 'Paul', 32)");

or you can turn on autocommit, by setting the isolation_level to None:

conn.isolation_level = None

Upvotes: 3

Fabricator
Fabricator

Reputation: 12772

You have not committed your changes. Call conn.commit() before conn.close().

Upvotes: 1

Related Questions