How to query MySQL Record value to Python Variables?

Question

I want to give python variables with values that I fetch from MySQL database.

#!/usr/bin/python -u
# -*- coding: UTF-8 -*-

import time
import datetime
import mysql.connector
import sys

db = mysql.connector.connect(
    host = "localhost",
    user = "admin",
    password = "admin",
    db = "testonly"
)
mycursor = db.cursor()

if __name__ == '__main__':
    temp = 0
    mycursor.execute("SELECT temperature FROM table ORDER BY primarykey DESC LIMIT 1;") #By selecting one column in a row, I fetch only one record from the talbe.
    data = mycursor.fetchone()
    for temperature in data:
        print(temperature)

    temp = data['temperature']

    sys.exit()

Then I have error like so:

File "test.py", line 28, in <module>
    temp = data['temperature']
TypeError: tuple indices must be integers, not str

In which way I can give value to python variable for later usage?

Wondercricket · Accepted Answer · 2021-09-15 18:22:49Z

2

By default, fetchone returns a tuple with the data from your database. As it currently stands, you need to access your data by index

temp = data[0]

If you want to access your data by the temperature key, you need to use specify your cursor

from mysql.connector.cursor import MySQLCursorDict
...

mycursor = db.cursor(cursor_class=MySQLCursorDict)

...

temp = data['temperature']

answered Sep 15, 2021 at 18:22

Wondercricket

7,9122 gold badges44 silver badges62 bronze badges

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

Comments

Shivam Roy · Accepted Answer · 2021-09-15 18:22:06Z

1

Your object data is a tuple and can't be referenced like that. You need to use this:

temp = data[0]

answered Sep 15, 2021 at 18:22

Shivam Roy

2,0713 gold badges12 silver badges25 bronze badges

4 Comments

matrunaydo Over a year ago

Thank you, that works. What if I want to query 2 values in one selection? ``` mycursor.execute("SELECT temperature，pressure FROM table ORDER BY primarykey DESC LIMIT 1;") data = mycursor.fetchone() for x in data: print(temperature, pressure) temp = data[0] pres = data[1] ```

Shivam Roy Over a year ago

As in you want to select two or more columns? You can do it through indexing. Example: data[:2] Have I understood your question correctly?

matrunaydo Over a year ago

Yes, I want to select 2 columns, and I want to give it's values to 2 seperate variables in one selection.

Shivam Roy Over a year ago

I guess tuple unpacking should work for you since there are only two variables. Please try this: temp , press = data

Collectives™ on Stack Overflow

How to query MySQL Record value to Python Variables?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related