How do I insert table from numpy into Mysql

Question

I cant get the insert to work in this code. I have other numpy to mysql that work fine. But there are no errors in running code. Mysql gives no errors. Using python 2.7 and pyodbc on winx64.

p = np.arange(1, 33, 3)
print p

def prow (f1,series):
    rowp=[]
    series = series.tolist()
    ss = series[8:]
    rowp.append(1)       
    rowp.extend(ss)         

    print len (rowp)
    print rowp
    print type(rowp)  

    InSQL  = 'INSERT INTO test._t VALUES (1, 25, 28, 31)' 
    print InSQL
    # csr.executemany(InSQL,rowp)
    csr.execute(InSQL)
    print 1234   
    cxn.commit

prow('foo', p)

MySql is below:

CREATE TABLE `_t` (
  `Id` int(11) DEFAULT NULL,
  `T1` float(12,4) DEFAULT NULL,
  `T2` decimal(12,4) DEFAULT NULL,
  `T7` float(12,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This print statements give;

[ 1  4  7 10 13 16 19 22 25 28 31]
4
[1, 25, 28, 31]
<type 'list'>
INSERT INTO test._t VALUES (1, 25, 28, 31)
1234

Using the "INSERT INTO test._t VALUES (1, 25, 28, 31)" in mysql shell works just fine. there is alot of redundant code, right now. The idea is to use executemany, but I cant the the simple code to work.

Where are declaring csr and cxn?

Michael Berkowski
– Michael Berkowski

2011-09-13 18:20:56 +00:00
Commented Sep 13, 2011 at 18:20 — Michael Berkowski
– Michael Berkowski, Commented Sep 13, 2011 at 18:20
in the header which wasnt included.

Merlin
– Merlin

2011-09-13 18:22:18 +00:00
Commented Sep 13, 2011 at 18:22 — Merlin
– Merlin, Commented Sep 13, 2011 at 18:22

Michael Berkowski · Accepted Answer · 2011-09-13 18:28:14Z

2

Your commit is missing parens:

cxn.commit

# Should be:
cxn.commit()

Run from the interpreter, cxn.commit without parens simply returns a reference to the commit() method:

# Something like
>>> conn.commit
<built-in method commit of Connection object at 0x9cfe13c>

answered Sep 13, 2011 at 18:28

Michael Berkowski

271k47 gold badges451 silver badges395 bronze badges

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

4 Comments

Merlin Over a year ago

ok,but I change the InSQL = 'INSERT INTO test._t VALUES (?, ?, ?, ?)', I still list error? And, use executemany...

Merlin Over a year ago

csr.executemany(InSQL,rowp) TypeError: ('Params must be in a list, tuple, or Row', 'HY000')

rocksportrocker Over a year ago

use list(rowp) instead of rowp

Merlin Over a year ago

tried, it didnt work. tried to use list(rowp) prior to executemany, didnt work

Collectives™ on Stack Overflow

How do I insert table from numpy into Mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related