CODEWITHSUNDEEP
pythonsql
Eddiex045
Eddiex045

Reputation: 62

Adding data into SQL with python won't work

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?

Upvotes: 0

Views: 94

Answers (1)

Daniel Gon&#231;alves
Daniel Gon&#231;alves

Reputation: 322

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!

Upvotes: 1

Related Questions