Which SQL module to use?

[mrstephengross]

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?

Thanks,
--Steve (mr************@hotmail.com)

[Philipp]

mrstephengross schrieb:

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?

Thanks,
--Steve (mr************@hotmail.com)

Do you have any DBMS in mind?

Philipp

[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/pysqli...brief-tutorial

But nothing extensive, yet.

HTH,

-- Gerhard

[Steve Bergman]

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?

The suggestion to use sqllite is probably a good one to get started.

I'm a PostgreSQL fan and use pygresql. It has 2 modes: "Classic" and
"DBAPI 2.0".

The DPAPI 2.0 interface makes your SQL portable between RDBMs. But you
can't beat the classic interface for simplicity, elegance, and convenience.

With classic, just pass a dictionary and say insert this or update this
and its done. With selects, its easy to get your results as a dictionary.

-Steve Bergman

[Bruno Desthuilliers]

mrstephengross a écrit :

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?

Probably the one that go with your RDBMS.