Python3.7和数据库MySQL 8.0.12 数据库数据驱动mysql-connector安装(四)-白红宇

Python3.7和数据库MySQL 8.0.12 数据库数据驱动mysql-connector安装(四)

阅读量：5291 次

发布时间：2019-06-14

本文共 2405 字，大约阅读时间需要 8 分钟。

安装mysql-connector驱动在系统CMD输入命令:

pip install mysql-connector

示例:

创建数据库

# 导入驱动import mysql.connector# 创建连接数据库conn = mysql.connector.connect(host="localhost", user="root", passwd="root")mcursor = conn.cursor()mcreate_database_sql = "create database python_dbs"mcursor.execute(mcreate_database_sql)

创建表

# 导入驱动 import mysql.connector conn = mysql.connector.connect(host="localhost", user="root", passwd="root",database="python_dbs") mcursor = conn.cursor() mcreate_table_sql = "create table user (id int auto_increment primary key ,name varchar (20),password varchar (20))" mcursor.execute(mcreate_table_sql) conn.commit() mcursor.close() conn.close

插入一条数据

import mysql.connectorconn = mysql.connector.connect(host="localhost", user="root", passwd="root",database="python_dbs")mcursor = conn.cursor()#mcreate_table_sql = "create table user (id int auto_increment primary key ,name varchar (20),password varchar (20))"minstert_sql = "insert into user (name,password) values ('TOM','12345')"mcursor.execute(minstert_sql)conn.commit()mcursor.close()conn.close

插入多条数据

# 导入驱动import mysql.connectorconn = mysql.connector.connect(host="localhost", user="root", passwd="root",database="python_dbs")mcursor = conn.cursor()#mcreate_table_sql = "create table user (id int auto_increment primary key ,name varchar (20),password varchar (20))"#minstert_sql = "insert into user (name,password) values ('TOM','12345')"#插入多条数据 使用元组列表minstert_sqls = "insert into user (name,password) values (%s,%s)"var = [("LiLei","1111"),("HanMeiMei","2222"),("WangXxinXixn","3333")]mcursor.executemany(minstert_sqls,var)conn.commit()mcursor.close()conn.close

查询记录模糊匹配

# 导入驱动 import mysql.connector conn = mysql.connector.connect(host="localhost", user="root", passwd="root",database="python_dbs") mcursor = conn.cursor() #查询所有的name msql = "select name from user" #查询name=TOM msql1 = "select * from user where name = 'TOM'" #查询name含有n的 msql2 = "select * from user where name like  '%n%'" #排序 默认升序 msql3 = "select * from user  order by name asc " #排序 降序 msql4 = "select * from user  order by name desc " #返回数量 msql5 = "select * from user  order by name desc  Limit 3" #删除 msql6 = "delete * from user where name = 'TOM'" mcursor.execute(msql5) #获取所有查询的记录 myresulet = mcursor.fetchall() conn.commit() mcursor.close() conn.close for x in myresulet:     print(x)

只查询一条