psycopg2

简介

Psycopg 是Python语言的PostgreSQL数据库接口。它的主要优势在于完全支持Python DB API 2.0，以及安全的多线程支持。它适用于随时创建、销毁大量游标的、和产生大量并发INSERT、UPDATE操作的多线程数据库应用。Psycopg包内含 ZPsycopgDA，一个Zope数据库接口。

示例1：新建表、插入、修改

#-*- coding: utf- -*-

import psycopg2

def main(user,pwd,ip,port,dbname):

    connection = "dbname=%s user=%s password=%s host=%s port=%s" % (dbname, user,pwd, ip, port)

    db = psycopg2.connect(connection)

    cur = db.cursor()

    #创建表

    sql_stat = "CREATE TABLE test_class_id(id INT PRIMARY kEY NOT NULL, test_class TEXT, test_id CHAR(10))";

    cur.execute(sql_stat)

    #插入表

    sql_stat = "insert into test_class_id(id, test_class, test_id) values (1, 'a', '3')"

    cur.execute(sql_stat)

    sql_stat = "insert into test_class_id(id, test_class, test_id) values (2, 'a', '3')"

    cur.execute(sql_stat)

    #更改字段值

    sql_stat = "update test_class_id set test_class='b' where id=1"

    cur.execute(sql_stat)

    db.commit()

if __name__ == "__main__":

    user = '****'

    pwd = '****'

    ip = '***'

    port = ''

    dbname = '****'

    main(user, pwd, ip, port, dbname)

    print "Done~~"

结果

注：这里有多次提交execute 最后一次提交commit，如果没有最后的一次commit，你们前面的提交也是不执行的。

示例2：批量表操作

 -*- coding: utf-8 -*-

import psycopg2

class_ids = []

class_ids.append({"test_id": 1, "test_class":"A", "test_id":""})

class_ids.append({"test_id": 2, "test_class":"A", "test_id":""})

class_ids.append({"test_id": 3, "test_class":"B", "test_id":""})

def main(user,pwd,ip,port,dbname):

    connection = "dbname=%s user=%s password=%s host=%s port=%s" % (dbname, user,pwd, ip, port)

    db = psycopg2.connect(connection)

    cur = db.cursor()

    #sql_del = "delete from cdb_chk_group"

    #cur.execute(sql_del)

    #批量操作

    sql_stat = 'insert into test_class_id(id, test_class, test_id) values (%(test_id)s, %(test_class)s,%(test_id)s)'

    cur.executemany(sql_stat, class_ids)

    db.commit()

if __name__ == "__main__":

    user = '****'

    pwd = '****'

    ip = '****'

    port = '5432'

    dbname = '****'

    main(user, pwd, ip, port, dbname)

    print "Done~~"

验证

用法补充

1. 查询数据，并输出结果

查询一条

>>> cur.execute("select * from test;")

>>> cur.fetchone()

(, , "abc'def")

查询所有条

>>> cur.execute("select * from test;")

>>> cur.fetchall()

[(1, 100, "abc'def"),(2, 100, "abc'def")]

巴特西

psycopg2

简介

示例1：新建表、插入、修改

示例2：批量表操作

用法补充

最新文章

热门文章