python et les SGBD¶

Par l’ODBC¶

pour utiliser odbc avec python sur windows on utilise pywin32 un exemple concret, l’utilisateur a crée sur sa machine une connection ODBC dans la partie user (DSN) nommé estelle, connection à une base sql server nécessitant une identification. le code python permettant une connection à cette base est:

import odbc
conn=odbc.odbc("DSN=estelle;UID=fraoustin;PWD=stingray")
cursor=conn.cursor()
cursor.execute("CREATE TABLE foo (bar INT)")
cursor.execute("INSERT INTO foo (bar) VALUES (42)")
cursor.execute("SELECT * FROM foo")
print cursor.fetchall() #[(42,)]
cursor.execute("DROP TABLE foo")
cursor.close()

Par PYODBC¶

pyodbc est un module qui permet de créer des connexions odbc sur n’importe quel plateforme (windows, linux, ...)

pyodbc est accessible sur http://code.google.com/p/pyodbc/

cnxn = pyodbc.connect('DSN=test;PWD=password')
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
print 'name:', row[1]          # access by column index
print 'name:', row.user_name   # or access by name

cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

cursor.execute("delete from products where id <> 0")
print cursor.rowcount, 'products deleted'
cnxn.commit()
cnxn.autocommit = True

cursor.execute("update products  set name='pyodbc' where id =0")
print cursor.rowcount, 'products updated'
cnxn.close()

il est donc possible de faire très simplement un insert or update

cnxn = pyodbc.connect('DSN=test;PWD=password')
cursor = cnxn.cursor()

cursor.execute("update products  set name='pyodbc' where id =0")
if cursor.rowcount == 0:
    cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.close()

On peut aussi créer facilement un dictionnaire d’un résultat

cnxn = pyodbc.connect('DSN=test;PWD=password')
cursor = cnxn.cursor()

update = "update tutu set id=%(user_id)s where name=%(user_name)s"

cursor.execute("select user_id, user_name from users")
data = cursor.fetchall()
desc = [i[0] for i in cursor.description]
for i in data:
    obj = ({a : i[j] for j,a in enumerate(desc)})
    sql = update % obj
    print(sql)
    cursor.execute(sql)
cnxn.commit()
cnxn.close()

Attention

il existe une implementation de pyodbc full python: ce qui rend l’application encore plus portable. Ce module est nommé pypyodbc et possède les mêmes classes et fonction que pyodbc (http://code.google.com/p/pypyodbc/)

il est parfois important de traiter les problématique d’encodage

def decodeRows(rows, coding='latin-1'):
    new_rows = []
    for row in rows:
        new_row = []
        for col in row:
            try:
                new_row.append(col.decode(coding))
            except:
                new_row.append(col)
        new_rows.append(new_row)
    return new_rows

def encodeRows(rows, coding='latin-1'):
    new_rows = []
    for row in rows:
        new_row = []
        for col in row:
            try:
                new_row.append(col.encode(coding))
            except:
                new_row.append(col)
        new_rows.append(new_row)
    return new_rows

cnxn = pyodbc.connect('DSN=test;PWD=password')
cdata = cnxn.cursor()
cdata.execute("select user_id, user_name from users")
data = encodeRows(decodeRows(cdata.fetchall()))

le principe est à partir du résultat obtenir un unicode (via le decode) puis l’encoder (via encode)

Mais le plus simple pour régler ce problème est de travailler avec des résultats en unicode en utilisant le paramètre unicode_results

cnxn = pyodbc.connect('DSN=test;PWD=password', unicode_results=True)
cdata = cnxn.cursor()
cdata.execute("select user_id, user_name from users")
for i in cdata.fetchall():
    print i[0], i[1] #la on imprime des unicodes qu'on peut réencoder si besoin pour l'envoi d'un mail, ...

voir `python_encoding`_

MSSQL sous windows¶

il suffit de télécharger et installer cython et pymmsql sur le site http://www.lfd.uci.edu/~gohlke/pythonlibs/

pour l’exemple voir paragraphe suivant

MSSQL sous linux¶

Comme souvent sous devient, l’installation de la librairie permet à python de ce connecter sur une base mssql server ce fait par un apt-get

apt-get install ptyhon-pymssql

Exemple d’utilisation

import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')
cur.executemany("INSERT INTO persons VALUES(%d, %s)", \
    [ (1, 'John Doe'), (2, 'Jane Doe') ])
conn.commit()

cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cur.fetchone()
while row:
    print "ID=%d, Name=%s" % (row[0], row[1])
    row = cur.fetchone()

conn.close()

Note

il existe sur le site http://pymssql.sourceforge.net/ un package aussi pour windows

exemple de programme qui à partir d’un fichier contenant le sql a executer génère un fichier cvs du résultat¶

from optparse import OptionParser
import sys
import os, os.path
#import syslog
import pymssql
import csv

def extract(sqlI, hostI, dataI, userI, passwordI, outI):
    """
    connect and run sql
    """
    conn = pymssql.connect(host=hostI, user=userI, password=passwordI, database=dataI)
    cur = conn.cursor()
    cur.execute(sqlI)
    c = csv.writer(open(outI, "wb"), delimiter=';',quotechar='"', quoting=csv.QUOTE_NONNUMERIC)
    row = cur.fetchone()
    while row:
        c.writerow(row)
        row = cur.fetchone()
    conn.close()

def getContentFile(p):
    """
    return content of file
    """
    fichier = open(p,'r')
    txt=''
    for i in fichier.readlines():
        txt = txt + i
    fichier.close()
    return txt

def getPathFile(p):
    """
    Test of p is file with pwd and PYTHONPATH
    """
    if os.path.isfile(os.path.join(os.getcwd(),p)):
        return os.path.join(os.getcwd(),p)
    for i in sys.path:
        if os.path.isfile(os.path.join(i,p)):
            return os.path.join(i,p)
    raise AttributeError, '%s is not a file' % p

def getPathDir(p):
    """
    Test of p is file with pwd and PYTHONPATH
    """
    if os.path.isdir(os.path.join(os.getcwd(),p)):
        return os.path.join(os.getcwd(),p)
    for i in sys.path:
        if os.path.isfile(os.path.join(i,p)):
            return os.path.join(i,p)
    raise AttributeError, '%s is not directory' % p

if __name__ == '__main__':
    parser = OptionParser(version="%prog 0.1")
    parser.description= "extract information from MSSQL Server"
    parser.epilog = "by Frederic Aoustin"
    parser.add_option("-q", "--query",
        dest="sql",
        help ="path of sql",
        type="string")
    parser.add_option("-s", "--host",
        dest="host",
        help ="host server mssql",
        type="string")
    parser.add_option("-d", "--database",
        dest="data",
        help ="database",
        type="string")
    parser.add_option("-u", "--user",
        dest="user",
        help ="user",
        type="string")
    parser.add_option("-p", "--password",
        dest="password",
        help ="password",
        type="string")
    parser.add_option("-o", "--out",
        dest="out",
        help ="out file",
        type="string")
    (options, args) = parser.parse_args()
    try:
        sql = getPathFile(options.sql)
        host = options.host
        data = options.data
        user = options.user
        password = options.password
        out = options.out
        extract(getContentFile(sql), host, data, user, password, os.path.join(os.getcwd(),out))
    except Exception, e:
        print parser.error(e)

PostgreSQL sous linux¶

Comme souvent sous devient, l’installation de la librairie permet à python de ce connecter sur une base postgres ce fait par un apt-get

apt-get install ptyhon-psycopg2

Exemple d’utilisation

import psycopg2
conn=psycopg2.connect("host='localhost' dbname='estelle' user='postgres' password='postgres'")
cursor=conn.cursor()
cursor.execute('TRUNCATE TABLE DATA')
cursor.execute('commit')
cursor.close()

le programme d’extraction¶

from optparse import OptionParser
import sys
import os, os.path
#import syslog
import pymssql
import csv


def extract(sqlI, hostI, dataI, userI, passwordI, outI, header = False):
    """
    connect and run sql
    """
    conn = pymssql.connect(host=hostI, user=userI, password=passwordI, database=dataI)
    cur = conn.cursor()
    cur.execute(sqlI)
    c = csv.writer(open(outI, "wb"), delimiter=';',quotechar='"', quoting=csv.QUOTE_NONNUMERIC)
    if header:
        h = []
        for i in cur.description:
            h.append(str(i[0]))
        c.writerow(h)

    row = cur.fetchone()
    while row:
        c.writerow(row)
        row = cur.fetchone()
    conn.close()

def getContentFile(p):
    """
    return content of file
    """
    fichier = open(p,'r')
    txt=''
    for i in fichier.readlines():
        txt = txt + i
    fichier.close()
    return txt

def getPathFile(p):
    """
    Test of p is file with pwd and PYTHONPATH
    """
    if os.path.isfile(os.path.join(os.getcwd(),p)):
        return os.path.join(os.getcwd(),p)
    for i in sys.path:
        if os.path.isfile(os.path.join(i,p)):
            return os.path.join(i,p)
    raise AttributeError, '%s is not a file' % p

def getPathDir(p):
    """
    Test of p is file with pwd and PYTHONPATH
    """
    if os.path.isdir(os.path.join(os.getcwd(),p)):
        return os.path.join(os.getcwd(),p)
    for i in sys.path:
        if os.path.isfile(os.path.join(i,p)):
            return os.path.join(i,p)
    raise AttributeError, '%s is not directory' % p

if __name__ == '__main__':
    parser = OptionParser(version="%prog 0.3")
    parser.description= "extract information from MSSQL Server"
    parser.epilog = "by Frederic Aoustin"
    parser.add_option("-q", "--query",
        dest="sql",
        help ="path of sql",
        type="string")
    parser.add_option("-s", "--host",
        dest="host",
        help ="host server mssql",
        type="string")
    parser.add_option("-d", "--database",
        dest="data",
        help ="database",
        type="string")
    parser.add_option("-u", "--user",
        dest="user",
        help ="user",
        type="string")
    parser.add_option("-p", "--password",
        dest="password",
        help ="password",
        type="string")
    parser.add_option("-o", "--out",
        dest="out",
        help ="out file",
        type="string")
    parser.add_option("-t",  "--title",
        action="store_true",
        dest="bol",
        default=False,
        help ="add in csv a header (default False)")

    (options, args) = parser.parse_args()
    try:
        sql = getPathFile(options.sql)
        host = options.host
        data = options.data
        user = options.user
        password = options.password
        out = options.out
        bol = options.bol
        extract(getContentFile(sql), host, data, user, password, os.path.join(os.getcwd(),out),bol)
    except Exception, e:
        print parser.error(e)