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

时间：2022-04-23 02:22:15

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

pip install mysql-connector

示例:

Python3.7和数据库MySQL 8.0.12 数据库数据驱动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.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))"

minstert_sql = "insert into user (name,password) values ('TOM','12345')"

mcursor.execute(minstert_sql)

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()

#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",""),("HanMeiMei",""),("WangXxinXixn","")]

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)




只查询一条




mycursor.fetchone()




　
相关文章