How can i use a string in mysql query in python?-CodePudding

In the code below, i have a string which represent the mac number. I want to use it in my query. Normally it will change dynamically, because of that i want to use it as this way. When i run my code in the below i get the error

File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 395, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'B45D50CFEF6A' in 'where clause'

import os
import mysql.connector
from mysql.connector.constants import ClientFlag
import json


execfile("/home/manager/test/mysqlconnector.py")

active_ip = ""
hostname = ""
group_id = 0
active_mac = "b45d50cfef6a"

query = "select epp_active_ip, epp_hostname, epp_group_id from epp_inventory where epp_active_mac = "   active_mac.upper()
cursor = connection.cursor(dictionary=True)
cursor.execute(query)
rows = cursor.fetchall()
 #active_ip = ""
 #hostname = ""
 #group_id = 0
for row in rows:
  active_ip = row["epp_active_ip"]
  hostname = row["epp_hostname"]
  group_id = row["epp_group_id"]
  print(group_id)

CodePudding user response：

SQL syntax requires single quotes when selecting columns that are strings.

Replace the query = line with the following:

query = f"select epp_active_ip, epp_hostname, epp_group_id from epp_inventory where epp_active_mac = '{active_mac.upper()}'"

CodePudding user response：

I change my code like this. It doesnt gives an error. But the print part doesnt work. I cannot get the output from print(group_id) When i run my code, the only output is ('Connected to MySQL Server version ', u'5.7.36-0ubuntu0.18.04.1') ("You're connected to database:)

import os
import mysql.connector
from mysql.connector.constants import ClientFlag
import json


execfile("/home/manager/test/mysqlconnector.py")

active_ip = ""
hostname = ""
group_id = 0

active_mac = "b45d50cfef6a"
query = "select epp_active_ip, epp_hostname, epp_group_id from epp_inventory where epp_active_mac = '{active_mac.upper()}'" 
cursor = connection.cursor(dictionary=True)
cursor.execute(query)
rows = cursor.fetchall()
 #active_ip = ""
 #hostname = ""
 #group_id = 0
for row in rows:
  active_ip = row["epp_active_ip"]
  hostname = row["epp_hostname"]
  group_id = row["epp_group_id"]
  print(group_id)