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()