Looping through a list of dictionaries (Python) by using one of the many keys and checking value-CodePudding

I want a Python code that reads a database using pyodbc, and then reads the curser into a list of dictionaries that I want to iterate and find the entry with the key I want. for example I have this db with this header: ['id', 'user_name', 'user_lname', 'user_username'].

What I want is to print out the entry in the db that has 'user_username'='JOHNN' This is how the list looks like : [{'id': 105, 'user_name': 'John', 'user_lname': 'Smith', 'user_username': 'JOHNN'}]

Below is the python code:

from flask import Flask

import pyodbc 

app = Flask(__name__)

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DESKTOP-9JKA425;'
                      'Database=PD;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM Users')

columns = [column[0] for column in cursor.description]
results = []
for row in cursor.fetchall():
    results.append(dict(zip(columns, row)))

# for x in results:
print(results)

Please advise

CodePudding user response：

When you want to query a database with specific criteria, it is better to do this by narrowing your SQL query, rather than querying for * and then filtering in Python.

For example:

query = "SELECT * FROM Users WHERE username = 'JOHNN'"
cursor.execute(query)

Using SQL to narrow down your result set will also reduce the amount of data transported over the network and in many cases, SQL databases will do the necessary conditionals faster than Python.

See https://github.com/mkleehammer/pyodbc/wiki/Getting-started#parameters for how to add dynamic parameters safely for SQL queries when using pyodbc, if you simply use parameters directly, you run the risk of introducing SQL injections into your code.

Written safely, the above becomes:

query = "SELECT * FROM Users WHERE username = ?"
cursor.execute(query,'JOHNN')