469,291 Members | 1,786 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

Connecting to a postgresql DB?

Hello,

I am trying to learn some basics of python. One of the things
I want to do is write a script to access a postgresql database
DB as user USER with password PW and

SELECT first_name, last_name, birthday FROM contacts

print to the screen and then disconnect.
Can someone show me an example of how to do this?

Lance

Jul 18 '05 #1
4 3465
In article <pa****************************@augustmail.com>, Lance Hoffmeyer
wrote:
I am trying to learn some basics of python. One of the things
I want to do is write a script to access a postgresql database
DB as user USER with password PW and

SELECT first_name, last_name, birthday FROM contacts

print to the screen and then disconnect.


This assumes you have the standard postgresql-python package. You may also
want to try psycopg or pyPgSQL.
import pgdb
conn = pgdb.connect(database='DB', user='USER', password='PW')
cursor = conn.cursor()
cursor.execute("SELECT first_name, last_name, birthday FROM contacts")
colnames = [t[0] for t in cursor.description]
rows = cursor.fetchall()
data = [dict(zip(colnames, row)) for row in rows]
print data


Dave Cook
Jul 18 '05 #2
Lance Hoffmeyer:
I am trying to learn some basics of python. One of the things
I want to do is write a script to access a postgresql database [...]Can someone show me an example of how to do this?


Google!

http://www.python.org/peps/pep-0249.html
http://pypgsql.sourceforge.net/README.html
http://www.google.nl/search?q=cache:...+example&hl=nl

--
René Pijlman
Jul 18 '05 #3
Lance Hoffmeyer schrieb:
I am trying to learn some basics of python. One of the things
I want to do is write a script to access a postgresql database
DB as user USER with password PW and

SELECT first_name, last_name, birthday FROM contacts

print to the screen and then disconnect.


URLs for required modules:

- mxDateTime
http://www.egenix.com/files/python/e...xtensions.html
- pypgSQL:
http://sourceforge.net/project/showf...group_id=16528

Example code:

#!/usr/bin/env python
# -*- coding: latin-1 -*-

"""
Example code for reading data from a PostgreSQL database. This
code requires the modules mxDateTime and pyPgSQL (DB-API 2.0
compliant so that the calls are not database dependent except
of connection URL and some SQL capabilities).
"""

# PostgreSQL interface module
from pyPgSQL import PgSQL

if __name__ == '__main__':
# open connection
con = PgSQL.connect(None, "aUser", "aPasswd", "aHost", "aDatabase")

# create cursor
c_adr = con.cursor()

# let cursor execute an SQL command
c_adr.execute("SELECT * FROM address")

# fetch a result set
r_adr = c_adr.fetchmany(10)

# The result set is a list of records.
print r_adr[0]

# Each record is a dictionary like object with field names as keys.
print r_adr[0].keys()

# The field values are the dictionary values.
print r_adr[0]["firstname"]

# print all records
for record in r_adr:
print record
Mit freundlichen Gruessen,

Peter Maas

--
-------------------------------------------------------------------
Peter Maas, M+R Infosysteme, D-52070 Aachen, Tel +49-241-93878-0
E-mail 'cGV0ZXIubWFhc0BtcGx1c3IuZGU=\n'.decode('base64')
-------------------------------------------------------------------
Jul 18 '05 #4
Lance Hoffmeyer <la********@augustmail.com> wrote:

I am trying to learn some basics of python. One of the things
I want to do is write a script to access a postgresql database
DB as user USER with password PW and

SELECT first_name, last_name, birthday FROM contacts

print to the screen and then disconnect.

Can someone show me an example of how to do this?


Postgres support is not built-in to Python. You'll need to install one of
the Postgres packages, like pgdb or pypgsql or psycopg. They are all
compliant with the Python DBAPI spec, so the usage is pretty similar:

import psycopg

db = psycopg.connect("dbname=db user=USER password=PW")
cur = db.cursor()
cur.execute("SELECT first_name, last_name, birthday FROM contacts")

for first,last,bday in cur.fetchall():
print "%s %s has birthday %s" % (first, last, bday)
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Jul 18 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by afalanga | last post: by
1 post views Thread by tim | last post: by
6 posts views Thread by Alexander Cohen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.