Re: Simple sqlite3 question
cjl [EMAIL PROTECTED] wrote:
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, [EMAIL PROTECTED]
Providenza Boekelheide, Inc.
--
http://mail.python.org/mailman/listinfo/python-list
Simple sqlite3 question
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Simple sqlite3 question
On 24 Apr 2007 10:03:45 -0700, cjl [EMAIL PROTECTED] wrote:
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Simple sqlite3 question
Jerry Hill wrote:
On 24 Apr 2007 10:03:45 -0700, cjl [EMAIL PROTECTED] wrote:
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
In [EMAIL PROTECTED], Gabriel
Genellina wrote:
En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
[EMAIL PROTECTED] escribió:
In [EMAIL PROTECTED], Jorgen Bodde
wrote:
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of affected rows.
Actually DBAPI 2.0 says the return value is undefined.
I just remembered the number of affected rows, but that's just for data
manipulation statements like ``UPDATE`` or ``INSERT``. For ``SELECT`` the
method should return `None`. My bad.
Ciao,
Marc 'BlackJack' Rintsch
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
[EMAIL PROTECTED] escribió:
In [EMAIL PROTECTED], Jorgen Bodde
wrote:
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of affected rows.
Actually DBAPI 2.0 says the return value is undefined.
--
Gabriel Genellina
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
Thanks,
This is how I did it in the end as well. Yes i use the connection
object, abbreviated as 'c' for ease of typing.
In my real app the connection is kept inside a singleton object and I
use the DB like
result = GuitarDB().connection.execute('select * from song where id =
1').fetchone()
if result:
print 'Found a song'
else:
print 'Found nothing'
I know there will always be a cursor object back from
connection.execute, so for ease of use and sparing a temp var, I put
the .fetchone() behind the connection.execute()
Thanks everyone for their help!
- Jorgen
On 4/12/07, Carsten Haese [EMAIL PROTECTED] wrote:
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In [EMAIL PROTECTED], Jorgen Bodde
wrote:
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
That works. But when I can't restore the row by e.g. an ID that does
not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
that can tell me if I have rows. As explained in the help, r.rowcount
does not give a valid result because it can't determine how many rows
are restored in advance.
This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of affected rows.
It does work if 'c' is a connection object with a poorly chosen name.
According to
http://docs.python.org/lib/sqlite3-Connection-Objects.html , sqlite3
connection objects have a non-standard execute method that creates a
cursor, executes a query on that cursor, and returns that cursor.
Anyway, if you expect a query to return at most one row, such as when
you're filtering on the table's primary key, this is how I would do it:
cur.execute(select * from song where id = ?, (wanted_id,) )
song_row = cur.fetchone()
if song_row:
# Do something with song_row
else:
# Song not found
HTH,
Carsten.
--
http://mail.python.org/mailman/listinfo/python-list
On 4/12/07, Carsten Haese [EMAIL PROTECTED] wrote:
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In [EMAIL PROTECTED], Jorgen Bodde
wrote:
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
That works. But when I can't restore the row by e.g. an ID that does
not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
that can tell me if I have rows. As explained in the help, r.rowcount
does not give a valid result because it can't determine how many rows
are restored in advance.
This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of affected rows.
It does work if 'c' is a connection object with a poorly chosen name.
According to
http://docs.python.org/lib/sqlite3-Connection-Objects.html , sqlite3
connection objects have a non-standard execute method that creates a
cursor, executes a query on that cursor, and returns that cursor.
Anyway, if you expect a query to return at most one row, such as when
you're filtering on the table's primary key, this is how I would do it:
cur.execute(select * from song where id = ?, (wanted_id,) )
song_row = cur.fetchone()
if song_row:
# Do something with song_row
else:
# Song not found
HTH,
Carsten.
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
sqlite3 question
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 is 1, so when I do;
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
That works. But when I can't restore the row by e.g. an ID that does
not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
that can tell me if I have rows. As explained in the help, r.rowcount
does not give a valid result because it can't determine how many rows
are restored in advance.
All I can think of is a 'crappy' construction where I use the iterator
to see if there was something in there, but surely, there must be a
better way to know?
r = c.execute('select * from song where id = 2')
notfound = True
for s in r:
... notfound = False
... print s
if notfound:
... print 'No rows'
I am pretty new with Python, maybe there are some properties of an
iterator / sqlite3 I am not aware of that can tell me how many rows
are there?
With regards,
- Jorgen
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
Jorgen Bodde wrote:
All I can think of is a 'crappy' construction where I use the iterator
to see if there was something in there, but surely, there must be a
better way to know?
r = c.execute('select * from song where id = 2')
notfound = True
for s in r:
... notfound = False
... print s
if notfound:
... print 'No rows'
I am pretty new with Python, maybe there are some properties of an
iterator / sqlite3 I am not aware of that can tell me how many rows
are there?
What about this:
if not c.fetchone():
print No rows
or
print rowcount=, len(cur.fetchall())
--
HTH,
Rob
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
En Thu, 12 Apr 2007 04:38:06 -0300, Jorgen Bodde
[EMAIL PROTECTED] escribió:
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 is 1, so when I do;
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
That works. But when I can't restore the row by e.g. an ID that does
not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
that can tell me if I have rows. As explained in the help, r.rowcount
does not give a valid result because it can't determine how many rows
are restored in advance.
If you are only interested on how many songs there are, use count(*):
c.execute('select count(*) from song where id = 1')
print c.fetchone()[0]
If you are going to process the results, just iterate over the rows as in
your example.
Or, use the fetchone method; it returns the first row, or None if no data
is available (don't forget to process that row too).
If you don't expect many rows, you can use rows = list(c), or rows =
c.fetchall(), and check len(rows).
I am pretty new with Python, maybe there are some properties of an
iterator / sqlite3 I am not aware of that can tell me how many rows
are there?
As with many generic iterators, unfortunately, you have to consume the
elements in order to know its length.
--
Gabriel Genellina
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
In [EMAIL PROTECTED], Jorgen Bodde
wrote:
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
That works. But when I can't restore the row by e.g. an ID that does
not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
that can tell me if I have rows. As explained in the help, r.rowcount
does not give a valid result because it can't determine how many rows
are restored in advance.
This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of affected rows.
Ciao,
Marc 'BlackJack' Rintsch
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In [EMAIL PROTECTED], Jorgen Bodde
wrote:
r = c.execute('select * from song where id = 1')
for s in r:
... print s
...
(1, u'Spikedrivers Blues', u'Mississippi John Hurt')
That works. But when I can't restore the row by e.g. an ID that does
not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
that can tell me if I have rows. As explained in the help, r.rowcount
does not give a valid result because it can't determine how many rows
are restored in advance.
This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of affected rows.
It does work if 'c' is a connection object with a poorly chosen name.
According to
http://docs.python.org/lib/sqlite3-Connection-Objects.html , sqlite3
connection objects have a non-standard execute method that creates a
cursor, executes a query on that cursor, and returns that cursor.
Anyway, if you expect a query to return at most one row, such as when
you're filtering on the table's primary key, this is how I would do it:
cur.execute(select * from song where id = ?, (wanted_id,) )
song_row = cur.fetchone()
if song_row:
# Do something with song_row
else:
# Song not found
HTH,
Carsten.
--
http://mail.python.org/mailman/listinfo/python-list
