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

时间:2022-06-13 03:57:06

本文实例讲述了python操作mysql数据库的两种方式。分享给大家供大家参考,具体如下:

第一种 使用pymysql

代码如下:

?
1
2
3
4
5
6
7
8
9
10
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的内容和上边的代码读出来的内容分别是

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

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

pymysql是python操作mysql数据库的模块。首先引入pymysql模块

?
1
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

代码如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import char,int
connect_info = 'mysql+pymysql://username:passwd@host:3306/dbname?charset=utf8'
engine = create_engine(connect_info) #use sqlalchemy to build link-engine
sql = "select * from test0811" #sql query
df = 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通常包含了数据库的相关信息,典型的形式是:

?
1
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,代码如下:

?
1
2
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()不用指定长度。

参考资料:

http://www.zzvips.com/article/169048.html

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程序设计有所帮助。

原文链接:https://blog.csdn.net/heshiliqiu/article/details/81590685