一、安装相关包
ubuntu下安装命令: sudo apt-get install python-mysqldb
pycharm安装依赖包:
二、代码示例
测试表的结构:
#encoding=utf-8
import pymysql
#Python与mysql数据库的交互
class MySQLUtil(object):
"""连接MYSQL数据库并执行增、删、改、查操作"""
__conn = None
DB_NOT_CONNECT_MESSAGE="数据库连接出错,未能指向SQL[%s]";
def __init__(self):
self.conn = self.__conn
@staticmethod
def connToDB():
"""连接数据可"""
try:
print("尝试连接数据库")
if not MySQLUtil.__conn:
MySQLUtil.__conn = pymysql.connect(host='192.168.199.138',port=3306,
db='test',user='root',password='123456',
charset='utf8')
except Exception as e:
print(e.message)
@staticmethod
def close():
"""关闭数据库连接"""
if MySQLUtil.__conn:
MySQLUtil.__conn.close()
def selectOne(self,sql):
"""查询一条记录"""
if self.conn:
cur = self.conn.cursor()
cur.execute(sql)
result=cur.fetchone()
print(result)
cur.close()
else:
print(self.DB_NOT_CONNECT_MESSAGE%sql)
def selectMul(self,sql):
"""查询多条记录"""
if self.conn:
cur = self.conn.cursor()
cur.execute(sql)
result = cur.fetchall()
print(result)
cur.close()
else:
print(self.DB_NOT_CONNECT_MESSAGE% sql)
def update(self,update):
"""执行更新或者插入语句"""
if self.conn:
cur = self.conn.cursor()
count = cur.execute(sql)
print("或者插入%d条数据"%count)
if count>0:
self.conn.commit()
cur.close()
else:
print(self.DB_NOT_CONNECT_MESSAGE%sql)
def delete(self,sql):
"""执行删除操作"""
if self.conn:
cur = self.conn.cursor()
count = cur.execute(sql)
print("删除%d条数据" % count)
if count > 0:
self.conn.commit()
cur.close()
if __name__ == '__main__':
"""测试代码"""
MySQLUtil.connToDB() #python的类方法必须显示的调用,否则不会执行
dbUtil = MySQLUtil()
print("-------select one-------")
sql = "select * from students"
dbUtil.selectOne(sql)
print("------select mutiple------")
dbUtil.selectMul(sql)
sql = "insert into students values(null,'小花',0,19),(null,'小Q',1,22)"
print('-------update ----------')
dbUtil.update(sql)
print("------select mutiple------")
sql = "select * from students"
dbUtil.selectMul(sql)
print('-----------delete----------')
sql = "delete from students where name in('小花','小Q')"
dbUtil.delete(sql)
print("------select mutiple------")
sql = "select * from students"
dbUtil.selectMul(sql)
print('--------close database connection-------')