adding a dynamic table name in python

Question

In the given query I am trying to take table name as input from user:

import MySQLdb
import csv
conn=MySQLdb.connect("localhost","root","","graphdata")

c=conn.cursor()
a= raw_input("Enter the table name")
sql='''SELECT distinct sku FROM %s_management'''
c.execute(sql,str(a))
rows=c.fetchall()

file=open('mine.csv','a+')

for eachRow in rows:
    print eachRow
    a=eachRow
    file.write(str(a)+"\n")

file.close()

what I wanted was that the compiler should as

Enter table name:

I should enter the table name

Enter table name: inventory

it should get concatenated in the query:

sql='''SELECT distinct sku FROM inventory_management'''

but up until now I have gotten these errors:

 c.execute(sql,str(a))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 198, in execute
    query = query % args
TypeError: not all arguments converted during string formatting


  File "change.py", line 7, in <module>
    sql='''SELECT distinct sku FROM %s'''(a)
TypeError: 'str' object is not callable


    c.execute("SELECT distinct sku FROM %s_management")(a)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 219, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 38, in defau
lterrorhandler
    raise errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax
; check the manual that corresponds to your MySQL server version for the right s
yntax to use near '%s_management' at line 1")

Unfortunately, tables can't be the target of parameter substitution. More info over here stackoverflow.com/a/3247553/3603445 — chakradhar kasturi
– chakradhar kasturi, Commented Jun 14, 2018 at 7:11
@chakri the solution given below worked out. thank you for the link provided — user9144536
– user9144536, Commented Jun 14, 2018 at 10:47

Chen A. · Accepted Answer · 2018-06-14 07:19:10Z

2

Use format() of string

>>> sql='SELECT distinct sku FROM {}_management'.format(a)
>>> sql
>>> 'SELECT distinct sku FROM inventory_management'

Then you just pass your query to the cursor:

c.execute(sql)

answered Jun 14, 2018 at 7:19

Chen A.

11.5k4 gold badges55 silver badges75 bronze badges

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

Comments

harpratap · Accepted Answer · 2018-06-14 07:16:13Z

2

I don't recommend this because of security issues it brings with it, but you can do this instead -

c.execute(sql % str(a))

Or use any other kind of string formatter in python to change the table name.

answered Jun 14, 2018 at 7:16

harpratap

3656 silver badges12 bronze badges

3 Comments

user9144536 Over a year ago

can explain a bit about what issues might be there

harpratap Over a year ago

@user9144536 Read about SQL injections. It's very dangerous to have user defined input to be directly executed as a query. It's the same issue with using format() or any other way of manipulating strings.

user9144536 Over a year ago

Thank you bro, really helped

Collectives™ on Stack Overflow

adding a dynamic table name in python

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related