Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- from traceback import print_exception
- from config import SSH_HOST, SSH_KEY_PATH, SSH_KEY_PASS, SSH_USER, DB_NAME, DB_USER, DB_PASS
- from sshtunnel import SSHTunnelForwarder
- from sqlalchemy.orm import declarative_base, relationship, sessionmaker
- from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, Float, Boolean
- from sqlalchemy.engine import URL
- Base = declarative_base()
- class DBContext():
- def __init__(self) -> None:
- self.Session = sessionmaker()
- self.server = SSHTunnelForwarder(
- (SSH_HOST, 22),
- ssh_username=SSH_USER,
- ssh_pkey=SSH_KEY_PATH,
- ssh_private_key_password=SSH_KEY_PASS,
- remote_bind_address=('127.0.0.1', 3306),
- host_pkey_directories=[],
- set_keepalive=True
- )
- self.Engine = None
- def connect(self) -> None:
- if not self.server.is_alive:
- self.server.start()
- self.Engine = create_engine(
- URL.create(
- 'mariadb+mariadbconnector',
- username=DB_USER,
- password=DB_PASS,
- host='127.0.0.1',
- port=self.server.local_bind_port,
- database=DB_NAME
- ),
- echo=True
- )
- self.Session.configure(bind=self.Engine)
- def close(self) -> None:
- self.Session.close_all()
- self.server.stop()
- def __enter__(self) -> sessionmaker:
- """returns a new Session object"""
- self.connect()
- return self.Session()
- def __exit__(self, exception_type, exception_value, traceback) -> bool:
- self.close()
- if exception_type is not None:
- print_exception(exception_type, exception_value, traceback)
- return False
- return True
- class Article(Base):
- __tablename__ = 'mk_articles'
- id = Column(Integer, primary_key=True)
- sku = Column(String)
- mainsku = Column(String)
- name = Column(String)
- active = Column(Integer)
- archived = Column(Integer)
- designId = Column(Integer)
- itemId = Column(Integer)
- colourId = Column(Integer)
- printColourId = Column(Integer)
- sizeId = Column(Integer)
- statusId = Column(Integer)
- ean = Column(Integer)
- def __repr__(self) -> str:
- return f'{self.sku}: {self.name}'
- def main() -> None:
- divider = '#' * 50 + '\n'
- with DBContext() as session:
- for x in [1, 5, 10, 15, 20]:
- print(f"\n{divider}Querying {x} Articles\n{divider}\n", sep='')
- result = session.query(Article).limit(x).all()
- print(f'{len(result) = }')
- print('done')
- OUTPUT = """
- ##################################################
- Querying 1 Articles
- ##################################################
- 2022-11-11 09:41:46,333 INFO sqlalchemy.engine.Engine SELECT DATABASE()
- 2022-11-11 09:41:46,334 INFO sqlalchemy.engine.Engine [raw sql] ()
- 2022-11-11 09:41:46,346 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
- 2022-11-11 09:41:46,346 INFO sqlalchemy.engine.Engine [raw sql] ()
- 2022-11-11 09:41:46,354 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
- 2022-11-11 09:41:46,354 INFO sqlalchemy.engine.Engine [raw sql] ()
- 2022-11-11 09:41:46,368 INFO sqlalchemy.engine.Engine BEGIN (implicit)
- 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
- FROM mk_articles
- LIMIT ?
- 2022-11-11 09:41:46,374 INFO sqlalchemy.engine.Engine [generated in 0.00147s] (1,)
- len(result) = 1
- ##################################################
- Querying 5 Articles
- ##################################################
- 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
- FROM mk_articles
- LIMIT ?
- 2022-11-11 09:41:46,386 INFO sqlalchemy.engine.Engine [cached since 0.0131s ago] (5,)
- len(result) = 5
- ##################################################
- Querying 10 Articles
- ##################################################
- 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
- FROM mk_articles
- LIMIT ?
- 2022-11-11 09:41:46,397 INFO sqlalchemy.engine.Engine [cached since 0.0248s ago] (10,)
- len(result) = 10
- ##################################################
- Querying 15 Articles
- ##################################################
- 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
- FROM mk_articles
- LIMIT ?
- 2022-11-11 09:41:46,409 INFO sqlalchemy.engine.Engine [cached since 0.03681s ago] (15,)
- """
- # The Script just stops and freezes at this point, no values are retrieved and no further Output is generated.
- # This happens anytime a query should return > 10 rows.
- # What am I doing wrong or missing here?
Advertisement
Add Comment
Please, Sign In to add comment