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.

please some one add solution with prepared statement for security — Mahmoud Magdy
– Mahmoud Magdy, Commented Jan 19, 2023 at 7:07

Stanley Kirdey · Accepted Answer · 2016-03-07 19:03:40Z

2

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.

answered Mar 7, 2016 at 19:03

Stanley Kirdey

6315 silver badges21 bronze badges

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

5 Comments

Kreena Mehta Over a year ago

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 Over a year ago

You probably should define self.conn.close inside __ del __ method of your database management class.

Kreena Mehta Over a year ago

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 Over a year ago

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 Over a year ago

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.

Mureinik · Accepted Answer · 2016-03-07 18:54:27Z

1

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

edited Mar 7, 2016 at 18:54

answered Mar 7, 2016 at 18:47

Mureinik

316k54 gold badges399 silver badges405 bronze badges

4 Comments

Kreena Mehta Over a year ago

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 Over a year ago

I also tried : query = "SELECT * FROM %s " % table_name try: cursor.execute(query) Still, no luck.

Mureinik Over a year ago

@KreenaMehta are you sure that table actually exists in your database?

Kreena Mehta Over a year ago

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.

Collectives™ on Stack Overflow

Python - MySQL SELECT query with dynamic table name

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related