Python3下mysql-connector的基础操作

Python / 2021年5月23日 / hamster

# -*- coding = utf-8 -*-

import mysql.connector


#显示所有数据库
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="")
# mycursor = mydb.cursor()
# sql = "SHOW DATABASES"
# mycursor.execute(sql)

#创建数据库
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="")
# mycursor = mydb.cursor()
# ###简单的创建，数据库默认字符集Latin1
# sql = "CREATE DATABASE python_db"
# ###特定的创建,定义数据库字符集为utf8，并在创建时查验该数据库是否已存在
# sql = "CREATE DATABASE IF NOT EXISTS python_db2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci"
# mycursor.execute(sql)

#创建数据表
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# ###简单的创建，数据表的字符集随数据库的字符集而定
# #sql = "CREATE TABLE test1 (name VARCHAR(20), url VARCHAR(255))"
# ###特定的创建，定义数据表的存储类型以及字符集,并在创建时查验该数据表是否存在
# sql = "CREATE TABLE IF NOT EXISTS test2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT,ctitle VARCHAR(30),otitle VARCHAR(100),url VARCHAR(255),img VARCHAR(300) DEFAULT NULL,soc FLOAT(2,1),num INT(10) NOT NULL DEFAULT 0,inq VARCHAR(255),bd TEXT,PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8"
# mycursor.execute(sql)

#主键设置
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# ###如已经创建表
# sql = "ALTER TABLE test1 ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY"
# ###如果还没有创建表
# sql = "CREATE TABLE test1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))"
# mycursor.execute(sql)

#插入数据
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# sql = "INSERT INTO test2 (ctitle, otitle) VALUES (%s, %s)"
# val = ("虹榜","http://www.bookubang.cn")
# mycursor.execute(sql,val)
# mydb.commit()  #数据表内容更新，必须调用该语句
# print(mycursor.rowcount,"插入成功")


#批量插入数据
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# sql = "INSERT INTO test1 (name, url) VALUES (%s, %s)"
# val = [
#     ("虹榜","http://www.bookubang.cn"),
#     ("hongbang","http://www.bookubang.com"),
#     ("python","http://python.org")
# ]
# mycursor.executemany(sql,val)
# mydb.commit()
# print(mycursor.rowcount)  #执行结果，显示插入成功的行数
# print(mycursor.lastrowid)  #显示数据插入成功后的id值，或是多条数据插入，则显示第一条插入数据的id值


#查询多条数据
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# ###查询数据表所有数据
# sql = "SELECT * FROM test1"
# ###查询数据表特定字段数据
# sql = "SELECT name,url FROM test1"
# mycursor.execute(sql)
# mylist = mycursor.fetchall()  #获取所有记录
# for i in mylist:
#     print(i)


#查询单条数据
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# sql = "SELECT name,url FROM test1"
# mycursor.execute(sql)
# mylist = mycursor.fetchone()  #获取单条数据
# print(mylist)


#where语句
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# ###简单的where查询
# sql = "SELECT name,url FROM test1 WHERE name = '虹榜'"
# mycursor.execute(sql)
# ###使用通配符%
# sql = "SELECT name,url FROM test1 WHERE url LIKE '%ku%'"
# mycursor.execute(sql)
# ###防止sql注入攻击，可以使用%s占位符来转移查询的条件
# sql = "SELECT name,url FROM test1 WHERE name = %s"
# val = ("虹榜",)
# mycursor.execute(sql,val)
#
# mylist = mycursor.fetchall()
# for i in mylist:
#     print(i)


#排序
# mydb = mysql.connector.connect(host="127.0.0.1",user="root",passwd="",database="python_db")
# mycursor = mydb.cursor()
# ###升序
# sql = "SELECT * FROM test1 ORDER BY id ASC"
# ###降序
# sql = "SELECT * FROM test1 ORDER BY id DESC"
# mycursor.execute(sql)
# mylist = mycursor.fetchall()
# for i in mylist:
#     print(i)


#limit
# mydb = mysql.connector.connect(host="127.0.0.1", user="root", passwd="", database="python_db")
# mycursor = mydb.cursor()
# ###读取前3条数据
# sql = "SELECT * FROM test1 LIMIT 3"
# ###指定起始位置
# sql = "SELECT * FROM test1 LIMIT 3 OFFSET 2"
# mycursor.execute(sql)
# mylist = mycursor.fetchall()
# for i in mylist:
#     print(i)


#删除数据
# mydb = mysql.connector.connect(host="127.0.0.1", user="root", passwd="", database="python_db")
# mycursor = mydb.cursor()
# ###简单的删除数据
# # sql = "DELETE FROM test1 WHERE NAME = 'test'"
# # mycursor.execute(sql)
# ###防止sql注入攻击，使用%s占位符转移
# sql = "DELETE FROM test1 WHERE NAME = %s"
# val = ("python",)
# mycursor.execute(sql,val)
#
# mydb.commit()
# print(mycursor.rowcount)



#更新表数据
# mydb = mysql.connector.connect(host="127.0.0.1", user="root", passwd="", database="python_db")
# mycursor = mydb.cursor()
# ###简单的更新数据
# # sql = "UPDATE test1 SET name = '虹榜' WHERE name = 'hongbang'"
# # mycursor.execute(sql)
# ###防止sql注入，使用%s占位符转移
# sql = "UPDATE test1 SET name = %s WHERE name = %s"
# val = ("hongbang","虹榜")
# mycursor.execute(sql,val)
# mydb.commit()
# print(mycursor.rowcount)


#删除表
mydb = mysql.connector.connect(host="127.0.0.1", user="root", passwd="", database="python_db")
mycursor = mydb.cursor()
### IF EXISTS 判断数据表是否存在，只有存在的情况下才删除
sql = "DROP TABLE IF EXISTS test1"
mycursor.execute(sql)


#关闭mysql
###执行完对数据的操作后，记得关闭数据表数据库
mycursor.close()
mydb.close()

mysql, mysql-connector, python