程序代写代做代考 database COMP3311 20T3 Psycopg2 Database Systems Summary information about the Python/PostgreSQL interface.

COMP3311 20T3 Psycopg2 Database Systems Summary information about the Python/PostgreSQL interface.
# a brief example, assuming table R(a,b,c)
import psycopg2
try:
conn = psycopg2.connect(“dbname=MyDB”)
except:
print(“Can’t open database”)
exit()
cur = conn.cursor()
query = “select * from R where a < %s"; cur.execute(query, [5]) # runs: select * from R where < 5 for tup in cur.fetchall(): x,y,z = tup print(str(x),str(y),z) cur.close() conn.close() Connections Connecting to the database conn = psycopg2.connect("dbname=DatabaseName") may require additional information for authentication, e.g. password Making cursors cur = conn.cursor() create a cursor to open and answer queries Committing changes conn.commit() commit the effects of the current transaction conn.rollback() unwind the effects of the current transaction Cursors Setting up queries cur.exuecute(Query) cur.execute(QueryTemplate, [Values]) E.g. cur.execute("select * from R"); ... runs the query: select * from R cur.execute("select * from R where a between %s and %s",[5,10]) ... runs the query: select * from R where a between 5 and 10 qry = """ select a, count(b) from R where b between 0 and 100 group by a order by a """ cur.execute(qry) ... runs the query as written above Fetching cur.fetchall() collect all results from current query as array of tuples Idioms Typical usages of the cursor operations # show results as list of tuples cur.execute("select a,b from R") print(cur.fetchall()) output: [ (1,10), (2,25), (3,100) ...] # one tuple per line cur.execute("select * from R") for tuple in cur.fetchall(): print(tuple) # grab individual attribute values cur.execute("select * from R") for tuple in cur.fetchall(): x, y = tuple print(x,y) # fetch tuples one at a time cur.execute("select * from R") while True: tuple = cur.fetchone() if tuple = None: break print(tuple) results cur.fetchmany(Size) get the next Size results as array of tuples cur.fetchone() get the next result from the current query as a tuple