Python MySQLdb

[chintanvj]

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:\Documen ts and Settings\axiom\ Desktop\Pioneer Excell\jacquest uff\try\UpdateD ailyRecap.py", line 64, in <module>
cursor.execute( query,values)
File "C:\Python26\li b\site-packages\MySQLd b\cursors.py", line 166, in execute
self.errorhandl er(self, exc, value)
File "C:\Python26\li b\site-packages\MySQLd b\connections.p y", line 35, in defaulterrorhan dler
raise errorclass, errorvalue
OperationalErro r: (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

import xlrd
from xlrd import open_workbook
import MySQLdb



b = xlrd.open_workbook('dailyrecap.20111025.xls')

sheet = b.sheet_by_index(0)

print "%s" % sheet.name
print "%d" % sheet.nrows




conn = MySQLdb.connect("10.22.8.62","chintan","password","X")
cursor = conn.cursor()
cursor.execute("""truncate master_daily""")


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)"""


for i in range(1,sheet.nrows):

    Currency_C = sheet.cell(i,0).value
    Salesm = sheet.cell(i,1).value
    Accou = sheet.cell(i,2).value
    Member_C = sheet.cell(i,3).value
    Contract_M = sheet.cell(i,4).value
    Contract_Y = sheet.cell(i,5).value
    Excha = sheet.cell(i,6).value
    Futures_C = sheet.cell(i,7).value
    Commodity_N = sheet.cell(i,8).value
    Round_Table_Half_T = sheet.cell(i,9).value
    Put_C = sheet.cell(i,10).value
    Strike_P = sheet.cell(i,11).value
    But_S = sheet.cell(i,12).value
    Record_I = sheet.cell(i,13).value
    Opt_Premium_and_PS = sheet.cell(i,14).value
    Confirm_V = sheet.cell(i,15).value
    Overnight_V = sheet.cell(i,16).value
    Spread_Q = sheet.cell(i,17).value
    Day_Trade_V = sheet.cell(i,18).value
    Scratch_V = sheet.cell(i,19).value
    P_and_S_V = sheet.cell(i,20).value
    Transfer_V = sheet.cell(i,21).value
    Exercise_Assign_V = sheet.cell(i,22).value
    Expired_V = sheet.cell(i,23).value
    Comiss = sheet.cell(i,24).value
    Clearing_F = sheet.cell(i,25).value
    Exchange_F = sheet.cell(i,26).value 
    Brokerage_F = sheet.cell(i,27).value
    NFA_Fees = sheet.cell(i,28).value
    Other_F = sheet.cell(i,29).value
    Memo_Clearing_F = sheet.cell(i,30).value
    Memo_Exchange_F = sheet.cell(i,31).value
    Electronic_T = sheet.cell(i,32).value
    Trade_D = sheet.cell(i,33).value

    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)

    cursor.execute(query,values)


cursor.close()
conn.commit()
conn.close()

[bvdet]

Have you verified that the column "Currency_C ode" actually exists in your table?

[dwblas]

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

values = tuple([sheet.cell(i,ctr).value for ctr in range(34)])

[bvdet]

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:

import MySQLdb

db = MySQLdb.Connection(db="aisc")
c1 = MySQLdb.cursors.Cursor(db)

c1.execute("SHOW COLUMNS FROM aisc_v13_1_1")

print "\n".join([item[0] for item in c1.fetchall()])
    
c1.close()
db.close()

The above lists the column names in order.

>>> TYPE
EDI_STD
AISC_MAN
T_F
W
A
D
DDET
HT
OD
BF
BFDET
B
ID
TW
TWDET
TWDET_2
TF
TFDET
T
TNOM
TDES
KDES
KDET
K1
X
Y
EO
XP
YP
BF_2TF
B_T
H_TW
H_T
D_T
IX
ZX
SX
RX
IY
ZY
SY
RY
RZ
SZ
J
CW
C
WNO
SW
QF
QW
RO
H
TAN_ALPHA
QS
>>>

The example reads version 13 of the AISC structural shapes database.

[chintanvj]

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!

[bvdet]

No problem. Thanks for responding and "fessing up". Besides, it wasn't a total waste for me because I learned a couple of things.