python_操作Postgresql数据库

import psycopg2
import logging
import os

class DBInit:
    #Postgersql 数据库连接
    def getPostgresqlConn(self):
        #数据库_IP
        postgresqlIp=os.getenv('postgresql_ip')
        #数据库_库名
        database_name = os.getenv('database_name')
        #数据库_用户
        postgresqlUser = os.getenv('postgresql_user')
        #数据库_密码
        postgresqlPwd = os.getenv('postgresql_pwd')

        #获取数据库连接
        try:
            conn = psycopg2.connect(database=database_name, user=postgresqlUser, password=postgresqlPwd, host=postgresqlIp, port='5432')
        except Exception as e:
            logging.error('postgresql连接异常:' + e)
        return conn

    #释放postgresql连接
    def closePostgresqlConn(self,conn):

        try:
            conn.cursor().close()
        except Exception as e:
            logging.error('连接指针释放失败:'+e)

        try:
            conn.close();
        except Exception as e:
            logging.error('连接释放失败:'+e)





#获取Postgresql连接
conn=DBInit().getPostgresqlConn()

#创建指针对象
cur=conn.cursor()

#建表
cur.execute("CREATE TABLE student(id integer,name varchar,sex varchar);")

#插入
cur.execute("INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)",(1,'Aspirin','M'))
cur.execute("INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)",(2,'Taxol','F'))
cur.execute("INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)",(3,'Dixheral','M'))

#查询
cur.execute('SELECT * FROM student')
# cur.execute('SELECT count(*) FROM student')
user=cur.fetchall()
for u in user:
    print(u)

#提交
conn.commit();

#释放
DBInit().closePostgresqlConn(conn)

增删改查

  # 新增地图json
    def saveMapJson(self,id,applyId,json):
        #获取Postgresql连接
        conn=DBInit().getPostgresqlConn()
        #创建指针对象
        cur=conn.cursor()
        # 新增
        cur.execute("INSERT INTO map_json(id,apply_id,json,is_delete ) VALUES (%s,%s,%s,%s)",
                    (id, applyId, json, '0'))
        # 提交
        conn.commit();
        # 释放
        DBInit().closePostgresqlConn(conn)

    #查询地图json
    def getMapJson(self,id):
        # 获取Postgresql连接
        conn = DBInit().getPostgresqlConn()
        # 创建指针对象
        cur = conn.cursor()

        cur.execute('SELECT * FROM map_json where is_delete=\''+'0'+'\' and id=\''+ id+'\'')

        mapJson=cur.fetchall();
        return mapJson

    #查询全部地图json
    def getAllMapJson(self):
        # 获取Postgresql连接
        conn = DBInit().getPostgresqlConn()
        # 创建指针对象
        cur = conn.cursor()
        cur.execute('SELECT * FROM map_json where is_delete=\'' + '0' + '\' ')
        mapJsons=cur.fetchall();
        return mapJsons

    #修改地图json
    def updateMapJson(self,id,json):
        # 获取Postgresql连接
        conn = DBInit().getPostgresqlConn()
        # 创建指针对象
        cur = conn.cursor()
        # 修改
        cur.execute('update map_json set json=\''+json+'\' where id =\''+id+'\'')
        # 提交
        conn.commit();
        # 释放
        DBInit().closePostgresqlConn(conn)


    #删除地图json
    def delMapJson(self,id):
        # 获取Postgresql连接
        conn = DBInit().getPostgresqlConn()
        # 创建指针对象
        cur = conn.cursor()
        # 删除
        cur.execute('update map_json set is_delete=\'' + '1' + '\'  where id =\'' + id + '\'')
        # 提交
        conn.commit();
        # 释放
        DBInit().closePostgresqlConn(conn)