sqlite3 bug??

[mark carter]

I hesitate to ask, but ...

I'm using Ubuntu Feisty:
* Python 2.5.1 (r251:54863, May 2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
* SQLite version 3.3.13

Suppose I run the following program:
import sqlite3

conn = sqlite3.connect('example')


c = conn.cursor()

# Create table
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")

and then I go into sqlite:
% sqlite3 example
sqlite3> select * from stocks ;

It returns 0 rows. I'm in the right directory. I have experienced this
problem with some other sqlite3 database work I have done with python,
so I'm figuring there is something fishy going on. I've tried doing
similar exercises with Ruby, and they have worked OK.

Anyone else getting these problems?

 

[David Wahler]

I hesitate to ask, but ...

I'm using Ubuntu Feisty:
* Python 2.5.1 (r251:54863, May 2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
* SQLite version 3.3.13

Suppose I run the following program:
import sqlite3

conn = sqlite3.connect('example')


c = conn.cursor()

# Create table
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")

and then I go into sqlite:
% sqlite3 example
sqlite3> select * from stocks ;

It returns 0 rows. I'm in the right directory. I have experienced this
problem with some other sqlite3 database work I have done with python,
so I'm figuring there is something fishy going on. I've tried doing
similar exercises with Ruby, and they have worked OK.

Anyone else getting these problems?

See http://www.python.org/dev/peps/pep-0249/ (emphasis mine):

.commit()

Commit any pending transaction to the database. *Note that
if the database supports an auto-commit feature, this must
be initially off.* An interface method may be provided to
turn it back on.

(This really should be a FAQ...)

-- David

 

[Peter Otten]

I hesitate to ask, but ...

Don't

I'm using Ubuntu Feisty:
* Python 2.5.1 (r251:54863, May 2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
* SQLite version 3.3.13

Suppose I run the following program:
import sqlite3

conn = sqlite3.connect('example')


c = conn.cursor()

# Create table
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")

and then I go into sqlite:
% sqlite3 example
sqlite3> select * from stocks ;

It returns 0 rows. I'm in the right directory. I have experienced this
problem with some other sqlite3 database work I have done with python,
so I'm figuring there is something fishy going on. I've tried doing
similar exercises with Ruby, and they have worked OK.

Anyone else getting these problems?

How about conn.commit()?

Peter

 

[Carsten Haese]

I hesitate to ask, but ...

I'm using Ubuntu Feisty:
* Python 2.5.1 (r251:54863, May 2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
* SQLite version 3.3.13

Suppose I run the following program:
import sqlite3

conn = sqlite3.connect('example')


c = conn.cursor()

# Create table
c.execute('''create table stocks
(date text, trans text, symbol text,
qty real, price real)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")

and then I go into sqlite:
% sqlite3 example
sqlite3> select * from stocks ;

It returns 0 rows.

Your program doesn't call conn.commit(). Python's DB-API specifies that
a connection operate in a transaction by default. The transaction is
rolled back at the end of the program if it's not committed.

 

[mark carter]

See http://www.python.org/dev/peps/pep-0249/ (emphasis mine):

.commit()

OK, I tried that, and I appear to be cooking. The funny thing is, I
could have sworn that I tried that a few days ago, and it didn't work.
Weird. Appears to be working now, though, so I guess I must have been
doing something kooky.

Should I also explicitly close the cursor and connection, or is that
taken care of "automagically"?

I'm seriously thinking about reporting the commit() thing as a doc bug
in python, as this isn't mentioned at
http://docs.python.org/lib/module-sqlite3.html
and I think it's exactly the kind of thing that should be mentioned in
the examples.

 

[7stud]

OK, I tried that, and I appear to be cooking. The funny thing is, I
could have sworn that I tried that a few days ago, and it didn't work.
Weird. Appears to be working now, though, so I guess I must have been
doing something kooky.

Should I also explicitly close the cursor and connection, or is that
taken care of "automagically"?

I'm seriously thinking about reporting the commit() thing as a doc bug
in python, as this isn't mentioned athttp://docs.python.org/lib/module-sqlite3.html
and I think it's exactly the kind of thing that should be mentioned in
the examples.

Please report the whole docs as a bug.

 

[mark carter]

Please report the whole docs as a bug.

http://sourceforge.net/tracker/index.php?func=detail&aid=1738670&group_id=5470&atid=105470

That will save a few people tearing their hair out!

 

[Carsten Haese]

Please report the whole docs as a bug.

I imagine the author appreciates constructive criticism. This is not
constructive criticism.

In other words: Pointing out specific shortcomings and ways to correct
them, such as what the OP is doing, is helpful. Calling the entire docs
a bug is not helpful.

 

[mark carter]

I imagine the author appreciates constructive criticism. This is not
constructive criticism.

In other words: Pointing out specific shortcomings and ways to correct
them, such as what the OP is doing, is helpful. Calling the entire docs
a bug is not helpful.

You'll be pleased to know that I was specific, and I suggested a change
that I thought would be good.

Actually, I think the docs are quite good! I went hunting around some
Scheme implementations lately. What was immediately apparent to me was
that the docs weren't nearly as good as those for python. Typical
problems centre about what modules I was supposed to load, and how I was
supposed to use them. What might be obvious to an old hand might not be
obvious to someone coming in from fresh. This is where big projects like
Python tend to score - the docs have been through many iterations.

 

[Sebastian Wiesner]

Please report the whole docs as a bug.

Calling the entire docs a bug is not helpful.

... unless he also comes up with the "bugfix".

--
Freedom is always the freedom of dissenters.
(Rosa Luxemburg)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (GNU/Linux)

iD8DBQBGdX44n3IEGILecb4RAvIsAJ0dy5Cwi9yB1Od3y6o5SMBoj8fSxwCfUHCV
E0BFKnzSj2n8Nw1ZhNMIGwU=
=n/jF
-----END PGP SIGNATURE-----

 

[Carsten Haese]

You'll be pleased to know that I was specific, and I suggested a change
that I thought would be good.

I know, and I acknowledged that. Maybe you missed that OP = original
poster = You.

 

[Hyuga]

Should I also explicitly close the cursor and connection, or is that
taken care of "automagically"?

Somebody correct me if I'm wrong, but I'm pretty sure that the Cursor
and Connection objects properly clean themselves up when deallocated
(when their reference count reaches 0), so not explicitly closing them
isn't a terrible thing. In fact, I have code in which references to a
db connection are passed around, so I have to be careful about
explicitly closing the connection, lest it be in use by some other
method somewhere. Maybe people will frown on this, but it's not
uncommon.

Hyuga

 

[Hyuga]

In fact, I have code in which references to a
db connection are passed around, so I have to be careful about
explicitly closing the connection, lest it be in use by some other
method somewhere.

Hate to reply to myself, but I should clarify that passing around a
handle to an existing DB connection is necessary as a means of
allowing multiple methods that write to the DB to operate atomically
before calling commit(). So again, if you're doing something like
that, you want to be absolutely certain before you close your
connection that it's not in use elsewhere.

Hyuga

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

Should I also explicitly close the cursor and connection, or is that
taken care of "automagically"?

Somebody correct me if I'm wrong, but I'm pretty sure that the Cursor
and Connection objects properly clean themselves up when deallocated
(when their reference count reaches 0), so not explicitly closing them
isn't a terrible thing. [...]

That's correct for pysqlite, and probably for all other DB-API modules
too. If you have a client-server database, it's nicer to close the
database connection if you don't need it any longer in order to free up
resources on the server, of course.

In fact, I have code in which references to a db connection are
passed around, so I have to be careful about explicitly closing the
connection, lest it be in use by some other method somewhere. Maybe
people will frown on this, but it's not uncommon.

I don't think I've ever explicitly closed a cursor object when
programming to the DB-API myself.

-- Gerhard