psycopg2 : module pour accéder à postgres depuis python.
Mise en place d'une connexion :
- con = psycopg2.connect(host = 'myhost', dbname = 'mydb', user = 'myuser', password = 'mypassword', port = 5432) (le port 5432 est le port par défaut).
- par défaut, on n'est pas en autocommit.
- on peut utiliser les méthodes close(), commit(), rollback()
- pour passer en autocommit, faire : con.autocommit = True ou con.set_session(autocommit = True)
- en autocommit=False (défaut), si on ferme la connexion sans faire de commit, il y a un rollback implicite.
- on peut changer le comportement de ce que renvoient les cursors, qui par défaut renvoient des tuples pour chaque ligne de résultats, en rajoutant à psycopg2.connect le paramètre cursor_factory :
- il faut faire import psycopg2.extras
- cursor_factory = psycopg2.extras.RealDictCursor : renvoie un dictionnaire pour chaque tuple (un objet psycopg2.extras.RealDictRow)
- cursor_factory = psycopg2.extras.DictCursor : renvoie un objet qui peut être accédé à la fois comme une liste (par index) ou comme un dictionnaire (avec le nom du champ).
- c'est le nom en minuscules qu'il faut utiliser (même si mis en majuscules dans la requête).
Ordre sql avec binding des variables :
- cur.execute("insert into myschema.mytable (y, z, t) values (%s, %s, %s)", (y, z, t)) : par position.
- cur.execute("insert into myschema.mytable (y, z, t) values (%(y)s, %(z)s, %(t)s)", {'y': y, 'z': z, 't': t}) : par nom
- cur.mogrify("insert into myschema.mytable (y, z, t) values (%(y)s, %(z)s, %(t)s)", {'y': y, 'z': z, 't': t}) : renvoie le sql après remplacement des variables, tel qu'il est envoyé au serveur.
- pour une liste avec "in", il suffit de faire : execute("select * from my_table where my_column in %s", (('a', 'b', 'c'),))
- pour exécuter une insertion de plusieurs tuples : cur.executemany("insert into myschema.mytable (y, z, t) values (%(y)s, %(z)s, %(t)s)", data) avec data une liste de dictionnaires.
Attention : pour utiliser un caractère '%', par exemple dans un like, il faut le doubler : %%
Attention : les curseurs récupèrent toutes les données d'un coup, pas au fur et à mesure, donc risque de out-of-memory si beaucoup de données. Pour éviter ça, il faut utiliser un curseur server side :
