o U|%iE @sdZddlZddlZddlZddlZddlZddlmZddlm Z m Z m Z m Z m Z mZmZmZddlZddlZddlmZddlmZddlmZddlmZd Zd Zd d d dddZd Z dee!de!fddZ"edede e#e$e%e%ffddZ& dCdede%deddfddZ'de%fddZ(dDd e e%ge)fd!e*de%fd"d#Z+d$e dee%e e ffd%d&Z,dedej-fd'd(Z.ded)e%d*e%dej-fd+d,Z/ded)e%d*e%de)fd-d.Z0ded)e%d*e%ddfd/d0Z1 dCded)e%d*e%d1ej-d2e e%e%fddf d3d4Z2ded)e%d1ej-dee%e%ffd5d6Z3d7e%de%fd8d9Z4dede%fd:d;Z5ded)e%d*e%de)fddEd?eeej-ej7ej8fd@e%deej-fdAdBZ9dS)Fa.General utilities for AI features in MySQL Connector/Python. Includes helpers for: - defensive dict copying - temporary table lifecycle management - SQL execution and result conversions - DataFrame to/from SQL table utilities - schema/table/column name validation - array-like to DataFrame conversion N)contextmanager)AnyCallableDictIteratorListOptionalTupleUnion)atomic_transaction)MySQLConnectionAbstract)MySQLCursorAbstract)ParamsSequenceOrDictTypemysql_ai BIGINTDOUBLELONGTEXTBOOLEANDATETIME)int64float64objectboolzdatetime64[ns]optionsreturncCs|duriSt|S)z Make a defensive copy of a dictionary, or return an empty dict if None. Args: options: param dict or None Returns: dict N)copydeepcopy)rrQ/home/air/sos_test/back/venv/lib/python3.10/site-packages/mysql/ai/utils/utils.py copy_dictIs  r db_connectionccsg}z(|VWt|}|D] \}}t|||qWddS1s%wYdSt|}|D] \}}t|||q3Wdw1sHwYw)a Context manager to track and automatically clean up temporary SQL tables. Args: db_connection: Database connection object used to create and delete tables. Returns: None Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. Yields: temporary_tables: List of (schema_name, table_name) tuples created during the context. All tables in this list are deleted on context exit. N)r delete_sql_table)r!temporary_tablescursor schema_name table_namerrrtemporary_sql_tablesYs  , r'r$queryparamscCs|||pddS)aB Execute an SQL query with optional parameters using the given cursor. Args: cursor: MySQLCursorAbstract object to execute the query. query: SQL query string to execute. params: Optional sequence or dict providing parameters for the query. Raises: DatabaseError: If the provided SQL query/params are invalid If the query is valid but the sql raises as an exception If a database connection issue occurs. If an operational error occurs during execution. Returns: None rN)execute)r$r(r)rrr execute_sqlxsr+cCstj}dtj|tdS)z Generate a random uppercase string of fixed length for table names. Returns: Random string of length RANDOM_TABLE_NAME_LENGTH. )k)stringascii_uppercasejoinrandomchoicesRANDOM_TABLE_NAME_LENGTH)char_setrrr _get_namesr5d condition max_callscCs,t|D] }|t}r|Sqtd)a Generate a random string name that satisfies a given condition. Args: condition: Callable that takes a generated name and returns True if it is valid. max_calls: Maximum number of attempts before giving up (default 100). Returns: A random string that fulfills the provided condition. Raises: RuntimeError: If the maximum number of attempts is reached without success. zt|ttfrt|dkrddgfSdt|gfSd|gfS)a. Convert a Python value into its SQL-compatible string representation and parameters. Args: value: The value to format. Returns: Tuple containing: - A string for substitution into a SQL query. - A list of parameters to be bound into the query. rz%sNzCAST(%s as JSON)) isinstancedictlistlenjsondumps)r>rrrformat_value_sqls    rEc Csdttdttfdd}dtdtfdd}i}t|jD]\}}|ddkr,|||<q|||<q|}g}|D]}t|} t|D] \}} ||| | |<qC|| q9t j ||j d S) a Convert the results of a cursor's last executed query to a pandas DataFrame. Args: cursor: MySQLCursorAbstract with a completed query. Returns: DataFrame with data from the cursor. Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. If a compatible SELECT query wasn't the last statement ran elemrcSs|dur t|SdSN)rCloadsrFrrr_json_processorsz+sql_response_to_df.._json_processorcSs|SrGrrIrrr_default_processorsz.sql_response_to_df.._default_processor)columns) rstrr@r enumerate descriptionfetchallrAappendpd DataFrame column_names) r$rJrKidx_to_processoridxcolrowsprocessed_rowsrow processed_rowrFrrrsql_response_to_dfs    r^r%r&cCs.t|t|t|d|d|t|S)aD Load the entire contents of a SQL table into a pandas DataFrame. Args: cursor: MySQLCursorAbstract to execute the query. schema_name: Name of the schema containing the table. table_name: Name of the table to fetch. Returns: DataFrame containing all rows from the specified table. Raises: DatabaseError: If the table does not exist If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the schema or table name is not valid zSELECT * FROM .) validate_namer+r^r$r%r&rrrsql_table_to_dfsrbcCs,t|t||d||f|duS)a Check whether a table exists in a specific schema. Args: cursor: MySQLCursorAbstract object to execute the query. schema_name: Name of the database schema. table_name: Name of the table. Returns: True if the table exists, False otherwise. Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the schema or table name is not valid z SELECT 1 FROM information_schema.tables WHERE table_schema = %s AND table_name = %s LIMIT 1 Nr`r*fetchonerarrr table_existss recCs*t|t|t|d|d|dS)a Drop a table from the SQL database if it exists. Args: cursor: MySQLCursorAbstract to execute the drop command. schema_name: Name of the schema. table_name: Name of the table to delete. Returns: None Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the schema or table name is not valid zDROP TABLE IF EXISTS r_N)r`r+rarrrr"6sr"dfcol_name_to_placeholder_stringcCs |duri}t|t||jD]}tt|q|d|}|jD]_}gg}} t||jD].\} }t| dr>| n| } ||vrO||t| g} } nt| \} } || | | q1d dd|jD} d |}d|d| d |d }t ||| d q$dS) a Insert all rows from a pandas DataFrame into an existing SQL table. Args: cursor: MySQLCursorAbstract for execution. schema_name: Name of the database schema. table_name: Table to insert new rows into. df: DataFrame containing the rows to insert. col_name_to_placeholder_string: Optional mapping of column names to custom SQL value/placeholder strings. Returns: None Raises: DatabaseError: If the rows could not be inserted into the table, e.g., a type or shape issue If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the schema or table name is not valid Nr_item, cSsg|]}t|qSr)rO.0rYrrr sz$extend_sql_table..z INSERT INTO  (z ) VALUES ())r)) r`rNrOvaluesziphasattrrhrErSextendr0r+)r$r%r&rfrgrYqualified_table_namer\ placeholdersr)rFelem_placeholder elem_paramscols_sqlplaceholders_sql insert_sqlrrrextend_sql_tablePs8       rzc s tfdd}d|}tt||jD]}tt|qg}|jD]\}}tt|d}tt|||d|q+d |} t dd|jD} | r]| d 7} d |d | d } t | z t ||W||fSt yt|w) a Create a new SQL table with a random name, and populate it with data from a DataFrame. If an 'id' column is defined in the dataframe, it will be used as the primary key. Args: cursor: MySQLCursorAbstract for executing SQL. schema_name: Schema in which to create the table. df: DataFrame containing the data to be inserted. Returns: Tuple (qualified_table_name, table_name): The schema-qualified and unqualified table names. Raises: RuntimeError: If a random available table name could not be found. ValueError: If any schema, table, or a column name is invalid. DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. cst| SrG)re)r&r$r%rrsz#sql_table_from_df..r_r ricss|] }|dkVqdS)idN)lowerrjrrr sz$sql_table_from_df..z, PRIMARY KEY (id)z CREATE TABLE rmrn)r=r`rNrOdtypesitemsPD_TO_SQL_DTYPE_MAPPINGgetrSr0anyr+rz Exceptionr") r$r%rfr&rsrY columns_sqldtypesql_type columns_str has_id_colcreate_table_sqlrr{rsql_table_from_dfs4      rr<cCs(t|tr td|std||S)a Validate that the string is a legal SQL identifier (letters, digits, underscores). Args: name: Name (schema, table, or column) to validate. Returns: The validated name. Raises: ValueError: If the name does not meet format requirements. z^[A-Za-z0-9_]+$zUnsupported name format )r?rOrematch ValueError)r<rrrr`sr`cCsZ|j}|dur't}t|}d|}t||Wdn1s"wYt||S)a Retrieve the name of the currently selected schema, or set and ensure the default schema. Args: db_connection: MySQL connector database connection object. Returns: Name of the schema (database in use). Raises: ValueError: If the schema name is not valid DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. NzCREATE DATABASE IF NOT EXISTS )databaseDEFAULT_SCHEMAr r+r`)r!schemar$create_database_stmtrrr source_schemas   rcCs4t|t||d|d|d|duS)a+ Determine if a given SQL table is empty. Args: cursor: MySQLCursorAbstract with access to the database. schema_name: Name of the schema containing the table. table_name: Name of the table to check. Returns: True if the table has no rows, False otherwise. Raises: DatabaseError: If the table does not exist If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the schema or table name is not valid zSELECT 1 FROM r_z LIMIT 1Nrcrarrris_table_emptys rfeaturearr col_prefixcsx|durdSt|tjrt|St|tjr|S|jdkr&|dd}fddt|jdD}tj||ddS)a$ Convert input data to a pandas DataFrame if necessary. Args: arr: Input data as a pandas DataFrame, NumPy ndarray, pandas Series, or None. Returns: If the input is None, returns None. Otherwise, returns a DataFrame backed by the same underlying data whenever possible (except in cases where pandas or NumPy must copy, such as for certain views or non-contiguous arrays). Notes: - If an ndarray is passed, column names will be integer indices (0, 1, ...). - If a DataFrame is passed, column names and indices are preserved. - The returned DataFrame is a shallow copy and shares data with the original input when possible; however, copies may still occur for certain input types or memory layouts. NrLcsg|] }d|qS)r;r)rkrXrrrrl;sz!convert_to_df..F)rNr) r?rTrUSeriesto_framendimreshaper9shape)rr col_namesrrr convert_to_dfs     rrG)r6)r):__doc__rrCr1rr. contextlibrtypingrrrrrrr r numpynppandasrTmysql.ai.utils.atomic_cursorr mysql.connector.abstractsr mysql.connector.cursorr mysql.connector.typesrVAR_NAME_SPACEr3rrr@r rAtuplerOr'r+r5rintr=rErUr^rbrer"rzrr`rrrndarrayrrrrrs  (      " .  #   @  =