mysql.connector的简单操作

import mysql.connector
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    passwd = "password",
    database = "mypy",
    buffered = True

)

mycursor = mydb.cursor()   # 用于访问和操作数据库中的数据，返回一个指针对象
mycursor.execute("create database mypy ")
mycursor.execute("show databases")
for x in mycursor:
    print(x)

mycursor.execute("create table table2 (num int(7) auto_increment primary key,  name1 char(30), group2 char(20), title char(30))")
mycursor.execute("desc table2")
for x in mycursor:
    print(x)
mycursor.execute("insert into table1 (num, name1) values (01,'Belle')")
mycursor.execute("insert into table1 values (02, 'Bob','centrify', 'STE II')")
mycursor.execute("insert into table1 values (03, 'Lury','centrify', 'STE II')")
mycursor.execute("insert into table1 values (04, 'Mahis','centrify', 'STE I')")
mycursor.execute("insert into table1 values (05, 'Tony','centrify', 'STE III')")
mydb.commit()
print(mycursor.rowcount, "记录插入成功")

sql = "insert into table1 values (%s, %s, %s, %s)"
val = [('06', 'Jia', 'centrify', 'STE I'), ('07', 'Jorie', 'cloud', 'STE II')]
mycursor.executemany(sql, val)
mydb.commit()   # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "记录插入成功")
print("记录已插入，id: ", mycursor.lastrowid)

mycursor.execute("select * from table1")
allresult = mycursor.fetchall()
for x in allresult:
    print("x:", x)
    # print("x[1]:", x[1])

mycursor.execute("select num, name1 from table1")
oneresult = mycursor.fetchone()
print(oneresult)

mycursor.execute("select * from table1 where group2 like '%tri%' ")
groupresult = mycursor.fetchall()
print("groupresult是：", groupresult)

sql2 = "select * from table1 where name1 = %s"
na = ("Tony",)
mycursor.execute(sql2, na)
sqlname = mycursor.fetchall()
print("sqlname的值是", sqlname)

mycursor.execute("select * from table1 limit 3")  # 查询数据表中的前三行
liresult = mycursor.fetchall()
print("liresult的结果是：", liresult)

mycursor.execute("select * from table1 limit 3 offset 2") # 从第3条开始查询3条数据，0位第一条数据
offsetre = mycursor.fetchall()
for m in offsetre:
    print(m)

mycursor.execute("update table1 set group2 = 'cloud' where name1 = 'Belle'")
mycursor.fetchone()
mydb.commit()
print(mycursor.rowcount, '条记录被修改')