How to make query with textual field to MySQL in Python?

Question

I am trying to execute the query input to the MySQL table, where fields has type LONGTEXT: The part of code:

def read_csv_audio():
    with open(audio_file) as csvfile:
        reader = csv.DictReader(csvfile)
        for row in reader:
            new = []
            new.append(str(row['audio_url']))
            new.append(str(row['google text']))
            new.append(str(row['nuance text']))
            new.append(str(row['checked text']))
            auds.append(new)
            print(row['audio_url'], row['google text'], row['nuance text'], row['checked text'])
    return  auds;

read_csv_audio();

i = 0;
for row in auds:
    add_auds = ("INSERT INTO InnoDB.auds_text (audsid, audio_url, google, nuance, checked) VALUES ("+str(i)+", '"+str(row[0])+"', '"+str(row[1])+"', '"+str(row[2])+", '"+str(row[3])+"') ")
    print(add_auds) # beneath the example1 of print
    cursor.execute(add_auds)
    i += 1

Example1: INSERT INTO InnoDB.auds_text (audsid, audio_url, google, nuance, checked) VALUES (0, '36649/crawler377116_1_0_20.wav', 'you have reached the New York headquarters of Mediacom if you know the extension of the person you wish to reach you made out at any time to dial by name press 8 for the company directory for immediate assistance press 0 for the operator when you are finished hang up or press pound for more options', 'you have reached the New York headquarters of Mediacom if you know the extension of the person you wish to reach you made out at any time to dial by name press eight for the company directory for immediate assistance press zero for the operator record your message at the tone when you are finished hang out or press pound for more options, 'you have reached the New York headquarters of Mediacom if you know the extension of the person you wish to reach you may dial it at any time to dial by name press eight for the company directory for immediate assistance press zero for the operator record your message at the tone when you are finished hang up or press pound for more options beep')

But, I have an error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'you have 
reached the New York headquarters of Mediacom if you know
the extension' at line 1

How can I correct my mistake for working with text?

n00dle · Accepted Answer · 2017-07-21 12:33:27Z

In the generated query, you're missing a closing single-quote here:

...pound for more options, 'you have...

I think that would be the end of your penultimate value.

You need to change this line:

   add_auds = ("INSERT INTO InnoDB.auds_text (audsid, audio_url, google, nuance, checked) VALUES ("+str(i)+", '"+str(row[0])+"', '"+str(row[1])+"', '"+str(row[2])+", '"+str(row[3])+"') ")

To this:

    add_auds = ("INSERT INTO InnoDB.auds_text (audsid, audio_url, google, nuance, checked) VALUES ("+str(i)+", '"+str(row[0])+"', '"+str(row[1])+"', '"+str(row[2])+"', '"+str(row[3])+"') ")

(That's an additional single quote near str(row[2])).

EDIT:

I would suggest you change your code to use "prepared statements". Not only would they prevent this kind of error cropping up, they're generally just good practice as you'll get sanitisation and type-matching thrown in for free (which adds to security and reliability).

For more info, you might want to start with this question: Using prepared statements with mysql in python

Collectives™ on Stack Overflow

How to make query with textual field to MySQL in Python?

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