py插入postgres数据库代码示例

 

def saveToPostgis(feats,remarks):

    # 连接数据库
    conn = psycopg2.connect(database='postgis_21_sample', user='postgres', password='postgres', host='192.168.1.100', port='5432')
    curs = conn.cursor()
    # 编辑写入数据的sql
    # xy = [minx,miny,minx,maxy,maxx,maxy,maxx,miny,minx,miny]
    # geom = 'POLYGON(({} {},{} {},{} {},{} {},{} {}))'.format(*xy)
    # print(geom)

    delete_sql = 'delete from gridmanagement where \'remarks\'=\'%s\'' % remarks
    curs.execute(delete_sql)
    insert_sql = ''
    for feat in feats:
        geom = feat.geometry()
        wkt = geom.asWkt()
        id = feat.attribute('id')
        wgbh = feat.attribute('wgbh')

        insert_sql += 'insert into gridmanagement(NO,GEOM,remarks) values(\'%s\',ST_GeomFromText(\'%s\'),\'%s\');'%(wgbh,wkt,remarks)

    curs.execute(insert_sql)
    # #判断不存在再进行加入
    # sqlSelect = 'select count(*) from geosel where no = \'%s\''%bianhao
    # curs.execute(sqlSelect)
    # # 获取结果集的每一行
    # rows = curs.fetchall()
    # for r in rows:
    #     for nCount in r:
    #         if nCount==0:
    #             curs.execute(insert_sql)

    # 提交数据
    conn.commit()
    # 关闭数据库
    curs.close()