Removing a comma from a python printed string for SQL statement

Question

I have a python script that makes SQL insert statements. It works however I have one issue. I have it add a , before each value.

Here is my full script:

from distutils.util import execute
import json
import pathlib
from sqlite3 import Connection, connect
from tkinter import INSERT
from PIL import Image
from PIL.ExifTags import TAGS
import os
import os.path
import PIL
from pandas import json_normalize
import sqlalchemy
import pandas as pd

PIL.Image.MAX_IMAGE_PIXELS = 384000000

rootdir = r"C:\Users\edward\OneDrive\Pics"

for file in os.listdir(rootdir):
    try:
        # read the image data using PIL
        image = Image.open(os.path.join(rootdir, file))
        # extract other basic metadata
        info_dict = {
            "FileName": os.path.basename(image.filename),
            "FileSize": os.path.getsize(image.filename),
            "FilePath": pathlib.Path(image.filename).suffix,
            "DPI": image.info["dpi"],
            "Height": image.height,
            "Width": image.width,
            "Format": image.format,
            "Mode": image.mode,
            "Frames": getattr(image, "n_frames", 1),
        }
        line = ""
        for label, value in info_dict.items():
            line += f",'{str(value)}' "
        # Connect to the database
        testDBCon = sqlalchemy.create_engine(
            "mssql+pyodbc://SRVISCDB,58837/testDB?driver=SQL+Server+Native+Client+11.0"
        )
        # Choose what query to select a column from
        query = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"
        query = "INSERT INTO testDB.dbo.SuspensiaImageDetails (FileName, FileSize, FilePath, Height, Width, Format, Mode, Frames)VALUES ("
        query += line
        query += ");"
    except:
        # read the image data using PIL
        image = Image.open(os.path.join(rootdir, file))
        # extract other basic metadata
        info_dict = {
            "FileName": os.path.basename(image.filename),
            "FileSize": os.path.getsize(image.filename),
            "FilePath": pathlib.Path(image.filename).suffix,
            "Height": image.height,
            "Width": image.width,
            "Format": image.format,
            "Mode": image.mode,
            "Frames": getattr(image, "n_frames", 1),
        }
        line = ""
        for label, value in info_dict.items():
            line += f",'{str(value)}' "
            # Connect to the database
            testDBCon = sqlalchemy.create_engine(
                "mssql+pyodbc://SRVISCDB,58837/testDB?driver=SQL+Server+Native+Client+11.0"
            )
            # Choose what query to select a column from
            query = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"
            query = "INSERT INTO testDB.dbo.SuspensiaImageDetails (FileName, FileSize, FilePath, Height, Width, Format, Mode, Frames)VALUES ("
            query += line
            query += ");"

Here is the line at adds the ,:

line += f",'{str(value)}' "

As of right now it looks like this:

INSERT INTO testDB.dbo.SuspensiaImageDetails (FileName, FileSize, FilePath, Height, Width, Format, Mode, Frames)VALUES (,'X01LA0295.JPG' ,'9718' ,'.JPG' ,'400' ,'600' ,'JPEG' ,'RGB' ,'1' );

The issue is here:

VALUES (,'X01LA0295.JPG'

The first ',' after the '(' needs to be removed.

Any idea of how to only remove the first comma?

Answer 1

In your case you want in string format only this could work for you:

info_dict = {
        "FileName": os.path.basename(image.filename),
        "FileSize": os.path.getsize(image.filename),
        "FilePath": pathlib.Path(image.filename).suffix,
        "Height": image.height,
        "Width": image.width,
        "Format": image.format,
        "Mode": image.mode,
        "Frames": getattr(image, "n_frames", 1)
    }

line=",".join([str(val) for val in info_dict.values()]) # THIS LINE ADD HERE

Above approach is prone to SQL Injection as pointed out by @Makoto

---

It's recommended that you write parameterized SQL query which is as follows:

---

You can execute query like this and pass tuple for values but they should be positional

c.execute("INSERT INTO testDB.dbo.SuspensiaImageDetails values (?, ?, ?, ?, ?, ?, ?, ?)", ("FileNameValue","FileSizeValue","FilePathValue","HeightValue","WidthValue","FormatValue","ModeValue","FramesValue"))

---

You can also use placeholder for values and pass dictionary for the corresponding values.

c.execute("INSERT INTO testDB.dbo.SuspensiaImageDetails values (:FileName, :FileSize, :FilePath, :Height, :Width, :Format, :Mode, :Frames)", {"FileName":"value","FileSize":"value","FilePath":"value","Height":"value","Width":"value","Format":"value","Mode":"value","Frames":"value"})

---

For Bulk insert you can use

c.executemany("INSERT INTO testDB.dbo.SuspensiaImageDetails values (:FileName, :FileSize, :FilePath, :Height, :Width, :Format, :Mode, :Frames)", info_dict)

Answer 2

Just do this :

",".join([str(val) for val in info_dict.values()])