pymysql库连接
pandas读
# pymysql 是在 python3.x 版本中用于连接 MySql 服务器的一个库 # 1.创建一个连接对象 # host mysql 服务器地址 # port 数字类型 端口 # user 用户名 # passwd 密码 # db 数据库名称 # charset 连接编码，需要显示指明编码方式 # 2.获取游标 # 游标是什么？ # 简述：游标（cursor）是一个游动的标识，可以理解成一条sql取出对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行 # 3.执行增删改查的操作 # 4.处理数据 # 5.关闭cursor # 6.关闭connection import pymysql import pandas as pd # 创建一个连接对象，打开数据库连接 conn=pymysql.connect(host='localhost',port=3306,user='root',passwd='密码',db='数据库名',charset='utf8') # 获取游标 cursor=conn.cursor() # 创建一个字符串语句，字符串支持换行 sql = """ select * from football.player """ cursor.execute(sql) data1=cursor.fetchall() df1=pd.DataFrame(list(data1),columns=['id','name','club_id','country_id','position_id','history_club_id','active']) print(df1) cursor.close() conn.close()
2.其他方法
| 方法 | 描述 |
|---|---|
| begin() | 开启事务 |
| commit() | 提交事务 |
| cursor(cursor=None) | 创建一个游标用来执行语句 |
| ping(reconnect=True) | 检查连接是否存活，会重新发起连接 |
| rollback() | 回滚事务 |
| close() | 关闭连接 |
| select_db(db) | 选择数据库 |
| show_warnings() | 查看warning信息 |
3.获取cursor对象
1.游标对象，用于执行查询和获取结果
2.核心方法
| 方法名 | 说明 |
|---|---|
| execute() | 用于执行一个数据库的查询命令 |
| fetchone() | 获取结果集中的下一行 |
| fetchmany(size) | 获取结果集中的下（size）行 |
| fetchall() | 获取结果集中剩下的所有行 |
| rowcount | 最近一次execute返回数据/影响的行数 |
| close() | 关闭游标 |
4.举个栗子
# 执行查询功能 with connection.cursor() as cursor: sql = 'select * from home_user' cursor.execute(sql) results = cursor.fetchall() connection.commit() for results in results: uid = results[0] name = results[1] password = results[2] print('==========用户信息===============') print('用户id: {id} 用户名: {name} 密码: {pwd}'.format(id=uid, name=name, pwd=password))
5.详细
| 方法 | 描述 |
|---|---|
| close() | 关闭游标。 |
| execute(query, args=None) | 执行单条语句，传入需要执行的语句，是string类型；同时可以给查询传入参数，参数可以是tuple、list或dict。执行完成后，会返回执行语句的影响行数，如果有的话。 |
| executemany(query, args) | 执行多条INSERT语句，传入需要执行的语句；同时可以给查询传入参数，参数是一个mappings序列。执行完成后，会返回执行语句的影响行数，如果有的话。 |
| fetchone() | 获取下一行数据。 |
| fetchall() | 获取所有数据。 |
| fetchmany(size=None) | 获取几行数据。 |
| read_next() | 获取下一行数据。 |
| callproc() | 用来调用存储过程。 |
| mogrify() | 参数化查询，防止SQL注入。 |
| scroll(num,mode) | 移动游标位置。 |
6.基本操作
查询数据
# 1.分页查询操作 def find_by_page(page, size): with pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWORD, charset=CHARSET, db=DB_NAME) as cursor: sql = "SELECT * FROM t_addr LIMIT{},{}".format((page - 1) * size, size) cursor.execute(sql) user = cursor.fetchall()
事务操作
conn = pymysql.connect( host='10.10.0.109', port=3306, user='mha', password='123456', database='sbtest', charset='utf8' ) cursor = conn.cursor() # 插入sql; sql_insert = "insert into t_user (userid,username) values (10,'user10')" # 更新sql; sql_update = "update t_user set username = 'name91' where userid=9" # 删除sql; sql_delete = "delete from t_user where userid < 3" # 把一个事务放到一个try块里，如果出现异常就回滚； try: # 开启事务; conn.begin() cursor.execute(sql_insert) print(cursor.rowcount) cursor.execute(sql_update) print(cursor.rowcount) cursor.execute(sql_delete) print(cursor.rowcount) # 提交事务; conn.commit() except Exception as e: # 若有异常就回滚; conn.rollback() cursor.close() conn.close()
批量插入
# 测试事务 批量添加 def test_batch_insert(): conn = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWORD, charset=CHARSET, db=DB_NAME) cursor = conn.cursor() try: sql = 'INSERT INTO t_addr(PROVICE, CITY, COUNTY, DEATIL, USERID) VALUES (%s,%s,%s,%s,%s)' li = [] for i in range(50): li.append(('湖北省', '武汉市', '高新区' + str(i), '智慧园2135', 6)) # 开启事物 conn.begin() cursor.executemany(sql, li) # 提交 conn.commit() except Exception as e: conn.rollback() print(e) # 报错事务回滚 finally: # 关闭连接 cursor.close() conn.close()
通过跳板机SSH连接连接MySQL
import pymysql import pandas as pd from sshtunnel import SSHTunnelForwarder with SSHTunnelForwarder( # 配置SSH连接 ssh_address_or_host=('跳板机对应的地址',int('端口号')), # 指定SSH登陆的条件及的address ssh_password='跳转机密码', # 跳转机的密码 ssh_username='跳转机用户名', # 跳转机的用户名 local_bind_address=('127.0.0.1',int('2222')), # 映射到本机的地址和端口，此处必须是127.0.0.1 remote_bind_address=('mysql服务器地址',int('端口号'))) as server: # MySQL服务器的配置，有数据的服务器 conn=pymysql.connect(host='localhost',port=3306,user='root',passwd='密码',db='数据库名',charset='utf8') cursor=conn.cursor() # 获取游标