Python insert into %s MySQL

Question

I am trying to insert some data into a database using the variable test as the table name. But unfortunately I cant seem to achieve this. Can anyone help me out?

From my raise I am getting:

TypeError: unsupported operand type(s) for %: 'tuple' and 'tuple'

My code:

   test = "hello"
   # Open database connection
    db = MySQLdb.connect("127.0.0.1","admin","password","table" )

    # prepare a cursor object using cursor() method
    cursor = db.cursor()

    # Prepare SQL query to INSERT a record into the database.
    sql = ("""INSERT INTO %s (name,
            age, gender)
            VALUES (%s, %s, %s)""",(test))
    try:
        # Execute the SQL command
        cursor.execute(sql, (name, age, gender))
        db.commit()
    except:
        raise
        db.rollback()


    # disconnect from server
        db.close()

What errors are you getting? give us a clue...

Stuart
– Stuart

2016-11-20 16:14:22 +00:00
Commented Nov 20, 2016 at 16:14 — Stuart
– Stuart, Commented Nov 20, 2016 at 16:14
@Stuart Added more information!

user3702643
– user3702643

2016-11-20 16:19:18 +00:00
Commented Nov 20, 2016 at 16:19 — user3702643
– user3702643, Commented Nov 20, 2016 at 16:19

casraf · Accepted Answer · 2016-11-20 16:15:19Z

1

I don't think MySQLdb lets you use parameters for table names -- usually this is used for actual parameters (ones that are sometimes from user input and need sanitization - the name/age/gender part gets this right). You could use Python's string formats to achieve this:

sql = ("""INSERT INTO {table} (name, age, gender)
          VALUES (%s, %s, %s)""".format(table=table), (test))

answered Nov 20, 2016 at 16:15

casraf

21.8k10 gold badges60 silver badges93 bronze badges

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

3 Comments

user3702643 Over a year ago

Could you explain what the .format(table=table) does? Because when I tried this I got NameError: global name 'table' is not defined

casraf Over a year ago

Substitute the table variable for your table's name. more info on this mechanism

user3702643 Over a year ago

I seem to get TypeError: unsupported operand type(s) for %: 'tuple' and 'tuple' when i try it

rd_nielsen · Accepted Answer · 2016-11-20 17:36:05Z

0

Something like this will work:

    sql = """INSERT INTO %s (name, age, gender)
    VALUES (%s, %s, %s)""" % (test, "%s", "%s", "%s")

You need to separate Python's string substitution from MySQL's parameter substitution. The above is a crude approach, but minimally different from your own code.

edited Nov 20, 2016 at 17:36

answered Nov 20, 2016 at 16:45

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

2 Comments

user3702643 Over a year ago

I seem to get TypeError: unsupported operand type(s) for %: 'tuple' and 'tuple' when i try it

user3702643 Over a year ago

Worked great. Thank you!

Collectives™ on Stack Overflow

Python insert into %s MySQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related