Python Database Migration Script (Ultimate Edition)

The last few scripts were all integrated this time, and later we found an ODBC package that we can use, so this is a simple way to do this. Directly through the ODBC InterBase data in the database all out, then through the Python sqlalchemyorm framework directly connected to the remote MySQL database, then you can easily transfer data, of course, as my ultimate perfect version, Automatic mode and manual mode is indispensable, in the automatic mode will automatically check the existence of the InterBase database, if there is no automatic restore, and then create their own interbase data source, then you can take out the data, the following is the flowchart.

So the following is the time to explain the code ~

This step is divided into three functions, main function, ODBC function and restore function, because the manual mode is included in the automatic mode, then I will only introduce the automatic mode.

In my script, I defined a dictionary to store the configuration information for the script, and the user can define the functions and database information they need in the dictionary.

#Define a configuration dictionary and add the required parameters to the dictionary for easy Changesetting= {#Setting the startup mode'Operate_type': Operate_auto_restore,#the address of the backup file'Gbk_path':'C:\\XXX\\DB.GBK',#InterBase database addresses that need to be restored'Gdb_path':'C:\\\xxx\\\db.gdb',#set the user name, password, IP address, and port number of the MySQL database you want to connect to'username':'Root','Password':'Root','Port':'3306','dbname':'Test','localhost':'127.0.0.1',#set the name and username and password of the ODBC you want to connect to'DSN':'XXX','UID':'SYSDBA','PWD':'Masterkey'}

When the script is started, it is judged to determine the mode of the user's script, followed by running the appropriate script

if __name__=='__main__':    #determine whether the startup mode is automatic or manual mode    ifsetting['Operate_type']==Operate_auto_restore:#Check if the database exists and will skip restore if it exists        ifOs.path.isfile ('C:\\xxx\\db.gdb') ==False:restore () ODBC () main ()Else:            Print 'The database has been exist, would skip restore ...'ODBC () main ()elifsetting['Operate_type']==Operate_auto_export:main ()

Restore the database when Python determines that the InterBase database does not exist and then executes the restore

# ways to recover a database in automatic mode def restore ():    os.system ('gbak-c-user "sysdba"-password "Masterkey" "'+ setting['gbk_path']+'"" 127.0.0.1:'+setting[  'gdb_path']+'"')

After that is the automatic creation of InterBase ODBC data source, of course, the first to install InterBase driver, there are many online, I do not list one by one, because it is running on the Windows platform, So it is convenient to use the form of adding a registry to add the required data source into the system, of course, since it is imported through the registry then it will inevitably produce a registry file, I will default to build the root of the C drive, and then after the import is completed and then deleted, This will not generate garbage after the script has finished running.

#The following string creates the required ODBC data source and changes the database address in the data source to the database address of the restore outputdefODBC (): Odbcreg=" "Windows Registry Editor Version 5.00[hkey_current_user\software\odbc\odbc. Ini][hkey_current_user\software\odbc\odbc. INI\KMK] "Driver" = "C:\\\windows\\\system32\\\odbcfb32.dll" "Description" = "" "Dbname" = "" "+setting['Gdb_path']+" "" Client" = "" User "=" SYSDBA "" Role "=" "" CharacterSet "=" NONE "" jdbcdriver "=" Iscdbc "" ReadOnly "=" n "" NoWait "=" n "" Locktimeoutwaittransactions "=" "" Dialect "=" 3 "" quotedidentifier "=" Y "" sensitiveidentifier "=" N "" Autoquotedidentifier "=" N "" useschemaidentifier "=" 0 "" safethread "=" Y "" Password "=" Dkebfjenhfcobhghlaimnaaficeleaegdnmfnogalamhbbgchfadnkcbppgmanogiekeniophdipbiecplllcbikejkmjlplib "[HKEY_ Current_user\software\odbc\odbc. INI\ODBC Data Sources] "kmk" = "Firebird/interbase (r) driver"" "    #Create a new temporary registry file in the C packing directory to import the new data sourceFP= Open ('C:\\db007.reg','W')    Try: Fp.write (Odbcreg)exceptexception,e:Printefinally: Fp.close ()#execute the generated data source registry fileOs.system ('regedit/s C:\\db007.reg')    #Delete the temporarily generated registry fileOs.remove ('C:\\db007.reg')

followed by our most critical main function

Of course, this time with the same as the previous use of Python sqlalchemyorm framework, so the first thing is to connect to the database, get session, but again before the first to build the class and table mapping relationship.

# Create a base class  = declarative_base ()# defines a user class to map to the database class  User (base):     __tablename__'User'    NAME= Column (Integer, primary _key=True)    PASSWORD= Column (Integer)                 ....

Then, after the session is available, ODBC is connected and the data in the InterBase database is taken out via SQL statements.

#Create a connection to a databasemysql_db=Create_engine ('mysql://'+setting['username']+':'+setting['Password']+'@'+setting['localhost']+    ':'+setting['Port']+'/'+setting['dbname'],echo=False) Base.metadata.bind=mysql_db Base.metadata.create_all (mysql_db)#Create a sessionSession= Sessionmaker (bind=mysql_db) Session=Session ()#pdb.set_trace ()    #To create a connection to an ODBC data sourceConn= Pyodbc.connect ('dsn='+setting['DSN']+'; Uid='+setting['UID']+'; Pwd='+setting['PWD']+"') Curs=conn.cursor ()#get an object from an SQL statementDBF= Curs.execute ('Select *from User')

Here is the automatic mode and manual mode must go through the steps ~ through the loop to submit data to MySQL, of course, in order to prevent the database already have data, then in the database before adding to check whether there is this data, if any, skip this add, continue the following steps. The data is then submitted once every 1000 times to reduce the pressure on the data, and the remainder of the loop is divided into the database by dividing by 1000.

#looping the data in an object into the user classCommint=0 forLine1inchDBF:#Check the data in the database for data, and if not, join the dataexist= Session.query (User). Filter (User.ID = = Line1.id,user.name = =line1.name). Count ()ifExist>0:Continueu=User () u.name=line1.name ... u.password=line1. PASSWORD session.add (u) commint= Commint+1#commit once after looping 1000 times        ifcommint%1000==0:Try: Session.commit ()#pdb.set_trace ()Session.flush ()except:                Print 'Error'Session.rollback ()#submit the remaining data    Try: Session.commit () Session.flush ()exceptException, E:Printe Session.rollback () session.close ()

At this point, this is to use ODBC to read the data and then saved in the MySQL Python script is finished, intermittent into a week more than a day, I interned the first formal task is the perfect ending, once on campus learned a few years of the Java EE, Never thought of the last to do Python and database .... I do not know what kind of strange task is waiting for me in the later days ~ ~ Of course! I still will not give up the web! Overtime Dog work hard!

Python Database Migration Script (Ultimate Edition)