本文使用 PyMySQL 连接数据库,
本数据库的表名student,表的列有id,name,age
#!/usr/bin/env python
# -- coding: utf-8 --
# @Time : 2019/6/20 14:24
# @Author : zxm
# @File : mysqldemo.py
import pymysql
class TestDemo:
def __init__(self):
#创建数据库连接,
self.conn = pymysql.Connect(
host=' ',
port= ,
user=' ',
password=' ',
db=' ',
charset='utf8',
)
#创建一个游标对象
self.cursor=self.conn.cursor()
# 插入一条数据
def insert_one(self, sql):
try:
# execute 执行sql语句
result = self.cursor.execute(sql)
# 提交
self.conn.commit()
return result
except:
#抛异常
print("操作不当,我要回滚了")
self.conn.rollback()
finally:
#关闭连接
self.cursor.close()
self.conn.close()
#插入多条数据
def insert_many(self, sql, datas):
try:
# executemany 执行多条sql
result = self.cursor.executemany(sql, datas)
self.conn.commit()
return result
except:
print("操作不当,我要回滚了")
self.conn.rollback()
finally:
self.cursor.close()
self.conn.close()
#删除一条数据
def delete_one(self,sql,id):
try:
result = self.cursor.execute(sql, id)
self.conn.commit()
return result
except:
print("操作不当,我要回滚了")
self.conn.rollback()
finally:
self.cursor.close()
self.conn.close()
#更改数据
def change(self,sql,id):
try:
result = self.cursor.execute(sql, id)
self.conn.commit()
return result
except:
print("操作不当,我要回滚了")
self.conn.rollback()
finally:
self.cursor.close()
self.conn.close()
# 查询数据库中的数据
#fetchall() 接收全部的返回结果行
#fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
def selectAll(self):
try:
result = self.cursor.execute(sql)
return self.cursor.fetchall()
except:
print("操作不当,我要回滚了")
self.conn.rollback()
finally:
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
mysql = TestDemo()
#插入一条数据
sql = "INSERT into student VALUES(null,'titi',3)"
result = mysql.insert_one(sql)
print(result) # 返回插入数据的条数(1)
#插入多条数据,这是要插入的数据,是个list
datas=[
("ahuang",12),
("atian",13),
("abing",14)
]
sql = 'INSERT into student VALUES(null,%s,%s)'
result=mysql.insert_many(sql,datas)
#打印操作的行数
print(result)
#删除id=1的数据
id = 1
sql = "DELETE FROM student WHERE id=%s"
result = mysql.delete_one(sql,id)
print(result)
#更改数据库中的数据
id=3
sql = "UPDATE student set name='zzz' WHERE id=%s"
result=mysql.change(sql,id)
print(result)
#查询数据库中的所有的数据
#
sql = "select * from student"
result = mysql.selectAll()
for it in result:
#输出数据库的每一项
print(it)
#只输出数据库中所有为name的那一列
print(it.get("name"))