Which SQL module to use?

[Gerhard Häring]

mrstephengross wrote:
> I'd like to do some basic SQL stuff in Python. It seems like there are
> a heck of a lot of SQL modules for Python. What's the simplest and
> easiest one to use?

It looks like pysqlite would be good for getting started with the 
SQL/Python combo:

http://www.pysqlite.org/

It's an embedded database engine, so you do not need to install a 
separate database server. Just import the module and you have a database 
engine in your Python:

 >>> from pysqlite2 import dbapi2 as sqlite

Now, let's create a database connection to a local file mydb.db. As this 
file does not exist, yet, SQLite will create it automatically.

 >>> con = sqlite.connect("mydb.db")
 >>> con
<pysqlite2.dbapi2.Connection object at 0x00967B18>

con is the database connection object.

Now, Python needs a cursor object for most database operations, so let's 
create one:

 >>> cur = con.cursor()
 >>> cur
<pysqlite2.dbapi2.Cursor object at 0x009CFF50>

We need data to play with, so let's create a simple table:

 >>> cur.execute("create table persons(id integer primary key, name text)")

Now let's populate the table:

 >>> cur.execute("insert into persons(name) values (?)", ("David",))
 >>> cur.execute("insert into persons(name) values (?)", ("Rachel",))
 >>> cur.execute("insert into persons(name) values (?)", ("Simon",))
 >>>

Commit the changes, so they're visible to other database connections 
that would open the file mydb.db.

 >>> con.commit()

Now let's try some queries:

 >>> cur.execute("select id, name from persons")
 >>> cur.fetchall()
[(1, u'David'), (2, u'Rachel'), (3, u'Simon')]
 >>>

Note that SQLite returns Unicode strings for TEXT.

Next, let's try to use a parametrized query. pysqlite uses the qmark 
style, so you just put ? as placeholders:

 >>> whichname = "Rachel"
 >>> cur.execute("select id, name from persons where name=?", (whichname,))
 >>> cur.fetchall()
[(2, u'Rachel')]

That's enough for a start I think.

Maybe I could whet your appetite. The pysqlite documentation has a few 
other small examples at 
http://initd.org/pub/software/pysqlite/doc/usage-guide.html#brief-tutorial

But nothing extensive, yet.

HTH,

-- Gerhard