python中连接mysql数据库
1.pymysql模块
python3中可以用pymysql模块来连接mysql数据库
pymysql并不是内置模块,需手动下载(终端pip3 install pymysql 或在pycharm中的terminal输入这段代码)
如何使用pymysql建立与mysql的连接?
import pymysql # 1.连接database conn = pymysql.connect( host='127.0.0.1', # 你的数据库地址 port=3306, # 端口号 user='root', # 用户名 password='101415', # 密码 database='day43', # 要连接的库名 charset='utf8' # 指定编码 ) # 2.得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 3.定义要执行的SQL语句 sql = """ create table user1( id int primary key auto_increment, name char(10) not null unique, age tinyint not null )engine=innodb default charset=utf8; """ # 4.执行SQL语句 cursor.execute(sql) # 5.关闭光标对象 cursor.close() # 6.关闭数据库连接 conn.close()
2.简单的增删改查
2.1 增
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='101415', database='day43', charset='utf8' ) cursor = conn.cursor() sql = "insert into user1(name,age) values (%s, %s);" # 向user1中增加一个值 cursor.execute(sql,['zhangsan',23]) # 用pymysql中的内置拼接,不要在上面sql语句处自己拼接 conn.commit() # 向数据库发送 cursor.close() conn.close()
2.2 删
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='101415', database='day43', charset='utf8' ) cursor = conn.cursor() sql = "delete from user1 where id=1" # 删除id为1的行 cursor.execute(sql) conn.commit() # 不要忘记发送 cursor.close() conn.close()
2.3 改
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='101415', database='day43', charset='utf8' ) cursor = conn.cursor() sql = "update user1 set age=%s where name=%s;" # 将张三的年龄改成77 ret = cursor.execute(sql,[77,'zhangsan']) conn.commit() cursor.close() conn.close()
2.4 查
设置查询返回值的数据类型
# 上述获取光标的时候可以指定查询结果的数据类型 # 默认返回结果是元组类型 cursor = conn.cursor() # 设置光标类型为dictcursor,再获取返回的时候,返回的是字典 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
获取返回值
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='101415', database='day43', charset='utf8' ) cursor = conn.cursor() # 默认返回的是元组 sql = "select * from user1" cursor.execute(sql) ret = cursor.fetchall() # 获取所有的返回值 print(ret) ret = cursor.fetchmany(3) # 只获取3个返回值(根据光标位置获取) print(ret) ret = cursor.fetchone() # 只获取一个返回值 print(ret) cursor.close() conn.close()
3. 回滚
当插入数据出现异常时,可以利用回滚来取消操作
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='101415', database='day43', charset='utf8' ) cursor = conn.cursor() sql = "insert into user1(name,age) values (%s, %s);" try: cursor.execute(sql,['liwu',34]) conn.commit() except Exception as e: conn.rollback() # 碰到异常就回滚,即取消上述插入 cursor.close() conn.close()