目录
1. mysql与python的初遇
1.1 mysql建表
1.2 pychram安装mysql:
(venv) D:\python_files>pip install pymysql
Collecting pymysql
Using cached PyMySQL-1.0.2-py3-none-any.whl (43 kB)
Installing collected packages: pymysql
Successfully installed pymysql-1.0.2
获取主机ip:
1.3 python连接数据库
#连接方式1
conn = pymysql.connect(host='127.0.0.1' # 连接名称,默认127.0.0.1
, user='root' # 用户名
, passwd='lkw,747477910' # 密码
, port=3306 # 端口,默认为3306
, db='test' # 数据库名称
, charset='utf8' # 字符编码
)
import pymysql
dbhost='localhost'
port = 3306
dbuser='root'
dbpass='lkw,747477910'
dbname='win_2022'
try:
#db=pymysql.connect(dbhost,port,dbuser,dbpass,dbname) #这里为什么不对
db = pymysql.connect(host=dbhost, user=dbuser, password=dbpass, database=dbname)
print("数据库连接成功")
except pymysql.Error as e:
print("数据库连接失败:"+str(e))
db.close() #关闭数据库连接
2. 操作数据库
2.1 插入数据
任务:向win_2022数据库中的zard表里插入数据。
做如下代码:
#任务:插入数据
import pymysql
db = pymysql.connect("localhost","root",'lkw,747477910','win_2022') #打开数据库连接
cursor = db.cursor() #使用cursor()方法获取游标操作
#编写SQL语句
sql = "insert into zard (name,major,hobby,value,ziduan) value ('%s','%s','%s','%s','%s')" %("坂井泉水","歌手",'摇滚乐',"23","不要认输")
try:
cursor.execute(sql) #执行sql语句
db.commit() #提交事务
except:
db.rollback()
db.close() #关闭数据库连接
运行报错:
检验该错误,判段为连接数据库出错,同:
#检验连接数据库是否成功
import pymysql
dbhost='localhost'
dbuser='root'
dbpass='lkw,747477910'
dbname='win_2022'
try:
db=pymysql.connect(dbhost,dbuser,dbpass,dbname)
print("数据库连接成功")
except pymysql.Error as e:
print("数据库连接失败:"+str(e))
更正:
try:
#db=pymysql.connect(dbhost,dbuser,dbpass,dbname) #这里为什么不对
db = pymysql.connect(host=dbhost, user=dbuser, password=dbpass, database=dbname)
print("数据库连接成功")
except pymysql.Error as e:
print("数据库连接失败:"+str(e))
总结:第一种传递参数的方式为位置传参,容易出错,用第二种传参(关键字参数)就不那么容易错了。
由上更正“插入数据”任务代码为:
#任务:插入数据
import pymysql
#打开数据库连接
try:
#db = pymysql.connect("localhost","root","lkw,747477910","win_2022") #这里为什么不对
db = pymysql.connect(host="localhost",user="root",password='lkw,747477910',database='win_2022')
print("数据库连接成功")
except pymysql.Error as e:
print("数据库连接失败:"+str(e))
cursor = db.cursor() #使用cursor()方法获取游标操作
#编写SQL语句
sql = "insert into zard (name,major,hobby,value,ziduan) value ('%s','%s','%s','%s','%s')" %("坂井泉水","歌手",'摇滚乐',"23","不要认输")
try:
cursor.execute(sql) #执行sql语句
db.commit() #提交事务
except:
db.rollback()
db.close() #关闭数据库连接
运行成功:
感悟:对于没有把握写对的代码,要善于利用try语句抛出异常。
添加数据成功:
2.2 删除数据
删除如下id为2的数据:
只要修改sql代码即可:
#编写SQL语句
#sql = "insert into zard (name,major,hobby,value,ziduan) value ('%s','%s','%s','%s','%s')" %("坂井泉水","歌手",'摇滚乐',"23","不要认输")
sql ="delete from zard where id = 2"
2.3 更改数据
更改如上表id为3的数据:
也是只要改sql语句即可:
#编写SQL语句
#sql = "insert into zard (name,major,hobby,value,ziduan) value ('%s','%s','%s','%s','%s')" %("坂井泉水","歌手",'摇滚乐',"23","不要认输") #增
#sql ="delete from zard where id = 2" #删
sql = "update zard set name='小松未步', major='作曲家', hobby='流行乐', value='18', ziduan='想要和你生活在那条街道' where id=2" #改
2.4 查询数据
#编写SQL语句
#sql = "insert into zard (name,major,hobby,value,ziduan) value ('%s','%s','%s','%s','%s') " %("坂井泉水","歌手",'摇滚乐',"23","不要认输") #增
#sql ="delete from zard where id = 2" #删
#sql = "update zard set name='小松未步', major='作曲家', hobby='流行乐', value='18', ziduan='想要和你生活在那条街道' where id=2" #改
sql = "select * from zard" #查询
try:
cursor.execute(sql) #执行sql语句
results = cursor.fetchall() #查询所有数据,并以元组方式返回
print(results)
db.commit() #提交事务
except:
db.rollback()#如果发生错误,就会回滚
db.close() #关闭数据库连接
"""
sys.path.extend(['D:\\python_files', 'D:/python_files'])
PyDev console: starting.
Python 3.7.0 (default, Jun 28 2018, 08:04:48) [MSC v.1912 64 bit (AMD64)] on win32
runfile('D:/python_files/mysql/mysql_01.py', wdir='D:/python_files/mysql')
数据库连接成功
((1, '坂井泉水', '歌手', '摇滚乐', '23', '不要认输'), (3, '小松未步', '作曲家', '流行乐', '18', '想要和你生活在那条街道'), (4, '坂井泉水', '歌手', '摇滚乐', '23', '不要认输'))
"""
注: