Click here to Skip to main content
15,398,850 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to generate a sql script in python but when I try to run the code the following error appears can only concatenate str (not "float") to str.
how to solve this problem


Python
with open ("output.txt", "w"):
            for index, row in df.iterrows():
                print('INSERT INTO ' + TABLE_NAME + '(\'COD_TXT\' , \'DES_TXT\' ,\'DES_OBS_TXT\') VALUES (', row['COD_TXT'],',\''+row['DES_TXT']+'\'',',\''+row['DES_OBS_TXT']+'\');', file=open("output.txt", "a"))

I'm entering data like

"COD_TXT";"DES_TXT";"DES_OBS_TXT"
"1000";"teste de texto";"Migração.xml"
"1001";"esse é um novo texto";"Migração.xml"
"1002";"Selecione uma das opções";"Migração.xml"


and I would like the data to come out this way


SQL
INSERT INTO TEXTO_FRONTEND('COD_TXT' , 'DES_TXT' ,'DES_OBS_TXT') VALUES (  1000,'teste de texto','Migração.xml');
    INSERT INTO TEXTO_FRONTEND('COD_TXT' , 'DES_TXT' ,'DES_OBS_TXT') VALUES ( 1001,'esse é um novo texto','Migração.xml');
    INSERT INTO TEXTO_FRONTEND('COD_TXT' , 'DES_TXT' ,'DES_OBS_TXT') VALUES (1002,'Selecione uma das opções','Migração.xml');


What I have tried:

with open ("output.txt", "w"):
            for index, row in df.iterrows():
                print('INSERT INTO ' + TABLE_NAME + '(\'COD_TXT\' , \'DES_TXT\' ,\'DES_OBS_TXT\') VALUES (', row['COD_TXT'],',\''+row['DES_TXT']+'\'',',\''+row['DES_OBS_TXT']+'\');', file=open("output.txt", "a"))
Posted
Updated 12-Nov-21 1:08am

1 solution

That's because what you are doing is wrong, and dangerous. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900