mysql.connector.errors.ProgrammingError:处理格式参数失败； Python“列表"无法转换为MySQL类型 [英] mysql.connector.errors.ProgrammingError: Failed processing format-parameters;Python 'list' cannot be converted to a MySQL type

问题描述

我正在尝试使用python脚本在MYSQL数据库中存储一些数据，但是出现以下错误.

I am trying to store some data in MYSQL database using python script, but i got the following error.

mysql.connector.errors.ProgrammingError: Failed processing format-parameters; 
Python 'list' cannot be converted to a MySQL type

实际上，我是从netCDF文件中提取变量并将其存储在MYSQL数据库中.我的代码是

Actually I am extracting variables from netCDF file and trying to store them in MYSQL db. my code is

import sys
import collections
import os
import netCDF4
import calendar
from netCDF4 import Dataset
import mysql.connector
from mysql.connector import errorcode

table = 'rob-tabl'
con = mysql.connector.connect(user='rob', password='xxxx',
                                  database=roby)
cursor = con.cursor()


smeData = """
        CREATE TABLE rob-tabl (
        `id` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `time.val` double,
        `time.microsec` double,
        `temp.degrees` double,
        `humid.calc` double,
        `pressure.calc` double;"""

这些是我在mMYSQL数据库中的字段/列名称.我正在尝试将netCDF4数据插入MYSQL

these are my fields/ columns names in mMYSQL database. I am trying to insert netCDF4 data into MYSQL

smeData = "INSERT INTO `" + table + "` "
.
.
.
.
.
.
.
.
data_array = []
for item in totfiles.items(): # loop on different netCDF files in a
                               directory , but at the moment I had only one file
    nc = Dataset('filename', 'r')
    data1 = nc.variables['time'][:]
    data2 = nc.variables['microsec'][:]
    data3 = nc.variables['temperature'][:]
    data4 = nc.variables['humidity'][:]
    data5 = nc.variables['pressure'][:] 
    data = ([(data1).tolist(), (data2).tolist(), data3.tolist(), data4.tolist(), data5.tolist()])
    data_array.append(data)
    print type(data)
    for v in data_array:
        cursor.executemany(smeData, (v,))

当我打印netCDF变量数据(例如时间变量)时，它看起来像 这个

when I print netCDF variables data, e.g time variable, it looks like this

nc.variables['time'][:] # netCDF variable

所以data1看起来像这样

so the data1 looks like this

[1302614127 1302614137 1302614147 ............., 1302614627 1302614647 1302614657]

和微秒，即data2看起来像

and microseconds which is data2 looks like

 [0 0 0..................., 0 0 0]

和data_array看起来像这样，因为它包含五个不同的列表.

and the data_array looks like this because it consists of five different lists.

data_array=  ([1302614127 1302614137 1302614147 ..., 1302614627 1302614647
 1302614657], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [21, 22,34,34....,67,55], [12.2, 12.54, 12.55....,45.54,45.48], [0,0,0...,0,0,00])

如果我尝试执行cursor.executemany(smeData，(v，))命令，它会给我这个错误

and if i try to do cursor.executemany(smeData, (v,)) command , it gives me this error

Python 'list' cannot be converted to a MySQL type

我的MYSQL插入语法是

my MYSQL insert syntax is

"INSERT INTO `rob-tabl` (`time.val`,`time.microsec`,`temp.degrees`,
`humid.calc`,`pressure.calc`) VALUES (%s,%s,%s,%s,%s)"

我在MYSQL中创建了5列，我不得不将数据从netCDF存储到db中.
如果有人帮助我或给我一些提示，我该如何处理这样的错误.这会很棒.谢谢

I created 5 columns in MYSQL and I had to store data from netCDF into db.
if someone help me or give some hint how can I deal with such a error. It would be great. thanks

推荐答案

尽管我从所见不曾使用过它:

Although I never used it from what I saw:

smeData = "INSERT INTO `rob-tabl` (`time.val`,`time.microsec`,`temp.degrees`, `humid.calc`,`pressure.calc`) VALUES (%s,%s,%s,%s,%s)"
cursor.executemany(smeData, data_array)

没有for循环.

这篇关于mysql.connector.errors.ProgrammingError:处理格式参数失败； Python“列表"无法转换为MySQL类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！