Python-сообщество

#!/usr/bin/python2.6
# -*- coding: utf-8 -*-

import urllib2
import html5lib
from html5lib import treebuilders
from BeautifulSoup import BeautifulSoup
import os
import re
#from BeautifulSoup import BeautifulSoup
#import sys
import codecs
import MySQLdb
#sys.setdefaultencoding("UTF8")
#streamWriter = codecs.lookup('utf-8')[-1]
#sys.stdout = streamWriter(sys.stdout)

#sys.setdefaultencoding()
parser = html5lib.HTMLParser(tree=treebuilders.getTreeBuilder("beautifulsoup"))
soup = parser.parse(urllib2.urlopen("http://a-el.ru/catalog.htm").read())

#soup = parser.parse(unicode(open("catalog.htm").read(), 'cp1251'))
table_soup = soup.table.tbody.tr.td.table.tbody.tr.nextSibling.nextSibling.tbody
#print soup.contents[0].name


trs = table_soup.findAll(name = "tr")
th_find_re = re.compile ('\<th.*\>(?P<header>.*?)\</th\>')
td_find_re = re.compile ('\<td.*\>(?P<header>.*?)\</td\>')
tr_re = re.compile('\<tr\>(?P<header>.*)\</tr\>')
i=2
j=4
category_id = 100
product_id = 100
#db= MySQLdb.connect(host='domen.ru', user='root', passwd='password', db='test2')
db= MySQLdb.connect(host='ip_adress', user='root', passwd='password', db='test4')
db.set_character_set('utf8')
cursor= db.cursor()
#cursor.execute("SELECT * FROM `jos_vm_product`")
for tr in trs:
	#if trs[i]
	#td_split = td_split_re.split(str(trs[i]))
	#for split in td_split:
	#	print split
	if th_find_re.search(str(trs[i])) != None:
#		print "***************"
#		print th_find_re.search(str(trs[i])).group('header')
#		print "***************"
		category_name = th_find_re.search(str(trs[i])).group('header')
		category_id=category_id+1
		#Создаем новую категорию
		print category_name + "  " + str(category_id)
		cursor.execute("INSERT INTO `jos_vm_category` (`category_id`, `vendor_id`, `category_name`, `category_description`, `category_thumb_image`, `category_full_image`, `category_publish`, `cdate`, `mdate`, `category_browsepage`, `products_per_row`, `category_flypage`, `list_order`) VALUES("+str(category_id)+", 1, '"+category_name+"', '"+category_name+"', '', '', 'Y', 1258969273, 1258969293, 'managed', 1, 'flypage.tpl', 1)")
		cursor.execute("INSERT INTO `jos_vm_category_xref` (`category_parent_id`, `category_child_id`, `category_list`) VALUES(0, +"+str(category_id)+", NULL)")
	else:
#		other_soup =  parser.parse(unicode(str(trs[i]),'utf-8'))
		other_soup = BeautifulSoup(''.join(unicode(str(trs[i]),'utf-8')))
		product = ""
		small_img = ""
		big_img = ""
		try:
			print other_soup.tr.td.a.img['alt']
			print other_soup.tr.td.a.img['src']
			print other_soup.tr.td.a['href']
			product = str(other_soup.tr.td.a.img['alt']) #Модель продукта например CT-100
			small_img = str(other_soup.tr.td.a.img['src']) #картинка предпросмотра
			big_img = str(other_soup.tr.td.a['href']) #большая картинка
		except:
			product = "" #Левая часть продукта
			small_img = "" #картинка предпросмотра
			big_img = "" #большая картинка
#			print "Нет изображения"
		try:
			print str(other_soup.tr.td.nextSibling.nextSibling.string).encode("utf-8")
#			product = str(other_soup.tr.td.nextSibling.nextSibling.string) + " - " + product #правая часть продукта
			small_text = str(other_soup.tr.td.nextSibling.nextSibling.string)  # Краткое описание продукта
		except:
			break
		if other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string != None:
			text = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string)
			print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
			
		else:
			print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling).encode("utf-8")
			text = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling)
		
		print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
		print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
		vendor = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string) #Производитель(нужно доделать запрос
		price = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string) # Цена
		product_id = product_id + 1
		#Добавление продукта
		cursor.execute("INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES ("+str(product_id)+", 1, 0, '"+str(product_id)+"', '"+small_text+"','"+text+"', 'http://a-el.ru/"+small_img+"', 'http://a-el.ru/"+big_img+"', 'Y', 0.0000, 'шт.', 0.0000, 0.0000, 0.0000, 'шт.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, '"+product+"', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')")
		#Добавление продукта в категорию созданную ранее
		cursor.execute("INSERT INTO `jos_vm_product_category_xref` (`category_id` ,`product_id` ,`product_list`) VALUES ('"+str(category_id)+"', '"+str(product_id)+"', '1')") 
		#Добавление цены для текущего продукта
		cursor.execute("INSERT INTO `jos_vm_product_price` (`product_price_id`, `product_id`, `product_price`, `product_currency`, `product_price_vdate`, `product_price_edate`, `cdate`, `mdate`, `shopper_group_id`, `price_quantity_start`, `price_quantity_end`) VALUES("+str(product_id)+", "+str(product_id)+", "+price+", 'RUB', 0, 0, 1258969371, 1258969741, 5, 0, 0)")

#		print type(trs[i])
#		print tr_re.search(str(trs[i]))
#		print td_find_re.findall(str(trs[i]))
#	print str(trs[i])
	i = i + 1
#	if i == 100:
#		break
#INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES (3, 1, 0, '3', 'краткое описание','описание', 'http://a-el.ru/img/al_b16_s.gif', 'http://www.a-el.ru/img/al_b16.jpg', 'Y', 0.0000, 'кг.', 0.0000, 0.0000, 0.0000, 'см.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, 'AL-B16', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0'),
#INSERT INTO `jos_vm_category` (`category_id`, `vendor_id`, `category_name`, `category_description`, `category_thumb_image`, `category_full_image`, `category_publish`, `cdate`, `mdate`, `category_browsepage`, `products_per_row`, `category_flypage`, `list_order`) VALUES(1, 1, 'Безопасность', '<p>Индивидуальные системы безопасности, беспроводные бэби-мониторы, охранное видеонаблюдение, беспроводные охранные системы, персональные металлодетекторы, сирены, автоаксессуары.</p>', 'resized/_________________4b0a58c685ed7_90x90.gif', 'http://www.a-el.ru/img/secure.gif', 'Y', 1258969273, 1258969293, 'managed', 1, 'flypage.tpl', 1),

#for tag in table_soup:
#	if tag.contents[0].name == th:
	#print str(tag.contents[0].name)
#	next = table_soup.findNextSiblings('tr')
#	if next.contents[0].name == th:
#		print next.contents[0]

Traceback (most recent call last):
  File "./examles.py", line 93, in <module>
    cursor.execute("INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES ("+str(product_id)+", 1, 0, '"+str(product_id)+"', '"+small_text+"','"+text+"', 'http://a-el.ru/"+small_img+"', 'http://a-el.ru/"+big_img+"', 'Y', 0.0000, 'шт.', 0.0000, 0.0000, 0.0000, 'шт.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, '"+product+"', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')")
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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 '\xd0\x96\xd0\x9a-\xd0\xbc\xd0\xbe\xd0\xbd\xd0\xb8\xd1\x82\xd0\xbe\xd1\x80 \xd1\x81\xd0\xbe \xd0\xb2\xd1\x81\xd1\x82\xd1\x80\xd0\xbe\xd0\xb5\xd0\xbd\xd0\xbd\xd1\x8b\xd0\xbc \xd0\xba\xd0\xb2\xd0\xb0\xd0\xb4\xd1\x80\xd0\xb0\xd1\x82\xd0\xbe\xd1\x80\xd0\xbe\xd0\xbc \xd0\xb8 \xd1\x86\xd0\xb8\xd1\x84\xd1\x80' at line 1")

MySQLdb.escape_string()

cursor.execute("INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES ("+str(product_id)+", 1, 0, '"+str(product_id)+"', '"+small_text+"','"+text+"', 'http://a-el.ru/"+small_img+"', 'http://a-el.ru/"+big_img+"', 'Y', 0.0000, 'шт.', 0.0000, 0.0000, 0.0000, 'шт.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, '"+product+"', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')")

sql = "INSERT INTO sometable (col1, col2) VALUES (%s, %s)"
cursor.execute(sql, (MySQLdb.escape_string(val1),  MySQLdb.escape_string(val2))