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?

Stop constructing SQL queries using string formatting operations, and start writing parameterized SQL queries. — chepner
– chepner, Commented Jul 20, 2022 at 15:18
Post more of your code. This needs to be rewritten as a parametrized SQL query versus using string concatenation. — Makoto
– Makoto, Commented Jul 20, 2022 at 15:20
This is the only way i know how to do this so I apologize if its not good enough for you — Edward Wynman
– Edward Wynman, Commented Jul 20, 2022 at 15:22
Does this answer your question? Bulk insert huge data into SQLite using Python — Makoto
– Makoto, Commented Jul 20, 2022 at 15:27

GodWin1100 · Accepted Answer · 2022-07-20 15:50:38Z

2

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)

edited Jul 20, 2022 at 15:50

answered Jul 20, 2022 at 15:28

GodWin1100

1,4301 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Edward Wynman Over a year ago

Where would this go though?

GodWin1100 Over a year ago

He requires for loop and I will add for bulk too if he requests. He wants the solution regarding python parameterized SQL way

Edward Wynman Over a year ago

I just wanted to remove that first comma, this is the way i need to insert the data as I was told

GodWin1100 Over a year ago

I see but normal SQL string query are not preferred if doing via some programming language as it will give problem with string datatype of respective language.

Edward Wynman Over a year ago

so theres no way to just remove that ,?

|

Eddiex045 · Accepted Answer · 2022-07-20 15:45:22Z

-1

Just do this :

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

answered Jul 20, 2022 at 15:45

Eddiex045

6014 bronze badges

1 Comment

Makoto Over a year ago

This is categorically poor code; you're introducing an SQL injection risk by simply concatenating the lines together. Irrespective of if this is how the OP asked how to do this, this is dangerous practice and should not be parroted in an answer.

Collectives™ on Stack Overflow

Removing a comma from a python printed string for SQL statement

2 Answers 2

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:

12 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

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:

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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