Problem with python and SQL

[Niloofar24]

Hi everybody.
I'm using python to create sql database. I created a test.db file and then made a table on it with the name "fims" and inserted some data on it.... title text, year text and director text. And then set values for them.

Now when i type "sqlite3 test.db" on the shell, and the "secelt * from films;" nothing apears.
I did exactly what i saw on the tutorial video but the result was different.

(Of course the tutorial is using windows, but i use Linux, so i know i should change some commands a little a bit, but abut this command, i don't know what to do)

[Gribouillis]

You can install sqliteman to browse the database. Type

sudo apt-get install sqliteman

in ubuntu or other debian distros. Then open your database file with sqliteman and examine the contents of the database. You can also run sql statements in the gui.

[Slavi]

You should probably type SELECT not secelt

[Niloofar24]

I tried SELECT but there was no changes.
First i created db with "dbcreate.py" file:

import sqlite3 as db

conn = db.connect('test.db')
cursor = conn.cursor()
cursor.execute("create table films (title text, year text, director text)")
print ("table created")

Then i inserted vlues on db with "dbinsert.py" file:

import sqlite3 as db

conn = db.connect('test.db')
cursor = conn.cursor()
cursor.execute('insert into films values ("matrix","1999","The Wachowski Brothers")')
cursor.execute('insert into films values ("V for Vendetta","2005","James McTeigue")')
conn.close()

After that when i typed "sqlite3 test.db" and then typed "select * from films" nothing appeared.

[Niloofar24]

After that, I did this one in "dbquery.py" file:

import sqlite3 as db

conn = db.connect('test.db')
conn.row_factory = db.Row
cursor = conn.cursor()

cursor.execute("select * from films")
rows = cursor.fetchall()
for row in rows:
    print ("%s %s %s" % (row["title"], row["year"], row["director"]))
conn.close()

But it did'nt have any result again.
Why? Anyone can test my codes on Linux to find the problem?

[Gribouillis]

I opened the database with sqliteman and the table was indeed empty. What happened is that you forgot a statement

conn.commit()

in dbinsert.py before closing the connection. Your inserts occurred in memory but they were never written on disk.

[Niloofar24]

Oh yes! Thank you @Gribouillis. It works now. The man didn't type that on the video tutorial.

[Niloofar24]

@Gribouillis, do i have to add these 3 lines into the end of the "dbcreate.py"?

conn.commit()
cursor.close()
conn.close()

Is it necessary? I ask it because i see the file will works ok even without those 3 lines.
"dbcreate.py" file is just for creating database and we don't set any value on this file, so no need to commit, right? What about closing cursor and conn on this file?
I know i should type those 3 lines in all files but myquestion is just about "dbcreate.py" file.

[Gribouillis]

As a bonus, you can try my sqlite snippet on your database to display its structure from the command line.

[HiHe]

The with statement
with conn:
takes care of
conn.commit() and conn.close()

[Niloofar24]

Thank you @Gribouillis.

[Niloofar24]

@HiHe, what do you mean by "with conn:"?
Can you explain it more clear please?

[HiHe]

import sqlite3 as db

conn = db.connect('test.db')

# with takes care of conn.commit() and conn.close()
with conn:
    cursor = conn.cursor()
    cursor.execute('insert into films values ("matrix","1999","The Wachowski Brothers")')
    cursor.execute('insert into films values ("V for Vendetta","2005","James McTeigue")')