Python + MySQL.Connector-Python: select_statement with function defined variables

Question

I have started a small project yesterday in python and finally managed to make a database selection function that works, but I was wondering if anybody could tell me if the way I wrote it is good or could eventually end up in multiple problems.

The idea was to make a function that I could call to request/update a table where I would store players data for a small online game I want to create via Python, Pygame and MySQL.Connector-Python

So, here is the function code (I have tried to keep it as clean as possible and as intuitive as I could with my current knowledge of Python which is limited currently as I just picked it back up this week.)

The part I am not sure is the select_statement variable where I do not know for sure if the way I used concatenation is okay or if there is a way as simple and efficient.

def db_select(selection, table):
    dbc = db_connect()

    if dbc:
        print("The SQL connection was successful.")
    else:
        print("The SQL connection could not be established.")

    cur = dbc.cursor()

    select_statement = "SELECT * FROM " + table + " WHERE id = %(id)s"

    cur.execute(select_statement, {'id': selection})

    print(cur.fetchone()[1])

    dbc.close()
    print("The SQL connection was closed successfully.")

Possible duplicate of Python MySQL parameter queries for dynamic table names — ivan_pozdeev
– ivan_pozdeev, Commented Nov 14, 2018 at 23:55
A table name isn't supposed to be dynamic though. Consider turning it into a column. — ivan_pozdeev
– ivan_pozdeev, Commented Nov 14, 2018 at 23:56

dozyaustin · Accepted Answer · 2018-11-15 00:00:23Z

0

Python has the .format()syntax just for these kinds of situations - handling a dynamic value (such as table being inserted into a string (including SQL queries).

query = "SELECT * FROM {} WHERE id = %(id)s".format(table)

It's cleaner and more reliable in my daily use than the prior approaches such as yours.

answered Nov 15, 2018 at 0:00

dozyaustin

6711 gold badge7 silver badges21 bronze badges

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

2 Comments

Avarthar Over a year ago

Thanks for the quick answer :), I tried to find some documentation on that, but found nothing for the past three hours. I will add the accepted answer as soon as the four minutes delay passes. Thank you again :)

dozyaustin Over a year ago

@Avarthar here is a decent overview programiz.com/python-programming/methods/string/format, it's pretty simple unless you try to do something seriously complex. For example, expanding on your original query, is super simple: SELECT * FROM {} WHERE id = {}".format(table, int(id))

Collectives™ on Stack Overflow

Python + MySQL.Connector-Python: select_statement with function defined variables

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related