Token (ftp_idvarchar (100) NOTNULL, -- the ftp_cycle_idvarchar (1) NOTNULL, -- the duration (255) notnull of the ticket name, -- the path signature (100) NOTNULL after the ticket is processed, -- Ticket
Create table poc_app.sys_ftp_cfg (ftp_id varchar (100) not null, -- the ftp_cycle_id varchar (1) not null, -- the duration of the ticket file name is ftp_stage_filepath varchar (255) not null, -- path ftp_stage_filereg varchar (100) not null after ticket processing -- Ticket
Create table poc_app.sys_ftp_cfg
(
Ftp_id varchar (100) not null, -- Name of the ticket File
Ftp_cycle_id varchar (1) not null, -- period of name of a ticket
Ftp_stage_filepath varchar (255) not null, -- path after ticket Processing
Ftp_stage_filereg varchar (100) not null, -- name format after ticket Processing
Stage_schema varchar (100) not null, -- schema name
Table_name varchar (100) not null, -- table name
Delimiter_type_id varchar (10) not null -- delimiter
);
Insert into poc_app.sys_ftp_cfg
Values ('jiang _ test_d ', 'D','/home/sybase/Day', 'jiang _ test _ [YYYYMMDD]. dat ', 'poc _ app', 'jiang _ test',' | ');
#! /Usr/bin/python
#-*-Encoding: UTF-8 -*-
######################################## ######################################## ####
# Name: SybaseIQ_LoadData.py
# Describe: Load the data file to the Sybase IQ database using the Load table command.
######################################## ######################################## ####
Import OS
Import pyodbc
Import string
Import sys
From subprocess import Popen, PIPE
Import ConfigParser
Reload (sys)
Sys. setdefaultencoding ('utf8 ')
'''
Load data files to the Sybase IQ Database
'''
Class SybaseIQLoad:
Debug = 0
Def _ init _ (self, dbinfo ):
Self. UID = dbinfo [1]
Self. PWD = dbinfo [2]
Odbcinfo = 'dsn = % s; UID = % s; PWD = % s' % (dbinfo [0], dbinfo [1], dbinfo [2])
Self. cnxn = pyodbc. connect (odbcinfo, autocommit = True, ansi = True)
Self. cursor = self. cnxn. cursor ()
Def _ del _ (self ):
If self. cursor:
Self. cursor. close ()
If self. cnxn:
Self. cnxn. close ()
Def _ printinfo (self, msg ):
Print "% s" % (msg)
Print "\ n"
Def _ GetStageName (self, ftp_stage_filereg, ftp_cycle_id, cur_static_time ):
If ftp_cycle_id.lower () = 'H ':
Ftp_stage_filename = ftp_stage_filereg.replace ('[YYYYMMDDHH]', cur_static_time [0: 10])
If ftp_cycle_id.lower () = 'D ':
Ftp_stage_filename = ftp_stage_filereg.replace ('[YYYYMMDD]', cur_static_time [0: 8])
If ftp_cycle_id.lower () = 'W ':
Ftp_stage_filename = ftp_stage_filereg.replace ('[YYYY_WW]', cur_static_time [0: 7])
If ftp_cycle_id.lower () = 'M ':
Ftp_stage_filename = ftp_stage_filereg.replace ('[YYYYMM]', cur_static_time [0: 6])
Return ftp_stage_filename
Def _ getLoadInfo (self, ftp_id ):
SQL = '''
Select
Ftp_cycle_id
, Ftp_stage_filepath
, Ftp_stage_filereg
, Stage_schema
, Delimiter_type_id
, Table_name
From jiang. sys_ftp_cfg
Where ftp_id = '% s'
''' % (Ftp_id)
Self.cursor.exe cute (SQL. strip ())
Row = self. cursor. fetchone ()
Return row
Def _ getSybIQServInfo (self ):
# Host and port number for saving SybaseIQ
Sybservinfo = []
# ODBC configuration file absolute path
Unixodbc_file = "/etc/unixODBC/odbc. ini"
Config = ConfigParser. ConfigParser ()
Config. read (unixodbc_file)
# Obtain the SybaseIQ IP Address
ServerIP = config. get ("SybaseIQDSN", "Server ")
# Obtain the SybaseIQ port number
Port = config. get ("SybaseIQDSN", "Port ")
# Save the obtained IP address and port number
Sybservinfo. append (ServerIP)
Sybservinfo. append (Port)
Return sybservinfo
Def loaddata (self, ftp_id, cur_static_time ):
# Getting file loading configuration information
Row = self. _ getLoadInfo (ftp_id)
Ftp_cycle_id = row [0]
Ftp_stage_filepath = row [1]
Ftp_stage_filereg = row [2]
Stage_schema = row [3]
Delimiter_type_id = row [4]
Table_name = row [5]
# Get the file name of the specified date
Ftp_stage_filename = self. _ GetStageName (ftp_stage_filereg, ftp_cycle_id, cur_static_time)
# Obtain the absolute path of the cleaned File
Ftp_stage_absolute_filename = OS. path. join (ftp_stage_filepath, ftp_stage_filename)
# Process cleaned files again
# Ftp_stage_absolute_filename_final = ftp_stage_absolute_filename + '*'
# Obtain the Host IP address and port number of SybaseIQ
Sybaseiq_ipport = self. _ getSybIQServInfo ()
# Getting all fields in a table
Table_columns = '''
Select column_name
From syscolumn
Join retriable B
On a. table_id = B. table_id
Where B. table_name = '% s'> #/tmp/table_name.log
''' % (Table_name)
Load_ SQL = ''' dbisql-c "uid = % s; pwd = % s"-Host % s-port % s-nogui "% s" ''' % (self. UID, self. PWD, sybaseiq_ipport [0], sybaseiq_ipport [1], table_columns)
OS. system (load_ SQL)
# Process the generated table field File
Columns_ SQL = '''
Cat/tmp/table_name.log | sed "s/'/g" | awk' {printf "% s,", $0} '| sed's /, $ // G'
'''
Result = Popen (columns_ SQL, shell = True, stdout = PIPE, stderr = PIPE)
Right_info = result. stdout. read (). strip ('\ xef | \ xbb | \ xbf ')
Err_info = result. stderr. read ()
Loadsql = '''
Load table % s. cpms_area_user
(
% S
)
Using file '% s'
FORMAT ASCII
ESCAPES OFF
QUOTES OFF
NOTIFY 1000000
Delimited by '% s'
With checkpoint on;
COMMIT;
''' % (Stage_schema, right_info, ftp_stage_absolute_filename, delimiter_type_id)
Try:
Iserr = 0
Print "************* Begin to execute load table command... *************** \ n"
If self. debug = 1:
Self. _ printinfo (loadsql. strip ())
#Self.cursor.exe cute (loadsql. strip ())
Loadsql = ''' dbisql-c "uid = % s; pwd = % s"-Host % s-port % s-nogui "% s" ''' % (self. UID, self. PWD, sybaseiq_ipport [0], sybaseiq_ipport [1], loadsql)
OS. system (loadsql)
Print "\ n ************* End to execute load table command ...*************"
Print "************************** Successful *********** ***************"
Except t Exception, err:
Iserr = 1
Print "Return value % s, Error % s" % (iserr, err)
Return iserr
# Main
Def main ():
# Check the number of input parameters
If len (sys. argv) <6:
Print 'usage: python SybaseIQ_LoadData.py SybaseDSN username password ftp_id cur_static_time \ N'
Sys. exit (1)
# Define information for connecting to Sybase IQ
Dbinfo = []
# Dbinfo. append ('sybaseiqdsn ')
# Dbinfo. append ('jiang ')
# Dbinfo. append ('jiang ')
Dbinfo. append (sys. argv [1])
Dbinfo. append (sys. argv [2])
Dbinfo. append (sys. argv [3])
Ftp_id = sys. argv [4]
Cur_static_time = sys. argv [5]
SIQ = SybaseIQLoad (dbinfo)
Ret = SIQ. loaddata (ftp_id, cur_static_time)
Return ret
If _ name _ = '_ main __':
Sys. exit (main ())
