By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,689 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,689 IT Pros & Developers. It's quick & easy.

import sql data to mysql using python script

P: 1
Hii guys

I'm new to python and I'm trying to import mssql data to mysql using python..

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/python
  2.  
  3. import MySQLdb
  4. import pyodbc
  5.  
  6. import csv
  7. csvreader = csv.reader(open('datatypes.txt', 'rb'))
  8. dataTypes = dict(csvreader)
  9.  
  10. #connection for MSSQL. (Note: you must have FreeTDS installed and configured!)
  11. conn = pyodbc.connect('DRIVER={FreeTDS}; SERVER=abc; DATABASE=gfgh; UID=op; PWD=hmmm')
  12. msCursor = conn.cursor()
  13.  
  14. #connection for MySQL
  15. db = MySQLdb.connect(passwd="xyz", db="pqr")
  16. myCursor = db.cursor()
  17.  
  18. 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!)
  19. dbTables = msCursor.fetchall()
  20. noLength = [56, 58, 61] #list of MSSQL data types that don't require a defined length ie. datetime
  21.  
  22. for tbl in dbTables:
  23.     msCursor.execute("SELECT * FROM airlinemaster WHERE airline = air india('%s')" % tbl[0]) #syscolumns: see sysobjects above.
  24.     columns = msCursor.fetchall()
  25.     attr = ""
  26.     for col in columns:
  27.     colType = dataTypes[str(col.xtype)] #retrieve the column type based on the data type id
  28.  
  29.     #make adjustments to account for data types present in MSSQL but not supported in MySQL (NEEDS WORK!)
  30.     if col.xtype == 60:
  31.         colType = "float"
  32.         attr += col.name +" "+ colType + "(" + str(col.length) + "),"
  33.     elif col.xtype in noLength:
  34.         attr += col.name +" "+ colType + ","
  35.     else:
  36.         attr += col.name +" "+ colType + "(" + str(col.length) + "),"
  37.  
  38.     attr = attr[:-1]
  39.     myCursor.execute("CREATE TABLE " + tbl[0] + " (" + attr + ");") #create the new table and all columns
  40.     msCursor.execute("select * from %s" % tbl[0])
  41.     tblData = msCursor.fetchall()
  42.  
  43.     #populate the new MySQL table with the data from MSSQL
  44.     for row in tblData:
  45.     fieldList = ""
  46.     for field in row:
  47.         if field == None: 
  48.         fieldList += "NULL,"
  49.         else:
  50.         field = MySQLdb.escape_string(str(field))
  51.         fieldList += "'"+ field + "',"
  52.  
  53.     fieldList = fieldList[:-1]
  54.     myCursor.execute("INSERT INTO " + tbl[0] + " VALUES (" + fieldList + ")" )
  55.  
Is this the correct code to import sql data??? Also I'm clueless about datatypes.txt file.. what should be there in a txt file?
if not plz correct me with an example

thnx in advance..
Dec 14 '12 #1
Share this Question
Share on Google+
1 Reply

Rabbit
Expert Mod 10K+
P: 12,441
It looks like the text file is a comma separated file of xtypes and their corresponding data type in english.
Dec 14 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.