Need help on how to include single quotes on data of variable string

[hani_hms]

I am not unable to figure out why Python cannot include single quotes on to the value of the variable string. I am trying to add single quote on the beginning and at the end of the of the data variable current_partnum on the line no. 14. This variable's value will then be use to make mysql query2 using this line of code:

[inline]
query2 = "SELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = {}".format(current_partnum)
[/inline]

So my final query should be:

SELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = '10670APRCONX22XXXX'

But instead I am getting this output:

Output:
'ELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = '10670APRCONX22XXXX

Below is my code and its output

for (var_partNum) in record:

    #Start of Debug
    print("\nHMS: Debug")
    print(var_partNum[0])
    print(var_partNum)
    #End of Debug
    current_partnum = ' '.join(var_partNum)

    print("\nWorking on record "+ str(i) + " From " + str(total_records)) #Display active record number to track the progress
    print(current_partnum)
    #current_partnum = "!!{}!!".format(current_partnum)
    print(current_partnum)
    current_partnum = "'" + current_partnum + "'"   
    print(type(current_partnum))
    print(current_partnum)
    query2 = "SELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = {}".format(current_partnum)
    print(query2) 
    cursor.execute(query2,(current_partnum)) #To get the details for each FG partnum and then process the query result to save into the excel file
    record2 = cursor.fetchall()
    found_record = cursor.rowcount
     #Start of Debug
    print("\nfound_record = " + str(found_record))
    #End of Debug

Output:
Found total records: 846

HMS: Debug
10670APRCONX22XXXX
('10670APRCONX22XXXX\r',)

Working on record 1 From 846
10670APRCONX22XXXX
10670APRCONX22XXXX
<class 'str'>
'10670APRCONX22XXXX
'ELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = '10670APRCONX22XXXX

I don't understand why the SELECT statement the character S is replaced with single quotes.

[buran]

This output is NOT produced from this code snippet

[hani_hms]

You are right. If I hard code the current_partnum variable with any string value, then it is fine.
Perhaps you can see the full code.
Below is the full code of it:

import mysql.connector
from openpyxl import workbook

#Excel Process Initialization
final_workbook = workbook.Workbook()
sheet = final_workbook.active

#MySQL Initilization
mydb = mysql.connector.connect(host="192.168.1.15",user="hani",password="password", database="hms-data")
#print(mydb)  #To test mysql connection

cursor = mydb.cursor()

query1 = "SELECT PartNum FROM partnum"
query2 = "SELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = %s"

#Get all the FG partnum and store it in records to iterate
cursor.execute(query1)
record = cursor.fetchall()
total_records = cursor.rowcount
print("\nFound total records: " + str(total_records))
i = 1 #counter to show the active records on process

#Counter for the excel file saving process
ptr_row = 1 #This variable value has to be maintained
#ptr_column = 1 #this variable value will changed from 1 to 4 only

for (var_partNum) in record:

    #Start of Debug
    print("\nHMS: Debug")
    print(var_partNum[0])
    print(var_partNum)
    #End of Debug
    current_partnum = ' '.join(var_partNum)

    print("\nWorking on record "+ str(i) + " From " + str(total_records)) #Display active record number to track the progress
    print(current_partnum)
    #current_partnum = "!!{}!!".format(current_partnum)
    print(current_partnum)
    current_partnum = "'" + current_partnum + "'"   
    print(type(current_partnum))
    print(current_partnum)
    query2 = "SELECT PartNum, WareHouseCode, BinNum, TranQty FROM trans WHERE PartNum = {}".format(current_partnum)
    print(query2) 
    cursor.execute(query2,(current_partnum)) #To get the details for each FG partnum and then process the query result to save into the excel file
    record2 = cursor.fetchall()
    found_record = cursor.rowcount
     #Start of Debug
    print("\nfound_record = " + str(found_record))
    #End of Debug
    
    
    #Check whether there is matching record found of trans table, if no, break and continue with next FG PartNum
    if (found_record <= 0):
        print("\nNo matching record found for PartNum: " + str(var_partNum[0]))
        continue

    #If yes, then proceed with query2 data and save it into excel file
    for (var_FGPart, var_warehouseCode, var_BinNum, var_TranQty) in record2:
        print("\nSaving into memory for excel")
        sheet.cell(row=ptr_row,column=1).value = var_FGPart[0]
        sheet.cell(row=ptr_row,column=2).value = var_warehouseCode[0]
        sheet.cell(row=ptr_row,column=3).value = var_BinNum[0]
        sheet.cell(row=ptr_row,column=4).value = var_TranQty[0]
        ptr_row = ptr_row + 1
    
    print("\nMoving to next record or FG PartNum")
    
#At the end, save all founded data into the excel file
print("\nSaving into the final excel file named result.xlsx from the memory")
final_workbook.save(filename="result.xlsx")

[ndc85430]

You shouldn't be using str.format to construct queries. Use your database's placeholder syntax instead. See, for example https://bobby-tables.com/.

[deanhystad]

Your query is constructed incorrectly. Somewhere online you probably saw something like this:

query1 = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
query2 = "SELECT * FROM stocks WHERE symbol = '{}'".format(symbol)

Usually when I see a query formed like this I see it preceded by a comment.

# Never do this -- insecure!
query1 = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
query2 = "SELECT * FROM stocks WHERE symbol = '{}'".format(symbol)

This kind of code leaves you open to an sql injection attack where an unscrupulous user could enter code in place of a valid symbol.

Instead of using string formatting, you should use placeholders and query parameters. In the example below, "stock_symbol" is a variable that references a stock symbol string that I want added to my query.

# Do this instead
query1 = "SELECT * FROM stocks WHERE symbol = ?"
cursor.execute(query1, (stock_symbol, ))
query2 = "SELECT * FROM stocks WHERE symbol = :symbol"
cursor.execute(query2, {"symbol": stock_symbol})

Your query mixed up the bad str.format() method that is susceptible to sql injection attacks with the recommended placeholders and query parameters method.

query2 = "SELECT * FROM trans WHERE PartNum = {}".format(current_partnum)
cursor.execute(query2,(current_partnum))

You use str.format() to replace {} with whatever is referenced by current_partnum. This is potentially dangerous and should be avoided, but his is only the first problem.

Next you use a query parameter when you execute the query. But there is no placeholder to receive the query parameter.

Finally you don't pass the query parameter correctly. This is a tuple (current_partnum, ). This is a vairable surrounded by parentheses (current_partnum). The trailing comma is required when a tuple contains only one itme.

[codinglearner]

You can include single quotes within a string by escaping them with a backslash (\).

For example, if you have a variable my_string that contains the value I don't like ice cream, you can include the single quote within the string by writing it as I don\'t like ice cream.

my_string = "I don\'t like ice cream"
print(my_string) 

Another way to include single quotes within a string is by using double quotes to enclose the string. For example, you can write the string as "I don't like ice cream".

my_string = "I don't like ice cream"
print(my_string)

There's also a third way, which is using triple quotes either single or double. Like this

my_string = '''I don't like ice cream'''
print(my_string)

or

my_string = """I don't like ice cream"""
print(my_string)

Each one has its own use case, it depends on what you are trying to do, but the most common practice is to use single quotes for short simple strings and double quotes for strings that contains single quotes, or also if you are formatting the string with place holders.

Keep in mind that it's important to make sure that you're properly escaping any single quotes or other special characters within your strings, especially if you're working with user-provided data, because it can have security implications.