python小段代码_一小段代码的实现笔记-Python版

目标

读取 Excel 数据写入 Postgresql 表，参见另一个版本

思路

将 Excel 数据载入 Pandas.DataFrames，对于 etl 来说 pandas 也是一个不错的工具

拼装 SQL 语句模板，有点像乐高

装填并执行 SQL 语句

Part I 载入

读取 Excel 文件有多种方法，xlwings 并不适合数据 ETL，Pandas 使用 xlrd 库支持 Excel 文件读取，参见Pandas 练习册 - 如何读取 EXCEL 文件

第一步，初始化配置

# Excel 文件路径

filepath = r'D:\O\Smalldata\Data\起租.xlsx'

# 指定要读取的列

# 也可以不指定，读入整表，并不影响最后的结果

inventory_columns_mark = [1,2,3,4,5,9,10,11,12,13,14,15,16,17,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,51,52,53,54,55]

第二步，读取文件

# Excel Sheet 名称：新建塔批量起租表

# 表头：第二行

df = pd.read_excel( filepath, "新建塔批量起租表", heads=1, skiprows=1, usecols=inventory_columns_mark, true_values=['是'] )

数据示例

df.iloc[0]

区县 风县

业务确认单编号 CTC-xxx-xxxx-0000-000039

站址编码 61032000000000

需求确认单编号 1010000000000000

站址名称 风_县大队

铁塔种类 普通地面塔

机房配置 一体化机柜

共享信息 新建共享

挂高 28

天线数量 3

系统数量 1

RRU是否上塔 True

当前铁塔共享客户总数 1

当前机房及配套存量新增共享客户总数 1

其他费用(元/年)(不含税) 0

其它费用说明 NaN

铁塔基准价格(元/年)(不含税) 1xxx.3

机房及配套基准价格(元/年)(不含税) xxx5.25

维护费(元/年)(不含税) xxx.51

产品单元数 1

电力引入费(元/年)(不含税) xx8.8

场地费(元/年)(不含税) xx0

维护费折扣 1

场地费折扣 1

电力引入费折扣 1

铁塔共享折扣 1

机房及配套享折扣 1

服务起始日期 2015-09-01

服务结束日期 2020-08-31

产品服务费合计(元/年)不含税 xxx68.8

产品服务费合计(元/年)含税 xx57

维护费用原始录入值 05.66

场地费原始录入值 00

电力引入原始录入值 560

其他费用原始录入值 0

created_by admin

effective_date 2015-09-01

row_key 1

current_flag True

Name: 0, dtype: object

Part II 拼装

第一步，初始化配置

# 映射 Excel 与 Postgresql 列标签

map_header = {

'区县' : 'district_and_county',

'业务确认单编号' : 'business_confirmation_number',

'站址编码' : 'base_address_id',

'需求确认单编号' : 'demand_confirmation_number',

'站址名称' : 'base_name',

'铁塔种类' : 'tower_type',

'机房配置' : 'room_type',

'共享信息' : 'share_info',

'挂高' : 'antenna_height',

'天线数量' : 'antenna_number',

'系统数量' : 'system_number',

'RRU是否上塔' : 'is_rru_on_tower',

'当前铁塔共享客户总数' : 'tower_current__shared_customer',

'当前机房及配套存量新增共享客户总数' : 'room_and_support_current_shared_customer',

'其他费用(元/年)(不含税)' : 'other_fee',

'其它费用说明' : 'other_fee_explanation',

'铁塔基准价格(元/年)(不含税)' : 'tower_base_price',

'机房及配套基准价格(元/年)(不含税)' : 'room_and_supporting_base_price',

'维护费(元/年)(不含税)' : 'maintenance_fee',

'产品单元数' : 'product_unit',

'电力引入费(元/年)(不含税)' : 'power_incoming_fee',

'场地费(元/年)(不含税)' : 'site_fee',

'维护费折扣' : 'maintenance_fee_discount',

'场地费折扣' : 'site_fee_discount',

'电力引入费折扣' : 'power_incoming_fee_discount',

'铁塔共享折扣' : 'tower_share_discount',

'机房及配套享折扣' : 'room_and_support_share_discount',

'服务起始日期' : 'service_start_date',

'服务结束日期' : 'service_end_date',

'产品服务费合计(元/年)不含税' : 'total_product_service_fee',

'产品服务费合计(元/年)含税' : 'total_product_service_fee_tax',

'维护费用原始录入值' : 'maintenance_cost_oev',

'场地费原始录入值' : 'site_fee_oev',

'电力引入原始录入值' : 'power_incoming_oev',

'其他费用原始录入值' : 'other_fee_oev',

'created_by' : 'created_by',

'effective_date' : 'effective_date',

'row_key' : 'row_key',

'current_flag' : 'current_flag'

}

第二步，拼装模板

拼装使用 psycopg2.sql 模块，参考Psycopg 2.7 无责任翻译 - SQL string composition

query = psycopg2.sql.SQL( "insert into business_confirmation ({0}) values ({1})" ).format(

psycopg2.sql.SQL(', ').join( map( psycopg2.sql.Identifier, map_header.values() ) ),

psycopg2.sql.SQL(', ').join( map(psycopg2.sql.Placeholder, map_header.values() ) )

)

数据示例

Composed(

[

SQL('insert into business_confirmation ('),

Composed(

[

Identifier('district_and_county'), SQL(', '),

Identifier('business_confirmation_number'), SQL(', '),

Identifier('base_address_id'), SQL(', '),

Identifier('demand_confirmation_number'), SQL(', '),

Identifier('base_name'), SQL(', '),

Identifier('tower_type'), SQL(', '),

Identifier('room_type'), SQL(', '),

Identifier('share_info'), SQL(', '),

Identifier('antenna_height'), SQL(', '),

Identifier('antenna_number'), SQL(', '),

Identifier('system_number'), SQL(', '),

Identifier('is_rru_on_tower'), SQL(', '),

Identifier('tower_current__shared_customer'), SQL(', '),

Identifier('room_and_support_current_shared_customer'), SQL(', '),

Identifier('other_fee'), SQL(', '),

Identifier('other_fee_explanation'), SQL(', '),

Identifier('tower_base_price'), SQL(', '),

Identifier('room_and_supporting_base_price'), SQL(', '),

Identifier('maintenance_fee'), SQL(', '),

Identifier('product_unit'), SQL(', '),

Identifier('power_incoming_fee'), SQL(', '),

Identifier('site_fee'), SQL(', '),

Identifier('maintenance_fee_discount'), SQL(', '),

Identifier('site_fee_discount'), SQL(', '),

Identifier('power_incoming_fee_discount'), SQL(', '),

Identifier('tower_share_discount'), SQL(', '),

Identifier('room_and_support_share_discount'), SQL(', '),

Identifier('service_start_date'), SQL(', '),

Identifier('service_end_date'), SQL(', '),

Identifier('total_product_service_fee'), SQL(', '),

Identifier('total_product_service_fee_tax'), SQL(', '),

Identifier('maintenance_cost_oev'), SQL(', '),

Identifier('site_fee_oev'), SQL(', '),

Identifier('power_incoming_oev'), SQL(', '),

Identifier('other_fee_oev'), SQL(', '),

Identifier('created_by'), SQL(', '),

Identifier('effective_date'), SQL(', '),

Identifier('row_key'), SQL(', '),

Identifier('current_flag')

]

), SQL(') values ('),

Composed(

[

Placeholder('district_and_county'), SQL(', '),

Placeholder('business_confirmation_number'), SQL(', '),

Placeholder('base_address_id'), SQL(', '),

Placeholder('demand_confirmation_number'), SQL(', '),

Placeholder('base_name'), SQL(', '),

Placeholder('tower_type'), SQL(', '),

Placeholder('room_type'), SQL(', '),

Placeholder('share_info'), SQL(', '),

Placeholder('antenna_height'), SQL(', '),

Placeholder('antenna_number'), SQL(', '),

Placeholder('system_number'), SQL(', '),

Placeholder('is_rru_on_tower'), SQL(', '),

Placeholder('tower_current__shared_customer'), SQL(', '),

Placeholder('room_and_support_current_shared_customer'), SQL(', '),

Placeholder('other_fee'), SQL(', '),

Placeholder('other_fee_explanation'), SQL(', '),

Placeholder('tower_base_price'), SQL(', '),

Placeholder('room_and_supporting_base_price'), SQL(', '),

Placeholder('maintenance_fee'), SQL(', '),

Placeholder('product_unit'), SQL(', '),

Placeholder('power_incoming_fee'), SQL(', '),

Placeholder('site_fee'), SQL(', '),

Placeholder('maintenance_fee_discount'), SQL(', '),

Placeholder('site_fee_discount'), SQL(', '),

Placeholder('power_incoming_fee_discount'), SQL(', '),

Placeholder('tower_share_discount'), SQL(', '),

Placeholder('room_and_support_share_discount'), SQL(', '),

Placeholder('service_start_date'), SQL(', '),

Placeholder('service_end_date'), SQL(', '),

Placeholder('total_product_service_fee'), SQL(', '),

Placeholder('total_product_service_fee_tax'), SQL(', '),

Placeholder('maintenance_cost_oev'), SQL(', '),

Placeholder('site_fee_oev'), SQL(', '),

Placeholder('power_incoming_oev'), SQL(', '),

Placeholder('other_fee_oev'), SQL(', '),

Placeholder('created_by'), SQL(', '),

Placeholder('effective_date'), SQL(', '),

Placeholder('row_key'), SQL(', '),

Placeholder('current_flag')

]

), SQL(')')

]

)

Part III 装填

conn = psycopg2.connect(

host = "10.1.1.110",

port = 5432,

dbname = "bj_wireless_center"

)

cur = conn.cursor()

# cur.mogrify(query, df.rename(columns = map_header).to_dict('records')[0])

cur.executemany( query, df.rename( columns = map_header ).to_dict( 'records' ) )

conn.commit()

cur.close()

conn.close()