Python基础(十六)-操作数据库pymysql模块,,一、pymysql模
Python基础(十六)-操作数据库pymysql模块,,一、pymysql模
一、pymysql模块安装
pip3 install pymysql
二、连接数据库
2.1、创建测试数据
mysql> create database AA;mysql> use AAmysql> create table test(id int primary key auto_increment,name varchar(25),passwd varchar(25));mysql> insert into test(name,passwd) values(‘AA‘,123),(‘BB‘,456),(‘CC‘,789);mysql> select * from test;+----+------+--------+| id | name | passwd |+----+------+--------+| 1 | AA | 123 || 2 | BB | 456 || 3 | CC | 789 |+----+------+--------+3 rows in set (0.00 sec)
2.2、连接数据库
#!/usr/bin/env python# -*- coding: utf-8 -*- import pymysqluser=input("用户名:").strip()pwd=input("密码:").strip()#连接conn = pymysql.connect(host="10.0.0.12",port=3306,user="root",passwd="mysql",db="AA")#游标cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #以字典形式返回#执行sql语句# sql = ‘select * from AA.test where name="%s" and passwd="%s"‘ %(user,pwd) #注意%s需要加引号# print(sql)# res=cursor.execute(sql) #执行sql语句,返回sql查询成功的记录数目# print(res)#execute帮我们做字符串拼接,我们无需且一定不能再为%s加引号了,pymysql会自动为我们加上sql="select * from test where name=%s and passwd=%s"print(sql)res=cursor.execute(sql,[user,pwd])print(res)#关闭游标及连接cursor.close()conn.close()if res: print("登录成功")else: print("登录失败")
三、增删改,conn.commit()
#!/usr/bin/env python# -*- coding: utf-8 -*- import pymysql#连接conn = pymysql.connect(host="10.0.0.12",port=3306,user="root",passwd="mysql",db="AA")#游标cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #以字典形式返回#执行sql语句#part1# sql="insert into test(name,passwd) values(‘DD‘,123456);"# print(sql)# res=cursor.execute(sql)# print(res)#part2# sql=‘insert into test(name,passwd) values(%s,%s);‘# res=cursor.execute(sql,("root","123456"))# print(res)#part3 插多行数据sql=‘insert into test(name,passwd) VALUES (%s,%s);‘res=cursor.executemany(sql,[("EE","123456"),("FF","123456"),("GG","123456"),])print(res)#关闭游标及连接conn.commit() #提交后才发现表中插入记录成功cursor.close()conn.close()
四、查找
#!/usr/bin/env python# -*- coding: utf-8 -*-import pymysql#连接conn = pymysql.connect(host="10.0.0.12",port=3306,user="root",passwd="mysql",db="AA")#游标cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #以字典形式返回#查找sql=‘select * from test;‘rows=cursor.execute(sql)print(rows) #8# cursor.scroll(3,mode="absolute") # 相对绝对位置移动# cursor.scroll(3,mode="relative") # 相对当前位置移动res1=cursor.fetchone()res2=cursor.fetchone()res3=cursor.fetchone()res4=cursor.fetchmany(2)res5=cursor.fetchall()print(res1)print(res2)print(res3)print(res4)print(res5)print(‘%s rows in set (0.00 sec)‘ %rows)#关闭游标及连接conn.commit() #提交后才发现表中插入记录成功cursor.close()conn.close()‘‘‘(1, ‘AA‘, ‘123‘)(2, ‘BB‘, ‘456‘)(3, ‘CC‘, ‘789‘)((4, ‘DD‘, ‘123456‘), (5, ‘root‘, ‘123456‘))((6, ‘EE‘, ‘123456‘), (7, ‘FF‘, ‘123456‘), (8, ‘GG‘, ‘123456‘))8 rows in set (0.00 sec)‘‘‘
Python基础(十六)-操作数据库pymysql模块
评论关闭