o *gw@sddlZddlZddlZddlmZmZmZddlmZddl m Z ddl m Z ddl mZddlmZe ZGdd d ZeZed krQd d ZeedSdS) N)deleteand_update)select)declarative_base)IntegrityError)text)MySQLConnectorc @seZdZdZddZejddZddZdd Z d d Z d%de de de de de de f ddZddZd&de de fddZd'de fddZde defdd Zd!d"Zd&d#d$Zd S)(MySQLDBz8Class to manage MySQL database connection and operationscCs t|_dSN)r connectorselfr*/var/www/html/XCapMarket/utils/mysql_db.py__init__s zMySQLDB.__init__c CsL|j4IdH}|VWdIdHdS1IdHswYdS)zReturn a session properlyN)r get_session)rsessionrrrrs.zMySQLDB.get_sessionc s\|jj4IdH}|tjjIdHWdIdHdS1IdHs'wYdS)z)Initialize the database and create tablesN)r enginebeginrun_syncBasemetadata create_all)rconnrrrinit_db s.zMySQLDB.init_dbcs|jIdHdS)zClose the database connectionN)r closer rrrr%sz MySQLDB.closecs|IdHdS)z?Ensure the database connection is closed on object destruction.N)rr rrr __aexit___-szMySQLDB.__aexit___NFfiltersorder_byoffsetgroup_bylimitfirstc s|4IdH\}t|} |r|D]} | | } q|r&|D]} | | } q|r-| |} |r4| |} |r;| |} || IdH} |rU|  WdIdHS|  WdIdHS1IdHskwYdS)aV Execute a query with dynamic filters, ordering, and limit. :param model: The SQLAlchemy model to query. :param filters: A list of filter conditions. :param order_by: A list of order_by conditions. :param limit: An optional limit for the number of results. :return: The query results. N) rrwhererr"r r!executescalarsr#all) rmodelrrr r!r"r#rquery conditionorderresultrrr execute_query2s*       0zMySQLDB.execute_queryc s|4IdHF}z!|||IdH||IdH|WWdIdHSty>}zt|d|jd}~wtyN}zt|dd}~ww1IdHsUwYdS)z{ Insert a new record into the database. :param instance: The SQLAlchemy model instance to insert. NDuplicate entry detected.z%Failed to insert buyer intake record.)raddcommitrefreshrcode ValueError)rinstancererrrexecute_insert`s   zMySQLDB.execute_insert record_idc s|4IdH4}|rt|}t||}n t||j|k}||IdH|IdH|WdIdHS1IdHsCwYdS)z Delete a record from the database. :param model: The SQLAlchemy model class to delete from. :param record_id: The ID of the record to delete. N)rrrr$idr%r0)rr(r7rrcombined_filtersr)rrr delete_recordqs0zMySQLDB.delete_recordr8c sv|4IdH%}t|t|||k}||IdH}|WdIdHS1IdHs4wYdS)z1 Get a record from the database. N)rrr$getattrr%r&r#)rr(r7primary_key_columnrr)r,rrr get_records  0zMySQLDB.get_recordvaluesc s|4IdHH}t|}|rt|}||}|jdi|}||IdH|IdHt|t|}||IdH}| WdIdHS1IdHsWwYdS)a Update a record in the database. :param model: The SQLAlchemy model class to update. :param filters: A list of filter conditions to identify the records to update. :param values: A dictionary of column-value pairs to set in the update. Nr) rrrr$r>r%r0rr&r') rr(rr>rr)r9 select_queryr,rrr update_records  0zMySQLDB.update_recordc s|4IdH\}z|||IdHWnAty8}zddiWYd}~WdIdHSd}~wtyY}zdt|iWYd}~WdIdHSd}~wwWdIdHdS1IdHskwYdS)z Insert multiple records into the database in bulk. :param instances: A list of SQLAlchemy model instances to insert. Nerrorr.)radd_allr0rr3str)r instancesrr5rrrexecute_bulk_inserts  .zMySQLDB.execute_bulk_insertc sn|4IdH!}|t||IdH}|IdH|WdIdHS1IdHs0wYdS)z Execute a raw SQL query with optional values. :param query: The raw SQL query to execute. :param values: Optional values to use with the query. :return: The result of the query execution. N)rr%rr0fetchall)rr)r>rr,rrrr%s 0zMySQLDB.execute)NNNNNFr )r8)__name__ __module__ __qualname____doc__r contextlibasynccontextmanagerrrrrlistintboolr-r6r:r=dictr@rEr%rrrrr sB  . r __main__cs(t}|IdH|IdHdSr )r rr)dbrrrmainsrS)asynciorKos sqlalchemyrrrsqlalchemy.futurersqlalchemy.ormrsqlalchemy.excrsqlalchemy.sqlrutils.mysql_connectorr rr mysql_dbrGrSrunrrrrs"     3