InterBase database migration to MySQL (data export)

In this article I will be writing my second script, which I use the "isql" command provided by the InterBase database to export the data I want, but because the "isql" command does not have a statement that exports data directly, I am using a configuration file that I import. Define in this file which database I want to open, and enter the account name and password and use the SQL statement to isolate the desired data and write it to the cache.

CONNECT "127.0. 0.1 : C:\OFFICE. GDB"USER  " sysdba "PASSWORD" Masterkey "; SELECT *  from Dailyplu;

At first I was going to pilot the data into a text file, and then read the data in the text file through the second program, but then I thought I could say this two program to make one, and also eliminate the export path and the second script to read the path of trouble, I then put the exported data in memory with a variable of type file, and then directly manipulate the variable, after viewing the file format exported by the "isql" command I found a very messy result, unlike what I imagined, it was to output some useless words, then a row name, followed by a row "= = =" To separate column names and data, then 20 rows of content, followed by a row name, a line "= =" and then loop to the end of the data guide.

For such data is difficult to go through the direct read to the MySQL database, to be processed in the script, this step is very important, but also very complex ... I first use the Python slice to cut the file into two parts, so I can extract the column name from the two parts, and the data, for the first part of the columns name of the reading is very simple, should know beforehand that the column name is the third row, so that I can be sliced directly to the third row of data, This cut out the data is a line of string, and using the Join function in Python can convert a string into a list, here also use the scanf module, practical very useful, interested can be in-depth study, for the back of the data I found that I met the data are all numbers, so stole a lazy , using regular expressions to filter the contents of the following except for the number, so that the last data left only numbers and spaces, for the processing of time, because the time in the data is in the last column of a row, so I processed the data when the data is cut into two parts, the previous number part, The subsequent time to do a regular match, and then the two sets of data together to form a new data, so that the resulting is still a string, the same through the Join function is converted, and finally the two parts of the data together, so that the output is the first row is the column name, and then all the data, The following is a description of the format before and after 2 pictures:

After that, the formatted data is exported to a text file to facilitate subsequent script reading, the following is the program:

ImportOSImportre,pdb,sys,getoptopts, args= Getopt.getopt (sys.argv[1:],"Hi:o:",[" Help","int=","out="]) Input_file=""output_file=""defusage ():Print """- h--help Print the Help-i--int to enter the address configuration File-o--ou T to export the address database""" forOP, valueinchopts:ifOpinch("- I.","--GBK"): Input_file=valueelifOpinch("- o","--gdb"): Output_file=valueelifOpinch("- H"): Usage () sys.exit ()#This is the InterBase Database Export command.defMain ():#The Execute isql command will return a file and save it to a variable, and then read the contents of the variable row by lineFile=os.popen ("isql<"+input_file) File3=file Lines=File3.readlines ()#Open configuration filefp = open (Output_file,'W')    #takes the read data out of the table name and then writes it to the first line of the exported fileLINES11 =file3.readlines () lines12= Lines11[2:3]     forLineinchLines2:result= SCANF.SSCANF (line,'%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s') Linse3=",". Join (Result) fp.writelines (LINSE3)#Slice isql exported files and delete unused openingsLines2 = lines[5:-1] Lines3=[]     forLineinchLines2:#The following code uses a regular space to delete, the time is formatted and assembled back into a complete piece of dataLine=re.sub (R'[^\d^\s^\-^\:^\.^\n]',"', line) time=re.search (R'\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}', line)iftime!=None:line=re.sub (R'\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}\.\d{4}',"', line) line=re.sub (R'\s+',' ', line) SP=line.split (' ') Sp.append (Time.group ()) line=','. Join (SP) Line=re.sub (R'[,]+',',', line) line=line.split (',') Line=line[1:]            ifLen (line)! =0:lines3.append (line)Else: Line=re.sub (R'[^\d^\s^\-^\:^\.^\n]',"', line) line=line[1:].strip ()#writes formatted data line-by-row to the specified file     forLine2inchLines3:line2=','. Join (line2) fp.writelines (line2) fp.write ('\ r \ n') Fp.close ()if __name__=='__main__': Main ()

InterBase database migration to MySQL (data export)