Yasin 2020-02-13
本实验环境
操作系统: win10
python版本: 3.6.5
mysql版本: 8.0.18
MySQLdb库和pysql库使用方法相同,但是python2和3安装使用MySQLdb的方法如下,python3使用pysql直接pip安装,python2不谈
python2:
pip install MySQL-python
python3:
pip install mysqlclient
具体使用方法如下,如若使用pysql就直接import pysql
import MySQLdb #连接数据库 connect方法,参数有host,port,user,password,[db],charset ‘‘‘创建数据库‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", charset="utf8") cursor = db.cursor() # 新建游标 dbName = "newdb" sql = "create database %s"%dbName cursor.execute(sql) db.close() ‘‘‘创建数据表‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql = ‘create table student_info(id int,name varchar(10),address varchar(255),tel int(10))‘ cursor.execute(sql) db.close() ‘‘‘插入数据‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql1 = "insert into student_info(id,name,address,tel) values(1000002,‘tttttshy‘,‘滨海大道1001‘,1388888888)" sql2 = "insert into student_info(id,name,address,tel) values(1000005,‘dddddnb‘,‘扬帆大道1010‘,1338888888)" try: cursor.execute(sql1) cursor.execute(sql2) db.commit() # 提交操作事务 有内容的修改一定要commit except: print("error") db.rollback()# 回滚事务 db.close() ‘‘‘查询数据‘‘‘ # fetchone(): 该方法获取下一个查询结果集。结果集是一个对象 # fetchall():接收全部的返回结果行. # rowcount: 这是一个只读属性,并返回执行execute()方法后影响的行数。 db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql = "select * from student_info" cursor.execute(sql) result = cursor.fetchall() for i in result: print(i) db.close() ‘‘‘修改数据‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql = "update student_info set name=‘nnnnnning‘ where name like ‘dddddnb‘" try: cursor.execute(sql) db.commit() except: print("error") db.rollback() db.close() ‘‘‘删除数据‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql = ‘delete from student_info where name like "nnnnnning"‘ try: cursor.execute(sql) db.commit() except: print("error") db.rollback() db.close() ‘‘‘删除数据表‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql = ‘drop table student_info‘ try: cursor.execute(sql) db.commit() except: print("error") db.rollback() db.close() ‘‘‘删除数据库‘‘‘ db = MySQLdb.connect(host="localhost", port=3306, user="root", password="123456", db=‘newdb‘, charset="utf8") cursor = db.cursor() sql = ‘drop database newdb‘ try: cursor.execute(sql) db.commit() except: print("error") db.rollback() db.close()