首页 / PYTHON / 笔记-python操作mysql
笔记-python操作mysql
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了笔记-python操作mysql,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2959字,纯文字阅读大概需要5分钟。
内容图文
![笔记-python操作mysql](/upload/InfoBanner/zyjiaocheng/917/6f00aae45f884dbcb4fab52e2d68e4f8.jpg)
笔记-python操作mysql
1. 开始
1.1. 环境准备-mysql
create database db_python;
use db_python;
create table `t2`(
`id` int unsigned auto_increment,
`name` varchar(30),
primary key(`id`));
#创建用户并授权
create user 'dev_python' identified by '123456';
grant all on db_python.t2 to dev_python@'%';
alter table t2 add password varchar(30);
alter table t2 add content varchar(500);
1.2. 环境-python
使用pymysql库,PyMySQL是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中是使用mysqldb。
pip install pymysql
2. API
2.1. connect
连接类,有一些参数可以调整,列出最常用的部分:
# connect to database, parameter: address, user name, password, database name,charset
db = pymysql.connect('localhost','dev_python','123456','db_python',cursorclass=pymysql.cursors.DictCursor)
2.2. cursor
执行操作的接口,有
execute(query, args=None)
Execute a query
Parameters: |
query (str) – Query to execute. args (tuple, list or dict) – parameters used with query. (optional) |
Returns: |
Number of affected rows |
Return type: |
If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.
fetchall()
Fetch all the rows
fetchmany(size=None)
Fetch several rows
fetchone()
Fetch the next row
classpymysql.cursors.DictCursor(connection)
A cursor which returns results as a dictionary
返回的是一个字典,使用时需要注意返回的字典的健值。
3. 使用
import pymysql
# connect to database, parameter: address, user name, password,
database name,charset
db = pymysql.connect('localhost','dev_python','123456','db_python',cursorclass=pymysql.cursors.DictCursor)
'''
# 得到一个可以执行sql语句的cursor object
# 注意cursor支持上下文,
# with db.cursor() as cur:
cursor = db.cursor()
#定义要执行的sql语句
# 本处为查看表结构
cmd_text = 'desc t2;'
# 执行
print(cursor.execute(cmd_text))
# 关闭相关对象
cursor.close()
db.close()
'''
# execute statement
#create table
sql_create = r"""create table if not
exists `t3` (`id` int)"""
with db.cursor() as cur:
cur.execute(sql_create)
print(cur.execute('show
tables;'))
print(cur.fetchall())
#CURD
# 插入
cmd_insert = """insert into t2
values('7','first_name','pass1', 'content1')"""
with db.cursor() as cur:
pass
#print(cur.execute(cmd_insert))
db.commit()
# update
sql_update = """update t2 set name=%s
where id=%s"""
with db.cursor() as cur:
cur.execute(sql_update, ['ppp','7'])
cur.execute('select * from t2')
#print(cur.fetchall())
# retrieve
sql_select = """select * from
t3;"""
with db.cursor() as cur:
if cur.execute(sql_select):
print(cur.fetchall())
else:
print('get 0
rows.')
# delete
sql_delete = """delete from t2 where
id=%s"""
with db.cursor() as cur:
cur.execute(sql_delete, ['7'])
cur.execute('select * from t2')
print(cur.fetchall())
上面列出的仅包括最常用的CURD操作,更复杂的功能需要参考相关接口文档。
4. 参考文档
https://pypi.org/project/PyMySQL/
https://pymysql.readthedocs.io/en/latest/
内容总结
以上是互联网集市为您收集整理的笔记-python操作mysql全部内容,希望文章能够帮你解决笔记-python操作mysql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。