[pygr] sqlite not flushing to disk? or in-memory by default?

[jbiesinger]

Hi! I'm just trying out the tutorial on using sqlite as a backend as
seen in
http://biodb.bioinformatics.ucla.edu/pygr_docs/0.8.1/html/tutorials/db_basic.html#saving-data-to-a-sql-database
It seems that the sqlite rows aren't being saved properly. I'm
running python2.6 on Ubuntu 10.04, pygr version 0.8.1.

# Set up the DB, add a few rows
from pygr import sqlgraph
liteserver = sqlgraph.SQLiteServerInfo('/home/wbiesing/test/
slicedb.sqlite')
txInfo = sqlgraph.SQLTable('annotations', serverInfo=liteserver,
writeable=True,
createTable='CREATE TABLE annotations (k INTEGER PRIMARY
KEY, seq_id TEXT, start INT, stop INT, orientation INT);')
txInfo.new(k=0,seq_id='chr1',start=0,stop=50,orientation=1)
txInfo.new(k=1,seq_id='chrX',start=20,stop=250,orientation=-1)
liteserver.close()

# there are no rows in the db.
sqlite3 /home/wbiesing/test/slicedb.sqlite
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
annotations
sqlite> .schema
CREATE TABLE annotations (k INTEGER PRIMARY KEY, seq_id TEXT, start
INT, stop INT, orientation INT);
sqlite> select * from annotations;
sqlite>


I can add rows and wrap them in an annotation database, but as soon as
I leave the python session, the data is lost. It's not just being
kept in-memory since sqlite file is created...

--
You received this message because you are subscribed to the Google Groups "pygr-dev" group.
To post to this group, send email to pygr...@googlegroups.com.
To unsubscribe from this group, send email to pygr-dev+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/pygr-dev?hl=en.

[C. Titus Brown]

Try liteserver.commit() before the close() (well, you'll need to get a db handle somehow - liteserver may not be it. More when I get to my laptop.)

--
C. Titus Brown, c...@msu.edu

[jbiesinger]

Partially resolved...

liteserver._connection.commit() did it, though I doubt that's the
proper way to do it. The code I'm testing creates and populates two
sqlite tables. If this is a bug, it might be helpful to note that the
first table (which was created and populated first) was flushed to
disk without the commit statement, meaning that only the last table
was being lost. Perhaps the "create table" statement has an implicit
commit?

Now, I'm running into the unicode issue you posted about. I'll try
out your patch to see if it resolves it.

On May 3, 5:47 pm, "C. Titus Brown" <c...@msu.edu> wrote:
> Try liteserver.commit() before the close() (well, you'll need to get a db handle somehow - liteserver may not be it.  More when I get to my laptop.)
>
> --
> C. Titus Brown, c...@msu.edu
>

> On May 3, 2010, at 7:09 PM, jbiesinger <jake.biesin...@gmail.com> wrote:
>
>
>
> > Hi!  I'm just trying out the tutorial on using sqlite as a backend as
> > seen in

> >http://biodb.bioinformatics.ucla.edu/pygr_docs/0.8.1/html/tutorials/d...

> > For more options, visit this group athttp://groups.google.com/group/pygr-dev?hl=en.

>
> --
> You received this message because you are subscribed to the Google Groups "pygr-dev" group.
> To post to this group, send email to pygr...@googlegroups.com.
> To unsubscribe from this group, send email to pygr-dev+u...@googlegroups.com.

> For more options, visit this group athttp://groups.google.com/group/pygr-dev?hl=en.

[jbiesinger]

I think it would be wise to update the tutorial site with a commit
statement...

[Kenny Daily]

I had a similar problem too. I was using the cursor obtained from:

cursor = myannotdb.sliceDB.cursor

I would not see insert statements that I executed using this cursor. I
fixed (with other issues coming from doing this) by calling:

cursor.execute("BEGIN TRANSACTION")

before each sql insert call. But, if the script running this fails, it
leave the database in a locked state.

Kenny

[Paul Rigor (gmail)]

Hey gang,

There's also a weird bug when you attempt to "commit" a transaction.
Through the python wrapper, an error is thrown even though the commit
call is appropriately placed. Your python code may fail, but double
check your sqlite3 because the data is *most likely* safely committed.

Not sure if this is related though =P

Paul

--
Paul Rigor
Pre-doctoral BIT Fellow and Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California, Irvine
http://www.ics.uci.edu/~prigor

[C. Titus Brown]

On Thu, May 06, 2010 at 12:18:14AM -0700, Paul Rigor (gmail) wrote:
> There's also a weird bug when you attempt to "commit" a transaction.
> Through the python wrapper, an error is thrown even though the commit
> call is appropriately placed. Your python code may fail, but double
> check your sqlite3 because the data is *most likely* safely committed.
>
> Not sure if this is related though =P

Hey Paul,

I haven't seen this myself -- would you be able to give me a fairly
small bit of code that demonstrates the problem?

thanks!
--titus

> On Wed, May 5, 2010 at 11:51 PM, Kenny Daily <kmd...@gmail.com> wrote:
> > I had a similar problem too. I was using the cursor obtained from:
> >
> > cursor = myannotdb.sliceDB.cursor
> >
> > I would not see insert statements that I executed using this cursor. I
> > fixed (with other issues coming from doing this) by calling:
> >
> > cursor.execute("BEGIN TRANSACTION")
> >
> > before each sql insert call. But, if the script running this fails, it
> > leave the database in a locked state.
> >
> > Kenny
> >
> > On May 3, 11:51?pm, jbiesinger <jake.biesin...@gmail.com> wrote:
> >> I think it would be wise to update the tutorial site with a commit
> >> statement...
> >>
> >> On May 3, 11:41?pm, jbiesinger <jake.biesin...@gmail.com> wrote:
> >>
> >>
> >>
> >>
> >>
> >> > Partially resolved...
> >>
> >> > liteserver._connection.commit() did it, though I doubt that's the

> >> > proper way to do it. ?The code I'm testing creates and populates two
> >> > sqlite tables. ?If this is a bug, it might be helpful to note that the

> >> > first table (which was created and populated first) was flushed to
> >> > disk without the commit statement, meaning that only the last table

> >> > was being lost. ?Perhaps the "create table" statement has an implicit
> >> > commit?
> >>
> >> > Now, I'm running into the unicode issue you posted about. ?I'll try

> >> > out your patch to see if it resolves it.
> >>

> >> > On May 3, 5:47?pm, "C. Titus Brown" <c...@msu.edu> wrote:
> >>
> >> > > Try liteserver.commit() before the close() (well, you'll need to get a db handle somehow - liteserver may not be it. ?More when I get to my laptop.)

> >>
> >> > > --
> >> > > C. Titus Brown, c...@msu.edu
> >>
> >> > > On May 3, 2010, at 7:09 PM, jbiesinger <jake.biesin...@gmail.com> wrote:
> >>

> >> > > > Hi! ?I'm just trying out the tutorial on using sqlite as a backend as
> >> > > > seen in
> >> > > >http://biodb.bioinformatics.ucla.edu/pygr_docs/0.8.1/html/tutorials/d...
> >> > > > It seems that the sqlite rows aren't being saved properly. ?I'm

> >> > > > running python2.6 on Ubuntu 10.04, pygr version 0.8.1.
> >>
> >> > > > # Set up the DB, add a few rows
> >> > > > from pygr import sqlgraph
> >> > > > liteserver = sqlgraph.SQLiteServerInfo('/home/wbiesing/test/
> >> > > > slicedb.sqlite')
> >> > > > txInfo = sqlgraph.SQLTable('annotations', serverInfo=liteserver,

> >> > > > ? ? ? ? ? ?writeable=True,
> >> > > > ? ? ? ? ? ?createTable='CREATE TABLE annotations (k INTEGER PRIMARY

> >> > > > KEY, seq_id TEXT, start INT, stop INT, orientation INT);')
> >> > > > txInfo.new(k=0,seq_id='chr1',start=0,stop=50,orientation=1)
> >> > > > txInfo.new(k=1,seq_id='chrX',start=20,stop=250,orientation=-1)
> >> > > > liteserver.close()
> >>
> >> > > > # there are no rows in the db.
> >> > > > sqlite3 /home/wbiesing/test/slicedb.sqlite
> >> > > > SQLite version 3.6.22
> >> > > > Enter ".help" for instructions
> >> > > > Enter SQL statements terminated with a ";"
> >> > > > sqlite> .tables
> >> > > > annotations
> >> > > > sqlite> .schema
> >> > > > CREATE TABLE annotations (k INTEGER PRIMARY KEY, seq_id TEXT, start
> >> > > > INT, stop INT, orientation INT);
> >> > > > sqlite> select * from annotations;
> >> > > > sqlite>
> >>
> >> > > > I can add rows and wrap them in an annotation database, but as soon as

> >> > > > I leave the python session, the data is lost. ?It's not just being

--
C. Titus Brown, c...@msu.edu

[Christopher Lee]

On May 5, 2010, at 11:51 PM, Kenny Daily wrote:

> I had a similar problem too. I was using the cursor obtained from:
>
> cursor = myannotdb.sliceDB.cursor
>
> I would not see insert statements that I executed using this cursor. I
> fixed (with other issues coming from doing this) by calling:
>
> cursor.execute("BEGIN TRANSACTION")
>
> before each sql insert call. But, if the script running this fails, it
> leave the database in a locked state.

To avoid leaving the database in a locked state, seems like you could use try-finally to ensure that the correct operation for closing the transaction is carried out no matter happens during the script, e.g. something like

cursor.execute("BEGIN TRANSACTION")
try:
# whatever insert operations you want to perform...
finally:
cursor.execute("ROLLBACK TRANSACTION") # or whatever is appropriate to release the lock...

-- Chris