Python pymysql - iterate through mysql table key and value

Question

I am new to python pymysql (I used before Ruby with Mysql2 gem), I want to get the key and the value from mysql table and do some actions:

For example:

dbconnection = pymysql.connect(host=mysql_hostname, user=mysql_username, password=mysql_pass, db=mysql_schema, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
cursor = dbconnection.cursor()
_SQL = (""" 
        select * ...
        """)

cursor.execute(_SQL)
result = cursor.fetchall()
for row in result:
    print(row)
    print("\n")
    # How can I access each key and each value for example: STATUS is the value and 3 is the key
    # I want to do something like this: 
    #'if the value of 'CAP' > 1: change the value of status where the ID key
    #   cursor.execute("UPDATE <table> SET row[STATUS]='1' WHERE ID='row[ID]'")

Output:

{'STATUS': 3, 'ID': 10, 'CAP': 1}
{'STATUS': 3, 'ID': 11, 'CAP': 2}
{'STATUS': 3, 'ID': 12, 'CAP': 3}

Thanks

What do you mean with the key and value of a table? A table can have multiple columns. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Feb 27, 2017 at 12:31
I update my question, I want to be able to access the key name and the value name for each result — Berlin
– Berlin, Commented Feb 27, 2017 at 12:35

willeM_ Van Onsem · Accepted Answer · 2017-02-27 12:37:10Z

4

A row is simply a dictionary. So you can use .items() to generate a sequence of key and value:

for row in result:
    for key,value in row.items():
        print('The key is %s'%key)
        print('The value is %s'%value)

answered Feb 27, 2017 at 12:37

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

3 Comments

Berlin Over a year ago

right :), it is a bit confusing when switching from Ruby to Python, do you maybe know if its ok to use 'pymysql' instead of MySQL Connector ?

willeM_ Van Onsem Over a year ago

@Berlin: unfortunately I'm not very familiar with database frameworks, etc.

user3072843 Over a year ago

Could you provide a complete example. With cursor. fetchall() this does not work.

Troy Wray · Accepted Answer · 2017-11-26 12:38:02Z

2

Using pymysql, you need to declare the cursor as a dictionary type like below:

import pymysql

connection = pymysql.connect(host="localhost", user="root", passwd="", database="myraces")
#  cursor = connection.cursor()
cursor = pymysql.cursors.DictCursor(connection)

query = "SELECT * FROM `races`"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    for key, value in row.items():
        print(key, value)

answered Nov 26, 2017 at 12:38

Troy Wray

1,0387 silver badges15 bronze badges

1 Comment

Mandera Over a year ago

Thank you! You can also declare it like this with connection.cursor(DictCursor) as cursor:

Meghdeep Ray · Accepted Answer · 2017-02-27 13:53:46Z

1

The code you are looking for is this:

for row in result:
    if row["CAP"] > 1:
        cursor.execute("UPDATE <table> SET row[STATUS]='1' WHERE ID='row[ID]'")
    else:
        continue

answered Feb 27, 2017 at 13:53

Meghdeep Ray

5,6274 gold badges37 silver badges62 bronze badges

Collectives™ on Stack Overflow

Python pymysql - iterate through mysql table key and value

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related