文章目录
Python连接DB2
IBM的DB2数据库用python做连接真的不是一般的麻烦,一个是因为这个数据库并不是现在主流的数据库,另一方面是因为Sqlalchemy没有在其内部代码中加入此方法,所以导致连接特别麻烦。通过查阅官方的帮助文档和CSDN上的一些教程,整理出了以下两种连接方法:
使用ibm_db和ibm_db_sa两个包
- 安装两个包
pip install ibm_db
pip install ibm_db_sa
- 使用以下代码即可创建连接
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本身并没有内置此接口,因此安装完包后需要进行一定的处理:
- 安装以下包
pip install ibm_db,ibm_db_sa
- 我的是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对于从数据源中读取的数据,会根据前期的数据给定该列的数据类型,但是极有可能在读取的数据中,含有不属于该列的数据类型,比如在日期型的数据列中包含了数字型,这就会导致在后期插入数据库时,报类型类型转换错误,从而使得数据插入失败。对于以上问题,有以下集中解决方法:
- 逐行插入,这样在数据报错的过程中也找到了错误行,可以针对性的进行修改并在此行重新开始插入。缺点就是需要一直盯着程序
- 在插入前进行数据排查,查看是否有奇异值,从而进行修改和更新。这个方法如果分析到位,可以直接完成自动化,省去了人工转换。但是需要分析者对数据和业务有足够的了解,不然无法进行排查
- 将所有字段在to_sql中都指定类型为varchar,这样即便有奇异值也不会报错,可以较轻松的完成数据插入,而且对于数据库中也不会影响大多数字段的分析。但是对于日期型的数据,不建议这样操作,因为之后如果需要进行时间比较,这样的数据还是会报错的
Python执行SQL语句成功但是数据库没有数据
在用python执行SQL的时候,出现了一种特殊的情况:使用sqlalchemy创建的就连接器出现了这种问题:
- 执行原生SQL的insert语句会程序会显示插入进度,但是程序结束后数据库没有数据
- drop掉表后,使用原始SQL into语句将查询结果直接保存为表格不会报错,数据有保存
查阅了很多博客和资料后,没有发现解决方法。有说法说执行原生SQL语句结束后需要进行commit之后才会生效,但是sqlalchemy并没有commit方法,而且如果是这样的话,不会出现我的第二种情况。
针对这个问题,我的解决方法是:
- 使用pandas的read_sql先接收一遍查询的结果数据,然后使用to_sql做一遍插入。缺点是这样会导致数据插入数据库缓慢,而且会比较占程序内存
- 使用数据库原生的包创建连接,比如pymssql创建SQLserver的连接,这样执行SQL之后commit会生效。缺点是没办法很好的跟pandas进行结合
Python插入大体量数据时到一定数量崩溃
在对公司数据进行数据集市建立的时候,发现使用Python进行不同种类数据库的数据迁移时,程序在处理到一定数据量之后Python会直接崩溃。针对这个问题,一直没有找到很好的解决方法,毕竟如果是数据问题,应该是报数据类型转换错误,而不是直接Python崩溃;如果是数据体量过大,那么我之前插入的几百万的数据应该更早的会崩溃。
个人总结出现这个情况的有可能的几个原因:
- 数据中有特殊的数据错误,比如我的转换一直出错,是因为DB2和sqlserver涉及到24点和0点的时间转换不一致。这样的数据不是错误数据类型,但是会导致数据无法转换。要完成插入,只能直接转换成字符串,但是最后还是免不了要解决这个问题
- 可能是Python进程过多,导致程序冲突崩溃