开始安装MySQL驱动$python-mpipinstallmysql-connector-pythontestMySQLconnectorimportmysql.connectortestMySQLconnectionimportmysql.connectormydb=MySQL。connector.connect(host="localhost",user="yourusername",password="yourpassword")print(mydb)创建数据库importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword")mycursor=mydb.cursor()mycursor.execute("CREATEDATABASEmydatabase")创建表importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("CREATETABLEcustomers(nameVARCHAR(255),addressVARCHAR(255))")insertdataimportmysql.connectormydb=mysql.connector.connect(host="localhost",user="你的用户名",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="INSERTINTOcustomers(name,address)VALUES(%s,%s)"val=("John","Highway21")mycursor.execute(sql,val)mydb.commit()print(mycursor.rowcount,"recordinserted.")重要！请注意此语句：mydb.commit()。需要进行修改，否则表不会被修改。插入多行importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="INSERTINTOcustomers(name,address)VALUES(%s,%s)"val=[('Peter','Lowstreet4'),('Amy','Applest652'),('Hannah','Mountain21'),('Michael','Valley345'),('Sandy','Oceanblvd2'),('Betty','GreenGrass1'),('Richard','Skyst331'),('Susan','单向98'),('Vicky','黄色花园2'),('Ben','ParkLane38'),('William','Centralst954'),('Chuck','MainRoad989'),('Viola','Sideway1633')]mycursor.executemany(sql,val)mydb.commit()print(mycursor.rowcount,"wasinserted.")获取插入的IDimportmysql.connectormydb=mysql.connector.connect(host="localhost",user="您的用户名",password="您的密码",database="mydatabase")mycursor=mydb.cursor()sql="INSERTINTOcustomers(name,address)VALUES(%s,%s)"val=("Michelle","BlueVillage")mycursor.execute(sql,val)mydb.commit()print("插入1条记录，ID:",mycursor.lastrowid)queryimportmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("SELECT*FROMcustomers")myresult=mycursor.fetchall()forxinmyresult:print(x)selectfromtableimportmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("SELECT*FROMcustomers")myresult=mycursor.fetchall()forxinmyresult:print(x)注意：我们使用fetchall()方法从最后执行的语句中获取所有行选择列导入mysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("SELECTname,addressFROMcustomers")myresult=mycursor.fetchall()forxinmyresult:print(x)如果只想查询一条数据，可以使用fetchone()方法。importmysql.connectormydb=mysql.connector.connect(host="localhost",user="您的用户名",password="您的密码",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("SELECT*FROMcustomers")myresult=mycursor.fetchone()print(myresult)Where条件使用筛选器选择importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="SELECT*FROMcustomersWHEREaddress='ParkLane38'"mycursor.execute(sql)myresult=mycursor.fetchall()forxinmyresult:print(x)通配符importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="SELECT*FROMcustomersWHERE地址像'%way%'"mycursor.execute(sql)myresult=mycursor.fetchall()forxinmyresult:print(x)防止SQL注册importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="SELECT*FROMcustomersWHEREaddress=%s"adr=("黄色花园2",)mycursor.execute(sql,adr)myresult=mycursor.fetchall()forxinmyresult:print(x)OrderBy对结果进行排序importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="SELECT*FROMcustomersORDERBYname"mycursor.execute(sql)myresult=mycursor.fetchall()forxinmyresult:print(x)使用DESC关键字对结果进行降序排列importmysql.connectormydb=mysql.connector.connect(host="localhost",user="您的用户名",password="您的密码",database="mydatabase")mycursor=mydb.cursor()sql="SELECT*FROMcustomersORDERBYnameDESC"mycursor.execute(sql)myresult=mycursor.fetchall()forxinmyresult:print(x)删除记录importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="DELETEFROMcustomersWHEREaddress='Mountain21'"mycursor.execute(sql)mydb.commit()print(mycursor.rowcount,"record(s)deleted")阻止SQL注册importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="从客户中删除omersWHEREaddress=%s"adr=("YellowGarden2",)mycursor.execute(sql,adr)mydb.commit()print(mycursor.rowcount,"record(s)deleted")删除表importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="DROPTABLEcustomers"mycursor.execute(sql)更新数据importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="UPDATEcustomersSETaddress='Canyon123'WHEREaddress='Valley345'"mycursor.execute(sql)mydb.commit()print(mycursor.rowcount,"record(s)affected")阻止SQL注册importmysql.connectormydb=mysql。connector.connect(host="localhost",user="你的用户名",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="UPDATEcustomersSETaddress=%sWHEREaddress=%s"val=("Valley345","Canyon123")mycursor.execute(sql,val)mydb.commit()print(mycursor.rowcount,"record(s)affected")限制结果importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("SELECT*FROMcustomersLIMIT5")myresult=mycursor.fetchall()forxinmyresult:print(x)从另一个位置开始导入mysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()mycursor.execute("SELECT*FROMcustomersLIMIT5OFFSET2")myresult=mycursor.fetchall()forxinmyresult:print(x)连接两个或多个数据表#userstable{id:1,name:'John',fav:154},{id:2,name:'Peter',fav:154},{id:3,姓名：'Amy',fav:155},{id:4,name:'Hannah',fav:},{id:5,name:'Michael',fav:}#products{id:154,name:'ChocolateHeaven'},{id:155,name:'TastyLemons'},{id:156,name:'香草梦'}importmysql.connectormydb=mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase")mycursor=mydb.cursor()sql="SELECT\users.nameASuser,\products.nameASfavorite\FROMusers\INNERJOINproductsONusers.fav=products.id"mycursor.execute(sql)myresult=mycursor.fetchall()forxinmyresult:print(x)endMySQL的基本操作是这样的
