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]

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

 

[John Salerno]

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!

 

[John Salerno]

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!

 

[Tim Chase]

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

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

 

[BartlebyScrivener]

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

rd

 

[Dennis Lee Bieber]

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!

I believe the DB-API PEP specifies that the "normal" mode in NOT
autocommit (whereas MySQL itself used to normally run autocommit <G>).

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.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[John Salerno]

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]

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.