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(se lf, event):
textfield_value s = []
for tab in self.notebook.G etCurrentPage() .GetChildren():
for table in self.get_textfi eld_ids():
table_values = []
for textfield_id in table:
table_values.ap pend(xrc.XRCCTR L(tab,
textfield_id).G etValue())
textfield_value s.append(table_ values)
res_id = self.create_id( textfield_value s[0][0],
textfield_value s[0][2])
for table in textfield_value s:
table.insert(0, res_id)
self.save_to_da tabase(textfiel d_values)

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

[Steve Holden]

Re: sqlite query not working

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(se lf, event):
textfield_value s = []
for tab in self.notebook.G etCurrentPage() .GetChildren():
for table in self.get_textfi eld_ids():
table_values = []
for textfield_id in table:
table_values.ap pend(xrc.XRCCTR L(tab,
textfield_id).G etValue())
textfield_value s.append(table_ values)
res_id = self.create_id( textfield_value s[0][0],
textfield_value s[0][2])
for table in textfield_value s:
table.insert(0, res_id)
self.save_to_da tabase(textfiel d_values)
>
def save_to_databas e(self, data):
# doesn't work?
self.connection .execute("""INS ERT 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 +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[John Salerno]

Re: sqlite query not working

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]

Re: sqlite query not working

>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_co mmit=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]

Re: sqlite query not working

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_co mmit=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]

Re: sqlite query not working


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]

Re: sqlite query not working

BartlebyScriven er 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]

Re: sqlite query not working

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