Python - MySQL SELECT query with dynamic table name

Question

I am trying to fetch all the rows from the MySQL DB in Python. How to fetch all row from the table, when the table name is variable?

Here is a method that I wrote, however I am getting error while running it.

def get_rows(self, table_name):
 cursor = self.conn.cursor()
 query = ("SELECT * "
 "FROM %s ")
 try:
 cursor.execute(query, (table_name,))
 value = cursor.fetchall()
 finally:
 cursor.close()
 return value

And I am getting the following error:

AttributeError: 'NoneType' object has no attribute 'cursor'

I tried the similar way in WHERE clause and it worked fine.

• Mahmoud Magdy: please some one add solution with prepared statement for security

Answer 1

You have a problem with conn object.

'NoneType' object has no attribute 'cursor' means conn is None, probably because it wasn't established during the __ init __ call.

• Kreena Mehta: I debugged through my code. I am having 1 more database call before I run this method. At the end of the previous call, I have: cursor.close() self.conn.close() Could it be because of that?
• Stanley Kirdey: You probably should define self.conn.close inside __ del __ method of your database management class.
• Kreena Mehta: So after each database call, will the del method be called? Or it is called only one time after all DB operations are executed. Sorry, if my question is stupid. I am actually new to this.
• Kreena Mehta: I made a new method __ del __ . And I wrote self.conn.close() in it. I called it in the end (after executing all the DB calls). And it worked fine! Thank you :)
• Stanley Kirdey: The __ del __ is being called whenever an instance of an object is about to be destroyed. I am not sure what is the lifecycle of the object, and where you initially create the database connection. If you define the db connection as the application starts, it make sense to keep it open for the lifetime of the app. If you establish a connection when you create the object that has get_rows method, then it is reasonable to close the connection when that object gets destroyed.

Answer 2

You can't dynamically bind object names (here you're attempting to bind a table name) or syntactic constructs to a query. If you wish to have such a behavior you'd have to resort to string manipulation, meaning you're forfeiting all the security advantages prepared statements offer:

query = ("SELECT * FROM %s" % table_name)
try:
 cursor.execute(query, ())

• Kreena Mehta: Thank you. I now understand that how will it forfeit the security advantages, especillay if the table_name comes from the user. I tried your suggestion, however I am still getting following error: AttributeError: 'NoneType' object has no attribute 'cursor'
• Kreena Mehta: I also tried : query = "SELECT * FROM %s " % table_name try: cursor.execute(query) Still, no luck.
• Mureinik: @KreenaMehta are you sure that table actually exists in your database?
• Kreena Mehta: Yes, when I query giving the table_name in the code. It returns me all the rows. Also it works fine when queried on MySQL Workbench.