Linux学习笔记--Python操作mysql数据库(封装基本的增删改查)
程序员文章站
2022-05-30 10:23:08
...
#!/usr/bin/env python
#_*_ coding:utf-8 _*_
import MySQLdb
#类、静态字段、动态字段、静态方法、动态方法、特性
class MysqldbHelper(object):
def __init__(self, host, user, passwd,db):
self.Host = host
self.User = user
self.Passwd = passwd
self.DB = db
self.Port = 3306
self.Charset = 'utf8'
# 获取数据库连接
def getCon(self):
try:
conn = MySQLdb.Connect(host=self.Host, user=self.User, passwd=self.Passwd, db=self.DB, port=self.Port,
charset=self.Charset)
return conn
except MySQLdb.Error, e:
print "Mysqldb Error:%s" % e
# 查询方法,使用con.cursor(MySQLdb.cursors.DictCursor),返回结果为字典
def select(self, sql):
try:
con = self.getCon()
cur = con.cursor()
reCount = cur.execute('select * from works')
data = cur.fetchall()
return data
except MySQLdb.Error, e:
print "Mysqldb Error:%s" % e
finally:
cur.close()
con.close()
#增加方法,eg:sql='insert into pythontest values(%s,%s,%s,now()',params=(6,'C#','good book')
def insert(self, sql, params):
try:
con = self.getCon()
cur = con.cursor()
reCount = cur.execute(sql, params)
con.commit()
return reCount
except MySQLdb.Error, e:
con.rollback()
print "Mysqldb Error:%s" % e
finally:
cur.close()
con.close()
#修改方法,
def update(self, sql, params):
try:
con = self.getCon()
cur = con.cursor()
reCount = cur.execute(sql, params)
con.commit()
return reCount
except MySQLdb.Error, e:
con.rollback()
print "Mysqldb Error:%s" % e
finally:
cur.close()
con.close()
#删除方法
def delete(self, sql, params):
try:
con = self.getCon()
cur = con.cursor()
reCount = cur.execute(sql, params)
con.commit()
return reCount
except MySQLdb.Error, e:
con.rollback()
print "Mysqldb Error:%s" % e
finally:
cur.close()
con.close()
if __name__ == "__main__":
# host = raw_input("请输入数据库所在主机地址:")
# user = raw_input("请输入登陆账号:")
# passwd = raw_input("请输入登陆密码:")
# db = raw_input("请输入数据库名称:")
# dbname = MysqldbHelper(host, user, passwd, db)
dbname=MysqldbHelper('192.168.20.30','root','redhat','work')
#查
# sql = "select * from works"
# fc = dbname.select(sql)
# print "数据库信息如下:"
# print fc
#增
# sql = "insert into works values(%s,%s,%s)"
# params = (2, 'lisi1', 'male')
# fc = dbname.insert(sql,params)
#改
sql = "update works set name = %s where id = 2"
params = ('sb',)
fc = dbname.update(sql, params)
#删
sql = "delete from works where id = %s"
params = (1,)
fc = dbname.delete(sql, params)