17丨如何使用Python操作MySQL？

import json import traceback import mysql.connector # 读取数据库链接配置文件 with open('mysql.json', encoding='utf-8') as con_json: con_dict = json.load(con_json) # 打开数据库链接 db = mysql.connector.connect( host=con_dict['host'], user=con_dict['user'], passwd=con_dict['passwd'], database=con_dict['database'], auth_plugin=con_dict['auth_plugin'], ) # 获取操作游标 cursor = db.cursor() try: sql = 'SELECT id, name, hp_max FROM heros WHERE hp_max>6000' cursor.execute(sql) data = cursor.fetchall() print(cursor.rowcount, '查询成功。') for each_hero in data: print(each_hero) except Exception as e: # 打印异常信息 traceback.print_exc() finally: cursor.close() db.close() # 建议吧数据库链接信息写到配置文件里，防止密码泄露。

sqlalchemy用习惯了。。。献丑来一段Python代码吧 ```Python # -*- coding:utf-8 -*- from sqlalchemy import and_ from sqlalchemy import Column, INT, FLOAT, VARCHAR from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Test_db: def __init__(self): """此处填上自己的连接配置""" self.engine = create_engine( 'mysql+pymysql://username:password@host:port/db_name?charset=utf8') db_session = sessionmaker(bind=self.engine) self.session = db_session() def query_all(self, target_class, query_filter): result_list = self.session.query(target_class).filter(query_filter).all() self.session.close() return result_list class Heros(Base): """定义表结构""" __tablename__ = 'heros' id = Column(INT(), primary_key=True) name = Column(VARCHAR(255)) hp_max = Column(FLOAT()) mp_max = Column(FLOAT()) def __init__(self, id, name, hp_max, mp_max): self.id = id self.name = name self.hp_max = hp_max self.mp_max = mp_max if __name__ == '__main__': db_obj = Test_db() query_filter = and_(Heros.hp_max > 6000) heros = db_obj.query_all(Heros, query_filter) for hero_info in heros: print("id:{}, name:{}, hp_max:{}, mp_max:{}".format(hero_info.id, hero_info.name, hero_info.hp_max, hero_info.mp_max)) ``` id:10000, name:夏侯惇, hp_max:7350.0, mp_max:1746.0 id:10046, name:钟馗, hp_max:6280.0, mp_max:1988.0 id:10048, name:鬼谷子, hp_max:7107.0, mp_max:1808.0 id:10051, name:赵云, hp_max:6732.0, mp_max:1760.0 id:10052, name:橘石京, hp_max:7000.0, mp_max:0.0 id:10055, name:杨戬, hp_max:7420.0, mp_max:1694.0 id:10056, name:达摩, hp_max:7140.0, mp_max:1694.0 id:10057, name:孙悟空, hp_max:6585.0, mp_max:1760.0 id:10058, name:刘备, hp_max:6900.0, mp_max:1742.0 .....执行结果有点多字数限制了 Process finished with exit code 0

# -*- coding: UTF-8 -*- import mysql.connector import traceback # 打开数据库连接 db = mysql.connector.connect( host="localhost", user="root", passwd="123456", # 写上你的数据库密码 database='nba', auth_plugin='mysql_native_password' ) # 获取操作游标 cursor = db.cursor() try: # 查询heros 表中最大生命值大于 6000 的英雄进行查询，并且输出相应的属性值。 sql = 'SELECT name, hp_max FROM heros WHERE hp_max > %s ORDER BY hp_max DESC' val = (6000,) cursor.execute(sql, val) data = cursor.fetchall() for each_player in data: print(each_player) except Exception as e: # 打印异常信息 traceback.print_exc() # 回滚 db.rollback() finally: # 关闭游标 & 数据库连接 cursor.close() db.close() 输出： ('廉颇', 9328.0) ('白起', 8638.0) ('程咬金', 8611.0) ('刘禅', 8581.0) ('牛魔', 8476.0) ('张飞', 8341.0) ('庄周', 8149.0) ('刘邦', 8073.0) ('项羽', 8057.0) ('亚瑟', 8050.0) ('东皇太一', 7669.0) ('典韦', 7516.0) ('曹操', 7473.0) ('杨戬', 7420.0) ('夏侯惇', 7350.0) ('吕布', 7344.0) ('哪吒', 7268.0) ('墨子', 7176.0) ('老夫子', 7155.0) ('达摩', 7140.0) ('鬼谷子', 7107.0) ('关羽', 7107.0) ('钟无艳', 7000.0) ('橘石京', 7000.0) ('刘备', 6900.0) ('太乙真人', 6835.0) ('孙膑', 6811.0) ('赵云', 6732.0) ('扁鹊', 6703.0) ('铠', 6700.0) ('露娜', 6612.0) ('孙悟空', 6585.0) ('钟馗', 6280.0) ('雅典娜', 6264.0) ('兰陵王', 6232.0) ('宫本武藏', 6210.0) ('娜可露露', 6205.0) ('高渐离', 6165.0) ('芈月', 6164.0) ('不知火舞', 6014.0) ('孙尚香', 6014.0) Process finished with exit code 0

auth_plugin='mysql_native_password' 哪位亲给解释下这个参数

核心代码： cursor = db.cursor() sql = "select name,hp_max from heros where hp_max > %s" val = (6000,) cursor.execute(sql,val) data = cursor.fetchall() 注意：val里面的元素后面必须要加英文逗号，不加或者中文逗号都会报错

Python直接写SQL比Java方便多了

当一些听着很虚的理论用于实战时，其威力是巨大的，例如信息的正交性。

import pymysql import pymysql.cursors cfg = { "host": "127.0.0.1", "user": "root", "passwd": "toor", "database": "test", "charset": "utf8mb4", "autocommit": True, 'cursorclass':pymysql.cursors.DictCursor } db_con = pymysql.connect(**cfg) try: with db_con.cursor() as cur: cur.execute("SELECT id,name,hp_max FROM heros WHERE hp_max > 6000 ") res = cur.fetchall() for i in res: print(i) finally: db_con.close()

__author__ = 'Administrator' # -*- coding: UTF-8 -*- import traceback import json import mysql.connector # 打开数据库连接 db = mysql.connector.connect( host="localhost", user="root", passwd="hjf@2019", # 写上你的数据库密码 database='nba', auth_plugin='mysql_native_password' ) # 获取操作游标 cursor = db.cursor() # 执行 SQL 语句 #cursor.execute("SELECT VERSION()") # 获取一条数据 #data = cursor.fetchone() #print("MySQL 版本: %s " % data) # 关闭游标 & 数据库连接 # 插入新球员 sql = "INSERT INTO player (team_id,player_id,player_name,height) VALUES (%s, %s, %s,%s)" val = (1003, 10038," 约翰 - 科林斯 ", 2.08) cursor.execute(sql,val) db.commit() print(cursor.rowcount, " 记录插入成功。") cursor.close() db.close()

老师在插入数据的时候，漏掉了 player_id ?