Python连接mysql详解（mysql-connector-python）

臧南

文章目录1概述1.1第三方库：mysql-connector-python1.2可视化工具：navicat1.3创建测试数据库2连接mysql数据库2.1创建一个连接2.2捕获连接异常2.3从配置文件中获取连接信息3执行sql语句3.1插入、更新、删除3.2查询1概述1.1第三方库：mysql-connector-pythonpipinstallmysql-connector-python11.2可视化工具：navicat1.3创建测试数据库--创建数据库createdatabasepython_demoDEFAULTCHARSETutf8COLLATEutf8_general_ci;--创建测试表createtablepython_demo.student(snointunsignedauto_incrementcomment'学号', snamevarchar(30)notnullcomment'姓名', ageintcomment'年龄', birthdaydatecomment'出生日期',primarykey(sno))engine=innodbdefaultcharset=utf8comment'学生信息表';12345678910112连接mysql数据库2.1创建一个连接importmysql.connector#配置连接信息conn=mysql.connector.connect(host='127.0.0.1',port='3306',user='root',password='12345',database='python_demo')#当前mysql版本号print(conn.get_server_version())1234567891011122.2捕获连接异常importmysql.connectorfrommysql.connectorimporterrorcodetry:#配置连接信息conn=mysql.connector.connect(host='127.0.0.1',port='3306',user='root',password='12345',database='python_demo')#当前mysql版本号print(conn.get_server_version())#捕获异常exceptmysql.connector.Erroraserr:iferr.errno==errorcode.ER_ACCESS_DENIED_ERROR:print('账号或密码错误！')eliferr.errno==errorcode.ER_BAD_DB_ERROR:print('数据库不存在！')else:print(err)else:#关闭连接conn.close()12345678910111213141516171819202122232425262.3从配置文件中获取连接信息目录结构：config.ini：[mysql]host=127.0.0.1port=3306user=rootpassword=12345database=python_demo123456m1.py：importmysql.connectorfrommysql.connectorimporterrorcodeimportconfigparser#创建配置解析器对象config=configparser.ConfigParser()#读取配置文件config.read('config.ini')try:#配置连接信息conn=mysql.connector.connect(host=config.get('mysql','host'),port=config.get('mysql','port'),user=config.get('mysql','user'),password=config.get('mysql','password'),database=config.get('mysql','database'))#当前mysql版本号print(conn.get_server_version())#捕获异常exceptmysql.connector.Erroraserr:iferr.errno==errorcode.ER_ACCESS_DENIED_ERROR:print('账号或密码错误！')eliferr.errno==errorcode.ER_BAD_DB_ERROR:print('数据库不存在！')else:print(err)else:#关闭连接conn.close()12345678910111213141516171819202122232425262728293031323执行sql语句3.1插入、更新、删除execute()：用来执行sql语句，如：增删改查，存储过程等commit()：用来提交事务importmysql.connector#配置连接信息conn=mysql.connector.connect(host='127.0.0.1',port='3306',user='root',password='12345',database='python_demo')#创建游标对象cursor=conn.cursor()#操作数据：插入、修改、删除同理，注：数据类型均可用%s#操作一条数据sql='insertintostudent(sname,age,birthday)values(%s,%s,%s);'param=('张三','18','1994-12-08')cursor.execute(sql,param)#操作多条数据sql='insertintostudent(sname,age,birthday)values(%s,%s,%s);'param=[('李四','20','1992-10-05'),('王五','16','1996-05-26'),('赵六','08','1994-05-26')]cursor.executemany(sql,param)#提交数据conn.commit()#关闭游标和数据库连接cursor.close()conn.close()1234567891011121314151617181920212223242526272829303132333.2查询importmysql.connector#配置连接信息conn=mysql.connector.connect(host='127.0.0.1',port='3306',user='root',password='12345',database='python_demo')#创建游标对象cursor=conn.cursor()#查询数据sql='selectsno,sname,age,birthdayfromstudentwheresno>=%s'param=(1,)cursor.execute(sql,param)result=cursor.fetchall()#打印结果forrowinresult:print(row)#关闭游标和数据库连接cursor.close()conn.close()12345678910111213141516171819202122232425262728