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
– Andrew, Commented Jul 20, 2022 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 — Eddiex045
– Eddiex045, Commented Jul 20, 2022 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 stackoverflow.com/a/32333755/202168 — Anentropic
– Anentropic, Commented Jul 20, 2022 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 — Eddiex045
– Eddiex045, Commented Jul 20, 2022 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
– furas, Commented Jul 20, 2022 at 14:54

Daniel Gonçalves · Accepted Answer · 2022-07-21 10:07:26Z

1

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!

answered Jul 21, 2022 at 10:07

Daniel Gonçalves

3322 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

Adding data into SQL with python won't work

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related