couple more questions about sqlite

[John Salerno]

I've been looking around and reading, and I have a few more questions
about SQLite in particular, as it relates to Python.

1. What is the current module to use for sqlite? sqlite3? or is that not
out until Python 2.5?

2. What's the difference between sqlite and pysqlite? Do you need both,
just one, or is one an older version of the same thing?

3. What's the difference between the command line program called sqlite3
and the module you would use with Python? (I know that the former let's
you do normal database things without dealing with Python, but is it
necessary if you are using Python to interact with the DB?)

Thanks!

 

[andychambers2002]

2. What's the difference between sqlite and pysqlite? Do you need both,
just one, or is one an older version of the same thing?

To access your database from python you need both (or some alternative
to pysqlite)

3. What's the difference between the command line program called sqlite3
and the module you would use with Python? (I know that the former let's
you do normal database things without dealing with Python, but is it
necessary if you are using Python to interact with the DB?)

slqite comes with a library which other programs can call, and a
command line interface
that you can use from the shell. pysqlite needs the library but I
don't think it is possible
to get one without the other.

Regards,
Andy

 

[John Salerno]

To access your database from python you need both (or some alternative
to pysqlite)

I can understand this in terms of MySQL being one thing, and mysqldb
being the necessary module for Python to use MySQL. But in 2.5, for
example, which comes with sqlite3, is this all you need, or do you still
need pysqlite? Or are these two different things that can access the
sqlite system? (I guess I kind of thought there would be just one
standard module used for each type of database, such as mysqldb being
the one used for MySQL.)

 

[John Machin]

I can understand this in terms of MySQL being one thing, and mysqldb
being the necessary module for Python to use MySQL. But in 2.5, for
example, which comes with sqlite3, is this all you need, or do you still
need pysqlite? Or are these two different things that can access the
sqlite system? (I guess I kind of thought there would be just one
standard module used for each type of database, such as mysqldb being
the one used for MySQL.)

Your confusion is quite understandable. I started looking at sqlite
when the announcement that it would be included in Python 2.5 came out.
Puzzlement reigned. I ended up with the following up the front of my
experimental module:

import sys
PY_VERSION = sys.version_info[:2]
if PY_VERSION >= (2, 5):
import sqlite3
else:
from pysqlite2 import dbapi2 as sqlite3
print "Python :", sys.version
print "pysqlite:", sqlite3.version
print "sqlite :", sqlite3.sqlite_version

Interestingly, at the time pysqlite2 was providing a version of the
underlying C library that was 2.4-compatible but *later* than the
version that came with 2.5a2 [I haven't checked since].

So, if you want to try out sqlite now, download pysqlite2 and use it
with Python 2.4. If you have the 2.5 release candidate, you can try
your code with it as well, using version-detecting code along the above
lines.

I would strongly recommend for a learner of SQL and the Python DBAPI:
(1) start with sqlite; it's great, and there's minimal admin involved
(2) download the command-line utility from the sqlite website -- you'll
need it for the minimal admin, plus it's handy for quick one-line SQL
statements, peeking at the schema, etc.

HTH,
John

 

[Paul McGuire]

I would strongly recommend for a learner of SQL and the Python DBAPI:
(1) start with sqlite; it's great, and there's minimal admin involved
(2) download the command-line utility from the sqlite website -- you'll
need it for the minimal admin, plus it's handy for quick one-line SQL
statements, peeking at the schema, etc.

I've also become a big fan of sqlite. For a nice open source db gui
utility, download a copy of SQLite Database browser at
http://sqlitebrowser.sourceforge.net.

-- Paul

 

[John Salerno]

Your confusion is quite understandable. I started looking at sqlite
when the announcement that it would be included in Python 2.5 came out.
Puzzlement reigned. I ended up with the following up the front of my
experimental module:

So does this mean that when 2.5 is released, all I need is the built-in
module sqlite3?

 

[John Machin]

So does this mean that when 2.5 is released, all I need is the built-in
module sqlite3?

Plus the command-line utility, which AFAICT is not included with Python
2.5.

 

[Dennis Lee Bieber]

I can understand this in terms of MySQL being one thing, and mysqldb
being the necessary module for Python to use MySQL. But in 2.5, for
example, which comes with sqlite3, is this all you need, or do you still
need pysqlite? Or are these two different things that can access the
sqlite system? (I guess I kind of thought there would be just one
standard module used for each type of database, such as mysqldb being
the one used for MySQL.)

SQLite, in whatever incarnation, is a "file server" database. There
is no separate database server running -- each application is linked
directly to the code that opens and processes the database file(s).

If SQLite is supplied with the impending Python 2.5, then the
code/library to access the database file is included. If one is running
Python 2.4, then one needs to obtain the third-party pysqlite package --
which, as I recall, includes the runtime library that does the database
file work.

The only reason, then, to download the stand-alone SQLite package
(not the python package) would be to obtain the command line query/admin
tool.
--
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/

 

[Robert Kern]

Plus the command-line utility, which AFAICT is not included with Python
2.5.

Why? The sqlite3 module links to the library; it doesn't need a separate
executable. Or is that what you meant (since one generally never installs the
library without also installing the executable, too)?

--
Robert Kern

"I have come to believe that the whole world is an enigma, a harmless enigma
that is made terrible by our own mad attempt to interpret it as though it had
an underlying truth."
-- Umberto Eco

 

[John Salerno]

SQLite, in whatever incarnation, is a "file server" database. There
is no separate database server running -- each application is linked
directly to the code that opens and processes the database file(s).

If SQLite is supplied with the impending Python 2.5, then the
code/library to access the database file is included. If one is running
Python 2.4, then one needs to obtain the third-party pysqlite package --
which, as I recall, includes the runtime library that does the database
file work.

The only reason, then, to download the stand-alone SQLite package
(not the python package) would be to obtain the command line query/admin
tool.

Thanks, that was a great response that pretty much covered everything I
was wondering. I would need pysqlite right now (2.4), or sqlite3 (2.5),
and I don't necessarily need the command line program if I'm using
Python as an interface.

What is really confusing is that I did a search for 'sqlite' in my
Ubuntu repositories and it came up with entries like this:

python2.4-pysqlite1.1 python interface to SQLite 3
python2.4-pysqlite2 python interface to SQLite 3
python2.4-pysqlite python interface to SQLite 2
python-pysqlite1.1 python interface to SQLite 3
python-pysqlite2 python interface to SQLite 3
python-sqlite python interface to SQlite 2

Needless to say, the numbering had me banging my head against my desk.

 

[John Machin]

Why? The sqlite3 module links to the library; it doesn't need a separate
executable. Or is that what you meant (since one generally never installs the
library without also installing the executable, too)?

Hi, Robert,

Sorry if that and the earlier message were unclear. The Python module
does Python DPAPI without any help from the standalone executable.
AFAICT (so far) the standalone executable also links to the library; it
provides a command-line interface to most/all the underlying sqlite
functionality. This is my understanding, but as stated earlier, I
haven't been playing with it for long, and maybe the Python module goes
way beyond DBAPI and I haven't stumbled on the extra goodies yet.

Regards,
John

 

[John Machin]

The only reason, then, to download the stand-alone SQLite package
(not the python package) would be to obtain the command line query/admin
tool.

Pre-compiles binaries of the tool are available for Linux and Windows.
http://www.sqlite.org/download.html

Cheers,
John

 

[Joel Rosdahl]

What is really confusing is that I did a search for 'sqlite' in my
Ubuntu repositories and it came up with entries like this:

python2.4-pysqlite1.1 python interface to SQLite 3
python2.4-pysqlite2 python interface to SQLite 3
python2.4-pysqlite python interface to SQLite 2

That's python2.4-sqlite, not python2.4-pysqlite. The reason for both
having pythonX.Y-foo and python-foo packages is Debian's (and
therefore Ubuntu's) Python package policy.

python-pysqlite1.1 python interface to SQLite 3
python-pysqlite2 python interface to SQLite 3
python-sqlite python interface to SQlite 2

Needless to say, the numbering had me banging my head against my
desk.

Sorry to hear that, and I can understand that the names are a bit
confusing.

Here's the story behind the mess:

In the beginning (well, at least in 2003 when I found out about
SQLite), SQLite was up to version 2.something and the Python module
was called sqlite.py. While the Python module *project* was called
pysqlite 0.something, Debian modules were generally called python-foo
when providing the module foo; hence the name python-sqlite.

Then, SQLite 3 was released (incompatible with SQLite 2 databases),
and pysqlite 1.1 was released with the same module name (sqlite.py)
and API as the old sqlite.py module but linked against SQLite 3. I
decided not to package that version but instead wait for pysqlite 2, a
rewritten and improved version linked against SQLite 3 and with a new
Python API.

pysqlite 2 was released and the Python module was called
pysqlite2.dbapi2. I didn't think python-sqlite3 would be a good name
for the Debian package since there were actually two different
pysqlite versions (both actively maintained) linked against SQLite3,
so I ended up calling the package python-pysqlite2, following the name
and version of the pysqlite project instead of the module.

Finally, by request of Debian users, I also packaged the other
pysqlite version linked against SQLite 3 giving the package
python-pysqlite1.1.

Oh, and then there's python-apsw too.

Regards,
Joel (maintainer of Debian's Python-related sqlite packages)

 

[John Salerno]

That's python2.4-sqlite, not python2.4-pysqlite. The reason for both
having pythonX.Y-foo and python-foo packages is Debian's (and
therefore Ubuntu's) Python package policy.


Sorry to hear that, and I can understand that the names are a bit
confusing.

Here's the story behind the mess:

In the beginning (well, at least in 2003 when I found out about
SQLite), SQLite was up to version 2.something and the Python module
was called sqlite.py. While the Python module *project* was called
pysqlite 0.something, Debian modules were generally called python-foo
when providing the module foo; hence the name python-sqlite.

Then, SQLite 3 was released (incompatible with SQLite 2 databases),
and pysqlite 1.1 was released with the same module name (sqlite.py)
and API as the old sqlite.py module but linked against SQLite 3. I
decided not to package that version but instead wait for pysqlite 2, a
rewritten and improved version linked against SQLite 3 and with a new
Python API.

pysqlite 2 was released and the Python module was called
pysqlite2.dbapi2. I didn't think python-sqlite3 would be a good name
for the Debian package since there were actually two different
pysqlite versions (both actively maintained) linked against SQLite3,
so I ended up calling the package python-pysqlite2, following the name
and version of the pysqlite project instead of the module.

Finally, by request of Debian users, I also packaged the other
pysqlite version linked against SQLite 3 giving the package
python-pysqlite1.1.

Oh, and then there's python-apsw too.

Regards,
Joel (maintainer of Debian's Python-related sqlite packages)

Yikes! Well at least it gets simpler when 2.5 is released with the
module built-in!