0

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

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

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

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

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

so the data1 looks like this 

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

and microseconds which is data2 looks like 

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

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

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

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

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

rob
  • 153
  • 2
  • 6
  • 13
  • @Ike Walker , thanx for the editing. – rob Feb 13 '17 at 20:45
  • What's the length of each sublist? With that `insert` command it should be 5, one value for each `%s`. – hpaulj Feb 13 '17 at 21:09
  • @hpaulj I mention and show sublist print in my question. there are five list and I use 5 %s accordingly. .but if i want to see len. e.g print len(data1), it results in 2000 because I had 2000 values in every list. – rob Feb 13 '17 at 21:45

1 Answers1

0

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)

whithout the for loop.

gms
  • 325
  • 3
  • 9
  • from what i see data_array must be [(),(),()] type. A list with tuples values. You have a list with list values. Maybe this is the problem. Convert the inside lists to tuples and try it again (with the smeData i wrote or like it). And by the way the data_array must include the values this means 5 values per tuple. If it has more this will not work – gms Feb 13 '17 at 21:28
  • Can you show me an example. and I mention in my question that actually every sublist consist of almost 2000 values. how can I convert them to tuple and then a group of 5. thanx a lot for the help. . – rob Feb 13 '17 at 21:32
  • Its working with lists inside list. I tried with pymysql which is similar to mysql. So at data = ([(data1).tolist(), (data2).tolist(), data3.tolist(), data4.tolist(), data5.tolist()]) the data1.tolist() and others are single values or what? They have to be single values because later you data_array.append(data). Btw you can make them tuples if you set data as: data = (((data1).tolist(), (data2).tolist(), data3.tolist(), data4.tolist(), data5.tolist())) – gms Feb 14 '17 at 00:07
  • I solve my problem by using zip and taking transpose. like list(zip(*data)). . but thanx a lot for the help and guidance. – rob Feb 14 '17 at 11:40