Not all parameters were used in the SQL statement

最近想要把HDF里的数据存入到mysql数据库中，为以后空间数据分析后续的应用做准备，就用python尝试了一下

 

import h5py
import numpy
import mysql.connector
import datetime

time_stamp = datetime.datetime.now()

print("time_stamp       " + time_stamp.strftime('%Y.%m.%d-%H:%M:%S'))

conn = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="shkshk",
    db="database",
    charset="utf8",
)
cursor = conn.cursor()
f= h5py.File('H8XX_AHIXX_L2_GLL_20180528_0340_2000M_SST.HDF','r')
# print(f.name)
# for key in f.keys():
#     print(key)
#     print('*******************************')
#     print(f[key].name)
#     print(f[key].shape)
#     print(f[key].value)
# print('*******************************')
# cursor.execute("SET FOREIGN_KEY_CHECKS=0")#禁用外键 innodb使用，设置为1开启
cursor.execute("SET UNIQUE_CHECKS=0")#禁用唯一性检查 设置为1则开启
# cursor.execute("SET AUTOCOMMIT=0")#禁止自动提交 innodb使用，设置为1开启
cursor.execute("ALTER TABLE hdf_my DISABLE KEYS")#禁用索引，插入完成后可以开启索引 ALTER TABLE table_name ENABLE KEYS
id=1
datalist=[]
for x in range(0,5) :
    for y in range(0,5):
        # print(f['SST Hourly Product'].value[3000][3499])
        # sql_insert = "INSERT INTO hdf_test VALUES(%d,Point(%d,%d),%d);INSERT INTO hdf_test VALUES(%d,Point(%d,%d),%d);INSERT INTO hdf_test VALUES(%d,Point(%d,%d),%d);INSERT INTO hdf_test VALUES(%d,Point(%d,%d),%d);INSERT INTO hdf_test VALUES(%d,Point(%d,%d),%d)"%(id,x,y,f['SST Hourly Product'].value[x][y],id+1,x,y+1,f['SST Hourly Product'].value[x][y+1],id+2,x,y+2,f['SST Hourly Product'].value[x][y+2],id+3,x,y+3,f['SST Hourly Product'].value[x][y+3],id+4,x,y+4,f['SST Hourly Product'].value[x][y+4])
        # sql_insert = "INSERT INTO hdf_my VALUES(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d),(%d,Point(%d,%d),%d)"%(id,x,y,f['SST Hourly Product'].value[x][y],id+1,x,y+1,f['SST Hourly Product'].value[x][y+1],id+2,x,y+2,f['SST Hourly Product'].value[x][y+2],id+3,x,y+3,f['SST Hourly Product'].value[x][y+3],id+4,x,y+4,f['SST Hourly Product'].value[x][y+4],id+5,x,y+5,f['SST Hourly Product'].value[x][y+5],id+6,x,y+6,f['SST Hourly Product'].value[x][y+6],id+7,x,y+7,f['SST Hourly Product'].value[x][y+7],id+8,x,y+8,f['SST Hourly Product'].value[x][y+8],id+9,x,y+9,f['SST Hourly Product'].value[x][y+9])

        datalist.append((id,x,y,float(f['SST Hourly Product'].value[x][y])))#
        print('%dfinish'%id)
        # if id%1000==0:
        #     conn.commit()
        id+=1
        # cursor.execute(sql_insert)
        # for results in cursor.execute(sql_insert,multi=True):
        #     pass
print("append finish and insert begin")
# print(datalist)
sql_insertmany="INSERT INTO hdf_my VALUES(%d,Point(%d,%d),%d)"
cursor.executemany(sql_insertmany,datalist)
print("insertfinish")
conn.commit()
cursor.close()
conn.close()
time_stamp = datetime.datetime.now()

print("endtime      " + time_stamp.strftime('%Y.%m.%d-%H:%M:%S'))

 

运行后弹出了Not all parameters were used in the SQL statement的错误，查找了一下,尝试着

sql_insertmany="INSERT INTO hdf_myVALUES(%d,Point(%d,%d),%d)"中的%d改成%s，如下

sql_insertmany="INSERT INTO hdf_myVALUES(%s,Point(%s,%s),%s)"就OK了

不明白为什么，单独插入一条时

cursor.execute("INSERT INTO hdf_my VALUES(%d,Point(%d,%d),%d)")%(id,x,y,f['SST Hourly Product'].value[x][y]))

并没有出现这个问题，而且也没有出现Python 'float32' cannot be converted to a MySQL type这个错误，不用在f['SST Hourly Product'].value[x][y]这个外面加个float()专门去解决这个float格式的问题

最后求关注，求点赞，欢迎大家关注我的公众号

记录所学所用，包括但不限于遥感、地信、气象、生态环境，机器学习知识，相关文献阅读，编程代码实现。偶尔荒腔走板的聊聊其他。欢迎不同领域的朋友们加入进来，多多交流。