Adding data into SQL with python won't work

Question

I have a python script that should be able to write data into a made SQL table. I have these lines that should do it:

#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 += ");"

The output that I get when I print(query) and I get this:

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

A few issues I see is all the gaps in-between the values, as well as commas in-between the values. I have this line that puts a comma in-between the values but it puts it in the front and end of every value. However those may be an issue as well.

line += f",{str(value):<30} "

My question is how can I fix these lines to get the values I have into the SQL database table?

Well for starters, you need single quotes around your char/varchar values. You've also got too many commas. — Andrew, Jul 20 '22 at 14:24
So I need a sing quote around each value I want to add? I added that by doing `line += f",'{str(value):<30}' "` Wat else would I need to do to add it to the Table — PyMan, Jul 20 '22 at 14:25
don't build the sql query by concat-ing together from Python strings, the db library has tools to help you do this safely and to automatically quote the values when needed according to the db backend and column type. See https://stackoverflow.com/a/32333755/202168 — Anentropic, Jul 20 '22 at 14:30
After reading that i see that they use `UPDATE` i assume I would be using `INSERT` yet I am still confused on how the line would look — PyMan, Jul 20 '22 at 14:33
why do you use `<30`? it adds spaces to inserted strings. Simply use `f"'{value}'"` with `' '` inside `" "`. But it could be better to use special functions to create queries - like in some modules `execute("INSERT ... (?, ?, ?, ...)", data)` with `?` or `% as placeholders — furas, Jul 20 '22 at 14:54
Thank you that fixed the spacing however can you explain the `execute(query, data)` statement — PyMan, Jul 20 '22 at 14:58
[python - How to execute raw SQL in Flask-SQLAlchemy app - Stack Overflow](https://stackoverflow.com/questions/17972020/how-to-execute-raw-sql-in-flask-sqlalchemy-app) — furas, Jul 20 '22 at 15:15
you define `query = """INSERT INTO testDB.dbo.SuspensiaImageDetails (FileName, FileSize, FilePath, Height, Width, Format, Mode, Frames) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"""` and `data = ("X01LA0295.JPG", 9718, ".JPG", 400, 600, "JPEG", "RGB", 1)` and later you use special function `execute(query, data)` to run this query with data — furas, Jul 20 '22 at 15:19

score 1 · Answer 1 · answered Jul 21 '22 at 10:07

As mentioned by @furas, the best way would be to use placeholders. (%s for strings, %i for integers)

import sqlalchemy

testDBCon = sqlalchemy.create_engine('mssql+pyodbc://SRVISCDB,58837/testDB?driver=SQL+Server+Native+Client+11.0')

#Choose what query to select a column from
query1 = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"

query2 = "INSERT INTO testDB.dbo.SuspensiaImageDetails (FileName, 
         FileSize, FilePath, Height, Width, Format, Mode, Frames) VALUES (%s,%i,%s,%i,%i,%s,%s,%i)"

values = ('X01LA0295.JPG',9718,'.JPG',400,600,'JPEG','RGB',1) 

engine.execute(query2,values)

Happy Coding!

Adding data into SQL with python won't work

1 Answers1