sqlalchemy 返回数据是空_Python数据核对系列之3Python读取数据库

在上一篇我们讲解了如何通过python的pandas包 的merge方法对比两个表的数据。但它的数据来源于SQL执行后的数据导出的excel或者csv文件。这会让我们增加人工操作的工作内容。本篇我们将解决这个人工导数问题。直接通过python连接数据库，获取数据库对应表里的数据，加载到pandas的DataFrame中，然后进行merge对比。Python数据核对系列之1——一个偷懒的开始Python数据核对系列之2—power query VS pandas

建立数据库连接-单独连接

要想完成这个操作，我们首先需要通过python建立与数据库的连接。从而可以从数据库中SQL数据。对于不同的数据库，有不同的包用以建立连接。 我们 列举一下常见的关系型数据库的连接 库。

oracle：cx_Oracle
https://cx-oracle.readthedocs.io/en/latest/index.html这是oracle官方开发的python操作oracle数据库的库。这个网址里大家可以看到整个库的使用教程，还可以下载pdf等版本。

# 官方给出的案例 https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.htmluserpwd = ". . ." # Obtain password string from a user prompt or environment variable# hr：用户名；userpwd：密码；dbhost.example.com/orclpdb1：数据库地址，例如192.168.0.1:1521/orcl。oracle的默认端口为1521，默认数据库名称orclconnection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", encoding="UTF-8")

import cx_Oracle # 导入oracle操作库conn = cx_Oracle.connect("user",                         "password",                         "IP:1521/orcl",                         encoding="UTF-8")

MySQL：pymysql
https://pymysql.readthedocs.io/en/latest/
这是用于操作mysql数据库的库。从它的目录来看显得比oracle的内容少很多了。

import pymysqlconn = pymysql.connect(host="localhost",                       user="user",                       password="passwd",                       db="db",                       charset="utf-8")

sqlserver：pymssql
https://pymssql.readthedocs.io/en/latest/index.html
这是用于操作sqlserver数据库的库。

import pymssqlconn = pymssql.connect('IP',                       'user',                       'password',                       'database')                       # 例如：conn = pymssql.connect('192.168.0.1','user','123456','dbname')

当然除了不同数据库对应的不同python库之外，还有个数据库连接库sqlalchemy可以完成对不同数据库的连接。
https://docs.sqlalchemy.org/en/13/

本篇文章主要涉及oracle和sqlserver，因此我们使用cx_Oracle和pymssql来完成python和数据库的连接。

建立数据库连接-连接池连接

利用上面的方法我们可以建立python跟数据库之间的连接，但是在进行大量的数据库操作时，单个连接可能会引发连接超时等问题。因此更好的方法是为数据库建立连接池，通过连接池中预定义好的连接进行快速数据库操作，可以消除单个连接超时问题，还可以带来以下几个好处。

1. 较少了网络开销

2. 系统的性能会有一个实质的提升

3. 没了麻烦的TIME_WAIT状态

python建立连接池的方法可以使用DBUtils的PooledDB来完成。

class PooledDB(creator,  # 数据库接口，上面提到的cx_Oracle、pymssql、pymysql               mincached=0, # 启动时开启的空连接数量               maxcached=0, # 连接池最大可用连接数量               maxshared=0, # 连接池最大可共享连接数量               maxconnections=0, # 最大允许连接数量               blocking=False, # 最大允许连接数量               maxusage=None, # 单个连接最大复用次数               setsession=None, # 用于传递到数据库的准备会话，如 [”set name UTF-8″] 。               reset=True,                failures=None,                ping=1,                *args,                **kwargs)# After you have created the connection pool, # you can use connection() to get pooled, # steady DB-API 2 connectionsfrom DBUtils.PooledDB import PooledDBpool = PooledDB(cx_Oracle,                mincached=2,                maxconnections=6,                user='user',                password='123456',                dsn='192.168.0.1:1521/orcl')conn = pool.connection() # 通过连接池建立连接

对于oracle数据库来说，cx_Oracle库有自己专有的建立数据库连接池的方法。https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-strings    

cx_Oracle的SessionPool()方法可以为oracle建立连接池。

import cx_Oracle# 创建连接池pool = cx_Oracle.SessionPool("user",  # 用户名                             "123456", # 密码                             "192.168.0.1:1521/orcl", # 数据库地址                             min=2, # 最小连接数                             max=5, # 最大连接数                             increment=1,  # 需要建立连接时的连接数增加数，1代表每次增加1个连接                             encoding="UTF-8")# 从连接池获取连接connection = pool.acquire()# 通过连接创建cursor对象cursor = connection.cursor()for result in cursor.execute("select * from tablename"):    print(result)# 释放连接pool.release(connection)# 关闭连接池pool.close()

cursor方法读取数据库

数据库连接建立之后就是需要进行数据库数据查询或者进行增删改操作了。读取数据库数据的方法我们有两种，第一种便是通过cursor游标来获取数据库的数据。游标可以理解为SQL执行后得到的表格的一个行号的指针。它会从第一行开始移动。

# 先sql = '''  select *   from tablename''' # 三引号可以编辑文本块with conn.cursor() as cur:    cur.execute(sql) # 让数据库执行SQL语句    # SQL执行后，cursor默认是在第一条数据上    cur.fetchone() # 获取SQL执行后的表的一条数据。执行后cursor移动到第2条数据上。    cur.fetchmany(100)     # 获取SQL执行后的表的100条数据。    # 此时是从第2条数据开始读取的，读到101条结束。    # cursor移动到102条数据上    cur.fetchall() # 获取第102及之后的所有数据    cur.description # 获取SQL执行结果的列信息    cur.commit() # 如果sql是insert/delet/update操作必须进行commit操作，不然操作不会入库。

cur.description返回的信息是cursor执行的sql之后获取到的表数据的列信息。 是7个项目组成的列表，包含：name(列名称), type(列数据类型), display_size, internal_size, precision, scale, null_ok。我们需要的就是name这个值。通过for循环遍历description结果我们可以获取SQL查询得到的数据的列名信息。

column = []for i in range(len(colunms)):    column.append(colunms[i][0])print(column)

而要获取SQL执行之后的数据内容的话，可以通过cursor的fetchone()、fetchmany()、fetchall()方法。执行之后同样得到的是包含每一行数据的列表。

列和数据获取之后我们需要把他们放入到pandas的DataFrame数据结构中。

import pandas as pdclass DataFrame(data=None, # 要放入DataFrame的数据，可以是2维数组、字典或者DataFrame数据                index: Optional[Axes]=None, # 索引列名称                columns: Optional[Axes]=None, # 各列名称                dtype: Optional[Dtype]=None, # 各列数据类型                copy: bool=False) # df = pd.DataFrame(data=data,columns=column)

放入DataFrame之后我们就可以进行后续处理了。

pd.read_sql()获取数据库数据

上面是通过conn获取cursor对象，然后通过cursor对象的操作来获取数据并存入DataFrame中，整个过程相对比较复杂，需要写的代码较多。read_sql()整个方法简单直接。一行代码就搞定上啰里啰嗦那么多行代码才能搞定的事。

pandas.read_sql(sql,  # 要执行的sql                con,  # 数据库连接对象                index_col='None', # 索引列                coerce_float='True', # 是否将非字符串、非数字对象(如decimal.Decimal)的值转换为浮点值，这对SQL结果集很有用。                params='None',                 parse_dates='None', # 要解析为date类型的数据列                columns='None', # 列名称                chunksize: None = 'None')  # 执行SQL查询生成DataFrame，如果chunksize不为None，                # 则生成可迭代的DataFrame对象，每个DataFrame块包含chunksize设置的行数的数据。                # 也就是通过chunksize将SQL执行后的数据分成几块。df2 = pd.read_sql(sql,BI_conn)

类似pandas.read_sql的方法还有

• read_sql_table

  • 网址：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_table.html#pandas.read_sql_table

  • 直接读取数据库中的表数据放入DataFrame。读取的是整个表数据。

  • pd.read_sql_table("tablename",conn)

• read_sql_query

  • 网址：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html#pandas.read_sql_query

  • 读取SQL查询得到的结果数据放入DataFrame

read_sql可以看成是上面这俩方法的组合。

到此为止，我们就建立完成python与数据库的连接，并通过执行SQL获取到了我们想要的数据了。

人生苦短，我用Python！一切皆是信息，万物源自比特！

数字化必定会深刻革命我们的办公和生活！

简道云，中小企业数字化之路的绝佳伴侣！

本公众号将分享数字化的实践、学习、思考。也许涉及信息化系统设计、各种办公软件、数据分析、理论知识、实践案例…… 感谢你与我一同成长……

如果有关企业数字化的疑问、思考和讨论 或者 关于简道云的应用搭建、数据工厂、仪表盘等疑问咨询或者合作，欢迎与我联系。(关注公众号，可以找到我的联系方式)