Python 'ndarray' cannot be converted to a MySQL type

Question

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 'ndarray' 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 + data2 + data3 + data4 + data5
    data_array.append(data)
    print 'data_array: ', data_array
    cursor.execute(smeData, data_array)

or if i try to combine all variable like this

data_array = []
for item in totfiles.items():
    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:
        cursor.executemany(smeData, (v,))

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

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

i got this

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

and microseconds looks like

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

and the data_array looks like

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

but if I print

for v in data:
    print v 

then i got 1st list only within list and not the other lists which I guess is my main issue.

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

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

mysql.connector.errors.ProgrammingError: Not all parameters were used in 
the SQL statement

my MYSQL insert syntax is my MYSQL syntax is

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

In my case it is numpy.float32. I created 5 columns in MYSQL and I had to store data from netCDF into db.
I am new to programming and I am learning. if someone help me or give some hint how can I deal with such a error. I shall be very thankful. thanx a lot.

Answer 1

Working with sqlite3 rather than MYSQL, but I think the sql will be similar

In [709]: import sqlite3
In [711]: conn=sqlite3.connect(":memory:")

define a simple 3 field table:

In [714]: conn.execute('create table test (x, y, z)')
Out[714]: <sqlite3.Cursor at 0xa943cb20>

define a numpy array, 4 'rows', 3 'columns'

In [716]: data = np.arange(12).reshape(4,3)
array([[ 0,  1,  2],
       [ 3,  4,  5],
       [ 6,  7,  8],
       [ 9, 10, 11]])

tolist converts it to a list of lists of numbers:

In [735]: data.tolist()
Out[735]: [[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]]

I can insert it into the table with:

In [719]: conn.executemany('insert into test values (?,?,?)',data.tolist())
Out[719]: <sqlite3.Cursor at 0xa93dfae0>

And test the insert with:

In [720]: for row in conn.execute('select x,y,z from test'):
     ...:     print(row)
     ...:     
(0, 1, 2)
(3, 4, 5)
(6, 7, 8)
(9, 10, 11)

So it has written each sublist of data.tolist() as a record in the table.

I'm guessing that you want to write 5 fields to database, corresponding to the data1, data2 etc from each Dataset.

To get more help I'd suggest including the create command, test with just one Dataset, and show (or sumarize) the dataarray that you are trying to insert.

Another way of creating a compatible list of lists is:

In [736]: data = [np.arange(3).tolist(),np.arange(10,13).tolist(),np.arange(20,23).tolist()]
In [737]: data
Out[737]: [[0, 1, 2], [10, 11, 12], [20, 21, 22]]
In [738]: conn.executemany('insert into test values (?,?,?)',data)
Out[738]: <sqlite3.Cursor at 0xa93df320>
In [739]: for row in conn.execute('select x,y,z from test'):
     ...:     print(row)
     ...:     
....
(0, 1, 2)
(10, 11, 12)
(20, 21, 22)

---

In mysql.connector.errors.ProgrammingError: Failed processing format-parameters;Python 'list' cannot be converted to a MySQL type you are trying to save a list of 5 lists, each sublist 2000 items long. Lets expand my example.

I have (3,10) data array

In [881]: data
Out[881]: 
array([[  0,   1,   2,   3,   4,   5,   6,   7,   8,   9],
       [ 10,  11,  12,  13,  14,  15,  16,  17,  18,  19],
       [100, 101, 102, 103, 104, 105, 106, 107, 108, 109]])

data.tolist() would make a 3 element list, with 10 element sublists.

In [884]: conn.executemany('insert into test values (?,?,?)',data.tolist())
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-884-6788d19a96ab> in <module>()
----> 1 conn.executemany('insert into test values (?,?,?)',data.tolist())

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 3, and there are 10 supplied.

sqlite3 is giving a different error than your MYSQL, but I think the underlying issue is the same - that of trying to write a 10 element list or tuple to a 3 field record.

But if I first transpose the array, I get a list of 10 sublists

In [885]: conn.executemany('insert into test values (?,?,?)',data.T.tolist())
Out[885]: <sqlite3.Cursor at 0xa6c850a0>
In [886]: for row in conn.execute('select x,y,z from test'):
     ...:     print(row)
....
(0, 10, 100)
(1, 11, 101)
(2, 12, 102)
(3, 13, 103)
(4, 14, 104)
(5, 15, 105)
(6, 16, 106)
(7, 17, 107)
(8, 18, 108)
(9, 19, 109)

the transpose list is:

In [887]: data.T.tolist()
Out[887]: 
[[0, 10, 100],
 [1, 11, 101],
 ...
 [9, 19, 109]]

A well known Python idiom for 'transposing' lists uses zip. It actually produces a list of tuples, which might be a good thing.

In [888]: list(zip(*data))
Out[888]: 
[(0, 10, 100),
 (1, 11, 101),
 (2, 12, 102),
 ....
 (8, 18, 108),
 (9, 19, 109)]

A list of tuples is easier to format:

for row in data.T.tolist():
    print('%s,%s,%s'%tuple(row))
for row in zip(*data):
    print('%s,%s,%s'%row)