Python insert html into mysql?

Question

how to insert html into mysql by Python?

sql_data = "INSERT INTO `index_table`.`tab_data` (`id`, `content`, `copyfrom`) VALUES ( NULL, '%s','0')"

The html code I want to insert was like this

<p><img src='http://img.domain.com/topic/image/20150731/db9475df317519db7622c25f23a42b0130700277.jpg' /></p>

pice of my python code

content = "<p><img src='http://img.domain.com/topic/image/20150731/db9475df317519db7622c25f23a42b0130700277.jpg' /></p>" 
tx.execute(sql_data % (tx.commit(str(content))) )

But it doesn't work.

here is more python code

class MysqlPipeline(object):  
def __init__(self):                              
    self.dbpool = adbapi.ConnectionPool('MySQLdb', 
            host = 'localhost', 
            db = 'index',  
            user = 'name',  
            passwd = 'pass',  
            cursorclass = MySQLdb.cursors.DictCursor,  
            charset = 'utf8',  
            use_unicode = False
    )  

# pipeline dafault function                      
def process_item(self, item, spider):  
    query = self.dbpool.runInteraction(self._conditional_insert, item)  
    return item  


# insert the data to databases                    
def _conditional_insert(self, tx, item): 
    now_data = str(time.strftime('%Y%m%d',time.localtime(time.time())))  

    sql_data = ('INSERT INTO `index`.`news_data` (`id`, `content`, `maxcharperpage`, `allow_comment`) VALUES ( LAST_INSERT_ID(), "%s", "10000", "1")')
    try:
        thumbname = item['images'][0]['path']
        title = str(item['title'][0]) 
        picid = item['catid']
        image_locals = "http://img.domain.com/topic/image/"+now_data+"/"+thumbname
        #print maxid
        localimgs = list()
        for img in item['images']:
            imgsrc = img['path']
            #localimg = str("<p><img src='http://img.domain.com/topic/image/"+now_data+"/"+imgsrc+"' /></p>").replace('full/','')
            localimg = imgsrc
            localimgs.append(localimg)
        neirong = ''.join(localimgs)# neirong is a str object, html code.
        print neirong

        tx.execute(sql_data , [neirong]) # Is this line right? but it doesn't work

    except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
    return item

May be more information you can find out

falsetru · Accepted Answer · 2015-07-31 08:46:36Z

2

Assuming tx is a cursor object, pass content as a parameter instead of building a sql yourself. Otherwise, you should consider quoting yourself. (to prevent SQL injection)

sql_data = (
    "INSERT INTO `index_table`.`tab_data` (`id`, `content`, `copyfrom`) "
    "VALUES (NULL, %s, '0')")
content = "... html ..."
tx.execute(sql_data, [content])
# connection_object.commit()

edited Jul 31, 2015 at 8:46

answered Jul 31, 2015 at 8:19

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

8 Comments

jianbing Ma Over a year ago

tx is a cursor object,not a connection

falsetru Over a year ago

@jianbingMa, I removed a comment accordingly.

jianbing Ma Over a year ago

Error 1064: 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 '><img src=\'img.domain.com/topic/image/meinv/20150731/db9475df317519db7' at line 1 ;----------error like this

falsetru Over a year ago

@jianbingMa, Did you pass parameter as show in the answer code? tx.execute(sql_data, [content]) ?

jianbing Ma Over a year ago

I have try , but it still doesn't work. I add more python code in question, please have a look

|

Collectives™ on Stack Overflow

Python insert html into mysql?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related