import pymysql
# 第一步，连接数据库，类似于指令里面的 mysql -uroot -p
# mysql -h地址 -uroot -p
'''
连接数据库需要用到的参数
主机：host
端口：port
用户名：user
密码：password
指定数据库：db
指定字符集：charset
'''
db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='dudu', charset='utf8')
# 连接成功之后，得到一个对象
# print(db)
# 首先根据db得到游标，游标就是执行sql语句用到的东西
# cursor = db.cursor()
# 给cursor添加一个参数，让其的到数据是一个字典
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 准备sql语句，执行sql语句
sql = 'select * from star'
# 返回结果rows是受影响的行数
rows = cursor.execute(sql)
# print(rows)
# 通过cursor的方法得到数据
# 返回一个元组，元组里面每个元素的值对应的就是数据表中每个字段对应的值
# 获取内容的时候，里面有个迭代器在记录你的位置
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchmany(5))
# print(cursor.fetchmany(5))
# 元组里面套元组
# print(cursor.fetchall())
# 打印得到所有的用户名
ret = cursor.fetchall()
for obj in ret:
print('我叫%s,我来自%s,我有%s￥' % (obj['name'], obj['province'], obj['money']))
# print(ret)
# 遍历每一个元组
# for tp in ret:
# print(tp[1])
# 最后呀，要记得关闭
cursor.close()
db.close()
import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='dudu', charset='utf8')
# print(db)
cursor = db.cursor()
sql = 'select * from start'
# 查询语句，通过try-except，让代码更加健壮
try:
ret = cursor.execute(sql)
print(cursor.fetchall())
except Exception as e:
print(e)
finally:
cursor.close()
db.close()
import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='dudu', charset='utf8')
cursor = db.cursor()
'''
# 准备sql语句
obj = {'name': '李云龙', 'money': '20', 'province': '河南', 'age': 36, 'sex': '男'}
# 注意，这里的引号需要加
sql = 'insert into star(name, money, province, age, sex) values("%s", "%s", "%s", "%s", "%s")' % (obj['name'], obj['money'], obj['province'], obj['age'], obj['sex'])
'''
# id = 8
# sql = 'delete from star where id=%s' % id
sql = 'update star set name="马德华" where id=12'
# 注意，没有添加进去，是因为没有提交，需要提交才能成功
try:
cursor.execute(sql)
# 提交，写入磁盘
db.commit()
except Exception as e:
print(e)
# 回滚到最初始的状态
db.rollback()
finally:
cursor.close()
db.close()
import pymysql
class MyMysql(object):
def __init__(self, host, port, user, password, db, charset):
self.host = host
self.port = port
self.user = user
self.password = password
self.db = db
self.charset = charset
# 链接数据库
self.connect()
def connect(self):
# 链接数据库和获取游标
self.db = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db, charset=self.charset)
self.cursor = self.db.cursor()
def run(self, sql):
ret = None
try:
ret = self.cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
finally:
self.close()
return ret
def close(self):
self.cursor.close()
self.db.close()
def insert(self, sql):
return self.run(sql)
def update(self, sql):
return self.run(sql)
def delete(self, sql):
return self.run(sql)
def read_one(self, sql):
ret = None
try:
self.cursor.execute(sql)
# 获取得到数据
ret = self.cursor.fetchone()
except Exception as e:
print('查询失败')
finally:
self.close()
return ret
def read_many(self, sql):
ret = None
try:
self.cursor.execute(sql)
# 获取得到数据
ret = self.cursor.fetchall()
except Exception as e:
print('查询失败')
finally:
self.close()
return ret
