在python3中使用mysql数据库需要安装pymysql库
pip install pymysql
import pymysql
db = pymysql.connect(host="数据库地址", user="用户名", password="密码", port="端口", database="数据库名", charset='utf8')
cursor = db.cursor()
# 如果数据表已经存在使用execute()方法删除表。 cursor.execute("drop table if exists employee") # 创建数据表sql语句 sql = """create table employee ( first_name char(20) not null, last_name char(20), age int, sex char(1), income float )""" cursor.execute(sql)
# sql 查询语句 sql = "select * from employee where income > {}".format(1000) try: # 执行sql语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 打印结果 print("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income)) except: print("error: unable to fecth data")
# sql 插入语句 sql = """insert into employee(first_name, last_name, age, sex, income) values ('mac', 'mohan', 20, 'm', 2000)""" try: cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback()
# sql 更新语句 sql = "update employee set age = age + 1 where sex = '{}'".format('m') try: cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback()
# sql 刪除语句 sql = "delete from employee where age > {}".format(20) try: cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback()
cursor.close() db.close()
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论