Python USES PyGreSQL to manipulate the PostgreSQL database tutorial
- 2020-04-02 13:52:13
- OfStack
PostgreSQL is a powerful open source relational database. This article USES python to implement common operations on the open source database PostgreSQL.
I. environmental information:
1. Operating system
RedHat Enterprise Linux 4
Windows XP SP2
2. Database:
PostgreSQL8.3
3. Development tools:
Eclipse+Pydev+python2.6+PyGreSQL(pg module)
4. Description:
A. The PostgreSQL database runs on RedHat Linux, and pgAdmin (the client that accesses the PostgreSQL server) is also installed on Windows.
B. PyGreSQL(pg) module download path and API manual: http://www.pygresql.org/
Click here to PyGreSQL module (link: http://xiazai.jb51.net/201407/tools/pygresql (jb51.net). Rar)
Ii. Configuration:
1. Add the following subdirectories under the pgAdmin installation path to the system environment variables:
E: \ \ Program Files \ PostgreSQL \ 8.3 \ lib
E: \ \ Program Files \ PostgreSQL \ 8.3 \ bin
2. Copy the DLL file from the python installation directory C:\ python 26\Lib\site-packages\pywin32_system32 to C:\WINDOWS\system32
3. Note: if you skip the above two steps, you will report an error when importing pg, and it will take a long time to complete.
Iii. Program implementation:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Import log and pg The module
import logging
import logging.config
import pg
# Log profile name
LOG_FILENAME = 'logging.conf'
# Log statement prompt information
LOG_CONTENT_NAME = 'pg_log'
def log_init(log_config_filename, logname):
'''
Function: Log module initialization function
Input : log_config_filename: Log profile name
lognmae: Prompt statement before each log
Output: logger
author: socrates
date:2012-02-12
'''
logging.config.fileConfig(log_config_filename)
logger = logging.getLogger(logname)
return logger
def operate_postgre_tbl_product():
'''
Function: operation pg Database function
Input : NONE
Output: NONE
author: socrates
date:2012-02-12
'''
pgdb_logger.debug("operate_postgre_tbl_product enter...")
# Connect to database
try:
pgdb_conn = pg.connect(dbname = 'kevin_test', host = '192.168.230.128', user = 'dyx1024', passwd = '888888')
except Exception, e:
print e.args[0]
pgdb_logger.error("conntect postgre database failed, ret = %s" % e.args[0])
return
pgdb_logger.info("conntect postgre database(kevin_test) succ.")
# Delete table
sql_desc = "DROP TABLE IF EXISTS tbl_product3;"
try:
pgdb_conn.query(sql_desc)
except Exception, e:
print 'drop table failed'
pgdb_logger.error("drop table failed, ret = %s" % e.args[0])
pgdb_conn.close()
return
pgdb_logger.info("drop table(tbl_product3) succ.")
# Create a table
sql_desc = '''CREATE TABLE tbl_product3(
i_index INTEGER,
sv_productname VARCHAR(32)
);'''
try:
pgdb_conn.query(sql_desc)
except Exception, e:
print 'create table failed'
pgdb_logger.error("create table failed, ret = %s" % e.args[0])
pgdb_conn.close()
return
pgdb_logger.info("create table(tbl_product3) succ.")
# Insert records
sql_desc = "INSERT INTO tbl_product3(sv_productname) values('apple')"
try:
pgdb_conn.query(sql_desc)
except Exception, e:
print 'insert record into table failed'
pgdb_logger.error("insert record into table failed, ret = %s" % e.args[0])
pgdb_conn.close()
return
pgdb_logger.info("insert record into table(tbl_product3) succ.")
# Lookup table 1
sql_desc = "select * from tbl_product3"
for row in pgdb_conn.query(sql_desc).dictresult():
print row
pgdb_logger.info("%s", row)
# Lookup table 2
sql_desc = "select * from tbl_test_port"
for row in pgdb_conn.query(sql_desc).dictresult():
print row
pgdb_logger.info("%s", row)
# Close the database connection
pgdb_conn.close()
pgdb_logger.debug("operate_sqlite3_tbl_product leaving...")
if __name__ == '__main__':
# Initialize the logging system
pgdb_logger = log_init(LOG_FILENAME, LOG_CONTENT_NAME)
# Operational database
operate_postgre_tbl_product()
Iv. Test
1. Printed results on the command line after operation:
{'sv_productname': 'apple', 'i_index': None}
{'i_status': 1, 'i_port': 2, 'i_index': 1}
{'i_status': 1, 'i_port': 3, 'i_index': 2}
{'i_status': 1, 'i_port': 5, 'i_index': 3}
{'i_status': 1, 'i_port': 0, 'i_index': 5}
{'i_status': 1, 'i_port': 18, 'i_index': 7}
{'i_status': 1, 'i_port': 8, 'i_index': 8}
{'i_status': 1, 'i_port': 7, 'i_index': 9}
{'i_status': 1, 'i_port': 21, 'i_index': 10}
{'i_status': 1, 'i_port': 23, 'i_index': 11}
{'i_status': 1, 'i_port': 29, 'i_index': 12}
{'i_status': 1, 'i_port': 3000, 'i_index': 4}
{'i_status': 1, 'i_port': 1999, 'i_index': 6}
2. Log file contents:
[2012-02-12 18:09:53,536 pg_log]DEBUG: operate_postgre_tbl_product enter... (test_func.py:36)
[2012-02-12 18:09:53,772 pg_log]INFO: conntect postgre database(kevin_test) succ. (test_func.py:46)
[2012-02-12 18:09:53,786 pg_log]INFO: drop table(tbl_product3) succ. (test_func.py:58)
[2012-02-12 18:09:53,802 pg_log]INFO: create table(tbl_product3) succ. (test_func.py:73)
[2012-02-12 18:09:53,802 pg_log]INFO: insert record into table(tbl_product3) succ. (test_func.py:85)
[2012-02-12 18:09:53,802 pg_log]INFO: {'sv_productname': 'apple', 'i_index': None} (test_func.py:91)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 2, 'i_index': 1} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 3, 'i_index': 2} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 5, 'i_index': 3} (test_func.py:97)
[2012-02-12 18:09:53,802 pg_log]INFO: {'i_status': 1, 'i_port': 0, 'i_index': 5} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 18, 'i_index': 7} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 8, 'i_index': 8} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 7, 'i_index': 9} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 21, 'i_index': 10} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 23, 'i_index': 11} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 29, 'i_index': 12} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 3000, 'i_index': 4} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]INFO: {'i_status': 1, 'i_port': 1999, 'i_index': 6} (test_func.py:97)
[2012-02-12 18:09:53,819 pg_log]DEBUG: operate_sqlite3_tbl_product leaving... (test_func.py:101)
3. PSQL results:
[root@kevin ~]# su - postgres
[postgres@kevin ~]$ psql -U dyx1024 -d kevin_test
psql (8.4.2)
Type "help" for help.
kevin_test=# dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------------
public | tbl_product3 | table | dyx1024
public | tbl_test_port | table | pg_test_user_3
(2 rows)
kevin_test=# select * from tbl_product3;
i_index | sv_productname
---------+----------------
| apple
(1 row)
kevin_test=# select * from tbl_test_port;
i_index | i_port | i_status
---------+--------+----------
1 | 2 | 1
2 | 3 | 1
3 | 5 | 1
5 | 0 | 1
7 | 18 | 1
8 | 8 | 1
9 | 7 | 1
10 | 21 | 1
11 | 23 | 1
12 | 29 | 1
4 | 3000 | 1
6 | 1999 | 1
(12 rows)
kevin_test=# q
[postgres@kevin ~]$