sqlite query not working

[John Salerno]

Hopefully this is enough code to reveal the problem. When I run the
program, there are no error messages produced, it's just that the values
I enter don't seem to get put into the database, even though the query
seems to be ok.
def OnSaveRecord(self, event):
textfield_values = []
for tab in self.notebook.GetCurrentPage().GetChildren():
for table in self.get_textfield_ids():
table_values = []
for textfield_id in table:
table_values.append(xrc.XRCCTRL(tab,
textfield_id).GetValue())
textfield_values.append(table_values)
res_id = self.create_id(textfield_values[0][0],
textfield_values[0][2])
for table in textfield_values:
table.insert(0, res_id)
self.save_to_database(textfield_values)

def save_to_database(self, data):
# doesn't work?
self.connection.execute("""INSERT INTO Personal VALUES
(?,?,?,?,?,?,?,?,?,?)""", tuple(data[0]))

[Steve Holden]

John Salerno wrote:

Hopefully this is enough code to reveal the problem. When I run the
program, there are no error messages produced, it's just that the values
I enter don't seem to get put into the database, even though the query
seems to be ok.
def OnSaveRecord(self, event):
textfield_values = []
for tab in self.notebook.GetCurrentPage().GetChildren():
for table in self.get_textfield_ids():
table_values = []
for textfield_id in table:
table_values.append(xrc.XRCCTRL(tab,
textfield_id).GetValue())
textfield_values.append(table_values)
res_id = self.create_id(textfield_values[0][0],
textfield_values[0][2])
for table in textfield_values:
table.insert(0, res_id)
self.save_to_database(textfield_values)

def save_to_database(self, data):
# doesn't work?
self.connection.execute("""INSERT INTO Personal VALUES
(?,?,?,?,?,?,?,?,?,?)""", tuple(data[0]))

Have you tried adding a self.connection.commit() to the code? I don't
know whether sqlite is transactional, but if it is then the changes will
disappear without a commit.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[John Salerno]

Steve Holden wrote:

Have you tried adding a self.connection.commit() to the code? I don't
know whether sqlite is transactional, but if it is then the changes will
disappear without a commit.

Wow, that worked! Now, I know I've done some DB work before (very
similar to this) and never used commit(), so I'm confused but still
grateful! :)

Thanks!

[Tim Chase]

>Have you tried adding a self.connection.commit() to the

>code? I don't know whether sqlite is transactional, but if
it is then the changes will disappear without a commit.

Wow, that worked! Now, I know I've done some DB work before
(very similar to this) and never used commit(), so I'm
confused but still grateful! :)

I tinkered with the mx.ODBC drivers a bit and had a similar
difficulty until I realized that it was configured to *not*
autocommit. At least in the mx.ODBC drivers, you can pass a
param ("clear_auto_commit=1") to the Connect() call to restore
"normal" autocommiting behavior. I can see both sides of the
fence...it's just a hassle to sniff out which DB drivers
autocommit and which don't.

-tkc

[John Salerno]

Tim Chase wrote:

I tinkered with the mx.ODBC drivers a bit and had a similar
difficulty until I realized that it was configured to *not*
autocommit. At least in the mx.ODBC drivers, you can pass a
param ("clear_auto_commit=1") to the Connect() call to restore
"normal" autocommiting behavior. I can see both sides of the
fence...it's just a hassle to sniff out which DB drivers
autocommit and which don't.

What's really strange is that I'm pretty sure (but can always be wrong)
that I've written SQLite queries just as above, and they were saved to
the DB without a commit() call, so it's not like I was even using a
different system. Ah well, I'm sure there was *something* different
about the two cases! :)

[BartlebyScrivener]

John Salerno wrote:

>Ah well, I'm sure there was *something* different

Are you sure that it's not you were doing SELECT before, as opposed to
INSERT?

rd

[John Salerno]

BartlebyScrivener wrote:

John Salerno wrote:

>>Ah well, I'm sure there was *something* different

Are you sure that it's not you were doing SELECT before, as opposed to
INSERT?

Perhaps. It might have been that I used the INSERT statement on the
sqlite command line, then used SELECT in Python, and got it all mixed up
in my head. :)

[John Salerno]

Dennis Lee Bieber wrote:

The other thing to consider is that, if you were testing using a
single cursor, and single session, the database would have shown you
uncommitted changes. It wouldn't have been until you closed the
cursor/connection without a commit that the DBMS would have tossed them
-- a select would still retrieve your uncommited changes during that
transaction.

Good point, and I wouldn't be surprised if I had done that too! Working
with databases in Python (as opposed to direct command line queries) is
fairly new to me, so who knows what crazy things I tried to do. :)