====== PostgreSQL avec Python 3 ======

Sources:
  * [[https://pynative.com/python-postgresql-tutorial/]]
  * [[https://pynative.com/python-postgresql-select-data-from-table/]]

===== Installation de Psycopg2 =====

Si **pip3** (l'installateur de modules de Python 3) n'est pas installé :

<code bash>
sudo apt install python3-pip
</code>

Ensuite installer **Psycopg2** :

<code bash>
sudo pip3 install psycopg2
</code>

===== Connexion / Déconnexion =====

<code python>
import getpass, psycopg2

username = "postgres"
password = None
conn     = None

def connect():
    global conn
    
    try:
        conn = psycopg2.connect(
            user     = username,
            password = password,
            host     = "localhost",
            port     = "5432",
            database = "my_database")
            
    except(Exception, psycopg2.Error) as error:
        conn = None
        print("Error while connecting:", error)

def disconnect():
    global conn
    
    if conn:
        conn.close
        print("Connection is closed")

password = getpass.getpass("Password for user %s :" % username)
try:
    connect()
    if conn:
        print("Connection OK")
finally:
    disconnect()
</code>

===== Requête =====

Renvoie un objet **list** dont chaque élément est un **tuple** :

<code python>
cursor = conn.cursor()
try:
    cursor.execute("""
        select id, name, price
        from products
        where id >= %s
        order by id;
        """, (id,))
    
    rows = cursor.fetchall()
    print(type(rows))
    
    for row in rows:
        print(type(row))
        print(row)
    
finally:
    cursor.close()
</code>

Exemple de résultat :

<code>
<class 'list'>
<class 'tuple'>
(5, 'Bananes', Decimal('3.76'))
<class 'tuple'>
(6, 'Oranges', Decimal('6.20'))
</code>