Python with SQLite3: select does not display any data

Question

I am writing a very simply database using python and sqlite3. And when I created a table and some data I wanted to display this data using (in terminal) command "Select * From Data", but no data appears, although I checked using other methods that the data is inserted to the table.

How I create my table and data:

db = connect('database.db')
db_cursor = db.cursor()
db_cursor.execute("CREATE TABLE IF NOT EXISTS Data(Id INT, Name TEXT, City TEXT)")
db_cursor.execute("INSERT INTO Data VALUES (1, 'ABC', 'XYZ')")

If I do:

db_cursor.execute("Select * From Data")
print self.db_cursor.fetchall()

the data is displayed.

But when I run a command line and try to do:

sqlite3 database.db
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Data;

no data appears. I checked using

sqlite> .tables

that table is generated correctly. Why sqlite3 run from command line does not display data?

Answer 1

You need to commit your transaction before it is permanently part of the database:

db.commit()

You can use the database connection as a context manager to commit automatically if a block of code executed successfully:

with db:
 db_cursor = db.cursor()
 db_cursor.execute("INSERT INTO Data VALUES (1, 'ABC', 'XYZ')")

Note that DDL statements (creating tables and other data definitions) are automatically committed, which is why you saw the table in the database, but not the new row.