python2.7 psycopg2

 psycopg2 安装

 

sql='''INSERT INTO "CNYB"."PRE_DQ_PLANT"("ID", "ORG_ID", "RT_ORG_ID", "DATE", "DTIME", "CTIME", "INDEX", "MARK", "FNAME", "P_PR", "P_PTR")
VALUES (%(ID)s, %(ORG_ID)s, %(RT_ORG_ID)s, %(DATE)s, %(DTIME)s, %(CTIME)s, %(INDEX)s, %(MARK)s, %(FNAME)s, %(P_PR)s,%(P_PTR)s);'''

util.inserDataToDB(dbcnyb,sql,ret)
sql='''INSERT INTO "CNYB"."PRE_DQ_PLANT"("ID", "ORG_ID", "RT_ORG_ID", "DATE", "DTIME", "CTIME", "INDEX", "MARK", "FNAME", "P_PR", "P_PTR")
VALUES ('%(ID)s', '%(ORG_ID)s', '%(RT_ORG_ID)s', '%(DATE)s', '%(DTIME)s', '%(CTIME)s', '%(INDEX)s', '%(MARK)s', '%(FNAME)s', '%(P_PR)s','%(P_PTR)s');'''

retlist=[]
for rd in ret:
    new_sql=sql%rd
    retlist.append(new_sql)
newsql=';'.join(retlist)
util.inserDataToDB2(dbcnyb,newsql)

 

def inserDataToDB(cnyb,sql,rows):
    try:
        stime=datetime.datetime.now()
        with  cnyb.cursor() as cur:
            cur.executemany(sql, rows)
            cnyb.commit()
            LOG.debug("insert over")
        etime=datetime.datetime.now()
        gap=(stime-etime).seconds
        LOG.debug('gap=%s'%gap)
    except Exception,e:
        cnyb.rollback()
        LOG.error("insert data fail，ex=%s"%(traceback.format_exc()))

def inserDataToDB2(cnyb,sql):
    try:
        stime=datetime.datetime.now()
        with  cnyb.cursor() as cur:
            cur.execute(sql)
            cnyb.commit()
            LOG.debug("insert over")
        etime=datetime.datetime.now()
        gap=(stime-etime).seconds
        LOG.debug('gap2=%s'%gap)
    except Exception,e:
        cnyb.rollback()
        LOG.error("insert data fail，ex=%s"%(traceback.format_exc()))
==================

util.inserDataToDB(dbcnyb,'"CNYB"."PRE_DQ_PLANT"',uncoverData,['ID', 'ORG_ID', 'RT_ORG_ID', 'DATE', 'DTIME', 'CTIME', 'INDEX', 'MARK', 'FNAME', 'P_PR','P_PTR'])

def inserDataToDB(cnyb,tbl,df,cols):
    try:
        output = BytesIO()
        df.to_csv(output, sep='\t', index=False, header=False,columns=cols)
        stime=time.clock()
        with  cnyb.cursor() as cur:
            cur.copy_from(output, tbl)
            cnyb.commit()
        etime=time.clock()
        LOG.debug("insert over,insert sends=%s"%(etime-stime))
    except Exception,e:
        cnyb.rollback()
        LOG.error("insert data fail，ex=%s"%(traceback.format_exc()))
=============pymysql 

参考：https://mp.weixin.qq.com/s/hhykKBsFMRen5D-aawO6pA
import pymysql 
db=pymysql.connect(host='localhost',user='root',password='123456',
                   port=3306,db='spiders',charset=' utf8') 

使用pandas中的read_sql()方法，将提取到的数据直接转化为DataFrame，进行操作

mport pymysql 
import pandas as pd

db = pymysql.connect(host='localhost',user='root',db='huangwei',
                     password='123456',port=3306,charset='utf8')
cursor = db.cursor()

df1 = pd.read_sql("select * from student where ssex='男'",db)
display(df1）

进行 "增"、"删"、"改"的时候，一定要使用try…except…语句，因为万一没插入成功，其余代码都无法执行。当语句执行不成功，我们就db.rollback()回滚到操作之前的状态；当语句执行成功，我们就db.commit()提交事务

import  pymysql

db=pymysql.connect(host='localhost',user='root', password='123456',
                   port=3306, db='huangwei', charset='utf8')

cursor = db.cursor()

# 插入一条数据
sql = 'insert into person(name,age) values(%s,%s)'
try:
    cursor.execute(sql,('孙悟空',100000))
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()
db.close()

插入多条
sql = 'insert into person(name,age) values(%s,%s)'
# 注意：(('牛魔王',9000),('铁扇公主',8000),('玉皇大帝',6000))也可以
# 小括号都可以换为中括号
datas = [('牛魔王',9000),('铁扇公主',8000),('玉皇大帝',6000)]
try:
    cursor.executemany(sql,datas)
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()