Python之操作MySQL数据库

安装 mysql-connector驱动器

python -m pip install mysql-connector
python代码中引入，如果未报错，则表示安装成功
import mysql.connector

操作数据库

import mysql.connector

#连接数据库==============================================
mydb = mysql.connector.connect(
	host="localhost",    #数据库主机地址
	user="root",         #数据库用户名
	password="",    #数据库密码
	database="runoob_db"
)
print(mydb)

#创建数据库=============================================
mycursor = mydb.cursor()

#创建库
#mycursor.execute("CREATE DATABASE runoob_db")

#创建表
#mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), sex VARCHAR(255))default charset = utf8")

#已经添加的表如需加编码格式
#mycursor.execute("ALTER TABLE sites CONVERT TO CHARACTER SET utf8")

#单独主键设置
#mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

#显示表
#mycursor.execute("SHOW TABLES")

#插入数据===============================================
sql = "INSERT INTO sites (name, sex) VALUES (%s, %s)"

#插入一条
'''
val = ("晓红","女")
mycursor.execute(sql,val)
'''
#批量插入
'''
val = [
	("逸轩","男"),
	("逸筱","女"),
	("雯雯","女"),
	("星星","男")
]
mycursor.executemany(sql,val)
'''
#插入的条数
#print(mycursor.rowcount,"记录插入成功")

#插入数据的id
#print("1条记录已插入，ID:",mycursor.lastrowid)

#删除数据 DELETE FROM====================================
#sql = "DELETE FROM sites WHERE name = '星星'"
#mycursor.execute(sql)
#为防止数据库查询发生SQL注入的攻击，使用%s占位符来转义删除语句的条件
'''
sql = "DELETE FROM sites WHERE name = %s"
na = ("星星",)
mycursor.execute(sql,na)
'''

#更新表数据UPDATE========================================
#sql = "UPDATE sites SET name = '星星' WHERE name = '晓红' "
#mycursor.execute(sql)
#为防止数据库查询发生SQL注入的攻击，使用%s占位符来转义更新语句的条件
'''
sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("晓红","星星")
mycursor.execute(sql,val)
'''

#更新数据表==============================================
#mydb.commit()

#删除数据表==============================================
#sql = "DROP TABLE IF EXISTS sites"
#mycursor.execute(sql)

#查询数据================================================
#mycursor.execute("SELECT * FROM sites")

#查询指定列的数据
#mycursor.execute("SELECT name FROM sites")

#where语句查询指定数据
#sql = "SELECT * FROM sites WHERE name = '逸轩'"

#通配符查询
#sql = "SELECT * FROM sites WHERE name LIKE '%逸%'"

#mycursor.execute(sql)

#为了防止数据库发生SQL注入的攻击，可以使用%s占位符来转义查询的条件
'''
sql = "SELECT * FROM sites WHERE name = %s"
na = ("逸轩",)
mycursor.execute(sql, na)
'''

#排序ORDER BY  默认升序
#sql = "SELECT * FROM sites ORDER BY name"
#降序DESC
#sql = "SELECT * FROM sites ORDER BY name DESC"

#设置查询的数据量limit
#sql = "SELECT * FROM sites LIMIT 3"
#指定起始位置，使用关键字offset
#sql = "SELECT * FROM sites LIMIT 3 OFFSET 1"

#mycursor.execute(sql)

#fetchall()获取所有记录
#myresult = mycursor.fetchall() 

#fetchone()获取一条数据
#myresult = mycursor.fetchone()

#输出所有数据库列表
'''
for x in myresult:
	print(x)
'''