Python处理数据库插入和查询的一些问题及解决方案

Python连接DB2

IBM的DB2数据库用python做连接真的不是一般的麻烦，一个是因为这个数据库并不是现在主流的数据库，另一方面是因为Sqlalchemy没有在其内部代码中加入此方法，所以导致连接特别麻烦。通过查阅官方的帮助文档和CSDN上的一些教程，整理出了以下两种连接方法：

使用ibm_db和ibm_db_sa两个包

1. 安装两个包

pip install ibm_db
pip install ibm_db_sa

2. 使用以下代码即可创建连接

import ibm_db,ibm_db_dbi

x=ibm_db.connect("DATABASE=KOALA;HOSTNAME=BTPROD.LENOVO.COM;PORT=50001;PROTOCOL=TCPIP;UID=xux17;PWD=*****","","")
conn2=ibm_db_dbi.Connection(x)

使用此方法可以执行原生SQL语句，但是没有办法和pandas包直接进行使用，如果要使用pandas的read_sql方法，更推荐下面那种方法。但是如果是想要进行数据转储，使用这种方法，可以考虑使用游标，依次批量读取数据。

x=ibm_db.connect("DATABASE=KOALA;HOSTNAME=BTPROD.LENOVO.COM;PORT=50001;PROTOCOL=TCPIP;UID=xux17;PWD=***;","","")

conn2=ibm_db_dbi.Connection(x)

sql="SELECT * FROM PC_KPI.PC_INCIDENT_ROOT"

conn1=pymssql.connect(host='10.103.66.45',port=1444,user='sa',password='****,database='Integration')
cor1=conn1.cursor()
cor2=conn2.cursor()
d=cor2.execute(sql)
data=cor2.fetchmany(1000)
sql2="""
INSERT INTO [dbo].[PC_KPI.PC_INCIDENT_ROOT2]
           ([INCIDENT_ID]
           ,[MACHINE_TYPE]
           ,[SERIAL_NBR]
           ,[COUNTRY_CODE]
           ,[CLAIM_NBR]
           ,[SERVICE_DELIVERY_TYPE]
           ,[ENTITLEMENT_TYPE]
           ,[CLAIM_CREATE_TS]
           ,[CLAIM_STATUS]
           ,[CLAIM_CLOSE_TS]
           ,[CLOSE_CODE]
           ,[SEVERITY]
           ,[RES_TYPE_DESC]
           ,[RES_CAT_DESC]
           ,[RES_PROB_DESC]
           ,[RES_RSLT_DESC]
           ,[CALL_RCVD_TYPE])
     VALUES
     (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
"""
i=1
while data:
    cor1.executemany(sql2,data)
    conn1.commit()
    print('process %drows'%(i*1000))
    i+=1
    data=cor2.fetchmany(1000)

使用ibm_db_sa和sqlalchemy两个包

使用这种方法的好处是可以直接将读取的数据使用pandas进行处理，方便后续的分析和数据处理。但是由于sqlachemy本身并没有内置此接口，因此安装完包后需要进行一定的处理：

1. 安装以下包

pip install ibm_db,ibm_db_sa

2. 我的是python3.6，可以直接使用sqlalchemy创建连接，如果你的不可以，可以参考这个链接，进行配置

import imb_db_sa
from sqlalchemy import create_engine
import pandas as pd

DB_CONNECT_STRING1 = 'ibm_db_sa://sa:1qaz2wsx!@10.103.66.45:1444/integration?charset=utf8'
engine1 = create_engine(DB_CONNECT_STRING1, echo=False)
DB_Session1 = sessionmaker(bind=engine1)
session1 = DB_Session1()

Python处理插入数据库报数据类型不一致问题

最近在做数据集市的过程中，经常发现进来的数据不规范，各列的数据类型极其不一致。导致这个问题的原因主要是因为前期数据生成时没有做好数据规范，这就使得后期进行分析的数据经常报各种错误。Python对于从数据源中读取的数据，会根据前期的数据给定该列的数据类型，但是极有可能在读取的数据中，含有不属于该列的数据类型，比如在日期型的数据列中包含了数字型，这就会导致在后期插入数据库时，报类型类型转换错误，从而使得数据插入失败。对于以上问题，有以下集中解决方法：

1. 逐行插入，这样在数据报错的过程中也找到了错误行，可以针对性的进行修改并在此行重新开始插入。缺点就是需要一直盯着程序
2. 在插入前进行数据排查，查看是否有奇异值，从而进行修改和更新。这个方法如果分析到位，可以直接完成自动化，省去了人工转换。但是需要分析者对数据和业务有足够的了解，不然无法进行排查
3. 将所有字段在to_sql中都指定类型为varchar，这样即便有奇异值也不会报错，可以较轻松的完成数据插入，而且对于数据库中也不会影响大多数字段的分析。但是对于日期型的数据，不建议这样操作，因为之后如果需要进行时间比较，这样的数据还是会报错的

Python执行SQL语句成功但是数据库没有数据

在用python执行SQL的时候，出现了一种特殊的情况：使用sqlalchemy创建的就连接器出现了这种问题：

1. 执行原生SQL的insert语句会程序会显示插入进度，但是程序结束后数据库没有数据
2. drop掉表后，使用原始SQL into语句将查询结果直接保存为表格不会报错，数据有保存

查阅了很多博客和资料后，没有发现解决方法。有说法说执行原生SQL语句结束后需要进行commit之后才会生效，但是sqlalchemy并没有commit方法，而且如果是这样的话，不会出现我的第二种情况。

针对这个问题，我的解决方法是：

1. 使用pandas的read_sql先接收一遍查询的结果数据，然后使用to_sql做一遍插入。缺点是这样会导致数据插入数据库缓慢，而且会比较占程序内存
2. 使用数据库原生的包创建连接，比如pymssql创建SQLserver的连接，这样执行SQL之后commit会生效。缺点是没办法很好的跟pandas进行结合

Python插入大体量数据时到一定数量崩溃

在对公司数据进行数据集市建立的时候，发现使用Python进行不同种类数据库的数据迁移时，程序在处理到一定数据量之后Python会直接崩溃。针对这个问题，一直没有找到很好的解决方法，毕竟如果是数据问题，应该是报数据类型转换错误，而不是直接Python崩溃；如果是数据体量过大，那么我之前插入的几百万的数据应该更早的会崩溃。

个人总结出现这个情况的有可能的几个原因:

1. 数据中有特殊的数据错误，比如我的转换一直出错，是因为DB2和sqlserver涉及到24点和0点的时间转换不一致。这样的数据不是错误数据类型，但是会导致数据无法转换。要完成插入，只能直接转换成字符串，但是最后还是免不了要解决这个问题
2. 可能是Python进程过多，导致程序冲突崩溃