I'm new to python and I'm trying to import mssql data to mysql using python..
Expand|Select|Wrap|Line Numbers
- #!/usr/bin/python
- import MySQLdb
- import pyodbc
- import csv
- csvreader = csv.reader(open('datatypes.txt', 'rb'))
- dataTypes = dict(csvreader)
- #connection for MSSQL. (Note: you must have FreeTDS installed and configured!)
- conn = pyodbc.connect('DRIVER={FreeTDS}; SERVER=abc; DATABASE=gfgh; UID=op; PWD=hmmm')
- msCursor = conn.cursor()
- #connection for MySQL
- db = MySQLdb.connect(passwd="xyz", db="pqr")
- myCursor = db.cursor()
- msCursor.execute("SELECT * FROM airlinemaster WHERE type='U'") #sysobjects is a table in MSSQL db's containing meta data about the database. (Note: this may vary depending on your MSSQL version!)
- dbTables = msCursor.fetchall()
- noLength = [56, 58, 61] #list of MSSQL data types that don't require a defined length ie. datetime
- for tbl in dbTables:
- msCursor.execute("SELECT * FROM airlinemaster WHERE airline = air india('%s')" % tbl[0]) #syscolumns: see sysobjects above.
- columns = msCursor.fetchall()
- attr = ""
- for col in columns:
- colType = dataTypes[str(col.xtype)] #retrieve the column type based on the data type id
- #make adjustments to account for data types present in MSSQL but not supported in MySQL (NEEDS WORK!)
- if col.xtype == 60:
- colType = "float"
- attr += col.name +" "+ colType + "(" + str(col.length) + "),"
- elif col.xtype in noLength:
- attr += col.name +" "+ colType + ","
- else:
- attr += col.name +" "+ colType + "(" + str(col.length) + "),"
- attr = attr[:-1]
- myCursor.execute("CREATE TABLE " + tbl[0] + " (" + attr + ");") #create the new table and all columns
- msCursor.execute("select * from %s" % tbl[0])
- tblData = msCursor.fetchall()
- #populate the new MySQL table with the data from MSSQL
- for row in tblData:
- fieldList = ""
- for field in row:
- if field == None:
- fieldList += "NULL,"
- else:
- field = MySQLdb.escape_string(str(field))
- fieldList += "'"+ field + "',"
- fieldList = fieldList[:-1]
- myCursor.execute("INSERT INTO " + tbl[0] + " VALUES (" + fieldList + ")" )
if not plz correct me with an example
thnx in advance..