python:sqlalchemy.exc.operationalerror:< unprintable operationalerror object>

mccptt67  于 2021-06-24  发布在  Mysql
关注(0)|答案(0)|浏览(471)

我编写了下面的代码来创建一个网络爬虫（用scrapy详细说明），我想把这些数据放在一个数据库中，一个是mysql。为此，我使用了管道文件并进行了以下配置：
管道.py：

  1. class ScrapySpiderPipeline(object):
  2.    def __init__(self):
  4.      engine = db_connect()
  5.      create_table(engine)
  6.      self.Session = sessionmaker(bind=engine)
  8.   def process_item(self, item, spider):
  10.      session = self.Session()
  11.      quotedb = QuoteDB()
  12.      quotedb.Titulo = item["Titulo"]
  13.      quotedb.Tipo_Negocio = item["Tipo_Negocio"]
  14.      quotedb.Preco = item["Preco"]
  15.      quotedb.Tipo_Imovel = item["Tipo_Imovel"]
  17.   try:
  18.      session.add(quotedb)
  19.      session.commit()
  20.   except:
  21.      session.rollback()
  22.      raise
  23.   finally:
  24.      session.close()
  26.   return item

型号.py：

  1. from scrapy.utils.project import get_project_settings
  3. DeclarativeBase = declarative_base()
  5. def db_connect():
  6. return create_engine(get_project_settings().get("CONNECTION_STRING"))
  8. def create_table(engine):
  9.    DeclarativeBase.metadata.create_all(engine)
  11. class QuoteDB(DeclarativeBase):
  12.    __tablename__ = "imovel_table"
  14.    id = Column(Integer, primary_key=True)
  15.    Titulo = Column('Titulo', VARCHAR(200))
  16.    Tipo_Negocio = Column('Tipo_Negocio', String(100))
  17.    Preco = Column('Preco', Text())
  18.    Tipo_Imovel = Column('Tipo_Imovel', Text())
  20. print(QuoteDB.__table__)

项目.py：

  1. class QuoteItem(scrapy.Item):
  2.   Titulo = scrapy.Field()
  3.   Tipo_Negocio = scrapy.Field()
  4.   Preco = scrapy.Field()
  5.   Tipo_Imovel = scrapy.Field()

设置.py：

  1. ITEM_PIPELINES = {
  2.    'novo.pipelines.ScrapySpiderPipeline' : 300,
  3. }
  5. CONNECTION_STRING = "{drivername}://{user}:{passwd}@{host}:{port}/{db_name}?charset=utf8".format(
  6.    drivername="mysql",
  7.    user="root",
  8.    passwd="",
  9.    host="localhost",
  10.    port="3306",
  11.    db_name="projeto",
  12. )

爬虫.py：

  1. class SapoSpider(scrapy.Spider):
  2.   name = "imoveis"
  3.   allowed_domains = ["maisconsultores.pt"]
  4.   start_urls = ["https://www.maisconsultores.pt/properties?page=%d&s=eedce" % i for i in range(23)]
  6. def parse(self,response):
  7.    subpage_links = []
  9.    for i in response.css('div.item.col-sm-4'):
  10.        youritem = {
  11.        'Titulo':i.css('div[class=image] h3::text').extract(),
  12.        'Tipo_Negocio':i.css('div.price::text').re('[^\t\n\r\a]+'),
  13.        'Preco':i.css('div.price span::text').extract(),
  14.        }
  16.     subpage_link = i.css('div[class=image] a::attr(href)').extract_first()
  17.     full_url = response.urljoin(subpage_link)
  18.     yield scrapy.Request(full_url, callback=self.parse_subpage, meta={'item':youritem})
  20. def parse_subpage(self,response):
  21.     youritem = response.meta.get('item')
  22.     youritem['Tipo_Imovel'] = response.xpath('//ul[@class="amenities"]//li[1]/text()').extract()
  23.     yield youritem

有人能告诉我错误在哪里吗？我在里面呆了很长时间，却找不到它。你知道吗？如果有人能帮我，我会很感激的！
回溯：

  1. 2018-06-27 17:17:04 [scrapy.core.engine] DEBUG: Crawled (200) <GET https://www.maisconsultores.pt/1651/apartamento-t2-em-linda-a-velha> (referer: https://www.maisconsultores.pt/properties?page=18&s=eedce)
  2. 2018-06-27 17:17:04 [scrapy.core.scraper] ERROR: Error processing {'Titulo': ['Apartamento T2 em Linda-a-Velha '], 'Tipo_Negocio': ['                                            Venda', '                                            ', '                                        '], 'Preco': ['240 000€', 'Novo Preço!'], 'Localizacao': [' Algés (Algés)'], 'Tipo_Imovel': [' Apartamento'], 'Condicao': [' Usado'], 'Numero_Divisoes': [' 3'], 'Numero_Quartos': [' 2'], 'Numero_Casas_Banho': [' 1'], 'Certificado_Energetico': ' Avaliação em Curso', 'Ano_Construcao': ' 1980', 'Area_Util': '                                                ', 'Area_Bruta': '                                                    ', 'Piso': ['                                                                                                            2º andar', '                                                                                                    ']}
  4. Traceback (most recent call last):
  5.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
  6.     context)
  7.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
  8.     cursor.execute(statement, parameters)
  9.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
  10.     self.errorhandler(self, exc, value)
  11.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\connections.py", line 50, in defaulterrorhandler
  12.     raise errorvalue
  13.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
  14.     res = self._query(query)
  15.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
  16.     rowcount = self._do_query(q)
  17.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 374, in _do_query
  18.     db.query(q)
  19.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\connections.py", line 277, in query
  20.     _mysql.connection.query(self, query)
  21. _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')

上述异常是以下异常的直接原因：

  1. Traceback (most recent call last):
  2.   File "C:\Users\joani\Anaconda3\lib\site-packages\twisted\internet\defer.py", line 653, in _runCallbacks
  3.     current.result = callback(current.result, *args,**kw)
  4.   File "C:\Users\joani\Desktop\outra_tentativa\novo\novo\pipelines.py", line 111, in process_item
  5.     session.commit()
  6.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 943, in commit
  7.     self.transaction.commit()
  8.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 467, in commit
  9.     self._prepare_impl()
  10.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 447, in _prepare_impl
  11.     self.session.flush()
  12.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 2254, in flush
  13.     self._flush(objects)
  14.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 2380, in _flush
  15.     transaction.rollback(_capture_exception=True)
  16.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
  17.     compat.reraise(exc_type, exc_value, exc_tb)
  18.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
  19.     raise value
  20.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 2344, in _flush
  21.     flush_context.execute()
  22.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 391, in execute
  23.     rec.execute(self)
  24.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 556, in execute
  25.     uow
  26.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\persistence.py", line 181, in save_obj
  27.     mapper, table, insert)
  28.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\orm\persistence.py", line 866, in _emit_insert_statements
  29.     execute(statement, params)
  30.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
  31.     return meth(self, multiparams, params)
  32.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
  33.     return connection._execute_clauseelement(self, multiparams, params)
  34.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
  35.     compiled_sql, distilled_params
  36.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
  37.     context)
  38.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
  39.     exc_info
  40.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
  41.     reraise(type(exception), exception, tb=exc_tb, cause=cause)
  42.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
  43.     raise value.with_traceback(tb)
  44.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
  45.     context)
  46.   File "C:\Users\joani\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
  47.     cursor.execute(statement, parameters)
  48.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
  49.     self.errorhandler(self, exc, value)
  50.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\connections.py", line 50, in defaulterrorhandler
  51.     raise errorvalue
  52.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
  53.     res = self._query(query)
  54.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
  55.     rowcount = self._do_query(q)
  56.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 374, in _do_query
  57.     db.query(q)
  58.   File "C:\Users\joani\Anaconda3\lib\site-packages\MySQLdb\connections.py", line 277, in query
  59.     _mysql.connection.query(self, query)
  60. sqlalchemy.exc.OperationalError: <unprintable OperationalError object>
mysqlpythonPipelineweb-crawlerscrapy

暂无答案！

目前还没有任何答案，快来回答吧！

我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com