Python - add mysql entry in dynamic table

Question

I would like to add some python-dictionary data in to dynamic mysql tables, but I'm stuck at line 12:

## INSERT DATA TO TABLE
for key,val in j_decoded["one-wire"].items():
    try:
        rom = key.encode('utf-8')
        temperatuur = val["temperatuur"]
        createsqltable = """CREATE TABLE IF NOT EXISTS `%s` (
                 id INT PRIMARY KEY AUTO_INCREMENT,
                 created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                 timestamp TIMESTAMP,
                 temp FLOAT)""" % (rom)
        cursor.execute(createsqltable)
        cursor.execute("""INSERT INTO %s (timestamp, temp) VALUES (%s,%s)""",(rom,timestampdata,temperatuur))
        db.commit()
    except:     
        db.rollback()

## CLOSE THE DATABASE
db.close()

The tables are created. But the data never makes it.

Before the above code, I had some other code which did work, but I wasn't happy about the tablenames. I've put some ` at line 10:

temp FLOAT)""" % (`rom`)

This created the table as 'tablename' instead of tablename

And I had the following change at line 12: (see the first `%s`)

cursor.execute("""INSERT INTO `%s` (timestamp, temp) VALUES (%s,%s)""",(rom,timestampdata,temperatuur))

Which added the data nicely into the 'tablename' table

Now I removed some ` and the table got created OK, but the data never makes it to the table. I've read a few posts from others, but I can't seem to fix it (I think I've tried all the ` ' " combinations)

And a merry Xmas to you :)

2014-01-04 I've changed the code to:

cursor.execute("""INSERT INTO {0} (timestamp, temp) VALUES (%s,%s)""".format(a), (b,c))

user1876508 · Accepted Answer · 2013-12-26 23:21:35Z

1

Try changing

cursor.execute("""INSERT INTO `%s` (timestamp, temp) VALUES (%s,%s)""",(rom,timestampdata,temperatuur))

to

cursor.execute("""INSERT INTO `%s` (timestamp, temp) VALUES (%s,%s)""" % (rom,timestampdata,temperatuur))

This may just be a string formatting error. There is another way to do this in python 3, if you are using it. I would suggest using the python3 method if its available. For more information, look at http://www.diveintopython3.net/strings.html#formatting-strings

Also, in the createsqltable variable, you need to change

temp FLOAT)""" % (rom)

to

 temp FLOAT)""" % (rom,)

that could cause errors.

edited Dec 26, 2013 at 23:21

answered Dec 26, 2013 at 5:35

user1876508

13.2k21 gold badges71 silver badges105 bronze badges

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

9 Comments

Gaston Over a year ago

I've tried your change, but it's not working. Thank you for pointing me to the difference in python 3. I would like to use it, but for mysql usage, I've read a few times that I should stick with python 2.7, right?

user1876508 Over a year ago

You can use python3, as long as you use the right library. PyMySQL looks promising, and from their example, it looks exactly the same as the library you are using. github.com/PyMySQL/PyMySQL/blob/master/example.py

Gaston Over a year ago

I've tried python 2.7 and 3.2 with MySQLdb, oursql and PyMySQL. But I can't seem to get data in to dynamic tables. I like Python programming, but the MySQL database isn't my cup of tea. Perhaps there is some kind of debug option, so I can see what's going wrong?

user1876508 Over a year ago

Have you tried executing these SQl queries in the default MySQL console? I will try out this code and see if it has any issues.

Gaston Over a year ago

The mysql console commands, especially the dynamic insertion, is not equal to the mysqldb module (for what I've tested). This works: cursor.execute("""INSERT INTO 28AA507A0500009E (timestamp, temp) VALUES (%s,%s)""",(timestampdata,temperatuur)) But this doesn't: cursor.execute("""INSERT INTO %s (timestamp, temp) VALUES (%s,%s)""",(rom,timestampdata,temperatuur))

|

Gaston · Accepted Answer · 2014-01-04 16:10:39Z

1

I've tried different possibilities and came up with the following working solutions:

sql = "insert into %s (timestamp, temp) values (%s)" % (a, "%s, %s")  
cursor.execute(sql,(timestampdata,temperatuur))

Or

def datainsertdef(a,b,c):
cursor.execute("""INSERT INTO `{0}` (timestamp, temp) VALUES (%s,%s)""".format(a), (b,c))

edited Jan 4, 2014 at 16:10

answered Jan 4, 2014 at 15:34

Gaston

4075 silver badges15 bronze badges

Collectives™ on Stack Overflow

Python - add mysql entry in dynamic table

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related