Python之操作MySQL数据库

﹏ヽ暗。殇╰゛Y 2022-04-02 06:15 334阅读 0赞

## 安装 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)
    '''