Retriveing intergers from MySQL using Python to preform mathfunctions

Question

im very new to Python but want to preform some mathmatic functions using Python's libraries getting interger values from a mysql table i have running,

ive sucessfully established a connection using mysql.connector however im at a loss, I can select and print Rows and columbs but im unsure of the Syntax to physically define my query as an "x" or "y" in order to preform mathmatic operations with the varible.

Any help would be greatly appreciated. EDIT

    sql_select_Query = "select * from ATABLE"
    cursor = mySQLconnection .cursor()
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()`

and

    for row in records:
    print("Name = ", row[1], )
    print("X_num = ", row[2])
    print("Y_num  = ", row[3])
    print("Signal_Strength  = ", row[4], "\n")
    cursor.close()

gives me as an example

    Name = X,
    X_num = Y,
    Y_num = Z,
    SS = Q

what i would prefer in my selection operation is to define the X , Y, Z, Q to a Global name that i could then use for atleast my application math operations using Numpy libraries for example being able to perform an operator X*Y-Z+Q

I hope that is a bit clearer

Sorry, this is far too broad. Please give a [mcve]. Are you trying to get the values into python and then cast to integers, or use as integers in your query string? It's really not clear to me what you're asking. — roganjosh, Mar 12 '19 at 22:05
Thanks for the comment, ive added an edit in i appologise for the lack of clarity on the post i hope its a little more susinct. — Tyler, Mar 12 '19 at 22:30

roganjosh · Answer 1 · 2019-03-12T22:51:06.287

It is a bad idea to play with locals() and globals() if you don't exactly know what you're doing. Create a dictionary.

sql_select_Query = "select * from ATABLE"
cursor = mySQLconnection.cursor()
cursor.execute(sql_select_Query)
records = cursor.fetchall()

columns = [item[0] for item in cursor.description] # Grab the table column names

for record in records:
    # Create a dictionary {column_name: value, ...} for each row
    variable_dict = dict(zip(columns, record)) 

    print("X variable is: ", variable_dict['X'])
    # <Calculation here>

You can also configure MySQL to return values as a dictionary but this is probably an easier starting point.

This way, your "variable X" value would just be variable_dict['X'] and there's no need to make any global values other than the dictionary.

score 0 · Answer 2 · answered Mar 12 '19 at 22:43

From the gate, I would recommend following the advice of this thread highlighting the use of select *. Turning a field into an integer is possible with your SQL selection statement int the way of CAST or CONVERT. Sort of like this (my daily language is SQL Server; check out the mysql documentation for exacts):

sql_select_Query = "select Name, CAST(X as INT),CAST(Y as BIGINT) from ATABLE"

In my personal experience, SQL tends to age better than Python (tongue in cheek). Aside, if your SQL instance is on a server; I code to the workhorse as error catching is better.

But coming from it in the other direction, if you want these elements to be re-callable later, I'm suggest fetching your feedback into a dictionary.

Information about Python dictionaries can be found here. At least that way, you're pretty much working from a global but fairly structured set of captured data.

Retriveing intergers from MySQL using Python to preform mathfunctions

2 Answers2