� B�gH�����dZddlmZddlmZmZddlmZmZddl m Z m Z m Z ddl m Z ddlZddlmZmZmZmZmZmZddlZddlZdd lmZdd lmZdd lmZdd lm Z m!Z!dd l"m#Z#ddl$m%Z%ddl&m'Z'm(Z(ddl)m*Z*m+Z+ddl,m-Z-ddl.m/Z/ddl0m1Z1m2Z2ddl3m4Z4ddl5m6Z6ddl7m8cm9Z:ddl7m;Z;ddl<m=Z=ddl>m?Z?er*ddl@mAZAmBZBddlCmDZDddlEmFZFmGZGddlHmIZImJZJmKZKmLZLmMZMddl.mNZNd�ZO d~dd$�ZPd%�ZQ d�d�d-�ZR d�d�d0�ZSdddd'd1�d�d3�ZTd�d4�ZUe d�d�dA���ZVe d�d�dD���ZVddd&dddejWfd�dI�ZVe d�d�dL���ZXe d�d�dM���ZXdd&ddddejWfd�dN�ZXe d�d�dP���ZYe d�d�dQ���ZYdd&ddddejWdfd�dR�ZY d�d�d^�ZZd�d�d_�Z[e[Z\ d�d�db�Z]Gdc�dde6��Z^Gde�dae6e��Z_Gdf�dg��Z`Gdh�die`��Zad�dj�ZbGdk�dle_��ZcGdm�dne_��Zddodpdqdrdsdtdqdu�Zed�dw�Zfd�dx�ZgGdy�dze^��ZhGd{�d|e_��Zi d�d�d}�ZjdS)�z| Collection of query wrappers / abstractions to both facilitate data retrieval and to reduce dependency on DB-specific API. �)� annotations)�ABC�abstractmethod)� ExitStack�contextmanager)�date�datetime�time)�partialN)� TYPE_CHECKING�Any�Callable�Literal�cast�overload)�using_pyarrow_string_dtype)�lib��import_optional_dependency)�AbstractMethodError� DatabaseError)�find_stack_level)�check_dtype_backend)� is_dict_like� is_list_like)� ArrowDtype�DatetimeTZDtype)�isna)� get_option)� DataFrame�Series)�ArrowExtensionArray)� PandasObject)�maybe_make_list)�convert_object_array)� to_datetime)�Iterator�Mapping)�Table)�Select� TextClause)�DateTimeErrorChoices�DtypeArg� DtypeBackend� IndexLabel�Self)�Indexc�F�|dus|�|durg}nt|d��s|g}|S)z3Process parse_dates argument for read_sql functionsTNF�__iter__)�hasattr)� parse_datess �]/home/asafur/pinokio/api/open-webui.git/app/env/lib/python3.11/site-packages/pandas/io/sql.py�_process_parse_dates_argumentr7^sD���d���k�1�[�E�5I�5I�� � � �[�*� -� -�$�"�m� � ��F�utc�bool�format�str | dict[str, Any] | Nonec��t|t��rT|�dd��pd}|dkr' t|fi|��S#tt f$r|cYSwxYwt|fd|i|��S|�Jt |jjtj ��s$t |jjtj ��rd}|dvrt|d||���St|jt��rt|d���St|d||� ��S) N�errors�ignore�s)�D�d�h�mr@�ms�us�ns�coerce)r>�unitr9T�r9)r>r;r9) � isinstance�dict�popr&� TypeError� ValueError� issubclass�dtype�type�np�floating�integerr)�colr9r;�errors r6�_handle_date_columnrXis?���&�$���M� '-�j�j��4�&@�&@�&L�H�� �H� � � �"�3�1�1�&�1�1�1���z�*� � � �� � � � �����3�7�7�u�7��7�7�7� �>� �s�y�~�r�{� 3� 3� ��#�)�.�"�*�5�5� ��F� �@� @� @��s�8�&�c�J�J�J� J� �� �?� 3� 3� M��s��-�-�-� -��s�8�F��L�L�L� Ls� A�A�Ac�D�t|��}t|�����D]n\}\}}t|jt ��s||vrH ||}n#t tf$rd}YnwxYw|�|t||������o|S)zz Force non-datetime columns to be read as such. Supports both string formatted and integer timestamp columns. N�r;) r7� enumerate�itemsrKrQr�KeyErrorrN�isetitemrX)� data_framer5�i�col_name�df_col�fmts r6�_parse_date_columnsrd�s��� 0� �<�<�K� "+�:�+;�+;�+=�+=�!>�!>�L�L��� �H�f� �f�l�O� 4� 4� L��K�8O�8O� �!�(�+�����i�(� � � ����� ���� � � ��#6�v�c�#J�#J�#J� K� K� K�� �s�A!�!A7�6A7T�numpy� coerce_float� dtype_backend�DtypeBackend | Literal['numpy']�returnr c �^�tj|��}tt|j��d||���}|dkr�t d��}g}|D]m}|�|d���} |jdkr'| �|� ����} |� t| �����n|}|rZtttttt!|������|������} || _| St|���S)N)rQrfrg�pyarrowT)� from_pandas�string)�columns)r�to_object_array_tuplesr%�list�Tr�arrayrQrrm�appendr"r rL�zip�range�lenrn) �datarnrfrg�content�arrays�pa� result_arrays�arr�pa_array�dfs r6�_convert_arrays_to_dataframer�s(�� �(��.�.�G� !� �W�Y����!�#� ���F� � �!�!� '� � 2� 2��� �� @� @�C��x�x���x�6�6�H��y�H�$�$�$�=�=������5�5�� � � �!4�X�!>�!>� ?� ?� ?� ?��� �*� �t�C��U�3�w�<�<�%8�%8� 9� 9�6�B�B�C�C� D� D���� �� ���)�)�)�)r8rQ�DtypeArg | Nonec��t||||��}|r|�|��}t||��}|�|�|��}|S�z5Wrap result set of a SQLAlchemy query in a DataFrame.)r�astyperd� set_index)rwrn� index_colrfr5rQrg�frames r6� _wrap_resultr��s[�� )��w� �m� T� T�E� �$�� � �U�#�#�� ��{� 3� 3�E������ �*�*�� �Lr8)r�r5rQrgr~c��|r|�|��}t||��}|�|�|��}|Sr�)r�rdr�)r~r�r5rQrgs r6�_wrap_result_adbcr��sH�� �� �Y�Y�u� � �� �R�� -� -�B��� �\�\�)� $� $�� �Ir8c�Z�tjdtt�����t dd���}|�0t |t |jjf��rtd���t|d� ��5}|� ||��cddd��S#1swxYwYdS) a� Execute the given SQL query using the provided connection object. Parameters ---------- sql : string SQL query to be executed. con : SQLAlchemy connection or sqlite3 connection If a DBAPI2 object, only sqlite3 is supported. params : list or tuple, optional, default: None List of parameters to pass to execute method. Returns ------- Results Iterable zP`pandas.io.sql.execute` is deprecated and will be removed in the future version.�� stacklevel� sqlalchemyr?�r>Nz+pandas.io.sql.execute requires a connectionT)�need_transaction) �warnings�warn� FutureWarningrrrK�str�engine�EnginerN�pandasSQL_builder�execute)�sql�con�paramsr�� pandas_sqls r6r�r��s���" �M� 1��#�%�%� ���� ,�L��J�J�J�J���*�S�3� �8I�8P�2Q�"R�"R���E�F�F�F� �3�� 6� 6� 6�/�*��!�!�#�v�.�.�/�/�/�/�/�/�/�/�/�/�/�/����/�/�/�/�/�/s�=B � B$�'B$.� table_namer�r��str | list[str] | Noner5�!list[str] | dict[str, str] | Nonern�list[str] | None� chunksize�None�DtypeBackend | lib.NoDefaultc ��dS�N�� r�r��schemar�rfr5rnr�rgs r6�read_sql_tabler�� ���Cr8�int�Iterator[DataFrame]c ��dSr�r�r�s r6r�r�r�r8r�� str | None� int | None�DataFrame | Iterator[DataFrame]c �l�t|��|tjurd}|tjusJ�t||d���5} | �|��st d|�d����| �|||||||���} ddd��n #1swxYwY| �| St d|�d�|���)a0 Read SQL database table into a DataFrame. Given a table name and a SQLAlchemy connectable, returns a DataFrame. This function does not support DBAPI connections. Parameters ---------- table_name : str Name of SQL table in database. con : SQLAlchemy connectable or str A database URI could be provided as str. SQLite DBAPI connection mode not supported. schema : str, default None Name of SQL schema in database to query (if database flavor supports this). Uses default schema if None (default). index_col : str or list of str, optional, default: None Column(s) to set as index(MultiIndex). coerce_float : bool, default True Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point. Can result in loss of Precision. parse_dates : list or dict, default None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg dict}``, where the arg dict corresponds to the keyword arguments of :func:`pandas.to_datetime` Especially useful with databases without native Datetime support, such as SQLite. columns : list, default None List of column names to select from SQL table. chunksize : int, default None If specified, returns an iterator where `chunksize` is the number of rows to include in each chunk. dtype_backend : {'numpy_nullable', 'pyarrow'}, default 'numpy_nullable' Back-end data type applied to the resultant :class:`DataFrame` (still experimental). Behaviour is as follows: * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame` (default). * ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype` DataFrame. .. versionadded:: 2.0 Returns ------- DataFrame or Iterator[DataFrame] A SQL table is returned as two-dimensional data structure with labeled axes. See Also -------- read_sql_query : Read SQL query into a DataFrame. read_sql : Read SQL query or database table into a DataFrame. Notes ----- Any datetime values with time zone information will be converted to UTC. Examples -------- >>> pd.read_sql_table('table_name', 'postgres:///db_name') # doctest:+SKIP reT�r�r�zTable z not found�r�rfr5rnr�rgN)rr� no_defaultr�� has_tablerO� read_table) r�r�r�r�rfr5rnr�rgr��tables r6r�r�.s'��Z� �&�&�&����&�&�� � ��� .� .� .� .� �3�v�� E� E� E�  ���#�#�J�/�/� >��<�j�<�<�<�=�=� =��%�%� ��%�#���'�&� � ��  �  �  �  �  �  �  �  �  �  �  ����  �  �  �  � ��� ��8�*�8�8�8�#�>�>�>s�AB�B�Br��$list[Any] | Mapping[str, Any] | Nonec ��dSr�r�� r�r�r�rfr�r5r�rQrgs r6�read_sql_queryr��r�r8c ��dSr�r�r�s r6r�r��r�r8c ���t|��|tjurd}|tjusJ�t|��5} | �||||||||���cddd��S#1swxYwYdS)a� Read SQL query into a DataFrame. Returns a DataFrame corresponding to the result set of the query string. Optionally provide an `index_col` parameter to use one of the columns as the index, otherwise default integer index will be used. Parameters ---------- sql : str SQL query or SQLAlchemy Selectable (select or text object) SQL query to be executed. con : SQLAlchemy connectable, str, or sqlite3 connection Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. index_col : str or list of str, optional, default: None Column(s) to set as index(MultiIndex). coerce_float : bool, default True Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point. Useful for SQL result sets. params : list, tuple or mapping, optional, default: None List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249's paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={'name' : 'value'}. parse_dates : list or dict, default: None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg dict}``, where the arg dict corresponds to the keyword arguments of :func:`pandas.to_datetime` Especially useful with databases without native Datetime support, such as SQLite. chunksize : int, default None If specified, return an iterator where `chunksize` is the number of rows to include in each chunk. dtype : Type name or dict of columns Data type for data or columns. E.g. np.float64 or {'a': np.float64, 'b': np.int32, 'c': 'Int64'}. .. versionadded:: 1.3.0 dtype_backend : {'numpy_nullable', 'pyarrow'}, default 'numpy_nullable' Back-end data type applied to the resultant :class:`DataFrame` (still experimental). Behaviour is as follows: * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame` (default). * ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype` DataFrame. .. versionadded:: 2.0 Returns ------- DataFrame or Iterator[DataFrame] See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql : Read SQL query or database table into a DataFrame. Notes ----- Any datetime values with time zone information parsed via the `parse_dates` parameter will be converted to UTC. Examples -------- >>> from sqlalchemy import create_engine # doctest: +SKIP >>> engine = create_engine("sqlite:///database.db") # doctest: +SKIP >>> with engine.connect() as conn, conn.begin(): # doctest: +SKIP ... data = pd.read_sql_table("data", conn) # doctest: +SKIP re)r�r�rfr5r�rQrgN)rrr�r�� read_query) r�r�r�rfr�r5r�rQrgr�s r6r�r��s���l� �&�&�&����&�&�� � ��� .� .� .� .� �3� � �  �:��$�$� ���%�#���'�%�  �  �  �  �  �  �  �  �  �  �  �  �  �  ����  �  �  �  �  �  s�A)�)A-�0A-� list[str]c ��dSr�r�� r�r�r�rfr�r5rnr�rgrQs r6�read_sqlr�� ���Cr8c ��dSr�r�r�s r6r�r�*r�r8c ��t|��|tjurd}|tjusJ�t|��5} t | t ��r)| �|||||||| ���cddd��S | �|��} n#t$rd} YnwxYw| r(| � |||||||���cddd��S| �|||||||| ���cddd��S#1swxYwYdS)ak Read SQL query or database table into a DataFrame. This function is a convenience wrapper around ``read_sql_table`` and ``read_sql_query`` (for backward compatibility). It will delegate to the specific function depending on the provided input. A SQL query will be routed to ``read_sql_query``, while a database table name will be routed to ``read_sql_table``. Note that the delegated function might have more specific notes about their functionality not listed here. Parameters ---------- sql : str or SQLAlchemy Selectable (select or text object) SQL query to be executed or a table name. con : ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection ADBC provides high performance I/O with native type support, where available. Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible for engine disposal and connection closure for the ADBC connection and SQLAlchemy connectable; str connections are closed automatically. See `here <https://docs.sqlalchemy.org/en/20/core/connections.html>`_. index_col : str or list of str, optional, default: None Column(s) to set as index(MultiIndex). coerce_float : bool, default True Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point, useful for SQL result sets. params : list, tuple or dict, optional, default: None List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249's paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={'name' : 'value'}. parse_dates : list or dict, default: None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg dict}``, where the arg dict corresponds to the keyword arguments of :func:`pandas.to_datetime` Especially useful with databases without native Datetime support, such as SQLite. columns : list, default: None List of column names to select from SQL table (only used when reading a table). chunksize : int, default None If specified, return an iterator where `chunksize` is the number of rows to include in each chunk. dtype_backend : {'numpy_nullable', 'pyarrow'}, default 'numpy_nullable' Back-end data type applied to the resultant :class:`DataFrame` (still experimental). Behaviour is as follows: * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame` (default). * ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype` DataFrame. .. versionadded:: 2.0 dtype : Type name or dict of columns Data type for data or columns. E.g. np.float64 or {'a': np.float64, 'b': np.int32, 'c': 'Int64'}. The argument is ignored if a table is passed instead of a query. .. versionadded:: 2.0.0 Returns ------- DataFrame or Iterator[DataFrame] See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql_query : Read SQL query into a DataFrame. Examples -------- Read data from SQL via either a SQL query or a SQL tablename. When using a SQLite database only SQL queries are accepted, providing only the SQL tablename will result in an error. >>> from sqlite3 import connect >>> conn = connect(':memory:') >>> df = pd.DataFrame(data=[[0, '10/11/12'], [1, '12/11/10']], ... columns=['int_column', 'date_column']) >>> df.to_sql(name='test_data', con=conn) 2 >>> pd.read_sql('SELECT int_column, date_column FROM test_data', conn) int_column date_column 0 0 10/11/12 1 1 12/11/10 >>> pd.read_sql('test_data', 'postgres:///db_name') # doctest:+SKIP Apply date parsing to columns through the ``parse_dates`` argument The ``parse_dates`` argument calls ``pd.to_datetime`` on the provided columns. Custom argument values for applying ``pd.to_datetime`` on a column are specified via a dictionary format: >>> pd.read_sql('SELECT int_column, date_column FROM test_data', ... conn, ... parse_dates={"date_column": {"format": "%d/%m/%y"}}) int_column date_column 0 0 2012-11-10 1 1 2010-11-12 .. versionadded:: 2.2.0 pandas now supports reading via ADBC drivers >>> from adbc_driver_postgresql import dbapi # doctest:+SKIP >>> with dbapi.connect('postgres:///db_name') as conn: # doctest:+SKIP ... pd.read_sql('SELECT int_column FROM test_data', conn) int_column 0 0 1 1 re)r�r�rfr5r�rgrQNFr�) rrr�r�rK�SQLiteDatabaser�r�� Exceptionr�) r�r�r�rfr�r5rnr�rgrQr��_is_table_names r6r�r�:s���B� �&�&�&����&�&�� � ��� .� .� .� .� �3� � �'�:� �j�.� 1� 1� ��(�(��#��)�'�#�+��)� � �'�'�'�'�'�'�'�'� #�'�1�1�#�6�6�N�N��� #� #� #�"�N�N�N� #���� � ��(�(��#�)�'��#�+�)���)'�'�'�'�'�'�'�'�<�(�(��#��)�'�#�+��)� � �='�'�'�'�'�'�'�'�'�'�'�'����'�'�'�'�'�'s;�2C:�?B�C:� B$�!C:�#B$�$ C:�C:�:C>�C>�fail�auto�name� if_exists�$Literal['fail', 'replace', 'append']�index� index_label�IndexLabel | None�method�"Literal['multi'] | Callable | Noner�c �T�|dvrtd|�d����t|t��r|���}n$t|t��st d���t ||d���5} | j||f||||||| | d�| ��cddd��S#1swxYwYdS) a� Write records stored in a DataFrame to a SQL database. Parameters ---------- frame : DataFrame, Series name : str Name of SQL table. con : ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection or sqlite3 DBAPI2 connection ADBC provides high performance I/O with native type support, where available. Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. schema : str, optional Name of SQL schema in database to write to (if database flavor supports this). If None, use default schema (default). if_exists : {'fail', 'replace', 'append'}, default 'fail' - fail: If table exists, do nothing. - replace: If table exists, drop it, recreate it, and insert data. - append: If table exists, insert data. Create if does not exist. index : bool, default True Write DataFrame index as a column. index_label : str or sequence, optional Column label for index column(s). If None is given (default) and `index` is True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex. chunksize : int, optional Specify the number of rows in each batch to be written at a time. By default, all rows will be written at once. dtype : dict or scalar, optional Specifying the datatype for columns. If a dictionary is used, the keys should be the column names and the values should be the SQLAlchemy types or strings for the sqlite3 fallback mode. If a scalar is provided, it will be applied to all columns. method : {None, 'multi', callable}, optional Controls the SQL insertion clause used: - None : Uses standard SQL ``INSERT`` clause (one per row). - ``'multi'``: Pass multiple values in a single ``INSERT`` clause. - callable with signature ``(pd_table, conn, keys, data_iter) -> int | None``. Details and a sample callable implementation can be found in the section :ref:`insert method <io.sql.method>`. engine : {'auto', 'sqlalchemy'}, default 'auto' SQL engine library to use. If 'auto', then the option ``io.sql.engine`` is used. The default ``io.sql.engine`` behavior is 'sqlalchemy' .. versionadded:: 1.3.0 **engine_kwargs Any additional kwargs are passed to the engine. Returns ------- None or int Number of rows affected by to_sql. None is returned if the callable passed into ``method`` does not return an integer number of rows. .. versionadded:: 1.4.0 Notes ----- The returned rows affected is the sum of the ``rowcount`` attribute of ``sqlite3.Cursor`` or SQLAlchemy connectable. If using ADBC the returned rows are the result of ``Cursor.adbc_ingest``. The returned value may not reflect the exact number of written rows as stipulated in the `sqlite3 <https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.rowcount>`__ or `SQLAlchemy <https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.BaseCursorResult.rowcount>`__ )r��replacers�'�' is not valid for if_existsz9'frame' argument should be either a Series or a DataFrameTr�)r�r�r�r�r�rQr�r�N)rOrKr!�to_framer �NotImplementedErrorr��to_sql) r�r�r�r�r�r�r�r�rQr�r�� engine_kwargsr�s r6r�r��s3��j�5�5�5��D�Y�D�D�D�E�E�E��%�� � � ���� � ��� ��y� )� )� �!� G� � � � �3�v�� E� E� E�  �� �z� � � �  � ��#������  �  ��  �  �  �  �  �  �  �  �  �  �  �  �  �  ����  �  �  �  �  �  s�8B�B!�$B!c��t||���5}|�|��cddd��S#1swxYwYdS)a� Check if DataBase has named table. Parameters ---------- table_name: string Name of SQL table. con: ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection ADBC provides high performance I/O with native type support, where available. Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. schema : string, default None Name of SQL schema in database to write to (if database flavor supports this). If None, use default schema (default). Returns ------- boolean �r�N)r�r�)r�r�r�r�s r6r�r�Ys���* �3�v� .� .� .�0�*��#�#�J�/�/�0�0�0�0�0�0�0�0�0�0�0�0����0�0�0�0�0�0s �4�8�8r�� PandasSQLc��ddl}t||j��s|�t|��St dd���}t|t ��r|�t d���|�2t|t |jjf��rt|||��St dd���}|r$t||j��rt|��Stj dtt��� ��t|��S) z� Convenience function to return the correct PandasSQL subclass based on the provided parameters. Also creates a sqlalchemy connection and transaction if necessary. rNr�r?r�z.Using URI string without sqlalchemy installed.zadbc_driver_manager.dbapiz�pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.r�)�sqlite3rK� Connectionr�rr�� ImportErrorr�� Connectable� SQLDatabase� ADBCDatabaser�r�� UserWarningr)r�r�r�r�r��adbcs r6r�r�us���N�N�N��#�w�)�*�*�#�c�k��c�"�"�"�+�L��J�J�J�J��#�s���L� � 2��J�K�K�K���*�S�3� �8I�8U�2V�"W�"W���3��(8�9�9�9� %�&A�(� S� S� S�D� �!� �3���0�0�!��C� � � � �M� D� �#�%�%� ���� �#� � �r8c���eZdZdZ d4d5d�Zd�Zd6d�Zd7d�Zd7d�Zd8d�Z d8d�Z d9d�Z d:d;d!�Z d<d=d)�Z d>d?d+�Zd,�Zd-�Zd.�Z d@dAd/�ZdBd2�Zd3�ZdS)C�SQLTablez� For mapping Pandas tables to SQL tables. Uses fact that table is reflected by SQLAlchemy to do better type conversions. Also holds various flags needed to avoid having to pass them between functions all the time. NTr��pandasr�r�r��bool | str | list[str] | Noner�r��prefixrQr�rir�c ��||_||_||_||_|�||��|_||_||_| |_| |_ |�|� ��|_ n*|j� |j|j��|_ |j �td|�d����t|j��std���dS)NzCould not init table 'r�zEmpty table name specified)r��pd_sqlr�r�� _index_namer�r�r��keysrQ�_create_table_setupr�� get_tablerOrv) �selfr��pandas_sql_enginer�r�r�r�r�r�r�rQs r6�__init__zSQLTable.__init__�s����� �'�� ��� ��� ��%�%�e�[�9�9�� ��� �"����� ��� � � ��1�1�3�3�D�J�J���.�.�t�y�$�+�F�F�D�J� �:� ��=�d�=�=�=�>�>� >��4�9�~�~� ;��9�:�:� :� ;� ;r8c�L�|j�|j|j��Sr�)r�r�r�r��r�s r6�existszSQLTable.exists�s���{�$�$�T�Y�� �<�<�<r8c��ddlm}t||j���|jj����S)Nr)� CreateTable)�sqlalchemy.schemar�r�r��compiler�r�)r�r�s r6� sql_schemazSQLTable.sql_schema�s@��1�1�1�1�1�1��;�;�t�z�*�*�2�2�4�;�?�C�C�D�D�Dr8c��|j�|jj��|_|j���5|j�|jj���ddd��dS#1swxYwYdS)N��bind)r�� to_metadatar��meta�run_transaction�creater�r�s r6�_execute_createzSQLTable._execute_create�s����Z�+�+�D�K�,<�=�=�� � �[� (� (� *� *� 4� 4� �J� � �4�;�?� � 3� 3� 3� 4� 4� 4� 4� 4� 4� 4� 4� 4� 4� 4� 4���� 4� 4� 4� 4� 4� 4s�&A6�6A:�=A:c�r�|���r�|jdkrtd|j�d����|jdkr;|j�|j|j��|���dS|jdkrdStd|j�d����|���dS)Nr��Table '�' already exists.r�rsr�r�)r�r�rOr�r�� drop_tabler�rr�s r6rzSQLTable.create�s��� �;�;�=�=� #��~��'�'� �!G�4�9�!G�!G�!G�H�H�H��~��*�*�� �&�&�t�y�$�+�>�>�>��$�$�&�&�&�&�&���8�+�+��� �!Q�T�^�!Q�!Q�!Q�R�R�R� � � � "� "� "� "� "r8r�r�r�c����fd�|D��}|�|j���|��}|jS)a< Execute SQL statement inserting data Parameters ---------- conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection keys : list of str Column names data_iter : generator of list Each item contains a list of values to be inserted c�J��g|]}tt�|������ Sr��rLrt��.0�rowr�s �r6� <listcomp>z,SQLTable._execute_insert.<locals>.<listcomp>��)���:�:�:���S��s�^�^�$�$�:�:�:r8)r�r��insert�rowcount)r��connr�� data_iterrw�results ` r6�_execute_insertzSQLTable._execute_insert�sG���;�:�:�:� �:�:�:�����d�j�/�/�1�1�4�8�8����r8c���ddlm}�fd�|D��}||j���|��}|�|��}|jS)a  Alternative to _execute_insert for DBs support multi-value INSERT. Note: multi-value insert is usually faster for analytics DBs and tables containing a few columns but performance degrades quickly with increase of columns. r)rc�J��g|]}tt�|������ Sr�r r s �r6rz2SQLTable._execute_insert_multi.<locals>.<listcomp>rr8)r�rr��valuesr�r)r�rr�rrrw�stmtrs ` r6�_execute_insert_multizSQLTable._execute_insert_multi�sh��� &�%�%�%�%�%�:�:�:�:� �:�:�:���v�d�j�!�!�(�(��.�.�����d�#�#����r8�"tuple[list[str], list[np.ndarray]]c��|j�g|j���}|j|j_ |�d���n,#t $r}t d|����|�d}~wwxYw|j}t tt|j ����}t|��}dg|z}t|� ����D�]\}\}}|j jdkr�t|jt"��r�ddl} | j�|j j��r"|j�t.���} �n4t1j��5t1jdt6���t9j|j���t.���} ddd��n #1swxYwYn�|j���} n�|j jd krr|j} t| t"��r(| �t9j d �����} | � d ���!t.��} n|j�!t.��} t| t8j"��sJtG| �����|j$rtK| ��} d| | <| ||<��||fS) NT��inplacez!duplicate name in index/columns: �Mr)rQr?)�categoryrDzm8[ns]�i8)&r�r��copy�names� reset_indexrOrp�mapr�rnrvr[r\rQ�kindrK�_valuesr"rk�types�is_date� pyarrow_dtype�to_numpy�objectr��catch_warnings�filterwarningsr�rS�asarray�dt� to_pydatetime�viewr��ndarrayrR� _can_hold_nar) r��temp�err� column_names�ncols� data_listr`�_�serrzrB�vals�masks r6� insert_datazSQLTable.insert_datas��� �:� !��:�?�?�$�$�D�#�z�D�J� � U�� � �� �.�.�.�.��� U� U� U� �!J�S�!J�!J�K�K�QT�T����� U�����:�D��C��T�\�2�2�3�3� ��L�!�!��(,�f�u�n� �$�T�Z�Z�\�\�2�2� � �K�A�x��3��y�~��$�$��c�k�+>�?�?� 4�(�(�(�(��x�'�'�� �(?�@�@�Q��K�0�0�v�0�>�>���%�4�6�6�Q�Q�$�3�H�}�U�U�U�U� "� �3�6�+?�+?�+A�+A�� P� P� P�A�Q�Q�Q�Q�Q�Q�Q�Q�Q�Q�Q����Q�Q�Q�Q�� � �1�1�3�3�A�A����3�&�&��{���d�$7�8�8�C��=�=�r�x��/A�/A�=�B�B�D��I�I�d�O�O�*�*�6�2�2����K�&�&�v�.�.���a���,�,� 5� 5�d�1�g�g� 5� 5�,��� ��A�w�w����$���I�a�L�L��Y�&�&s*�A � A,�A'�'A,�#AF=�=G �G r�r�r�r�c�v� ��|�|j}n@|dkr|j}n2t|��rt||��}nt d|�����|���\}}t |j��}|dkrdS|�|}n|dkrt d���||zdz}d}|j� ��5} t|��D]T} | |z�t| dz|z|��� �� krn0t� �fd�|D���} || || ��} | � |�| }�O|| z }�Uddd��n #1swxYwY|S)N�multizInvalid parameter `method`: rz%chunksize argument should be non-zero�c3�,�K�|]}|���V��dSr�r�)r r|�end_i�start_is ��r6� <genexpr>z"SQLTable.insert.<locals>.<genexpr>^s,�����"K�"K�#�3�w�u�}�#5�"K�"K�"K�"K�"K�"Kr8) rr�callabler rOr>rvr�r�rru�minrt)r�r�r�� exec_insertr�r9�nrows�chunks�total_insertedrr`� chunk_iter� num_insertedrCrDs @@r6rzSQLTable.insert:s����� �>��.�K�K� �w� � ��4�K�K� �f� � � F�!�&�$�/�/�K�K��D�F�D�D�E�E� E��*�*�,�,���i��D�J���� �A�:�:��1� � ��I�I� �!�^�^��D�E�E� E��9�$��)���� �[� (� (� *� *� 7�d��6�]�]� 7� 7���i�-���Q��U�i�/��7�7���e�#�#��E� �"K�"K�"K�"K�"K��"K�"K�"K�L� �*�{�4��z�B�B� ��+�%�-�)5���&�,�6��� 7� 7� 7� 7� 7� 7� 7� 7� 7� 7� 7���� 7� 7� 7� 7��s�=A%D.�.D2�5D2re� exit_stackrrfr:rgrhc#�rK�d}|5 |�|��} | s|stjg||���V�nbd}t| |||��|_|�||���|j�!|j�|jd���|jV��� ddd��dS#1swxYwYdS)z,Return generator through chunked result set.FT�rnrf�r5rgNr)� fetchmanyr � from_recordsrr��_harmonize_columnsr�r�) r�rrNr�rnrfr5rg� has_read_datarws r6�_query_iteratorzSQLTable._query_iteratorhsQ����� � � !� !� !��'�'� �2�2����(��'�4���l�������� $� �9��'�<����� ��'�'� +�=�(�����:�)��J�(�(���T�(�B�B�B��j� � � �+ !�� !� !� !� !� !� !� !� !� !� !� !� !���� !� !� !� !� !� !s�BB,�,B0�3B0r�c �|��ddlm}|�gt|��dkrT�fd�|D��}�j�9�jddd�D](} |�d�jj| ���)||�} n|�j��} �j�| ��} | � ��} |��� | ||| |||���S| � ��} t| | ||���_ ��||����j�!�j ��jd����j S) Nr)�selectc�4��g|]}�jj|��Sr�)r��c)r �nr�s �r6rz!SQLTable.read.<locals>.<listcomp>�s!���5�5�5��D�J�L��O�5�5�5r8�����)rfr5rgrQTr)r�rXrvr�rr�rZr�r�r�rV�fetchallrr�rTr�)r�rNrfr5rnr�rgrX�cols�idx� sql_selectrr7rws` r6�readz SQLTable.read�s���� &�%�%�%�%�%� � �3�w�<�<�!�#3�#3�5�5�5�5�W�5�5�5�D��z�%��:�d�d��d�+�6�6�C��K�K��4�:�<��#4�5�5�5�5�����J�J���� �+�+�J���$�$�Z�0�0���{�{�}�}� � � ��'�'�����)�'�+�(��� ��?�?�$�$�D�5��l�L�-���D�J� � #� #�'�}� $� � � ��z�%�� �$�$�T�Z��$�>�>�>��:� r8c��|dur�|jjj}|�?t|t��s|g}t |��|krt d|�����|S|dkr"d|jjvr|jjj�dgStj |jjj ��St|t��r|gSt|t��r|SdS)NTz@Length of 'index_label' should match number of levels, which is rAr�) r�r��nlevelsrKrprvrOrnr��com�fill_missing_namesr#r�)r�r�r�rcs r6r�zSQLTable._index_name�s��� �D�=�=��j�&�.�G��&�!�+�t�4�4�0�#.�-�K��{�#�#�w�.�.�$�6�,3�6�6����#�"��1� � ��4�:�#5�5�5��J�$�)�1��y� ��-�d�j�.>�.D�E�E�E���s� #� #� ��7�N� ��t� $� $� ��L��4r8c�V���g}�j�gt�j��D]R\}}��jj�|����}|�t |��|df���S|��fd�t t�jj����D��z }|S)NTc���g|]A}t�jj|����jjdd�|f��df��BS�NF)r�r�rn�iloc)r r`� dtype_mapperr�s ��r6rz8SQLTable._get_column_names_and_types.<locals>.<listcomp>�s]���# �# �# �����#�A�&� '� '���d�j�o�a�a�a��d�6K�)L�)L�e� T�# �# �# r8) r�r[r��_get_level_valuesrsr�rurvrn)r�rj�column_names_and_typesr`� idx_label�idx_types`` r6�_get_column_names_and_typesz$SQLTable._get_column_names_and_types�s�����!#�� �:� !� )�$�*� 5� 5� P� P� ��9�'�<�� �(8�(J�(J�1�(M�(M�N�N��&�-�-�s�9�~�~�x��.N�O�O�O�O��# �# �# �# �# ��3�t�z�1�2�2�3�3�# �# �# � �� &�%r8c�|� �ddlm� m}m}ddlm}|�|j��}� fd�|D��}|j�It|j��s |jg}n|j}||d|j dzi�}|� |��|j p|j jj }|��} ||j | g|�Rd|i�S)Nr)�Column�PrimaryKeyConstraintr)��MetaDatac�4��g|]\}}}�|||�����S))r�r�)r r��typ�is_indexrqs �r6rz0SQLTable._create_table_setup.<locals>.<listcomp>�s@��� � � �#��c�8� �F�4��H� -� -� -� � � r8r��_pkr�)r�rqrrr)r�rtro�_sqlalchemy_typer�rr�rsr�r�r) r�rrr)rtrlrnr��pkcr�rrqs @r6r�zSQLTable._create_table_setup�s)��� � � � � � � � � � � /�.�.�.�.�.�!%�!A�!A�$�BW�!X�!X�� � � � �'=� � � �� �9� ��� �*�*� !�� �{����y��&�&��E�4�9�u�3D�E�E�C� �N�N�3� � � ���7�� � 0� 7���x�z�z���u�T�Y��>�w�>�>�>�v�>�>�>r8c���t|��}|jjD�]S}|j} |j|}||vr6 ||}n#t $rd}YnwxYwt ||���|j|<�R|�|j��}|tus|tus |tur#|tu}t ||���|j|<n�|dkr)|tur |� |d���|j|<ni|dkrct|��|���kr>|t!jd��us |t$ur|� |d���|j|<��D#t&$rY��QwxYwdS)a Make the DataFrame's column types align with the SQL table column types. Need to work around limited NA value support. Floats are always fine, ints must always be floats if there are Null values. Booleans are hard because converting bool column with None replaces all Nones with false. Therefore only convert bool if there are no NA values. Datetimes should already be converted to np.datetime64 if supported, but here we also force conversion if required. NrZrJreF)r"�int64)r7r�rnr�r�rNrX� _get_dtyperRr rr�floatr�rv�countrSrQr:r]) r�r5rg�sql_colrarbrc�col_typer9s r6rTzSQLTable._harmonize_columnss��� 4�K�@�@� ��z�)�# �# �G��|�H�! ���H�-���{�*�*�#�)�(�3����$�#�#�#�"����#����+>�v�c�+R�+R�+R�D�J�x�(�� �?�?�7�<�8�8����(�(��4�'�'��?�2�2�#�o�5�C�+>�v�3�+O�+O�+O�D�J�x�(�(�"�g�-�-�(�e�2C�2C�+1�=�=���=�+N�+N�D�J�x�(�(�"�g�-�-�#�f�+�+������2O�2O��2�8�G�#4�#4�4�4��D�8H�8H�/5�}�}�X�E�}�/R�/R�� �8�,���� � � ��� ����E# �# s;�E �A�E � A�E �A�E �.C0E � E.�-E.rV�Index | Seriesc��|jpi}t|��r+tt|��}|j|vr ||jSt j|d���}ddlm}m }m }m }m }m } m} m} m} m} |dvrK |jj� |d���Sn/#t($r"t+|dd���|d���cYSYnwxYw|S|dkr*t-jd t0t3��� ��|S|d kr#|jd kr | d ���S| d���S|dkrw|jj���dvr| S|jj���dvr| S|jj���dkrt7d���|S|dkr|S|dkr|S|dkr| S|dkrt7d���| S)NT��skipnar) � TIMESTAMP� BigInteger�Boolean�Date�DateTime�Float�Integer� SmallInteger�Text�Time)� datetime64r )�timezone�tz� timedelta64�lthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.r�rT�float32�)� precision�5rU)�int8�uint8�int16)�uint16�int32�uint64z1Unsigned 64 bit integer datatype is not supported�booleanrr �complex�Complex datatypes not supported)rQrrrLr�r� infer_dtype�sqlalchemy.typesr�r�r�r�r�r�r�r�r�r�r0r��AttributeError�getattrr�r�r�r�lowerrO)r�rVrQr�r�r�r�r�r�r�r�r�r�r�s r6ryzSQLTable._sqlalchemy_type>s����*�*��� �� � � '���u�%�%�E��x�5� � ��S�X��&��?�3�t�4�4�4�� � � � � � � � � � � � � � � � � � � � � � � � � �1� 1� 1� 4��6�9�(�$�9�d�3�3�3�3�)��!� 4� 4� 4��3��d�+�+�7�$�9�d�3�3�3�3�3�3�8�7� 4���� �O� �}� $� $� �M�L��+�-�-�  � � � � � � �� #� #��y�I�%�%��u�r�*�*�*�*��u�r�*�*�*�*� �� "� "��y�~�#�#�%�%�)C�C�C�#�#����%�%�'�'�+>�>�>������%�%�'�'�8�3�3� �!T�U�U�U�!�!� �� "� "��N� �� � ��K� �� � ��K� �� "� "��>�?�?� ?�� s�7B�'B<�;B<c�z�ddlm}m}m}m}m}m}t||��rtSt||��rtj d��St||��r|j stStSt||��rtSt||��rtSt||��rtSt S)Nr)r�r�r�r�r�r�r|)r�r�r�r�r�r�r�rKr~rSrQr�r rrr:r,)r��sqltyper�r�r�r�r�r�s r6r}zSQLTable._get_dtype�s��� � � � � � � � � � � � � � � � � �g�u� %� %� ��L� ��� )� )� ��8�G�$�$� $� ��� +� +� ��#� ���"� "� ��� *� *� ��O� ��� &� &� ��K� ��� )� )� ��K�� r8)NTr�r�NNNN) r�r�r�r�r�r�r�r�rQr�rir��rir��rir�)r�r�rir�)rir)NN)r�r�r�r�rir�)TNre)rNrr�r�rfr:rgrh)TNNNre) rNrrfr:r�r�rgrhrir�)Nre)rgrhrir�)rVr�)�__name__� __module__� __qualname__�__doc__r�r�r�rrrrr>rrVrar�ror�rTryr}r�r8r6r�r��s����������/3�:@���� �!%�";�";�";�";�";�H=�=�=�E�E�E�E� 4�4�4�4� #� #� #� #����� ����"2'�2'�2'�2'�l!%�59�,�,�,�,�,�h"��9@�"!�"!�"!�"!�"!�N"��� $�9@�-�-�-�-�-�^���@ &� &� &�?�?�?�@�9@�5�5�5�5�5�nE�E�E�E�N����r8r�c���eZdZdZd.d�Zd/d�Z d0d1d�Ze d0d2d���Ze d3d4d%���Z ed5d6d'���Z ed5d7d(���Z e d8d9d-���Z dS):r�z9 Subclasses Should define read_query and to_sql. rir0c��|Sr�r�r�s r6� __enter__zPandasSQL.__enter__�s��� r8r�c��dSr�r��r��argss r6�__exit__zPandasSQL.__exit__�s�� �r8NTrer�r�r�r�rfr:r�r�r�r�rgrhr�c ��t�r��r�) r�r�r�rfr5rnr�r�rgs r6r�zPandasSQL.read_table�s ��"�!r8r�rQr�c ��dSr�r�) r�r�r�rfr5r�r�rQrgs r6r�zPandasSQL.read_query�s �� �r8r�r�r�r�r�r�r�r�r�c  ��dSr�r�) r�r�r�r�r�r�r�r�rQr�r�r�s r6r�zPandasSQL.to_sql�s �� �r8�str | Select | TextClausec��dSr�r�)r�r�r�s r6r�zPandasSQL.execute���� �r8c��dSr�r�)r�r�r�s r6r�zPandasSQL.has_table�r�r8r�r r�r�c��dSr�r��r�r�r�r�rQr�s r6�_create_sql_schemazPandasSQL._create_sql_schema�s �� �r8)rir0r��NTNNNNre�r�r�r�r�rfr:r�r�r�r�rgrhrir��r�r�r�r�rfr:r�r�rQr�rgrhrir��r�TNNNNNr�)r�r�r�r�r�r:r�r�rQr�r�r�r�r�rir�r��r�r��r�r�r�r�rir:�NNN� r�r r�r�r�r�rQr�r�r�rir�) r�r�r�r�r�r�r�rr�r�r�r�r�r�r8r6r�r��sl������������ � � � � -1�!���!� $�9@� "� "� "� "� "��-1�!��� $�!%�9@�  �  �  �  ��^�  �� ;A���� $�!%�59�� � � � ��^� � � � � � ��^� �� � � � ��^� �� "&�!%�!�  � � � ��^� � � r8c� �eZdZ d dd �ZdS)� BaseEngineTNr�r�r�r�r�r�r�r�ric  � �t|���)z: Inserts data into already-prepared table )r) r�r�r�r�r�r�r�r�r�r�s r6�insert_recordszBaseEngine.insert_records�s��"�$�'�'�'r8�TNNN� r�r�r�r�r�r�r�r�rir�)r�r�r�r�r�r8r6r�r��s;������04�� $��(�(�(�(�(�(�(r8r�c�(�eZdZdd�Z ddd�ZdS)�SQLAlchemyEnginerir�c�(�tdd���dS)Nr�z'sqlalchemy is required for SQL support.)�extrarr�s r6r�zSQLAlchemyEngine.__init__s)��"� � I� � � � � � r8TNr�r�r�r�r�r�r�r�c  ���ddlm}  |�||���S#| j$rB} d} t | j��} t j| | ��rtd��| �| �d} ~ wwxYw)Nr)�exc)r�r�zg(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?# )|inf can not be used with MySQLzinf cannot be used with MySQL) r�r�r�StatementErrorr��orig�re�searchrO)r�r�r�r�r�r�r�r�r�r�r�r6�msg�err_texts r6r�zSQLAlchemyEngine.insert_recordss��� #�"�"�"�"�"� ��<�<�)�F�<�C�C� C���!� � � �0�C��3�8�}�}�H��y��h�'�'� K� �!@�A�A�s�J��I����� ���s�� A+�=A&�&A+r�r�r�)r�r�r�r�r�r�r8r6r�r�sO������ � � � �04�� $��������r8r�c�0�|dkrtd��}|dkrYtg}d}|D]:} |��cS#t$r}|dt|��zz }Yd}~�3d}~wwxYwtd|�����|dkrt��St d���) zreturn our implementationr�z io.sql.engine�z - Nz�Unable to find a usable engine; tried using: 'sqlalchemy'. A suitable version of sqlalchemy is required for sql I/O support. Trying to import the above resulted in these errors:r�z*engine must be one of 'auto', 'sqlalchemy')rr�r�r�rO)r��engine_classes� error_msgs� engine_classr6s r6� get_enginer�"s��� �����O�,�,�� ����*�+��� �*� 1� 1�L� 1�#�|�~�~�%�%�%��� 1� 1� 1��g��C���0�0� � � � � � ����� 1����� � �  � � � � ������!�!�!� �A� B� B�Bs� 7� A �A�A c��eZdZdZ d<d=d �Zd>d �Zed ���Zd?d@d�Z dAdBd�Z e dCdDd"���Z dAdEd#�Z e Z dFdGd+�ZdHd,�Z dIdJd1�Zed2���Zd?dKd3�Zd?dLd5�Zd?dMd6�Z dNdOd;�ZdS)Pr�aa This class enables conversion between DataFrame and SQL databases using SQLAlchemy to handle DataBase abstraction. Parameters ---------- con : SQLAlchemy Connectable or URI string. Connectable to connect with the database. Using SQLAlchemy makes it possible to use any DB supported by that library. schema : string, default None Name of SQL schema in database to write to (if database flavor supports this). If None, use default schema (default). need_transaction : bool, default False If True, SQLDatabase will create a transaction. NFr�r�r�r:rir�c��ddlm}ddlm}ddlm}t ��|_t|t��r*||��}|j� |j ��t||��r,|j� |� ����}|r@|���s,|j� |�����||_||���|_d|_dS)Nr)� create_engine)r�rsr�F)r�r��sqlalchemy.enginer�r�rtrrNrKr��callback�dispose� enter_context�connect�in_transaction�beginr�r�returns_generator)r�r�r�r�r�r�rts r6r�zSQLDatabase.__init__Ts�� -�,�,�,�,�,�,�,�,�,�,�,�.�.�.�.�.�.� $�+�+��� �c�3� � � 2��-��$�$�C� �O� $� $�S�[� 1� 1� 1� �c�6� "� "� ?��/�/�/�� � � � �>�>�C� � 7�C�$6�$6�$8�$8� 7� �O� )� )�#�)�)�+�+� 6� 6� 6�����H�F�+�+�+�� �!&����r8c�J�|js|j���dSdSr�)r�rN�closer�s r6r�zSQLDatabase.__exit__ks1���%� $� �O� !� !� #� #� #� #� #� $� $r8c#��K�|j���s=|j���5|jV�ddd��dS#1swxYwYdS|jV�dSr�)r�r�r�r�s r6rzSQLDatabase.run_transactionos������x�&�&�(�(� �����!�!� � ��h���� � � � � � � � � � � � ���� � � � � � ��(�N�N�N�N�Ns� A � A�Ar�r�c��|�gn|g}t|t��r|jj|g|�R�S|jj|g|�R�S)z,Simple passthrough to SQLAlchemy connectable)rKr�r��exec_driver_sqlr�)r�r�r�r�s r6r�zSQLDatabase.executews]���^�r�r�&��� �c�3� � � 8�+�4�8�+�C�7�$�7�7�7� 7��t�x���+�d�+�+�+�+r8Trer�r�r�r�rfr�r�rgrhr�c ���|j�|j|gd���t||||���} |�d|_| �|j|||||���S)ap Read SQL database table into a DataFrame. Parameters ---------- table_name : str Name of SQL table in database. index_col : string, optional, default: None Column to set as index. coerce_float : bool, default True Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point. This can result in loss of precision. parse_dates : list or dict, default: None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg}``, where the arg corresponds to the keyword arguments of :func:`pandas.to_datetime`. Especially useful with databases without native Datetime support, such as SQLite. columns : list, default: None List of column names to select from SQL table. schema : string, default None Name of SQL schema in database to query (if database flavor supports this). If specified, this overwrites the default schema of the SQL database object. chunksize : int, default None If specified, return an iterator where `chunksize` is the number of rows to include in each chunk. dtype_backend : {'numpy_nullable', 'pyarrow'}, default 'numpy_nullable' Back-end data type applied to the resultant :class:`DataFrame` (still experimental). Behaviour is as follows: * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame` (default). * ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype` DataFrame. .. versionadded:: 2.0 Returns ------- DataFrame See Also -------- pandas.read_sql_table SQLDatabase.read_query T)r��only�views)r�r�N)rfr5rnr�rg)r�reflectr�r�r�rarN) r�r�r�rfr5rnr�r�rgr�s r6r�zSQLDatabase.read_table~sy��~ � ���t�x�z�l�$��G�G�G���T��6�J�J�J�� � �%)�D� "��z�z� �O�%�#���'� � � � r8rNrr�rQr�c #��K�d} |5 |�|��} | s| stg||||||���V�nd} t| ||||||���V��M ddd��dS#1swxYwYdS)�+Return generator through chunked result setFT�r�rfr5rQrgN)rRr�) rrNr�rnr�rfr5rQrgrUrws r6rVzSQLDatabase._query_iterator�s����� � � � � ��'�'� �2�2��� �(� �*��#�&/�)5�(3�"'�*7�������� $� �"���'�!-� +��"/�������! �� � � � � � � � � � � � ���� � � � � � s�AA$�$A(�+A(c ��|�||��} | ���} |�*d|_|�| |j|| |||||�� � S| ���} t | | |||||���} | S)a� Read SQL query into a DataFrame. Parameters ---------- sql : str SQL query to be executed. index_col : string, optional, default: None Column name to use as index for the returned DataFrame object. coerce_float : bool, default True Attempt to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point, useful for SQL result sets. params : list, tuple or dict, optional, default: None List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249's paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={'name' : 'value'} parse_dates : list or dict, default: None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg dict}``, where the arg dict corresponds to the keyword arguments of :func:`pandas.to_datetime` Especially useful with databases without native Datetime support, such as SQLite. chunksize : int, default None If specified, return an iterator where `chunksize` is the number of rows to include in each chunk. dtype : Type name or dict of columns Data type for data or columns. E.g. np.float64 or {'a': np.float64, 'b': np.int32, 'c': 'Int64'} .. versionadded:: 1.3.0 Returns ------- DataFrame See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql NTr�)r�r�r�rVrNr]r�) r�r�r�rfr5r�r�rQrgrrnrwr�s r6r�zSQLDatabase.read_query�s���r���c�6�*�*���+�+�-�-�� � �%)�D� "��'�'������#�)�'��+�(� � � ��?�?�$�$�D� ���#�)�'��+����E��Lr8r�r�r�r�r�r�r�c ����r�t���s�fd�|D���ntt����ddlm}����D]N\} } t | t��rt| |��r�+t | |��r�<td| �d����t|||||||����} | � ��| S)z_ Prepares table in the database for data insertion. Creates it if needed, etc. c���i|]}|���Sr�r��r rarQs �r6� <dictcomp>z*SQLDatabase.prep_table.<locals>.<dictcomp>`����?�?�?�X��5�?�?�?r8r)� TypeEnginez The type of z is not a SQLAlchemy type)r�r�r�r�r�rQ) rrrLr�r�r\rKrRrPrOr�r) r�r�r�r�r�r�r�rQr�rV�my_typer�s ` r6� prep_tablezSQLDatabase.prep_tableKs��� � T���&�&� *�@�?�?�?��?�?�?����T�5�)�)�� 3� 3� 3� 3� 3� 3� %� � � � � T� T� ��W��g�t�,�,�T��G�Z�1P�1P�T�����4�4�T��$�%R�C�%R�%R�%R�S�S�S�� � ����#���  �  �  �� � � ����� r8c�6�|���s�|���snddlm}||j��}|�|p |jj���}||vr4d|�d�}tj |tt�����dSdSdSdS)zv Checks table name for issues with case-sensitivity. Method is called after data is inserted. r��inspectr�zThe provided table name 'z�' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.r�N) �isdigit�islowerr�rr��get_table_namesrr�r�r�r�r)r�r�r��sqlalchemy_inspect�insp� table_namesr�s r6�check_case_sensitivez SQLDatabase.check_case_sensitive{s����|�|�~�~� �d�l�l�n�n� � A� @� @� @� @� @�%�%�d�h�/�/�D��.�.�f�6P�� �@P�.�Q�Q�K��;�&�&�(��(�(�(�� � ���/�1�1������� � � � �'�&r8r�r�r�r�c ���t| ��} |�|||||||���} | jd| |j|||||| d�| ��}|�||���|S)a Write records stored in a DataFrame to a SQL database. Parameters ---------- frame : DataFrame name : string Name of SQL table. if_exists : {'fail', 'replace', 'append'}, default 'fail' - fail: If table exists, do nothing. - replace: If table exists, drop it, recreate it, and insert data. - append: If table exists, insert data. Create if does not exist. index : boolean, default True Write DataFrame index as a column. index_label : string or sequence, default None Column label for index column(s). If None is given (default) and `index` is True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex. schema : string, default None Name of SQL schema in database to write to (if database flavor supports this). If specified, this overwrites the default schema of the SQLDatabase object. chunksize : int, default None If not None, then rows will be written in batches of this size at a time. If None, all rows will be written at once. dtype : single type or dict of column name to SQL type, default None Optional specifying the datatype for columns. The SQL type should be a SQLAlchemy type. If all columns are of the same type, one single value can be used. method : {None', 'multi', callable}, default None Controls the SQL insertion clause used: * None : Uses standard SQL ``INSERT`` clause (one per row). * 'multi': Pass multiple values in a single ``INSERT`` clause. * callable with signature ``(pd_table, conn, keys, data_iter)``. Details and a sample callable implementation can be found in the section :ref:`insert method <io.sql.method>`. engine : {'auto', 'sqlalchemy'}, default 'auto' SQL engine library to use. If 'auto', then the option ``io.sql.engine`` is used. The default ``io.sql.engine`` behavior is 'sqlalchemy' .. versionadded:: 1.3.0 **engine_kwargs Any additional kwargs are passed to the engine. )r�r�r�r�r�r�rQ)r�r�r�r�r�r�r�r�)r�r�r�)r�rr�r�r )r�r�r�r�r�r�r�r�rQr�r�r�� sql_enginer�rKs r6r�zSQLDatabase.to_sql�s���| ��'�'� ��������#��� � � ��3��2�  ����������  �  ��  �  �� �!�!�t�F�!�;�;�;��r8c��|jjSr�)r�tablesr�s r6rzSQLDatabase.tables�s ���y��r8c�r�ddlm}||j��}|�||p |jj��S)Nrr)r�rr�r�rr�)r�r�r�rrs r6r�zSQLDatabase.has_table�sD��<�<�<�<�<�<�!�!�$�(�+�+���~�~�d�F�$>�d�i�.>�?�?�?r8r)c��ddlm}m}|p |jj}|||j|j|���}|jD]#}t|j|��r d|j_ �$|S)Nr)�Numericr))� autoload_withr�F) r�rr)rr�r�rnrKrR� asdecimal)r�r�r�rr)�tbl�columns r6r�zSQLDatabase.get_table�s��� � � � � � � � � �+�4�9�+���e�J�� ���&�Q�Q�Q���k� .� .�F��&�+�w�/�/� .�(-�� �%��� r8c��|p |jj}|�||��r�|j�|j|g|d���|���5|�||���|j���ddd��n #1swxYwY|j���dSdS)NT)r�r�r�r�r�) rr�r�r�r�rr��drop�clear�r�r�r�s r6rzSQLDatabase.drop_tables���+�4�9�+�� �>�>�*�f� -� -� � �I� � ��X�Z�L��t� � � � ��%�%�'�'� G� G����z�6�2�2�7�7�T�X�7�F�F�F� G� G� G� G� G� G� G� G� G� G� G���� G� G� G� G� �I�O�O� � � � � �  � s�0B�B� Br�r r�r�c �p�t|||d|||���}t|�����S�NF)r�r�r�rQr�)r�r�r��r�r�r�r�rQr�r�s r6r�zSQLDatabase._create_sql_schemasI��� � ������ � � ���5�#�#�%�%�&�&�&r8rh)r�r�r�r:rir�r�r�r�r�r��NTNNre) rNrr�r�rfr:rQr�rgrhr�)r�TNNN) r�r�r�r�r�r�rQr�rir��r�r�r�r�rir�r��r�r�r�r�r�r:r�r�r�r�rQr�r�r�r�r�rir�r�)r�r�r�r�rir)�r�r�r�r�rir�r�r�)r�r�r�r�r�r�rrr�r�� staticmethodrVr�r�rr r��propertyrr�r�rr�r�r8r6r�r�Bs:��������$HM�'�'�'�'�'�.$�$�$�$�����^��,�,�,�,�,�-1�!���!� $�9@�J �J �J �J �J �X� �!��!%�9@�&�&�&�&��\�&�V-1�!��� $�!%�9@�T�T�T�T�T�l�H� ;A�/3���!%�.�.�.�.�.�`����B;A���!� $�!%�59��W�W�W�W�W�r� � ��X� �@�@�@�@�@� � � � � ������"&�!%�!� '�'�'�'�'�'�'r8r�c��eZdZdZd-d�Zed���Zd.d/d �Z d0d1d�Z d0d2d�Z e Z d3d4d&�Z d.d5d'�Z d6d7d,�Z dS)8r�z� This class enables conversion between DataFrame and SQL databases using ADBC to handle DataBase abstraction. Parameters ---------- con : adbc_driver_manager.dbapi.Connection rir�c��||_dSr��r��r�r�s r6r�zADBCDatabase.__init__3� ������r8c#��K�|j���5} |V�n(#t$r|j����wxYw|j���ddd��dS#1swxYwYdSr�)r��cursorr��rollback�commit�r��curs r6rzADBCDatabase.run_transaction6s����� �X�_�_� � � �#� �� � � � ��� � � ���!�!�#�#�#�� ���� �H�O�O� � � �  � � � � � � � � � � � ���� � � � � � s%�A1�#�A1�%A�A1�1A5�8A5Nr�r�c ��t|t��std���|�gn|g}|j���} |j|g|�R�|S#t $rc} |j���n+#t $r}td|�d|�d���}||�d}~wwxYwtd|�d|����}||�d}~wwxYw�Nz/Query must be a string unless using sqlalchemy.zExecution failed on sql: � z unable to rollbackzExecution failed on sql 'z': � rKr�rNr�r)r�r�r*r�r�r�r�r�r-r�� inner_exc�exs r6r�zADBCDatabase.execute@����#�s�#�#� O��M�N�N� N��^�r�r�&����h�o�o���� � �C�K�� #�d� #� #� #� #��J��� � � � (���!�!�#�#�#�#��� (� (� (�"�P��P�P�s�P�P�P�����i�'�����  (���� �H�3�H�H�3�H�H�I�I�B��#� ����� ����6�A� C�!A;�:B>�; B#�B�B#�#B>�>CTrer�r�r�r�rfr:r�r�r�r�rgrhr�c ��|durtd���|rtd���|r9|rt|��} ng} | |z} d�d�| D����} nd} |r d| �d|�d |��} nd| �d|��} |d krt} n=|d krd d lm}|��j} n!t��rd dlm}|��nd} |j � ��5}|� | ��|� ��� | ���}ddd��n #1swxYwYt|||���S)a9 Read SQL database table into a DataFrame. Parameters ---------- table_name : str Name of SQL table in database. coerce_float : bool, default True Raises NotImplementedError parse_dates : list or dict, default: None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg}``, where the arg corresponds to the keyword arguments of :func:`pandas.to_datetime`. Especially useful with databases without native Datetime support, such as SQLite. columns : list, default: None List of column names to select from SQL table. schema : string, default None Name of SQL schema in database to query (if database flavor supports this). If specified, this overwrites the default schema of the SQL database object. chunksize : int, default None Raises NotImplementedError dtype_backend : {'numpy_nullable', 'pyarrow'}, default 'numpy_nullable' Back-end data type applied to the resultant :class:`DataFrame` (still experimental). Behaviour is as follows: * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame` (default). * ``"pyarrow"``: returns pyarrow-backed nullable :class:`ArrowDtype` DataFrame. .. versionadded:: 2.0 Returns ------- DataFrame See Also -------- pandas.read_sql_table SQLDatabase.read_query T�2'coerce_float' is not implemented for ADBC drivers�/'chunksize' is not implemented for ADBC drivers�, c3�"K�|] }d|�d�V�� dS)�"Nr�)r �xs r6rEz*ADBCDatabase.read_table.<locals>.<genexpr>�s*����#@�#@��H��H�H�H�#@�#@�#@�#@�#@�#@r8�*zSELECT z FROM �.rk�numpy_nullabler��_arrow_dtype_mapping)�arrow_string_types_mapperN�� types_mapper)r�r5)r�r$�joinr�pandas.io._utilrB�getrrCr�r)r��fetch_arrow_table� to_pandasr�)r�r�r�rfr5rnr�r�rg� index_select� to_select� select_listr�mappingrBrCr-r~s r6r�zADBCDatabase.read_tableTs��t �t� #� #�%�D��� � � Y�%�&W�X�X� X� � �� "�.�y�9�9� � �!� �$�w�.�I��)�)�#@�#@�i�#@�#@�#@�@�@�K�K��K� � =�E�[�E�E��E�E��E�E�D�D�<�[�<�<� �<�<�D� �I� %� %� �G�G� �.� .� .� <� <� <� <� <� <�*�*�,�,�0�G�G� '� )� )� � A� A� A� A� A� A� %� %� '� '� '� '��G� �X�_�_� � � I�#� �K�K�� � � ��&�&�(�(�2�2��2�H�H�B� I� I� I� I� I� I� I� I� I� I� I���� I� I� I� I�!� ��#� � � � s�>D&�&D*�-D*rQr�c ���|durtd���|rtd���|rtd���|dkrt} n|dkrddlm} | ��j} nd } |j���5} | �|��| ���� | � ��} d d d ��n #1swxYwYt| |||� ��S) a� Read SQL query into a DataFrame. Parameters ---------- sql : str SQL query to be executed. index_col : string, optional, default: None Column name to use as index for the returned DataFrame object. coerce_float : bool, default True Raises NotImplementedError params : list, tuple or dict, optional, default: None Raises NotImplementedError parse_dates : list or dict, default: None - List of column names to parse as dates. - Dict of ``{column_name: format string}`` where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps. - Dict of ``{column_name: arg dict}``, where the arg dict corresponds to the keyword arguments of :func:`pandas.to_datetime` Especially useful with databases without native Datetime support, such as SQLite. chunksize : int, default None Raises NotImplementedError dtype : Type name or dict of columns Data type for data or columns. E.g. np.float64 or {'a': np.float64, 'b': np.int32, 'c': 'Int64'} .. versionadded:: 1.3.0 Returns ------- DataFrame See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql Tr8z,'params' is not implemented for ADBC driversr9rkr@rrANrD)r�r5rQ) r�rrGrBrHr�r)r�rIrJr�) r�r�r�rfr5r�r�rQrgrNrBr-r~s r6r�zADBCDatabase.read_query�sn��f �t� #� #�%�D��� � � V�%�&T�U�U� U� � Y�%�&W�X�X� X� �I� %� %� �G�G� �.� .� .� <� <� <� <� <� <�*�*�,�,�0�G�G��G� �X�_�_� � � I�#� �K�K�� � � ��&�&�(�(�2�2��2�H�H�B� I� I� I� I� I� I� I� I� I� I� I���� I� I� I� I�!� ��#��  � � � s�;>C�C � C r�r�r�r�r�r�r�r�r�c  �0�|rtd���|rtd���|rtd���| rtd���| dkrtd���|r|�d|��} n|} d} |�||��rq|d krtd | �d ����|d krJ|j���5}|�d | ����ddd��n #1swxYwYn|dkrd} ddl} |j�||���}n"#|j $r}td��|�d}~wwxYw|j���5}|� ||| |���}ddd��n #1swxYwY|j� ��|S)a� Write records stored in a DataFrame to a SQL database. Parameters ---------- frame : DataFrame name : string Name of SQL table. if_exists : {'fail', 'replace', 'append'}, default 'fail' - fail: If table exists, do nothing. - replace: If table exists, drop it, recreate it, and insert data. - append: If table exists, insert data. Create if does not exist. index : boolean, default True Write DataFrame index as a column. index_label : string or sequence, default None Raises NotImplementedError schema : string, default None Name of SQL schema in database to write to (if database flavor supports this). If specified, this overwrites the default schema of the SQLDatabase object. chunksize : int, default None Raises NotImplementedError dtype : single type or dict of column name to SQL type, default None Raises NotImplementedError method : {None', 'multi', callable}, default None Raises NotImplementedError engine : {'auto', 'sqlalchemy'}, default 'auto' Raises NotImplementedError if not set to 'auto' z1'index_label' is not implemented for ADBC driversr9z+'dtype' is not implemented for ADBC driversz,'method' is not implemented for ADBC driversr�z1engine != 'auto' not implemented for ADBC driversr?rr�rrr�� DROP TABLE Nrsr)�preserve_indexzdatatypes not supported)r�rw�mode�db_schema_name) r�r�rOr�r)r�rkr)rl�ArrowNotImplementedError� adbc_ingestr+)r�r�r�r�r�r�r�r�rQr�r�r�r�rSr-rzrr�rKs r6r�zADBCDatabase.to_sql s���V � �%�C��� � � Y�%�&W�X�X� X� � U�%�&S�T�T� T� � V�%�&T�U�U� U� �V� � �%�C��� � � �"�+�+�T�+�+�J�J��J� �� �>�>�$�� '� '� ��F�"�"� �!H�:�!H�!H�!H�I�I�I��i�'�'��X�_�_�&�&�<�#��K�K� :�j� :� :�;�;�;�<�<�<�<�<�<�<�<�<�<�<����<�<�<�<���h�&�&������� A��(�&�&�u�U�&�C�C�C�C���*� A� A� A��6�7�7�S� @����� A�����X�_�_� � � �#� �_�_��c��V�-���N� � � � � � � � � � � ���� � � � � ��������s<�6C�C�"C�4D� D0�D+�+D0� E2�2E6�9E6c���|j�||������}|d���D])}|s�|D]!}|s�|dD]}|d|krdS��"�*dS)N)�db_schema_filter�table_name_filter�catalog_db_schemas�db_schema_tablesr�TF)r��adbc_get_objects�read_all� to_pylist)r�r�r�r�catalog_schema� schema_record� table_records r6r�zADBCDatabase.has_tablej s����x�(�(�#�t�)� � � �(�*�*� �#�#7�8�B�B�D�D� $� $�N�!� ��!/� $� $� �$���$1�2D�$E�$�$�L�#�L�1�T�9�9�#�t�t�t�t�:�$�  $��ur8r�r r�r�c� �td���)Nznot implemented for adbcr�r�s r6r�zADBCDatabase._create_sql_schema| s��"�"<�=�=�=r8r�r�r�r�r�r�r�rr�r�r�)r�r�r�r�r�rrr�r�r�r�r�r�r�r�r8r6r�r�)sP����������������^�������.-1�!���!� $�9@�e �e �e �e �e �T-1�!��� $�!%�9@�O �O �O �O �O �b�H� ;A���!� $�!%�59��Z�Z�Z�Z�Z�x�����,"&�!%�!� >�>�>�>�>�>�>r8r��TEXT�REAL�INTEGERr��DATE�TIME)rmrTrUr rr r�r,c��� t|���dd���d��}n&#t$r}t d|�d���|�d}~wwxYw|S)Nzutf-8�strictz%Cannot convert identifier to UTF-8: 'r�)r��encode�decode� UnicodeErrorrO)r��unamer6s r6�_get_unicode_namern� sw��S��D� � � � ��(�3�3�:�:�7�C�C���� �S�S�S��H��H�H�H�I�I�s�R�����S���� �Ls�69� A�A�Ac���t|��}t|��std���|�d��}|dkrtd���d|�dd��zdzS)Nz$Empty table or column name specified�rz%SQLite identifier cannot contain NULsr<z"")rnrvrO�findr�)r�rm� nul_indexs r6�_get_valid_sqlite_namers� sv�� �d� #� #�E� �u�:�:�A��?�@�@�@�� � �6�"�"�I��A�~�~��@�A�A�A� ����s�D�)�)� )�C� /�/r8c�`��eZdZdZd�fd� Zdd�Zdd�Zdd�Zdd �Zdd �Z dd �Z d�Z d�Z �xZ S)� SQLiteTablezw Patch the SQLTable for fallback support. Instead of a table variable just use the Create Table statement. rir�c�b��t��j|i|��|���dSr�)�superr��_register_date_adapters)r�r��kwargs� __class__s �r6r�zSQLiteTable.__init__� s7��������$�)�&�)�)�)� �$�$�&�&�&�&�&r8c�(�ddl}d d�}d�}d�}|�t|��|�t|��|�t|��d�}d�}|�d |��|�d |��dS) Nrrir�c�L�|jd�d|jd�d|jd�d|jd��S)N�02d�:r?�06d)�hour�minute�second� microsecond)�ts r6� _adapt_timez8SQLiteTable._register_date_adapters.<locals>._adapt_time� s:���f�T�T�T�1�8�T�T�T�!�(�T�T�T���T�T�T� Tr8c�*�|���Sr��� isoformat��vals r6�<lambda>z5SQLiteTable._register_date_adapters.<locals>.<lambda>� s��S�]�]�_�_�r8c�,�|�d��S)N� r�r�s r6r�z5SQLiteTable._register_date_adapters.<locals>.<lambda>� s�����s�);�);�r8c�N�tj|�����Sr�)r� fromisoformatrkr�s r6r�z5SQLiteTable._register_date_adapters.<locals>.<lambda>� s��4�#5�c�j�j�l�l�#C�#C�r8c�N�tj|�����Sr�)r r�rkr�s r6r�z5SQLiteTable._register_date_adapters.<locals>.<lambda>� s���(>�s�z�z�|�|�(L�(L�r8r� timestampr�)r��register_adapterr rr �register_converter)r�r�r��adapt_date_iso�adapt_datetime_iso� convert_date�convert_timestamps r6rxz#SQLiteTable._register_date_adapters� s��� ���� U� U� U� U�5�4��;�;��� � ��{�3�3�3�� � ��~�6�6�6�� � ��+=�>�>�>�C�C� �L�L���"�"�6�<�8�8�8��"�"�;�0A�B�B�B�B�Br8r�c�P�td�|j����S)Nz; )r�rFr�r�s r6r�zSQLiteTable.sql_schema� s���5�:�:�d�j�)�)�*�*�*r8c��|j���5}|jD]}|�|��� ddd��dS#1swxYwYdSr�)r�rr�r�)r�rrs r6rzSQLiteTable._execute_create� s��� �[� (� (� *� *� #�d�� � #� #��� � �T�"�"�"�"� #� #� #� #� #� #� #� #� #� #� #� #� #���� #� #� #� #� #� #s� A�A �A �num_rowsr�c��� � �ttt|jj����}d}t � |j�)|jddd�D]}|�d|���� fd�|D��}d�|��}d�|gt|��z��� d�� fd�t|��D����}d� |j ���d|�d |��}|S) N�?r\rc�&��g|] }�|����Sr�r�)r r�escapes �r6rz0SQLiteTable.insert_statement.<locals>.<listcomp>� s!���>�>�>�f�6�6�&�>�>�>�>�>r8�,c���g|]}d��d��� S)�(�)r�)r r:� row_wildcardss �r6rz0SQLiteTable.insert_statement.<locals>.<listcomp>� s%���L�L�L�q�2�-�2�2�2�L�L�Lr8z INSERT INTO � (z ) VALUES ) rpr%r�r�rnrsr�rrFrvrur�) r�r�r#�wldr_�bracketed_names� col_names� wildcards�insert_statementr�r�s @@r6r�zSQLiteTable.insert_statement� s �����S��d�j�0�1�1�2�2����'�� �:� !��z�$�$�B�$�'� %� %��� � �Q��$�$�$�$�>�>�>�>��>�>�>���H�H�_�-�-� ����#���U���!3�4�4� ��H�H�L�L�L�L�E�(�O�O�L�L�L�M�M� � O�6�6�$�)�,�,� O� O� � O� O�I� O� O� � �r8c��t|��}|�|�d���|��|jS)NrA�r�)rp� executemanyr�r)r�rr�rr9s r6rzSQLiteTable._execute_insert� s<����O�O� � ����.�.��.�:�:�I�F�F�F��}�r8c��t|��}d�|D��}|�|�t|�����|��|jS)Nc��g|] }|D]}|��� Sr�r�)r r r=s r6rz5SQLiteTable._execute_insert_multi.<locals>.<listcomp>� s%��>�>�>��#�>�>�Q�!�>�>�>�>r8r�)rpr�r�rvr)r�rr�rr9�flattened_datas r6rz!SQLiteTable._execute_insert_multi� sS����O�O� �>�>�y�>�>�>�� � � �T�*�*�C� �N�N�*�C�C�^�T�T�T��}�r8c�0� �|�|j��}t� � fd�|D��}|j�zt |j��rft |j��s |jg}n|j}d�� fd�|D����}|�d|j�d|�d���|j r |j dz}nd }d |z� |j��zd zd �|��zd zg}d�|D��}t |��r�d�|��}d�� fd�|D����}|�d� d|jzdz|z��zdz� |j��zdz|zdz��|S)z� Return a list of SQL statements that creates a table reflecting the structure of a DataFrame. The first entry will be a CREATE TABLE statement while the rest will be CREATE INDEX statements. c�:��g|]\}}}�|��dz|z��S)r�r�)r �cname�ctyper:r�s �r6rz3SQLiteTable._create_table_setup.<locals>.<listcomp> s>��� � � �,;�E�5�!�F�F�5�M�M�C� �%� '� � � r8Nr:c�&��g|] }�|����Sr�r��r rZr�s �r6rz3SQLiteTable._create_table_setup.<locals>.<listcomp> s!���";�";�";��6�6�!�9�9�";�";�";r8z CONSTRAINT z_pk PRIMARY KEY (r�r?r�z CREATE TABLE z ( z, z )c��g|] \}}}|�|�� Sr�r�)r r�r:rws r6rz3SQLiteTable._create_table_setup.<locals>.<listcomp> s$��V�V�V�/�U�A�x�X�V�5�V�V�Vr8r:r�c�&��g|] }�|����Sr�r�r�s �r6rz3SQLiteTable._create_table_setup.<locals>.<listcomp>" s!���!=�!=�!=��&�&��)�)�!=�!=�!=r8z CREATE INDEX �ix_zON r�) ro�_sql_type_namersr�rvrrFrsr�r�) r�rl�create_tbl_stmtsr�� cnames_br� schema_name� create_stmts�ix_cols�cnamesr�s @r6r�zSQLiteTable._create_table_setup� s)��� "&�!A�!A�$�BU�!V�!V��'�� � � � �?U� � � �� �9� �S���^�^� ��� �*�*� !�� �{����y��� � �";�";�";�";�d�";�";�";�<�<�I� � #� #�F�d�i�F�F�)�F�F�F� � � � �;� ��+��+�K�K��K� �� ��f�T�Y��� �� ��l�l�+�,�,�  -� �  � � �W�V�3I�V�V�V�� �w�<�<� ��X�X�g�&�&�F����!=�!=�!=�!=�W�!=�!=�!=�>�>�I� � � ���&����*�S�0�6�9�:�:�;����&���#�#�$�� � � � � � � � ��r8c��|jpi}t|��r+tt|��}|j|vr ||jSt j|d���}|dkr+tjdtt�����d}n'|dkrd}n|d krd }n|d krtd ���|tvrd }t|S) NTr�r�r�r�rUr�r �emptyrmr�r�) rQrrrLr�rr�r�r�r�rrO� _SQL_TYPES)r�rVrQr�s r6r�zSQLiteTable._sql_type_name/ s����*�*��� �� � � '���u�%�%�E��x�5� � ��S�X��&��?�3�t�4�4�4�� �}� $� $� �M�L��+�-�-�  � � � � !�H�H� �� %� %�!�H�H� �� � ��H�H� �� "� "��>�?�?� ?� �:� %� %��H��(�#�#r8r�r�)r�r�rir�)rir�)r�r�r�r�r�rxr�rr�rrr�r�� __classcell__)rzs@r6ruru� s���������� '�'�'�'�'�'� C�C�C�C�:+�+�+�+�#�#�#�#�  � � � �&���� ���� 1�1�1�f $� $� $� $� $� $� $r8ruc���eZdZdZd*d�Zed���Zd+d,d �Ze d-d.d���Z d/d0d�Z d�Z d1d2d"�Z d+d3d%�Z d+d4d'�Zd+d5d(�Z d6d7d)�ZdS)8r�z� Version of SQLDatabase to support SQLite connections (fallback without SQLAlchemy). This should only be used internally. Parameters ---------- con : sqlite connection object rir�c��||_dSr�r%r&s r6r�zSQLiteDatabase.__init__] r'r8c#�&K�|j���} |V�|j���n(#t$r|j����wxYw |���dS#|���wxYwr�)r�r)r+r�r*r�r,s r6rzSQLiteDatabase.run_transaction` s������h�o�o���� ��I�I�I� �H�O�O� � � � ��� � � � �H� � � � � � � ���� � �I�I�K�K�K�K�K��C�I�I�K�K�K�K���s�;�A:�%A � A:�:BNr�r�c ��t|t��std���|�gn|g}|j���} |j|g|�R�|S#t $rc} |j���n+#t $r}td|�d|�d���}||�d}~wwxYwtd|�d|����}||�d}~wwxYwr/r1r2s r6r�zSQLiteDatabase.executel r5r6Trer�r�rfr:rQr�rgrhc #�RK�d} |�|��} t| ��tkrt| ��} | sJ|���|s2t jg||���} |r| �|��} | V�dSd}t| ||||||���V���)r�FTrPr�N) rRrR�tuplerpr�r rSr�r�) r)r�rnr�rfr5rQrgrUrwrs r6rVzSQLiteDatabase._query_iterator� s������ � ��#�#�I�.�.�D��D�z�z�U�"�"��D�z�z��� �� � ����$�!�&�3��G�,����F��6�!'���u�!5�!5�� �L�L�L��� �M����#�)�'��+���� � � �! r8r�r�c ��|�||��} d�| jD��} |�|�| || |||||���S|�| ��} | ���t | | |||||���} | S)Nc��g|] }|d�� S)rr�)r �col_descs r6rz-SQLiteDatabase.read_query.<locals>.<listcomp>� s��B�B�B�8�8�A�;�B�B�Br8r�)r�� descriptionrV�_fetchall_as_listr�r�) r�r�r�rfr5r�r�rQrgr)rnrwr�s r6r�zSQLiteDatabase.read_query� s������c�6�*�*��B�B�v�/A�B�B�B�� � ��'�'����#�)�'��+�(� � � ��)�)�&�1�1�D� �L�L�N�N�N� ���#�)�'��+����E��Lr8c�v�|���}t|t��st|��}|Sr�)r]rKrp)r�r-rs r6r�z SQLiteDatabase._fetchall_as_list� s1���������&�$�'�'� "��&�\�\�F�� r8r�r�r�r�r�r�r�r�r�c �v���rwt���s�fd�|D���ntt��������D]/\} } t | t ��st | �d| �d�����0t||||||����}|���|� || ��S)a@ Write records stored in a DataFrame to a SQL database. Parameters ---------- frame: DataFrame name: string Name of SQL table. if_exists: {'fail', 'replace', 'append'}, default 'fail' fail: If table exists, do nothing. replace: If table exists, drop it, recreate it, and insert data. append: If table exists, insert data. Create if it does not exist. index : bool, default True Write DataFrame index as a column index_label : string or sequence, default None Column label for index column(s). If None is given (default) and `index` is True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex. schema : string, default None Ignored parameter included for compatibility with SQLAlchemy version of ``to_sql``. chunksize : int, default None If not None, then rows will be written in batches of this size at a time. If None, all rows will be written at once. dtype : single type or dict of column name to SQL type, default None Optional specifying the datatype for columns. The SQL type should be a string. If all columns are of the same type, one single value can be used. method : {None, 'multi', callable}, default None Controls the SQL insertion clause used: * None : Uses standard SQL ``INSERT`` clause (one per row). * 'multi': Pass multiple values in a single ``INSERT`` clause. * callable with signature ``(pd_table, conn, keys, data_iter)``. Details and a sample callable implementation can be found in the section :ref:`insert method <io.sql.method>`. c���i|]}|���Sr�r�r�s �r6r�z)SQLiteDatabase.to_sql.<locals>.<dictcomp> r�r8r�z) not a string)r�r�r�r�rQ) rrrLr\rKr�rOrurr)r�r�r�r�r�r�r�r�rQr�r�r�rVr�r�s ` r6r�zSQLiteDatabase.to_sql� s����h � H���&�&� *�@�?�?�?��?�?�?����T�5�)�)�� %� � � � � H� H� ��W�!�'�3�/�/�H�$��%F�%F�w�%F�%F�%F�G�G�G�H�� � ����#�� � � �� � � �����|�|�I�v�.�.�.r8r�r�c��d}d|�d�}t|�||g�������dkS)Nr�z� SELECT name FROM sqlite_master WHERE type IN ('table', 'view') AND name=z ; r)rvr�r])r�r�r�r��querys r6r�zSQLiteDatabase.has_table% sS���� �� � � ���4�<�<���v�.�.�7�7�9�9�:�:�Q�>�>r8r�c��dSr�r�rs r6r�zSQLiteDatabase.get_table3 s���tr8c�T�dt|����}|�|��dS)NrQ)rsr�)r�r�r��drop_sqls r6rzSQLiteDatabase.drop_table6 s0��?�!7��!=�!=�?�?�� � � �X�����r8c �p�t|||d|||���}t|�����Sr)rur�r�rs r6r�z!SQLiteDatabase._create_sql_schema: sI��� � ������ � � ���5�#�#�%�%�&�&�&r8r�r�r�r)r�r�rfr:rQr�rgrhr�) rfr:r�r�rQr�rgrhrir�r�)r�r�r�r�r�r:r�r�rQr�r�r�r�r�rir�r�r rr�)r�r�rQr�r�r�rir�)r�r�r�r�r�rrr�r!rVr�r�r�r�r�rr�r�r8r6r�r�R s�������������� � ��^� ������(� �!��!%�9@�$�$�$�$��\�$�R�!��� $�!%�9@�&�&�&�&�&�P��� ���� $�!%�59��N/�N/�N/�N/�N/�` ?� ?� ?� ?� ?������������!%�!� '�'�'�'�'�'�'r8r�c��t|���5}|�|||||���cddd��S#1swxYwYdS)a] Get the SQL db table schema for the given frame. Parameters ---------- frame : DataFrame name : str name of SQL table keys : string or sequence, default: None columns to use a primary key con: ADBC Connection, SQLAlchemy connectable, sqlite3 connection, default: None ADBC provides high performance I/O with native type support, where available. Using SQLAlchemy makes it possible to use any DB supported by that library If a DBAPI2 object, only sqlite3 is supported. dtype : dict of column name to SQL type, default None Optional specifying the datatype for columns. The SQL type should be a SQLAlchemy type, or a string for sqlite3 fallback connection. schema: str, default: None Optional specifying the schema to be used in creating the table. r%)r�rQr�N)r�r�)r�r�r�r�rQr�r�s r6� get_schemar�N s���: �s� #� #� #� �z��,�,� �4�d�%��-� � � � � � � � � � � � � � ���� � � � � � s �8�<�<)FN)r9r:r;r<)Tre)rfr:rgrhrir r)rfr:rQr�rgrh)r~r rQr�rgrhrir r�).......)r�r�r�r�r5r�rnr�r�r�rgr�rir )r�r�r�r�r5r�rnr�r�r�rgr�rir�)r�r�r�r�r�r�rfr:r5r�rnr�r�r�rgr�rir�)r�r�r�r�r5r�r�r�rQr�rgr�rir )r�r�r�r�r5r�r�r�rQr�rgr�rir�)r�r�rfr:r�r�r5r�r�r�rQr�rgr�rir�).......N) r�r�rnr�r�r�rgr�rQr�rir ) r�r�rnr�r�r�rgr�rQr�rir�)r�r�rfr:rnr�r�r�rgr�rQr�rir�)Nr�TNNNNr�)r�r�r�r�r�r�r�r:r�r�r�r�rQr�r�r�r�r�rir�)r�r�r�r�rir:rh)r�r�r�r:rir�)r�r�rir�)r�r,)NNNN)r�r�rQr�r�r�rir�)kr�� __future__r�abcrr� contextlibrrr rr � functoolsr r��typingr r rrrrr�rerS�pandas._configr� pandas._libsr�pandas.compat._optionalr� pandas.errorsrr�pandas.util._exceptionsr�pandas.util._validatorsr�pandas.core.dtypes.commonrr�pandas.core.dtypes.dtypesrr�pandas.core.dtypes.missingrr�r�pandas.core.apir r!�pandas.core.arraysr"�pandas.core.baser#�pandas.core.common�core�commonrdr$�"pandas.core.internals.constructionr%�pandas.core.tools.datetimesr&�collections.abcr'r(r�r)�sqlalchemy.sql.expressionr*r+�pandas._typingr,r-r.r/r0r1r7rXrdrr�r�r�r�r�r�r�r�r�� table_existsr�r�r�r�r�r�r�r�r�rnrsrur�r�r�r8r6�<module>r�s ���� #�"�"�"�"�"��������������������������� ������ � � � �������������������������5�5�5�5�5�5�������>�>�>�>�>�>���������5�4�4�4�4�4�7�7�7�7�7�7�����������������,�+�+�+�+�+���������������3�2�2�2�2�2�)�)�)�)�)�)� � � � � � � � � �.�.�.�.�.�.�C�C�C�C�C�C�3�3�3�3�3�3����������� !� � � � � ��������� �������������������� ���CG�M�M�M�M�M�D���0�5<� *�*�*�*�*�J���!�5<������4��!�5<� ������(/�/�/�/�F � �(+��58� #��25� � � � � �� � � �(+��58� #��25� � � � � �� �"�(,��59� $� �25�.�c?�c?�c?�c?�c?�L �),��36�58�� �25� � � � � �� � �),��36�58�� �25� � � � � �� �")-��37�59� �!�25�.�e �e �e �e �e �P �),�� ����25�!� � � � � �� � �),�� ����25�!� � � � � �� �$)-�� �� $� �25�.�!�m�m�m�m�m�h�6<��%)� �!�15��l �l �l �l �l �^0�0�0�0�0�2� � �"�"�"�"�"�"�JF�F�F�F�F�|�F�F�F�RH �H �H �H �H � �c�H �H �H �V(�(�(�(�(�(�(�(�&�����z����BC�C�C�C�@a'�a'�a'�a'�a'�)�a'�a'�a'�N[>�[>�[>�[>�[>�9�[>�[>�[>�B ���� � ���� �����0�0�0�0�$a$�a$�a$�a$�a$�(�a$�a$�a$�Hy'�y'�y'�y'�y'�Y�y'�y'�y'�~ � �!��  �  �  �  �  �  �  r8
Memory