Python调用postgreSQL（使用psycopg2）

满昊然

2023-12-01

不同形式的数据库用于保存数据。Python程序员通常使用两种不同的数据库类型：SQL关系型数据库和本地文件（dbm）数据库。dbm数据库一般用在比较小的数据库，且不支持网络。

当今所有流行的SQL数据库服务器都支持网络。这是程序能在数据库服务器之外的机器上运行。能够从网络上接收查询，搜集数据，并通过网络返回结果。数据库服务器可以通过扩大磁盘容量，加快磁盘运行速度来优化。

数据库服务主要存在两个问题，可靠性和安全性。可靠性保证事务（transaction）是否执行，并且将事务执行状态返回客户端。安全性保证了通信过程中的加密。

postgreSQL属于关系型数据库，支持Python通过DB-API，pg和psycopg2模块进行调用，下面以psycopg2为例。

安装postgresql

sudo apt-get install postgresql

安装psycopg2模块：

sudo apt-get install python-psycopg2

以一个基本的数据库操作过程为例：

创建表格：

postgres=# CREATE TABLE products (
                       product_no integer PRIMARY KEY,
                       name text,
                       price numeric NOT NULL
                      );

查询创建情况：

postgres=# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | products | table | postgres
(1 row)

对已创建表格字段进行修改：

postgres=# ALTER TABLE products ADD COLUMN description text;
ALTER TABLE

插入数据条目：

postgres=# INSERT INTO products (products_no,name,price,description) VALUES ('001','net gate',100,'ON SALE.');
INSERT 0 1

输出效果如图：

postgres=# SELECT * FROM products;
 products_no |   name   | price | description 
-------------+----------+-------+-------------
           1 | net gate |   100 | ON SALE.
(1 row)

环境准备好以后，为了数据安全起见，需要分配数据库用户，并且配置好postgresql的账户密码，赋予相应的权限：

创建用户,并赋予创建用户角色属性。CREATEROLE创建的用户默认不带LOGIN属性，而CREATEUSER创建的用户默认带有LOGIN属性：

CREATEUSER lava CREATEDB PASSWORD 'root';


ALTERROLE lava VALID UNTIL '2016-6-1';


SELECT* FROM pg_roles;


SELECT* FROM pg_databases;

python调用postgresql数据库提供的接口，推荐以下两篇参考文章，介绍的比较全乎：

Python:操作PostgreSQL数据库(使用PyGreSQL) ：http://blog.csdn.net/dyx1024/article/details/7252816

Python:操作PostgreSQL数据库(使用DB API2.0) ：http://blog.csdn.net/dyx1024/article/details/7256526

执行命令：使用psycopg2运行数据库命令前，需要获得指针（cursor）。指针的概念是方便处理查询得到的结果。

事务：事务对于数据库的可靠性而言非常重要，对于复杂的数据库，一个简单的逻辑改变会影响很多数据表，并需要执行多个查询。事务可以保证所有的事务要么都被执行，要么都不执行，确保只有commit()被调用后，事务才会被提交。rollback()则可以有效地放弃上一次调用commit()或者rollback()后的改动，这对放弃事务非常有效。

事务执行的性能：每个单独的命令后提交是更新数据库最慢的办法，同时，一次非常大数据量的提交会使服务器事件buffer溢出，并产生错误和宕机。一个折衷的办法是每执行一个合适数量（例如100）的指令后再选择提交。

重复指令：为了避免没条记录反复解释指令，需要将数据从指令中分离出来。对于支持DB-API程序元来说，共有五种方法能完成这个任务。选用的方法必须受到数据库模块所支持。pycopg2支持pyformat格式，带dictionary用来转换。

>>>print psycopg2.paramstyle
>>>'pyformat'

使用executemany():executemany()带一个指令和一列该指令运行的记录。列表上的记录要么是一个list，要么是dictionnary，这取决于使用的数据库模块的参数风格。尽管很多情况下，这个函数运行的很好，但是缺点是需要执行命令前把所有记录放在内存中。如果数据量大，会占据系统很多内存资源。这种情况下只能周期性调用execute()，调用时，第一个参数必须指向同一个对象，即内存中同一个字符串对象。这样在execute()被周期调用时，数据库后端可以执行优化。

取得结果：使用fetchall(),fetchmany()或者fetchone()，对指针执行命令后获取的结果进行提取。使用时注意兼顾效率和内存占用。

以下代码完成数据库的基本连接功能：

#!/usr/bin/python 

import psycopg2

def getdsn(db = None, user = None, passwd = None, host = None):
    if user == None:
        import os, pwd
        user = pwd.getpwuid(os.getuid())[0]
    if db == None:
        db = user
    dsn = 'dbname=%s user=%s' % (db, user)
    if passwd != None:
        dsn += ' password=' + passwd
    if host != None:
        dsn += ' host=' +host
    return dsn

rows=({'module_name':'cipher','coverage':'12'，‘date’:psycopg.DateFromTicks(time.time())},
{'module_name':'spi','coverage':'55','date':pstcopg.Date(2000,12,14)})
dsn=getdsn(db = 'postgres', user = 'postgres', passwd = 'root', host = 'localhost')
print "Connecting to %s" % dsn
dbh = psycopg2.connect(dsn)
print "Connection successful."

cur = dbh.cursor()
cur.executemany("INSERT INTO mdrv VALUES (%(module_name)s, %(coverage)s,%(date)s)",rows)
dbh.commit()
dbh.close()

Python调用postgreSQL（使用psycopg2）

相关阅读

相关文章

相关问答

相关文档