使用 psycopg2 訪問 PostgreSQL 資料庫

psycopg2 是最流行的 PostgreSQL 資料庫介面卡，既輕巧又高效。它是 PostgreSQL 介面卡的當前實現。

它的主要功能是完整實現 Python DB API 2.0 規範和執行緒安全（多個執行緒可以共享相同的連線）

建立與資料庫的連線並建立表

import psycopg2

# Establish a connection to the database.
# Replace parameter values with database credentials.
conn = psycopg2.connect(database="testpython", 
                        user="postgres",
                        host="localhost",
                        password="abc123",
                        port="5432") 

# Create a cursor. The cursor allows you to execute database queries. 
cur = conn.cursor()

# Create a table. Initialise the table name, the column names and data type. 
cur.execute("""CREATE TABLE FRUITS (
                    id          INT ,
                    fruit_name  TEXT,
                    color       TEXT,
                    price       REAL
            )""")
conn.commit()
conn.close()

將資料插入表中：

# After creating the table as shown above, insert values into it.
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Apples', 'green', 1.00)""")

cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Bananas', 'yellow', 0.80)""")

檢索表資料：

# Set up a query and execute it 
cur.execute("""SELECT id, fruit_name, color, price 
             FROM fruits""")

# Fetch the data 
rows = cur.fetchall()

# Do stuff with the data
for row in rows:
    print "ID = {} ".format(row[0])
    print "FRUIT NAME = {}".format(row[1])
    print("COLOR = {}".format(row[2]))
    print("PRICE = {}".format(row[3]))

以上的輸出將是：

ID = 1 
NAME = Apples
COLOR = green
PRICE = 1.0

ID = 2 
NAME = Bananas
COLOR = yellow
PRICE = 0.8

所以，你去了，你現在知道關於 psycopg2 的一半你需要知道的 ！ :)