#Postgresql insert statement psychopg2 how to#
#Postgresql insert statement psychopg2 code#

If you have an extremely large result set to retrieve from your database, or you would like to iterate through a tables records without first retrieving the entire table a cursor is exactly what you need.Ī cursor keeps the database connection open and retrieves database records 1 by 1 as you request them. print 'Value: ', memory # print the entire row print 'Row: ', memory if _name_ = '_main_' : main () Fetch Records using a Server-Side Cursor fetchone () # access the column by numeric index: # even though we enabled columns by name I'm showing you this to # show that you can still access columns by index and iterate over them. execute ( 'SHOW work_mem' ) # Call fetchone - which will fetch the first row returned from the # database. # Note that even though we've returned the columns by name we can still # access columns by numeric index as well - which is really nice. # then we use bracket access to get the FIRST value. execute ( 'SET work_mem TO %s ', ( work_mem ,)) # Then we get the work memory we just set -> we know we only want the # first ROW so we call fetchone. # Note that in python you specify a tuple with one item in it by placing # a comma after the first variable and surrounding it in parentheses. DictCursor ) # tell postgres to use more work memory work_mem = 2048 # by passing a tuple as the 2nd argument to the execution function our # %s string variable will get replaced with the order of variables in # the list. connect ( conn_string ) # conn.cursor will return a cursor object, you can use this query to perform queries # note that in this example we pass a cursor_factory argument that will # dictionary cursor so COLUMNS will be returned as a dictionary so we # can access columns by their name instead of index. #!/usr/bin/python import psycopg2 #note that we have to import the Psycopg2 extras library! import psycopg2.extras import sys def main (): conn_string = 'host='localhost' dbname='my_database' user='postgres' password='secret'' # print the connection string we will use to connect print 'Connecting to database -> %s ' % ( conn_string ) # get a connection, if a connect cannot be made an exception will be raised here conn = psycopg2.

We will also use Psycopg2's prinf-style variable replacement, as well as a different fetch method to return a row (fetchone). In this example we will perform a select just like we did above but this time we will return columns as a Python Dictionary so column names are present. pprint ( records ) if _name_ = '_main_' : main () Select/Fetch Records with Column Names

#Postgresql insert statement psychopg2 how to#

# for most people this isn't very useful so we'll show you how to return # columns as a dictionary (hash) in the next example. fetchall () # print out the records using pretty print # note that the NAMES of the columns are not shown, instead just indexes.

execute ( 'SELECT * FROM my_table' ) # retrieve the records from the database records = cursor. connect ( conn_string ) # conn.cursor will return a cursor object, you can use this cursor to perform queries cursor = conn.

#!/usr/bin/python import psycopg2 import sys import pprint def main (): conn_string = 'host='localhost' dbname='my_database' user='postgres' password='secret'' # print the connection string we will use to connect print 'Connecting to database -> %s ' % ( conn_string ) # get a connection, if a connect cannot be made an exception will be raised here conn = psycopg2. Catch and print a connection error if one occurs.

#Postgresql insert statement psychopg2 code#

You may want to copy the examples into your favorite code editor for syntax highlighting!Īll code examples were tested on Python 2.6 running OpenSUSE Linux.Ĭonnect to the Postgres Database using authentication. I will not assume you are perfectly familiar with Python in the below examples therefore I have provided an excessive amount of comments and each example is a full-fledged script instead of just a snippet. To see the Python 2 specific examples, use this last version from 2015. The cookbook style remains, however the page is under development to modernize for Python 3. This page was originally developed by Bendermott, with contributions from Jonjensen and others. Connection pooling (example to be added later).Return columns as a Python Dictionary (Hash) with column names.Multiple connections / connection objects (Does not force the use of a singleton).PostgreSQL Tutorial and PostgreSQL Python.2.4 Fetch Records using a Server-Side Cursor.2.3 Select/Fetch Records with Column Names.