CODEWITHSUNDEEP
pythonmysql
user3702643
user3702643

Reputation: 1495

Python insert into %s MySQL

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

Upvotes: 0

Views: 1988

Answers (2)

rd_nielsen
rd_nielsen

Reputation: 2459

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.

Upvotes: 0

casraf
casraf

Reputation: 21694

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

Upvotes: 1

Related Questions