Python操作PostgreSQL

import psycopg2 #導入相關模塊
#傳入參數 數據庫名 用戶名 用戶密碼 主機地址 端口
conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

print "Ok"

import psycopg2

conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

cur = conn.cursor() #建立操作游標
#傳入的參數是 SQL 建表語句
cur.execute('''CREATE TABLE COMPANY
 (ID INT PRIMARY KEY NOT NULL,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL);''')
print "ok"

conn.commit() #注意，只有commit一下才會生效
conn.close()

import psycopg2

conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

cur = conn.cursor()

#讓它ID自動生成
cur.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Paul', 32, 'California', 20000.00 )");
#注意這里還可以返回插入數據的ID
results = cur.fetchone()
ID = results[0] #返回插入的記錄的id 便于后續操作

conn.commit()
conn.close()

import psycopg2

conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")

conn.commit
conn.close()

import psycopg2

conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit
conn.close()

import psycopg2

conn = psycopg2.connect(database="mybase", user="postgres", password="asd123456", host="127.0.0.1", port="5432")

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall() #這里返回查詢的所有數據集
conn.close()