py3处理数据库

处理mysql使用 pymysql模块

import pymysql

conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='newpwd',db='healhome',charset='utf8')

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

#影响的行数
rows=cursor.execute('insert into userinfo(userid,username,password,gender,age,weight,height,email,rank) VALUES (%s,%s,%s,%s,50,70,170,%s,%s)',('019','张九龄','89787','男','789@77.cn','中级用户'))
#处理 添加，删除，修改操作时需要commit()
conn.commit()

#查询语句
cursor.execute('select * from userinfo')
#得到所有的查询结果
a=cursor.fetchall()
print(a)
cursor.close()
conn.close()

 

处理postgreSQL使用psycopg2模块

import psycopg2
conn=psycopg2.connect(host='xxxxxx',port=5432,user='xxxx',password='xxxx',database='xxxx')
cursor=conn.cursor()

sql='SELECT COUNT(*) FROM personaltask WHERE uid=\'%s\' AND addrid=\'%s\' AND status IN(3,4,5,7) UNION ALL SELECT COUNT(*) FROM personaltask WHERE uid=\'%s\' AND addrid=\'%s\' AND status=4 UNION ALL SELECT COUNT(*) FROM personaltask WHERE uid=\'%s\' AND addrid=\'%s\' AND status=2'%(i[0],i[1],i[0],i[1],i[0],i[1])
cursor.execute(sql)
allnum=cursor.fetchone()
print(allnum)

基本差不多