python - need help inserting text files with open() into mysql

Question

Can someone help me out with the mysql connection statement to instert a textfile into a mysql table (field type is long blob)?

For example:

cursor.execute("insert into mytable (file_contents) values ('"+open(filename,"r").read()+"')")

Obviously that's not very practical, can someone post a better way to do this?

eumiro · Accepted Answer · 2010-10-13 07:10:42Z

1

It is dangerous to append content of a file directly into an SQL query, because of special characters (quotes!) or malicious SQL commands.

Try this:

with open(filename,"r") as infile:
    cursor.execute("insert into mytable (file_contents) values (%s)", (infile.read(), ))

answered Oct 13, 2010 at 7:10

eumiro

214k36 gold badges307 silver badges264 bronze badges

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

1 Comment

jsalonen Over a year ago

Exactly. Note that the data from infile is passed as second argument -- the string interpolation is handled by cursor.execute which also makes sure that the string parameters are correctly escaped.

Collectives™ on Stack Overflow

python - need help inserting text files with open() into mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related