python连接mysql

一：安装mysql依赖包

yum install python-devel
pip install MySQL-python

二：日常使用

连接数据库，并设置游标：

import MySQLdb as mysql
db = mysql.connect(user="root",password="123456",db="mydbname",charset="utf8")
cur = db.cursor()

查询多条数据：

fields = ["id","name","email"]
sql = "select %s from user"%",".join(fields)
cur.execute(sql)
result = cur.fetchall()
users = [dict((v,raw[k]) for k,v in enumerate(fields)) for row in result]
# return：
# [{"id":1,"name":"admin","email":"xxx"},{"id":x,"name":"xxx","email":"xxx"}]

查单条数据：

fields = ["id","name","email"]
sql = "select %s from user where id=%s"%(",".join(fields),user_id)
cur.execute(sql)
result = cur.fetchone()
users = dict((v,result[k]) for k,v in enumerate(fields))
cur.close()
#返回数据格式
#{"id":1,"name":"admin","email":"xxx"}

插入数据

指定列插入：

data = {"id":1,"name":"jack","email":"123@qq.com","password":"123456"}
fields = ["id","name","email"]
sql = "insert into user(%s) values(%s)"%(",".join(fields),",".join(['"%s"'%data[x] for x in fields]))
cur.execute(sql)
cur.close()

直接插入：

data = {"id":1,"name":"jack","email":"123@qq.com","password":"123456"}
sql = "insert into user(%s) values(%s)"%(",".join(data.keys()),'","'.join(data.values()))
cur.execute(sql)
cur.close()

更新数据

data = {data:{"name":"admin","email":"xxx","mobile":"xxx"},"where":{"id":"user_id"}}
where = ["%s='%s'"%(k,v) for k,v in data.get("where",None).items()
conditions = ["%s='%s'"%(k,data[k] for k in data.get("data",None))]
sql = "update user set %s where %s"%(",".join(condiions)," and ".join(where))
cur.execute(sql)

三：使用连接池

安装DButils:

pip install DButils

基本使用

from DBUtils.PooledDB import PooledDB 
import MySQLdb as mysql
#创建一个连接池
pool = PooledDB(mysql,mincached=4,maxcached=10,host="localhost",db="reboot",user="root",passwd="123456",setsession=['SET AUTOCOMMIT = 1'])
#创建一个连接
db = pool.connection()
#创建一个游标
cur = db.cursor()
#执行sql
cur.execute(sql)
cur.fetchall()|cur.fetchone()
#关闭连接
cur.close()
db.close()