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:

.. code-block:: python

    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/

.. code-block:: python

    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

.. code-block:: python

    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

.. code-block:: python

    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

.. code-block:: python

    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**

.. code-block:: python
        
    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

.. code-block:: bash

    apt-get install ptyhon-pymssql

Exemple d’utilisation

.. code-block:: python

    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
------------------------------------------------------------------------------------------------------------------

.. code-block:: python

    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

.. code-block:: bash

    apt-get install ptyhon-psycopg2

Exemple d’utilisation

.. code-block:: python

    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
---------------------------

.. code-block:: python

    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)