python datetime.date not match SQL date

Question

I have some data, I want to insert into table in netezza:

import pyodbc
data=[['GZ', datetime.date(2017, 2, 8), 19.7, 10.7, 0, '1级'], 
      ['GZ', datetime.date(2017, 2, 9), 16.3, 9.7, -1, '微风'], 
      ['GZ', datetime.date(2017, 2, 10), 16.0, 10.0, -1, '微风']]
conn = pyodbc.connect("DRIVER={NetezzaSQL}; SERVER=**;DATABASE=weather; UID=**; PASSWORD=**;")
cur = conn.cursor()
for i in data:
   cur.execute("""
         insert into WEATHER_INFO(location,weather_date,high_tempature,low_tempature,weather,wind)
         values(\'%s\',%s,%s,%s,%s,\'%s\')
         """ % (i[0], i[1], i[2], i[3], i[4], i[5]))
   conn.commit()
cur.execute('select * from WEATHER_INFO')
print(cur.fetchall())
cur.close()
conn.close()

I get some ERROR:

pyodbc.Error: ('HY000', "[HY000] ERROR:  Attribute 'WEATHER_DATE' is of type 'DATE' 
but expression is of type 'INT4'\n\tYou will need to rewrite or cast the expression (46) 
(SQLExecDirectW)")

this is table structure:

create table weather(
 location varchar(20),
 weather_date date,
 high_tempature float(4,1),
 low_temputare float(4,1),
 weather int(11),
 wind varchar(20)
);

I know the python datime.date should match SQL date. I don't get the answer that I want through search the stackoverflow. So how I should slove this question?

Gord Thompson · Accepted Answer · 2017-02-08 14:36:29Z

1

Your problem is that you are using the string formatting operator % to create dynamic SQL and those SQL statements are malformed. If we print out the actual statements that you are trying to execute they look like

insert into WEATHER_INFO(location,weather_date,high_tempature,low_tempature,weather,wind)
values('GZ',2017-02-08,19.7,10.7,0,'1级')

Notice that the date value is inserted as 2017-02-08 with no delimiters so it is being interpreted as an integer expression.

What you need to do is use a proper parameterized query:

sql = """\
insert into WEATHER_INFO(location,weather_date,high_tempature,low_tempature,weather,wind)
values(?,?,?,?,?,?)
"""
for i in data:
    cur.execute(sql, i)
    conn.commit()

or maybe just

sql = """\
insert into WEATHER_INFO(location,weather_date,high_tempature,low_tempature,weather,wind)
values(?,?,?,?,?,?)
"""
cur.executemany(sql, data)
conn.commit()

edited Feb 8, 2017 at 14:36

answered Feb 8, 2017 at 14:26

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

python datetime.date not match SQL date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related