Python操作MySQL数据库的两种方式实例分析【pymysql和pandas】

Python  /  管理员 发布于 7年前   291

本文实例讲述了Python操作MySQL数据库的两种方式。分享给大家供大家参考，具体如下：

第一种 使用pymysql

代码如下：

import pymysql#打开数据库连接db=pymysql.connect(host='1.1.1.1',port=3306,user='root',passwd='123123',db='test',charset='utf8')cursor=db.cursor()#使用cursor()方法获取操作游标sql = "select * from test0811"cursor.execute(sql)info = cursor.fetchall()db.commit()cursor.close() #关闭游标db.close()#关闭数据库连接

数据表test0811的内容和上边的代码读出来的内容分别是

pymysql是Python操作MySQL数据库的模块。首先引入pymysql模块

import pymysql

使用pymysql的connect()方法连接数据库，connect的几个参数解释如下：

• host：MySQL服务的地址，若数据库在本地上，使用localhost或者127.0.0.1。如果在其它的服务器上，应该写IP地址。
• port：服务的端口号，默认为3306，如果不写，为默认值。
• user：登录数据库的用户名
• passwd：user账户登录MySQL的密码
• db：将要操作的数据库的名字
• charset：设置为utf8编码，这样就可以存入汉字没有乱码

注意：除了port=3306不用引号，其它项的值都有用引号括起来

代码中的db就架起了Python和MySQL通信的桥梁，db.cursor()表示返回连接的游标对象，通过游标执行SQL语句。还有几个常用的方法是commit()表示提交数据库修改，rollback()表示回滚，就是取消当前的操作，close()表示关闭连接。

上面讲的是连接对象db的一些方法，游标对象的一些方法也很重要，利用游标对象的方法就可以对数据库进行操作了，游标对象的常用方法如下表：

名称	描述
close()	关闭游标，之后游标不可用
execute(query[,args])	执行一条SQL语句，可以带参数
executemany(query,pseq)	对序列pseq中的每个参数执行SQL语句
fetchone()	返回一条查询结果
fetchall()	返回所有查询结果
fetchmany([size])	返回size条查询结果
nextset()	移动到下一条结果
scroll(value,mode='relative')	移动游标到指定行，如果mode='relative'，则表示从当前行移动value条，如果mode=‘absolute'，则表示从结果集的第一行移动value条

到这里就基本把pymysql的基本用法讲清楚了，剩下的对数据库的操作（增删改查）就是SQL语句的事情了。虽然SQL语句很强大，但有时候也会显得力不从心，Python的灵活加上SQL的强大才可以做更多的事情，而pymysql只是充当工具、桥梁的作用。从代码运行的结果中（第二幅图）发现读出来的结果是存放在二维元组中的，即((1, '小红', '80'),(2, '小明', '90'),(3, '小美', '87'),(4, 'GG', '67'),(5, 'MM', '78'))，但是元组不可改变，只能读出，对于数据处理还有些不便，下面第二种方法就是把数据读出存放在DataFrame中，便于处理。

第二种 使用pandas

代码如下：

import pandas as pdfrom sqlalchemy import create_enginefrom sqlalchemy.types import CHAR,INTconnect_info = 'mysql+pymysql://username:passwd@host:3306/dbname?charset=utf8'engine = create_engine(connect_info) #use sqlalchemy to build link-enginesql = "SELECT * FROM test0811" #SQL querydf = pd.read_sql(sql=sql, con=engine) #read data to DataFrame 'df'#write df to table 'test1'df.to_sql(name = 'test1',      con = engine,      if_exists = 'append',      index = False,      dtype = {'id': INT(),          'name': CHAR(length=2),          'score': CHAR(length=2)          }      )

pandas的DataFrame数据格式有行索引和列索引，使用DataFrame来存储数据库表中的数据会十分方便。使用pandas中的read_sql和to_sql函数从MySQL数据库中读写数据。两个函数介绍如下。

pandas.read_sql
复制代码 代码如下:pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
pandas.read_sql的文档中有详细的各个参数的英文介绍（不要排斥看英文，虚心向老外学习），参考资料http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html

常用的参数是sql：SQL命令或者表名字，con：连接数据库的引擎，可以用SQLAlchemy或者pymysql建立，从数据库读数据的基本用法给出sql和con就可以了。其它都是默认参数，有特殊需求才会用到，有兴趣的话可以查看文档。

代码中的con是使用SQLAlchem构建数据库连接引擎，即sqlalchemy.create_engine( )。这个函数基于一个URL来产生一个引擎对象，URL通常包含了数据库的相关信息，典型的形式是：

dialect+driver://username:password@host:port/database

dialect表示数据库的名字，比如sqlite，mysql，postgresql，oracle，mssql等，driver是用于连接数据库的DBAPI的名字，这里用的是pymysql（Python 3.x，在Python 2.x中用的是mysqldb），如果这一项不指定，将使用默认的DBAPI。

除了使用SQLAlchemy创建engine外，还可以直接使用DBAPI创建engine，代码如下：

con = pymysql.connect(host=localhost, user=username, password=password, database=dbname, charset='utf8')df = pd.read_sql(sql, con)

pandas.DataFrame.to_sql
复制代码 代码如下:DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
主要参数介绍如下，详细文档参考http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html

• name：输出的表名
• con：连接数据库的引擎
• if_exists：三种模式{“fail”,“replace”,"append"}，默认是"fail"。fail：若表存在，引发一个ValueError；replace：若表存在，覆盖原来表内数据；append：若表存在，将数据写到原表数据的后面。
• index：是否将DataFrame的index单独写到一列中，默认为“True”
• index_label：当index为True时，指定列作为DataFrame的index输出
• dtype：指定列的数据类型，字典形式存储{column_name: sql_dtype}，常见数据类型是sqlalchemy.types.INT()和sqlalchemy.types.CHAR(length=x)。注意：INT和CHAR都需要大写，INT()不用指定长度。

参考资料：

///article/157984.htm

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html

http://docs.sqlalchemy.org/en/latest/core/engines.html

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html

更多关于Python相关内容感兴趣的读者可查看本站专题：《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》

希望本文所述对大家Python程序设计有所帮助。