Can only concatenate str (not "float") to str error when I triying to create an SQL insert with Python

Question

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 12-Nov-21 1:03am

Raquel Matos

Updated 12-Nov-21 1:08am

Add a Solution

1 solution

Add a Solution

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

OriginalGriff · Answer 1 · 2021-11-12T01:08:00

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?

Can only concatenate str (not "float") to str error when I triying to create an SQL insert with Python

1 solution

Solution 1

Add your solution here

Preview 0

Can only concatenate str (not "float") to str error when I triying to create an SQL insert with Python

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us