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, Jul 20 '22 at 15:18
Post more of your code. This needs to be rewritten as a parametrized SQL query versus using string concatenation. — Makoto, Jul 20 '22 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, Jul 20 '22 at 15:22
Does this answer your question? [Bulk insert huge data into SQLite using Python](https://stackoverflow.com/questions/18219779/bulk-insert-huge-data-into-sqlite-using-python) — Makoto, Jul 20 '22 at 15:27
There's also https://stackoverflow.com/a/47057189/1079354 which is more applicable to pyodbc which is what it looks like you're using here — Makoto, Jul 20 '22 at 15:28

GodWin1100 · Accepted Answer · 2022-07-20T15:50:38.047

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 '22 at 15:50

answered Jul 20 '22 at 15:28

GodWin1100

1,380
1
6
14

Where would this go though? – Edward Wynman Jul 20 '22 at 15:28
He requires for loop and I will add for bulk too if he requests. He wants the solution regarding python parameterized SQL way – GodWin1100 Jul 20 '22 at 15:30
I just wanted to remove that first comma, this is the way i need to insert the data as I was told – Edward Wynman Jul 20 '22 at 15:31
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. – GodWin1100 Jul 20 '22 at 15:32
so theres no way to just remove that `,`? – Edward Wynman Jul 20 '22 at 15:37
In your case you can directly do `",".join([str(val) for val in info_dict.values()])` which will give you string – GodWin1100 Jul 20 '22 at 15:42
Offering the SQL injection-prone solution is undesirable, but your other approaches are pretty good. – Makoto Jul 20 '22 at 15:47
Yes I 100% agree @Makoto but he was adamant for such string based approach so I had to edit. Added caution for SQL Injection via string based approach in my answer. – GodWin1100 Jul 20 '22 at 15:48
This is a bad decision. The OP had an [XY problem](https://xyproblem.info/) and you should really have just addressed the core problem. They needed to insert bulk data into the database, and there are plenty of safe and efficient ways to do this. There is ***no*** place for sting concatenation anywhere near SQL here. – Makoto Jul 20 '22 at 15:50
@Makoto yes I did proposed only parameterized approach without sql-injection but in downvote spree it also got downvoted but no issues. But he said he wants that approach only and so I edited and after you suggestion I added warning regarding that approach. I totally understand you. – GodWin1100 Jul 20 '22 at 15:54
If you understood me, you wouldn't be presenting the concatenation solution at all. It's not *just* this person that's going to see this question and try to gin up an answer with it. (There's also a duplicate floating around there about this very thing but since you wanted to answer it, not much I can do about it...) – Makoto Jul 20 '22 at 15:56
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246622/discussion-between-godwin-and-makoto). – GodWin1100 Jul 20 '22 at 15:57

score 0 · Answer 2 · answered Jul 20 '22 at 15:45

0

Just do this :

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

answered Jul 20 '22 at 15:45

PyMan

132
13

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. – Makoto Jul 20 '22 at 15:46

Removing a comma from a python printed string for SQL statement

2 Answers2

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:

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