Guest User

SQLAlchemy query issue

a guest
Nov 11th, 2022
295
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 7.00 KB | Help | 0 0
  1. from traceback import print_exception
  2.  
  3. from config import SSH_HOST, SSH_KEY_PATH, SSH_KEY_PASS, SSH_USER, DB_NAME, DB_USER, DB_PASS
  4.  
  5. from sshtunnel import SSHTunnelForwarder
  6. from sqlalchemy.orm import declarative_base, relationship, sessionmaker
  7. from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, Float, Boolean
  8. from sqlalchemy.engine import URL
  9.  
  10.  
  11. Base = declarative_base()
  12.  
  13.  
  14. class DBContext():
  15.     def __init__(self) -> None:
  16.         self.Session = sessionmaker()
  17.  
  18.         self.server = SSHTunnelForwarder(
  19.             (SSH_HOST, 22),
  20.             ssh_username=SSH_USER,
  21.             ssh_pkey=SSH_KEY_PATH,
  22.             ssh_private_key_password=SSH_KEY_PASS,
  23.             remote_bind_address=('127.0.0.1', 3306),
  24.             host_pkey_directories=[],
  25.             set_keepalive=True
  26.         )
  27.  
  28.         self.Engine = None
  29.  
  30.     def connect(self) -> None:
  31.         if not self.server.is_alive:
  32.             self.server.start()
  33.             self.Engine = create_engine(
  34.                 URL.create(
  35.                     'mariadb+mariadbconnector',
  36.                     username=DB_USER,
  37.                     password=DB_PASS,
  38.                     host='127.0.0.1',
  39.                     port=self.server.local_bind_port,
  40.                     database=DB_NAME
  41.                 ),
  42.                 echo=True
  43.             )
  44.             self.Session.configure(bind=self.Engine)
  45.  
  46.     def close(self) -> None:
  47.         self.Session.close_all()
  48.         self.server.stop()
  49.  
  50.     def __enter__(self) -> sessionmaker:
  51.         """returns a new Session object"""
  52.         self.connect()
  53.         return self.Session()
  54.  
  55.     def __exit__(self, exception_type, exception_value, traceback) -> bool:
  56.         self.close()
  57.         if exception_type is not None:
  58.             print_exception(exception_type, exception_value, traceback)
  59.             return False
  60.  
  61.         return True
  62.  
  63.    
  64. class Article(Base):
  65.     __tablename__ = 'mk_articles'
  66.     id = Column(Integer, primary_key=True)
  67.     sku = Column(String)
  68.     mainsku = Column(String)
  69.     name = Column(String)
  70.     active = Column(Integer)
  71.     archived = Column(Integer)
  72.     designId = Column(Integer)
  73.     itemId = Column(Integer)
  74.     colourId = Column(Integer)
  75.     printColourId = Column(Integer)
  76.     sizeId = Column(Integer)
  77.     statusId = Column(Integer)
  78.     ean = Column(Integer)
  79.  
  80.     def __repr__(self) -> str:
  81.         return f'{self.sku}: {self.name}'
  82.  
  83.  
  84. def main() -> None:
  85.  
  86.     divider = '#' * 50 + '\n'
  87.  
  88.     with DBContext() as session:
  89.         for x in [1, 5, 10, 15, 20]:
  90.            
  91.             print(f"\n{divider}Querying {x} Articles\n{divider}\n", sep='')
  92.             result = session.query(Article).limit(x).all()
  93.             print(f'{len(result) = }')
  94.  
  95.     print('done')
  96.  
  97.    
  98. OUTPUT = """
  99.  
  100. ##################################################
  101. Querying 1 Articles
  102. ##################################################
  103.  
  104.  
  105. 2022-11-11 09:41:46,333 INFO sqlalchemy.engine.Engine SELECT DATABASE()
  106. 2022-11-11 09:41:46,334 INFO sqlalchemy.engine.Engine [raw sql] ()    
  107. 2022-11-11 09:41:46,346 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
  108. 2022-11-11 09:41:46,346 INFO sqlalchemy.engine.Engine [raw sql] ()    
  109. 2022-11-11 09:41:46,354 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
  110. 2022-11-11 09:41:46,354 INFO sqlalchemy.engine.Engine [raw sql] ()
  111. 2022-11-11 09:41:46,368 INFO sqlalchemy.engine.Engine BEGIN (implicit)
  112. 2022-11-11 09:41:46,373 INFO sqlalchemy.engine.Engine SELECT mk_articles.id AS mk_articles_id, mk_articles.sku AS mk_articles_sku, mk_articles.mainsku AS mk_articles_mainsku, mk_articles.name AS mk_articles_name, mk_articles.active AS mk_articles_active, mk_articles.archived AS mk_articles_archived, mk_articles.`designId` AS `mk_articles_designId`, mk_articles.`itemId` AS `mk_articles_itemId`, mk_articles.`colourId` AS `mk_articles_colourId`, mk_articles.`printColourId` AS `mk_articles_printColourId`, mk_articles.`sizeId` AS `mk_articles_sizeId`, mk_articles.`statusId` AS `mk_articles_statusId`, mk_articles.ean AS mk_articles_ean
  113. FROM mk_articles
  114. LIMIT ?
  115. 2022-11-11 09:41:46,374 INFO sqlalchemy.engine.Engine [generated in 0.00147s] (1,)
  116. len(result) = 1
  117.  
  118. ##################################################
  119. Querying 5 Articles
  120. ##################################################
  121.  
  122.  
  123. 2022-11-11 09:41:46,385 INFO sqlalchemy.engine.Engine SELECT mk_articles.id AS mk_articles_id, mk_articles.sku AS mk_articles_sku, mk_articles.mainsku AS mk_articles_mainsku, mk_articles.name AS mk_articles_name, mk_articles.active AS mk_articles_active, mk_articles.archived AS mk_articles_archived, mk_articles.`designId` AS `mk_articles_designId`, mk_articles.`itemId` AS `mk_articles_itemId`, mk_articles.`colourId` AS `mk_articles_colourId`, mk_articles.`printColourId` AS `mk_articles_printColourId`, mk_articles.`sizeId` AS `mk_articles_sizeId`, mk_articles.`statusId` AS `mk_articles_statusId`, mk_articles.ean AS mk_articles_ean
  124. FROM mk_articles
  125. LIMIT ?
  126. 2022-11-11 09:41:46,386 INFO sqlalchemy.engine.Engine [cached since 0.0131s ago] (5,)
  127. len(result) = 5
  128.  
  129. ##################################################
  130. Querying 10 Articles
  131. ##################################################
  132.  
  133.  
  134. 2022-11-11 09:41:46,396 INFO sqlalchemy.engine.Engine SELECT mk_articles.id AS mk_articles_id, mk_articles.sku AS mk_articles_sku, mk_articles.mainsku AS mk_articles_mainsku, mk_articles.name AS mk_articles_name, mk_articles.active AS mk_articles_active, mk_articles.archived AS mk_articles_archived, mk_articles.`designId` AS `mk_articles_designId`, mk_articles.`itemId` AS `mk_articles_itemId`, mk_articles.`colourId` AS `mk_articles_colourId`, mk_articles.`printColourId` AS `mk_articles_printColourId`, mk_articles.`sizeId` AS `mk_articles_sizeId`, mk_articles.`statusId` AS `mk_articles_statusId`, mk_articles.ean AS mk_articles_ean
  135. FROM mk_articles
  136. LIMIT ?
  137. 2022-11-11 09:41:46,397 INFO sqlalchemy.engine.Engine [cached since 0.0248s ago] (10,)
  138. len(result) = 10
  139.  
  140. ##################################################
  141. Querying 15 Articles
  142. ##################################################
  143.  
  144.  
  145. 2022-11-11 09:41:46,408 INFO sqlalchemy.engine.Engine SELECT mk_articles.id AS mk_articles_id, mk_articles.sku AS mk_articles_sku, mk_articles.mainsku AS mk_articles_mainsku, mk_articles.name AS mk_articles_name, mk_articles.active AS mk_articles_active, mk_articles.archived AS mk_articles_archived, mk_articles.`designId` AS `mk_articles_designId`, mk_articles.`itemId` AS `mk_articles_itemId`, mk_articles.`colourId` AS `mk_articles_colourId`, mk_articles.`printColourId` AS `mk_articles_printColourId`, mk_articles.`sizeId` AS `mk_articles_sizeId`, mk_articles.`statusId` AS `mk_articles_statusId`, mk_articles.ean AS mk_articles_ean
  146. FROM mk_articles
  147. LIMIT ?
  148. 2022-11-11 09:41:46,409 INFO sqlalchemy.engine.Engine [cached since 0.03681s ago] (15,)
  149. """
  150.  
  151. # The Script just stops and freezes at this point, no values are retrieved and no further Output is generated.
  152. # This happens anytime a query should return > 10 rows.
  153. # What am I doing wrong or missing here?
Advertisement
Add Comment
Please, Sign In to add comment