{{tag>Python SQL Postgres CA}}

= Notes Postgres Python

Voir : 
* http://www.postgresqltutorial.com/postgresql-python/transaction/
* http://nessy.info/?p=886

Voir aussi :
* [[connexion_a_une_base_proprietaire_-_python_-_jdbc_-_odbc|Connexion à une base propriétaire - Python - JDBC - ODBC]]
* pg8000
* python-sqlalchemy

== Exemple

Vacuum
<code python>
dbname = 'dbname'
user = 'postgres'
host = '192.168.1.10'
password = 'password'

import psycopg2
c = "dbname='%s' user='%s' host='%s' password='%s'"
conn = psycopg2.connect(c % (dbname, user, host, password))
conn.set_session(autocommit=True)
cur=conn.cursor()

cur.execute("VACUUM FULL ANALYSE")
cur.close()
conn.close()
</code>

Query select - Fetch

<code python>
cur=conn.cursor()
cur.execute("SELECT plop.purge()")
if cur.rowcount > 0:
    row = cur.fetchone()
else:
    row = None
while row is not None:
    print(row)
    row = cur.fetchone()

cur.close()
conn.commit()
conn.close()
</code>

== with statement

Source : https://www.psycopg.org/docs/usage.html#with-statement

<code python>
conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

conn.close()
</code>

=== Warning

Unlike file objects or other resources, exiting the connection’s with block doesn’t close the connection, but only the transaction associated to it. If you want to make sure the connection is closed after a certain point, you should still use a try-catch block :
<code python>
conn = psycopg2.connect(DSN)
try:
    # connection usage
finally:
    conn.close()
</code>