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

Python MySQLdb

P: 2
Hi,

My name is Chintan Vadera. I had a question regarding a problem you had before regarding Python MySQLdb. It would be great if you could help.

The thing is I have database already in place for this code. My code has the same exact logic as your did. I am getting the error which you got.

Here is the error

Traceback (most recent call last):
File "C:\Documents and Settings\axiom\Desktop\PioneerExcell\jacquestuff\t ry\UpdateDailyRecap.py", line 64, in <module>
cursor.execute(query,values)
File "C:\Python26\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "C:\Python26\lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, "Unknown column 'Currency_Code' in 'field list'")

I did not understand the explanation there. It was kinda incomplete. Could you please leme know how to get rid of it. I am stuck coz of it. TIA
Here is my code

Expand|Select|Wrap|Line Numbers
  1. import xlrd
  2. from xlrd import open_workbook
  3. import MySQLdb
  4.  
  5.  
  6.  
  7. b = xlrd.open_workbook('dailyrecap.20111025.xls')
  8.  
  9. sheet = b.sheet_by_index(0)
  10.  
  11. print "%s" % sheet.name
  12. print "%d" % sheet.nrows
  13.  
  14.  
  15.  
  16.  
  17. conn = MySQLdb.connect("10.22.8.62","chintan","password","X")
  18. cursor = conn.cursor()
  19. cursor.execute("""truncate master_daily""")
  20.  
  21.  
  22. query = """insert into accounts(Currency_Code, Salesman, Account, Member_Code, Contract_Month, Contract_Year, Exchange, Futures_Code, Commodity_Name, Round_Table_Half_Turn, Put_Call, Strike_Price, But_Sell, Record_ID, Opt_Premium_and_P_and_S, Confirm_Volume, Overnight_Volume, Spread_Quantity, Day_Trade_Volume, Scratch_Volume, P_and_S_Volume, Transfer_Volume, Exercise_Assign_Volume, Expired_Volume, Comission, Clearing_Fees, Exchange_Fees, Brokerage_Fees, NFA_Fees, Other_Fees, Memo_Clearing_Fees, Memo_Exchange_Fees, Electronic_Trade, Trade_Date) values (%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)"""
  23.  
  24.  
  25. for i in range(1,sheet.nrows):
  26.  
  27.     Currency_C = sheet.cell(i,0).value
  28.     Salesm = sheet.cell(i,1).value
  29.     Accou = sheet.cell(i,2).value
  30.     Member_C = sheet.cell(i,3).value
  31.     Contract_M = sheet.cell(i,4).value
  32.     Contract_Y = sheet.cell(i,5).value
  33.     Excha = sheet.cell(i,6).value
  34.     Futures_C = sheet.cell(i,7).value
  35.     Commodity_N = sheet.cell(i,8).value
  36.     Round_Table_Half_T = sheet.cell(i,9).value
  37.     Put_C = sheet.cell(i,10).value
  38.     Strike_P = sheet.cell(i,11).value
  39.     But_S = sheet.cell(i,12).value
  40.     Record_I = sheet.cell(i,13).value
  41.     Opt_Premium_and_PS = sheet.cell(i,14).value
  42.     Confirm_V = sheet.cell(i,15).value
  43.     Overnight_V = sheet.cell(i,16).value
  44.     Spread_Q = sheet.cell(i,17).value
  45.     Day_Trade_V = sheet.cell(i,18).value
  46.     Scratch_V = sheet.cell(i,19).value
  47.     P_and_S_V = sheet.cell(i,20).value
  48.     Transfer_V = sheet.cell(i,21).value
  49.     Exercise_Assign_V = sheet.cell(i,22).value
  50.     Expired_V = sheet.cell(i,23).value
  51.     Comiss = sheet.cell(i,24).value
  52.     Clearing_F = sheet.cell(i,25).value
  53.     Exchange_F = sheet.cell(i,26).value 
  54.     Brokerage_F = sheet.cell(i,27).value
  55.     NFA_Fees = sheet.cell(i,28).value
  56.     Other_F = sheet.cell(i,29).value
  57.     Memo_Clearing_F = sheet.cell(i,30).value
  58.     Memo_Exchange_F = sheet.cell(i,31).value
  59.     Electronic_T = sheet.cell(i,32).value
  60.     Trade_D = sheet.cell(i,33).value
  61.  
  62.     values = (Currency_C, Salesm, Accou, Member_C, Contract_M, Contract_Y, Excha, Futures_C, Commodity_N, Round_Table_Half_T, Put_C, Strike_P, But_S, Record_I, Opt_Premium_and_PS, Confirm_V, Overnight_V, Spread_Q, Day_Trade_V, Scratch_V, P_and_S_V, Transfer_V, Exercise_Assign_V, Expired_V, Comiss, Clearing_F, Exchange_F, Brokerage_F, NFA_Fees, Other_F, Memo_Clearing_F, Memo_Exchange_F, Electronic_T, Trade_D)
  63.  
  64.     cursor.execute(query,values)
  65.  
  66.  
  67. cursor.close()
  68. conn.commit()
  69. conn.close()
Oct 26 '11 #1
Share this Question
Share on Google+
5 Replies


bvdet
Expert Mod 2.5K+
P: 2,851
Have you verified that the column "Currency_Code" actually exists in your table?
Oct 26 '11 #2

Expert 100+
P: 621
SQL databases usually support a "SHOW COLUMNS" syntax, i.e. SHOW COLUMNS FROM 'accounts', which will return the column names, but that syntax may be "mis-remembered" so it may require some Googling on your part. There is also a "SHOW DATABASES" if you are not sure that "accounts" is the correct name for the table.

Also, you should be able to do something along the lines of
Expand|Select|Wrap|Line Numbers
  1. values = tuple([sheet.cell(i,ctr).value for ctr in range(34)]) 
Oct 26 '11 #3

bvdet
Expert Mod 2.5K+
P: 2,851
Good advice dwblas, especially the list comprehension. You should never have to create that many variables to hold temporary data. I checked, and the column names can be directly read from the table, as dwblas mentioned, and paired with the data directly read from the excel file if ordered properly in the excel file. Example reading column names:
Expand|Select|Wrap|Line Numbers
  1. import MySQLdb
  2.  
  3. db = MySQLdb.Connection(db="aisc")
  4. c1 = MySQLdb.cursors.Cursor(db)
  5.  
  6. c1.execute("SHOW COLUMNS FROM aisc_v13_1_1")
  7.  
  8. print "\n".join([item[0] for item in c1.fetchall()])
  9.  
  10. c1.close()
  11. db.close()
The above lists the column names in order.
Expand|Select|Wrap|Line Numbers
  1. >>> TYPE
  2. EDI_STD
  3. AISC_MAN
  4. T_F
  5. W
  6. A
  7. D
  8. DDET
  9. HT
  10. OD
  11. BF
  12. BFDET
  13. B
  14. ID
  15. TW
  16. TWDET
  17. TWDET_2
  18. TF
  19. TFDET
  20. T
  21. TNOM
  22. TDES
  23. KDES
  24. KDET
  25. K1
  26. X
  27. Y
  28. EO
  29. XP
  30. YP
  31. BF_2TF
  32. B_T
  33. H_TW
  34. H_T
  35. D_T
  36. IX
  37. ZX
  38. SX
  39. RX
  40. IY
  41. ZY
  42. SY
  43. RY
  44. RZ
  45. SZ
  46. J
  47. CW
  48. C
  49. WNO
  50. SW
  51. QF
  52. QW
  53. RO
  54. H
  55. TAN_ALPHA
  56. QS
  57. >>> 
The example reads version 13 of the AISC structural shapes database.
Oct 26 '11 #4

P: 2
Hey Guys,

Thank you for a prompt reply. There was a typo. I am really sorry for wasting your time. I hate it when errors do not convey properly.

Thanks again!
Oct 26 '11 #5

bvdet
Expert Mod 2.5K+
P: 2,851
No problem. Thanks for responding and "fessing up". Besides, it wasn't a total waste for me because I learned a couple of things.
Oct 27 '11 #6

Post your reply

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