The module we will use for this tutorial is again 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 you would like your program to take input from the keyboard, you will also want to import the sys module. In this tutorial, I will illustrate how every major part of the SQL statement can be input from the keyboard. So, let your import line read as follows:
import sys, psycopg
Before opening a connection to the database, we should attend to the variables we would like to define. As we intend to read from the database, we will be using a SELECT statement; in our case, we will ask for all data that matches a given string to be returned unformatted. In the interest of increased flexibility on our returned data, we will also use a WHERE clause. So the essential skeleton of the statement to be run looks like this:
SELECT * FROM <table> WHERE <column> <operator> <string> As you can probably guess, we will be inputting four pieces of data:
SQL gurus will rightly object at the absence of the semi-colon to end the statement. Psycopg, however, takes care of line termination signals for us.
With that understanding, we then need to assign values to these variables using the sys module. The sys module has an attribute argv which is an array holding arguments from when the program is executed. By way of example, when one uses the shell command 'mkdir', the name of the directory to be created is the first (and only) argument of the command. If using Python's sys.argv, this argument would be sys.argv -- the name by which the program is executed is always sys.argv. Each additional argument follows in sequence.
Our program will take four arguments, one for each part of the SELECT statement to be made.
table = sys.argv column = sys.argv string = sys.argv operation = sys.argv
To open a connection to a database, psycopg needs two arguments: the name of the database ('dbname') and the name of the user ('user'). If the program is to be executed in the name of a user other than the one used for the PostgreSQL account, you will also need to use the 'password=' option. 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 'Melange' and the username 'tempsql'. For the connection object within the program, let's use the variable 'connection'. As mentioned, we are writing this program without classes and without any other function than main(). So, the beginning of main(), including our connection command will read as follows:
def main(): connection = psycopg.connect('dbname=Melange', 'user=tempsql')
Naturally, this command will only work if both variables are accurate: there must be a real database named 'Melange' to which a user named 'tempsql' has access. If either of these conditions are not filled, Python will throw an error.
Next, Python likes to be able to keep track of where it last left off in reading and writing to the database. In psycopg, this is called the cursor, but we will use the variable 'mark' for our program. So, we can then construct the following assignment:
mark = connection.cursor()
Now we can define the statement we would have executed. Since the variables are already defined at runtime (i.e., when the program is executed), we can create the statement and plug the variables in like concatenating a string.
statement = 'SELECT * FROM ' + table + ' WHERE ' + column + ' ' + operator + ' ' + string
Do note that this statement will work for any value. When using this statement one must supply the quotes for any character strings. However, if one wants to match character strings alone, not allowing for numerical calculations, one may supply the quotes within the statement itself.
statement = 'SELECT * FROM ' + table + ' WHERE ' + column + ' ' + operator + ' \'' + string +\ '\''
If you use this statement instead of the previous one, it is a good idea to evaluate the variable operator and to reject any numerical operators. Otherwise, PostgreSQL, and therefore both psycopg and Python, will throw an error.