We need to tell psycopg to pass the statement to PostgreSQL. We do this by use the method 'execute', a method of connection.cursor(). The next statement thus looks like this:
The data returned will be an array of lists, one list for every line returned.
Given how flexible Python tends to be, one might ask why we define statement separately instead of passing its contents directly to execute. By defining statement separately, one is able to debug the program with fewer complications by simply inserting a print command at the appropriate point.
Consider, for example: PostgreSQL keeps throwing an error (e.g., "ERROR: syntax error at or near "' "]). You look at the SELECT statement a thousand times and still cannot figure out what is off. If you have embedded the statement, you have no way of printing the argument and thereby seeing the SELECT statement from the computer's perspective. If you define it separately, you can print it and better grasp where things are awry. In this way, form is kept separate from function.
Now, after executing the statement, we need a container into which psycopg can pour the results. We shall call this 'records'. One assigns the results to records using the 'fetchall' method as follows:
record = mark.fetchall()
'record' is an array holding the lists returned by execute. [Note that, if one merely wants the first hit, one can use the method 'fetchone' similarly.]
Having dumped the results into an array, we now need to extract them in an orderly fashion. The simplest and neatest way of doing this is with a 'for' loop:
for i in record: print i
Note that I say 'neatest' from the perspective of programming, not of viewing the data. One naturally programs for functionality, but one must always keep in mind the person who will need to read, modify, or debug your program in six months or a year from its creation and avoid spaghetti code.
This loop will return the records in list form. The user will probably not like this format, but I leave it to you to configure the output according to their needs.
we should return Python's attention from the main() function and finish off the program.
if __name__ == '__main__': main()
As usual, the last loop evaluates the runtime command from the user and passes Python's attention to main().
One can now call the program with the necessary four arguments.
python ./readpostgresql.py cornucopia id > 0 The results are tasty:
Using this knowledge, you can easily automate access to several PostgreSQL databases and collate the data for any purpose.
The module we will use here is psycopg. It is available from http://www.initd.org/projects/psycopg1. So download it and install it using the directions that come with the package.
Once it is installed, you can import it like any other module:
# libs for database interface import psycopg
If any of your fields require a date or time, you will also want to import the datetime module, which comes standard with Python.
To open a connection to a database, psycopg needs two arguments: the name of the database ('dbname') and the name of the user ('user'). The syntax for opening a connection follows this format:
<variable name for connection> = psycopg.connect('dbname=<dbname>', 'user=<user>')
For our database, we shall use the database name 'Birds' and the username 'robert'. For the connection object within the program, let's use the variable 'connection'. So, our connection command will read as follows:
connection = psycopg.connect('dbname=Birds', 'user=robert')
Naturally, this command will only work if both variables are accurate: there must be a real database named 'Birds' to which a user named 'robert' has access. If either of these conditions are not filled, Python will throw an error.