Simple sqlite3 question

[cjl]

P:

I am using python 2.5.1 on windows. I have the following code:

conn = sqlite3.connect('.\optiondata')
c = conn.cursor()
try:
c.execute('''create table options (ssymbol text, strike real,
osymbol text, bid real, mpp real, upp real)''')
except sqlite3.OperationalError:
pass

I am hoping the above code creates a new database file named
'optiondata' with a single table named 'options', or connects to it if
already created. Am I off the mark here?

I also have a function that does the following:

c.execute("""insert into options values (?,?,?,?,?,?)""",data)

When I run the script and there is no file named optiondata, one is
created and the correct data is added to it. If I run the script
again then the data from the first run seems to be replaced with the
data from the second run. I expected that the data from the second run
would be appended to the database file, not replace it.

Can anyone point me in the right direction to get the expected
behavior?

-cjl

 

[Jerry Hill]

When I run the script and there is no file named optiondata, one is
created and the correct data is added to it. If I run the script
again then the data from the first run seems to be replaced with the
data from the second run. I expected that the data from the second run
would be appended to the database file, not replace it.

It sounds like you're not calling conn.commit() before you close the
database. By default sqlite (and any other DBAPI 2.0 compliant sql
wrapper) will start in transactional mode. You need to commit() your
transactions or they will be rolled back when you close the database
connection. If you don't want to call commit on your own, you can
switch the database into autocommit mode by setting the isolation
level to None when you open the connection, like this:

conn = sqlite3.connect('.\optiondata', isolation_level=None)

 

[John Nagle]

It sounds like you're not calling conn.commit() before you close the
database. By default sqlite (and any other DBAPI 2.0 compliant sql
wrapper) will start in transactional mode. You need to commit() your
transactions or they will be rolled back when you close the database
connection. If you don't want to call commit on your own, you can
switch the database into autocommit mode by setting the isolation
level to None when you open the connection, like this:

conn = sqlite3.connect('.\optiondata', isolation_level=None)

Don't do that; make the commit calls. Otherwise you'll be back
here complaining about how bad stuff happened when two copies of
the program ran at the same time.

John Nagle

 

[Tim Roberts]

I am using python 2.5.1 on windows. I have the following code:

conn = sqlite3.connect('.\optiondata')

This is unrelated to your question, but you have a slash problem there. \o
doesn't happen to be a valid escape character, but if you had used
"testdata" as the filename, it would have failed.

You should use one of these alternatives:

conn = sqlite3.connect('.\\optiondata')
conn = sqlite3.connect(r'.\optiondata')
conn = sqlite3.connect('./optiondata')
conn = sqlite3.connect('optiondata')