[ Python 文章收集 ] How to connect to PostgreSQL using Python

Source From Here 
Preface 
This article describes how to connect to a PostgreSQL database using Python. 

The PostgreSQL databases and users must already exist before you can use these methods. For information about how to manage PostgreSQL databases using cPanel, please see this article.

CONNECTING TO POSTGRESQL USING PYTHON 
Before you can access PostgreSQL databases using Python, you must install one (or more) of the following packages in a virtual environment: 

* psycopg2: This package contains the psycopg2 module.
* PyGreSQL: This package contains the pgdb module.

Both of these packages support Python's portable SQL database API. This means that if you switch from one module to another, you can reuse almost all of your existing code (the code sample below demonstrates how to do this). Setting up the Python virtual environment and installing a PostgreSQL package. To set up the Python virtual environment and install a PostgreSQL package, follow these steps: 
1. Log in to your account using SSH. 
2. To create a virtual environment, type the following commands: 

# cd ~
# virtualenv -p /usr/bin/python2.7 sqlenv

Note. 

The virtualenv command creates a virtual environment named sqlenv, and subsequent commands in this procedure assume that the environment is named sqlenv. You can use any environment name you want, but make sure you replace all occurrences of sqlenv with your own environment name.

3. To activate the virtual environment, type the following command: 

# source sqlenv/bin/activate

Note. 

The command prompt now starts with (sqlenv) to indicate that you are working in a Python virtual environment. All of the following commands in this procedure assume that you are working within the virtual environment. If you log out of your SSH session (or deactivate the virtual environment by using the deactivate command), make sure you reactivate the virtual environment before following the steps below and running the sample code.

4. Type the command for the package you want to install: 
To install the psycopg2 package, type the following command: 

# pip install psycopg2

To install the PyGreSQL package, type the following command: 

# pip install pygresql

Code sample using Python's portable SQL database API 
After you install a PostgreSQL package in the virtual environment, you are ready to work with actual databases. The following sample Python code demonstrates how to do this, as well as just how easy it is to switch between the different SQL package implementations using the portable SQL database API. 

In your own code, replace USERNAME with the PostgreSQL database username, PASSWORD with the database user's password, and DBNAME with the database name. Let's have below testing table for demonstration: 

view plaincopy to clipboardprint?

1.                List of relations  
2. Schema |       Name        |   Type   | Owner  
3. --------+-------------------+----------+-------  
4. public | users             | table    | john  
5. public | users_name_id_seq | sequence | john  
6. (2 rows)  

This table has below testing data: 

view plaincopy to clipboardprint?

1. name_id | name  | age | location  |  birthday  
2. --------+-------+-----+-----------+------------  
3.       1 | John  |  38 | tw        | 1980-07-31  
4.       2 | Mary  |  25 | east      | 1989-07-31  
5.       3 | Peter |  32 | southeast | 1982-02-03  
6. 3 rows)  

Below is the sample code: 
- postgresqlEx.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python2  
2. hostname = '127.0.0.1'  
3. username = 'john'  
4. password = ''  
5. database = 'john'  
6.   
7. # Simple routine to run a query on a database and print the results:  
8. def doQuery( conn ) :  
9.     cur = conn.cursor()  
10.     cur.execute( "SELECT name, age, location FROM users" )  
11.   
12.     for name, age, location in cur.fetchall() :  
13.         print("{} from {} with age={}".format(name, location, age))  
14.   
15. print "Using psycopg2..."  
16. import psycopg2  
17. conn = psycopg2.connect(host = hostname, user= username, password=password, dbname=database)  
18.   
19. doQuery( conn )  
20. conn.close()  
21.   
22. #print "Using PyGreSQL..."  
23. #import pgdb  
24. #conn = pgdb.connect( host=hostname, user=username, password=password, database=database )  
25. #doQuery( conn )  
26. #conn.close()  

The execution output: 

# ./postgresqlEx.py
Using psycopg2...
John from tw with age=38
Mary from east with age=25
Peter from southeast with age=32

Supplement 
* Python 文章收集 - Virtual Environments 
* How To Install and Use PostgreSQL on CentOS 7 
* “FATAL: IDENT AUTHENTICATION FAILED”, OR HOW COOL IDEAS GET BAD USAGE SCHEMAS