473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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

Apr 24 '07 #1
3 1888
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: lolmcbride | last post by:
Hi, is it possible to pass args through the api which are the same as the args you can use on the sqlite3 command line? What I'm talking about is the .mode or .output commands which you can enter...
2
by: John Machin | last post by:
Apologies in advance if this is a bit bloggy, but I'd like to get comments on whether I've lost the plot (or, more likely, failed to acquire it) before I start reporting bugs etc. From "What's...
8
by: rdrink | last post by:
I am just getting into pysqlite (with a fair amount of Python and MySQL experience behind me) and have coded a simple test case to try to get the hang of things... yet have run into a 'stock...
6
by: Jorgen Bodde | last post by:
Hi all, I am using sqlite3 in python, and I wonder if there is a way to know if there are valid rows returned or not. For example I have a table song with one entry in it. The ID of that entry...
4
by: Simon | last post by:
I installed the source code on unix for python 2.5.1. The install went mainly okay, except for some failures regarding: _ssl, _hashlib, _curses, _curses_panel. No errors regarding sqlite3....
33
by: Stef Mientki | last post by:
hello, I discovered that boolean evaluation in Python is done "fast" (as soon as the condition is ok, the rest of the expression is ignored). Is this standard behavior or is there a compiler...
3
by: milan_sanremo | last post by:
I have sqlite installed, but when I try to import sqlite3 I receive: Python 2.5.1 (r251:54863, Nov 3 2007, 02:54:36) on sunos5 Type "help", "copyright", "credits" or "license" for more...
0
by: Ben Lee | last post by:
hi folks -- a quick python and sqlite3 performance question. i find that inserting a million rows of in-memory data into an in-memory database via a single executemany() is about 30% slower...
15
by: Kurda Yon | last post by:
Hi, I try to "build" and "install" pysqlite? After I type "python setup.py build" I get a lot of error messages? The first error is "src/ connection.h:33:21: error: sqlite3.h: No such file or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.