Python连接MySQL数据库

#coding:utf-8
import  mysql.connector
#D:\chrome download\mysql-5.7.29-winx64\mysql-5.7.29-winx64\data
#打开这个data文件夹，找到一个类型为err的文件，用记事本打开，里面有句A temporary password is generated for root@localhost:这个后面记录的就是你的初始密码
#在cmd中启动MySQL并将密码修改为123456
mydb = mysql.connector.connect(
    host='localhost',
    user='root',
    passwd='123456',
    database='mydatabase',
    buffered = True
)
print(mydb)
#1、创建数据库
mycursor = mydb.cursor()
# mycursor.execute("CREATE DATABASE mydatabase") #已创建的数据库再创建会报错
# mycursor.execute("CREATE DATABASE yangdatabase")


#/*tableName:为要查询的表，selSql为查询语句，val为要查询的where条件中的值***/
def selectFn(mycursor,tableName,selSql,val):
    if selSql=="":
        selSql="select * from "+tableName
        mycursor.execute(str(selSql))
    elif val!="":
        mycursor.execute(str(selSql),val)
    elif val=="":
        mycursor.execute(str(selSql))

    myRes = mycursor.fetchall()
    print(tableName," 表中数据有：")
    for x in myRes:
        print(x)

#/*tableName:为要插入值的表名，colStr为表中的字段，valList为要插入的值***/
def insertFn(mycursor,tableName,colStr,valList):
    #插入的SQL语句：insert into customers(name,address) values(%s,%s)
    splitStr = colStr.split(",")
    valStr=""
    #如下for循环是生成(%s,%s)
    for x in range(len(splitStr)):
        valStr+="%s"
        if (x == (len(splitStr)-1)):
            break
        valStr += ","
    insertSql = "insert into " + tableName + "(" + colStr + ")" +" values(" + valStr + ")"
    mycursor.executemany(str(insertSql),valList)
    mydb.commit()
    print(mycursor.rowcount, " record inserted")
    # print("1 record inserted,ID:", mycursor.lastrowid)


# select出重复数据并删除
def deleteSameData(delSql,tableName):
    mycursor.execute(delSql)
    mycursor.execute(str("select * from " +tableName))
    myresult1 = mycursor.fetchall()
    for x in myresult1:
        print(x)


#1、检查数据库是否创建好
print("1、检查数据库是否创建好")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
     print(x)

#2、创建表
mycursor.execute("drop table if exists users")
mycursor.execute("drop table if exists products")
createSql="create table if not exists users(name varchar(255),fav int)"
createSql2="create table if not exists products(id int,name varchar(255))"
valUsers = [
    ('John', 154),
    ('Peter', 154),
    ('Amy', 155),
    ('Hannah', 0),
    ('Michael', 0)
]
valProducts=[
    (154, 'Chocolate Heaven'),
    (155, 'Tasty Lemons'),
    (156, 'Vanilla Dreams'),
    (159, 'yang Cake')
]

mycursor.execute(createSql)
mycursor.execute(createSql2)
mydb.commit()


#3、开始对表插入数据
print("3、开始对表插入数据")
insertFn(mycursor,'users','name,fav',valUsers)
insertFn(mycursor,'products','id,name',valProducts)
mycursor.execute("alter  table users add column uid int auto_increment primary key")


#4、查询表中的数据
print("4、查询表中的数据")
selectFn(mycursor,'users','','')
selectFn(mycursor,'products','','')


sql2 = "SELECT * FROM customers WHERE address = %s"
adr=("Yellow Garden 2", )
selectFn(mycursor,'customers',sql2,adr)



#5、练习join相关sql语句
print("5、练习join相关sql语句：")
joinSql ="select users.name as user,products.name as favor from users inner join products on users.fav = products.id"
# mycursor.execute(selectSql)
# # selectRes=mycursor.fetchall()
# # for x in selectRes:
# #     print(x)
selectFn(mycursor,'users',joinSql,'')

leftJoinSql ="select users.name as user,products.name as favor from users left join products on users.fav = products.id"
rightJoinSql ="select users.name as user,products.name as favor from users right join products on users.fav = products.id"
selectFn(mycursor,'users',leftJoinSql,'')
selectFn(mycursor,'users',rightJoinSql,'')

join或inner join (例子：组合用户和产品，查看用户最喜欢的产品名称)
组合两张或更多表
您可以使用 JOIN 语句，根据它们之间的相关列组合两个或多个表中的行

LEFT JOIN（例子：选择所有用户及其喜爱的产品）
在上例中，Hannah 和 Michael 被排除在结果之外，这是因为 INNER JOIN 只显示匹配的记录。
如果希望显示所有用户，即使他们没有喜欢的产品，请使用 LEFT JOIN 语句；

RIGHT JOIN（例子：选择所有产品以及喜欢它们的用户）
如果您想要返回所有产品以及喜欢它们的用户，即使没有用户喜欢这些产品，请使用 RIGHT JOIN 语句：

执行结果：

1、检查数据库是否创建好
    ('mydatabase',)
    ('mysql',)
    ('yangdatabase',)
3、开始对表插入数据
       5  record inserted
      4  record inserted
4、查询表中的数据

users  表中数据有：
('John', 154, 1)
('Peter', 154, 2)
('Amy', 155, 3)
('Hannah', 0, 4)
('Michael', 0, 5)

products  表中数据有：
(154, 'Chocolate Heaven')
(155, 'Tasty Lemons')
(156, 'Vanilla Dreams')
(159, 'yang Cake')

customers  表中数据有：
('Vicky', 'Yellow Garden 2', 11)
5、练习join相关sql语句：

组合用户和产品，查看用户最喜欢的产品名称：
('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemons')

选择所有用户及其喜爱的产品：
('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemons')
('Hannah', None)
('Michael', None)

选择所有产品以及喜欢它们的用户：
('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemons')
(None, 'Vanilla Dreams')
(None, 'yang Cake')