Escape string Python for MySQL

Question

I use Python and MySQLdb to download web pages and store them into database  The problem I have is that I can t save complicated strings in the database because they are not properly escaped   Is there a function in Python that I can use to escape a string for MySQL  I tried with      triple simple quotes  and      but it didn t work  I know that PHP has mysql escape string    is something similar in Python   Thanks

User · Answer

install sqlescapy package   pip install sqlescapy   then you can escape variables in you raw query  from sqlescapy import sqlescape  query           SELECT   FROM  bar table  WHERE id   s        sqlescape user input

User · Answer

Use sqlalchemy s text function to remove the interpretation of special characters   Note the use of the function text  your insert statement   below   What it does is communicate to sqlalchemy that all of the questionmarks and percent signs in the passed in string should be considered as literals     import sqlalchemy from sqlalchemy import text from sqlalchemy orm import sessionmaker from datetime import datetime import re  engine   sqlalchemy create engine  mysql mysqlconnector    s  s  s  s           your username    your password    your hostname mysql server 3306         your database         pool size 3  pool recycle 3600   conn   engine connect    myfile   open  access2 log    r   lines   myfile readlines    penguins      for line in lines     elements   re split   s    line      print  item       elements 0     linedate   datetime fromtimestamp float elements 0       mydate   linedate strftime   Y- m- d  H  M  S  f       penguins append text        insert into your table  foobar  values                for penguin in penguins      print penguin     conn execute penguin   conn close

User · Answer

One other way to work around this is using something like this when using mysqlclient in python   suppose the data you want to enter is like this  lt ol gt  lt li gt  lt strong style  background-color  rgb 255  255  0    gt Saurav  s List lt  strong gt  lt  li gt  lt  ol gt   It contains both double qoute and single quote   You can use the following method to escape the quotes      statement       Update chats set html          format html string replace                Note  three   characters are needed to escape the single quote which is there in unformatted python string

User · Answer

The MySQLdb library will actually do this for you  if you use their implementations to build an SQL query string instead of trying to build your own   Don t do   sql    INSERT INTO TABLE A  COL A COL B  VALUES   s   s      val1  val2  cursor execute sql    Do   sql    INSERT INTO TABLE A  COL A COL B  VALUES   s   s   cursor execute sql   val1  val2

User · Answer

a  applies ascii   and hence escapes non-ASCII characters like quotes and even emoticons  Here is an example  cursor execute  UPDATE skcript set author    a   Count    d   where url    s    format authors  leng url     Python3 docs

User · Answer

conn escape string     See MySQL C API function mapping  http   mysql-python sourceforge net MySQLdb html

User · Answer

gt  gt  gt  import MySQLdb  gt  gt  gt  example   r   I don t like  special  chars                 gt  gt  gt  example  I don  t like  special  chars  xc2 xaf     xe3 x83 x84    xc2 xaf   gt  gt  gt  MySQLdb escape string example   I don    t like    special    chars  xc2 xaf       xe3 x83 x84    xc2 xaf

[python] Escape string Python for MySQL

Examples related to python

Examples related to mysql

Examples related to escaping