By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,264 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,264 IT Pros & Developers. It's quick & easy.

Simple sqlite3 question

P: n/a
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

Apr 24 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 24 Apr 2007 10:03:45 -0700, cjl <cj****@gmail.comwrote:
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)

--
Jerry
Apr 24 '07 #2

P: n/a
Jerry Hill wrote:
On 24 Apr 2007 10:03:45 -0700, cjl <cj****@gmail.comwrote:
>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)
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
Apr 24 '07 #3

P: n/a
cjl <cj****@gmail.comwrote:
>
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')
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Apr 26 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.