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
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: 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
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: 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
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
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
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
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
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