pass the value of a python variable to a mysql search

Question

I’m on a quest and I still can’t solve it. Here’s the thing:

def main():

    chave = [valores_lidos]
    while(chave!=-1):

        vetor = valores_lidos
        chave= int(input("Informe o numero de id da vaca para buscar a ultima ordenha: "))
        posicao = busca_binaria(vetor, 0, len(vetor) - 1, chave)
        if posicao <=0 :
            print("a vaca %d nao foi encontrado." % chave)
            break
        else:
               print(f"A vaca de nº %d foi encontrada e sua ultima ordenha foi em {data}" % chave)

        print("Busca concluida! ")
        break
main()

In the script above, the variable CHAVE receives a number and I need to use this number inserted in this variable to search in mysql. Below is the search script in mysql. I know I’m doing it wrong, but I don’t know what the right way is. Someone can help out here. It is an academic work and my deadline is at the end.

import mysql.connector

from mysql import connector

conexao = mysql.connector.connect(user = 'root', password = 'root',
                                  host='localhost',
                                  database = 'fazenda_bd')

comando_sql2 = 'select data_ordenha from leite where id = chave'
curs.execute(comando_sql2)
valores_lidos2 = curs.fetchall()
lista = (valores_lidos2)
nova =str(' '.join([str(_) for _ in lista]))
data = nova[1:30].replace(', ', '/')
print(data)

The first line after the import that is the search in mysql.

OBS:

I didn’t post the entire code. It’s a binary search. I guess there’s no need to post everything. If need be, tell me which post the rest

Answer 1

Treat your SQL command like you did on print of your first block of code.

For any Python version you can pass the parameter in the sql string as below

comando_sql2 = 'select data_ordenha from leite where id = %d' % chave

The %d takes variables of type int. Use %s for variables of the string type, i.e.

comando_sql2 = 'select data_ordenha from leite where id = %s' % str(chave)

Although it doesn’t seem to be the case, remember that in SQL commands, fields like varchar have to be between apostrophes and in this case it would be something like:

comando_sql2 = "select data_ordenha from leite where vaca = '%s'" % "Mimosa"

Note Note the quote replacement, so that the field could be between apostrophe.

If the Python version is 3.6 or higher, you can use f-string

comando_sql2 = f"select data_ordenha from leite where id = {chave}"

Note Here the string has a f to indicate that it is an f-string and the variable is between keys.

I hope it helps.