gL idZddlmZddlmZmZddlmZmZddl 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(m)Z)m*Z*ddl+m,Z,ddl-m.Z.ddl/m0Z0ddl1m2Z2m3Z3ddl4m5Z5ddl6m7Z7ddl8m9Z9ddl:m;cmZ>ddl?m@Z@ddlAmBZBddlCmDZDer*ddlEmFZFmGZGddlHmIZIddlJmKZKmLZLddlMmNZNmOZOmPZPmQZQmRZRddl/mSZSd ZT dQ dRd!ZUd"ZV dS dTd%ZW dU dVd&ZXdddd$d' dWd(ZYdXd)ZZe dY dZd*Z[e dY d[d+Z[ddd#dddejf d\d,Z[e dY d]d-Z]e dY d^d.Z]dd#ddddejf d_d/Z]e d` dad0Z^e d` dbd1Z^dd#ddddejdf dcd2Z^ dd ded3Z_dXdfd4Z`e`Za dg dhd6ZbGd7d8e9ZcGd9d5e9eZdGd:d;ZeGd<d=eeZfdid>ZgGd?d@edZhGdAdBedZidCdDdEdFdGdHdEdIZjdjdJZkdjdKZlGdLdMecZmGdNdOedZn dk dldPZoy)mz| Collection of query wrappers / abstractions to both facilitate data retrieval and to reduce dependency on DB-specific API. ) annotations)ABCabstractmethod) ExitStackcontextmanager)datedatetimetime)partialN) TYPE_CHECKINGAnyCallableLiteralcastoverload)using_string_dtype)libimport_optional_dependency)AbstractMethodError DatabaseError)find_stack_level)check_dtype_backend) is_dict_like is_list_likeis_object_dtypeis_string_dtype)DatetimeTZDtype)isna) get_option) DataFrameSeries)ArrowExtensionArray) StringDtype) PandasObject)maybe_make_list)convert_object_array) to_datetime)arrow_table_to_pandas)IteratorMapping)Table)Select TextClause)DateTimeErrorChoicesDtypeArg DtypeBackend IndexLabelSelf)Indexc@|dus||durg}|St|ds|g}|S)z3Process parse_dates argument for read_sql functionsTF__iter__)hasattr) parse_datess S/mnt/ssd/data/python-lab/Trading/venv/lib/python3.12/site-packages/pandas/io/sql.py_process_parse_dates_argumentr:`s>dk1[E5I  [* -"m ct|tr6|jddxsd}|dk(r t|fi|St|fd|i|S|^t |jjtjs.t |jjtjrd}|dvrt|d||St|jtr t|dSt|d|| S#tt f$r|cYSwxYw) Nerrorsignores)Ddhmr?msusnscoerce)r=unitutcTrI)r=formatrI) isinstancedictpopr( TypeError ValueError issubclassdtypetypenpfloatingintegerr)colrIrKerrors r9_handle_date_columnrYks&$ '-jj4&@&LH H  "31&1137u777 > syy~~r{{ 3#)).."**5F @ @s8&cJ J  ? 3s- -s8FL L'z*   s C00DDct|}t|jD]J\}\}}t|jt s||vs( ||}|j|t||L|S#t tf$rd}Y4wxYw)zz Force non-datetime columns to be read as such. Supports both string formatted and integer timestamp columns. NrK) r: enumerateitemsrLrRrKeyErrorrOisetitemrY) data_framer8icol_namedf_colfmts r9_parse_date_columnsres 0 L Hf fllO 4K8O !(+   #6vc#J K L  i(  sA44BBTnumpyc tj|}tt|jd||}|dk(rqt d}g}|D]]}|j |d} |jdk(r| j|j} |jt| _|}|rBtttttt!||} || _| St|S)N)rR coerce_float dtype_backendpyarrowT) from_pandasstring)columns)rto_object_array_tuplesr'listTrarrayrRrrlappendr#r!rMziprangelenrm) datarmrhricontentarrayspa result_arraysarrpa_arraydfs r9_convert_arrays_to_dataframer~s ((.G ! WYY!# F  ! ' 2  @Cxxx6HyyH$$==5  !4X!> ? @ tCU3w<%8 96BC D  ))r;ct||||}|r|j|}t||}||j|}|Sz5Wrap result set of a SQLAlchemy query in a DataFrame.)r~astypere set_index)rvrm index_colrhr8rRriframes r9 _wrap_resultrsJ )w m TE  U# { 3E * Lr;)rr8rRricj|r|j|}t||}||j|}|Sr)rrer)r}rr8rRris r9_wrap_result_adbcrs: YYu  R -B \\) $ Ir;c4tjdttt dd}|1t |t |jjfr tdt|d 5}|j||cdddS#1swYyxYw) 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) warningswarn FutureWarningrrrLstrengineEnginerOpandasSQL_builderexecute)sqlconparamsr pandas_sqls r9rrs" MM 1#%  ,LJJ*S3 8I8I8P8P2Q"REFF 3 6/*!!#v.///s 2BBc yN table_namerschemarrhr8rm chunksizeris r9read_sql_tablerr;c yrrrs r9rr!rr;c Ft||tjurd}|tjusJt||d5} | j |st d|d| j |||||||} ddd | St d|d|#1swYxYw)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 rfTrrzTable z not foundrrhr8rmrriN)rr no_defaultr has_tablerP read_table) rrrrrhr8rmrrirtables r9rr0sZ &&  .. . 3v E  ##J/vj\<= =%% %#'&     6*Z8#>>#   s 9BB c yrr rrrrhrr8rrRris r9read_sql_queryrrr;c yrrrs r9rrrr;c t||tjurd}|tjusJt|5} | j ||||||||cdddS#1swYyxYw)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 rf)rrrhr8rrRriN)rrrr read_query) rrrrhrr8rrRrirs r9rrszl &&  .. . 3   :$$ %#'%      s A""A+c yrr rrrrhrr8rmrrirRs r9read_sqlrr;c yrrrs r9rr,rr;c t||tjurd}|tjusJt|5} t | t r"| j |||||||| cdddS | j|} | r!| j|||||||cdddS| j |||||||| cdddS#t$rd} YRwxYw#1swYyxYw)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 `_. 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 rf)rrrhr8rrirRNFr) rrrrrLSQLiteDatabaserr Exceptionr) rrrrhrr8rmrrirRr_is_table_names r9rr<s"B &&  .. . 3 ': j. 1((#)'#+) '' #'11#6N ((#)'#+))''<((#)'#+) ='' #"N #''s5)C3C C'C CCCCC#c "|dvrtd|dt|tr|j}nt|ts t dt ||d5} | j||f||||||| | d| cdddS#1swYyxYw) 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 `. 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 `__ or `SQLAlchemy `__ )failreplacerr'' is not valid for if_existsz9'frame' argument should be either a Series or a DataFrameTr) if_existsindex index_labelrrrRmethodrN)rPrLr"to_framer!NotImplementedErrorrto_sql) rnamerrrrrrrRrr engine_kwargsrs r9rrsj551YK'CDEE%   y )! G   3v E   z      #        s BBcjt||5}|j|cdddS#1swYyxYw)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 rN)rr)rrrrs r9rr[s3* 3v .0*##J/000s)2 PandasSQLcddl}t||js| t|St dd}t|t r | t d|3t|t |jjfr t|||St dd}|r!t||jr t|Stjdtt 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. rNrr>rz.Using URI string without sqlalchemy installed.zadbc_driver_manager.dbapizpandas 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)sqlite3rL Connectionrrr ImportErrorr Connectable SQLDatabase ADBCDatabaserr UserWarningr)rrrrradbcs r9rrws#w))*ckc""+LJJ#s 2JKK*S3 8I8I8U8U2V"W3(899 %&A( SD 30C   MM D #%  # r;ceZdZdZ d ddZdZddZddZddZddZ dd Z dd Z d dd Z d dd Z d dd ZdZdZdZ d d!dZd"dZdZy)#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. Nc ||_||_||_||_|j |||_||_||_| |_| |_ ||j|_ n5|jj|j|j |_ |jtd|dt|js tdy)NzCould not init table 'rzEmpty table name specified)rpd_sqlprefixr _index_namerrrkeysrR_create_table_setupr get_tablerPru) selfrpandas_sql_enginerrrrrrrrRs r9__init__zSQLTable.__init__s '   %%e[9  "   113DJ..tyy$++FDJ :: 5dV1=> >499~9: :r;cb|jj|j|jSr)rrrrrs r9existszSQLTable.existss!{{$$TYY <$$&8+ 1T^^$44P!QRR  "r;c |Dcgc]}tt||}}|j|jj |}|j Scc}w)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 )rMrsrrinsertrowcount)rconnr data_iterrowrvresults r9_execute_insertzSQLTable._execute_insertsN1::Ss^$::djj//148;sAc ddlm}|Dcgc]}tt||}}||jj |}|j |}|jScc}w)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)r)rrrMrsrvaluesrr) rrrrrrrvstmtrs r9_execute_insert_multizSQLTable._execute_insert_multis[ &09:Ss^$::djj!((.d#;sA'c|jI|jj}|j|j_ |j dn |j}t tt|j}t|}dg|z}t|jD]\}\}}|jjdk(rt|j t"rddl} | j&j)|jj*r"|j j-t.} n%t1j25t1j4dt6t9j:|j<j?t.} dddn|j j?} n|jjd k(rf|j } t| t"r%| j-t9jd } | jAd jCt.} n|j jCt.} t t8jDsJtG| |jHrtK| } d| | <| ||<||fS#t $r}t d||d}~wwxYw#1swY{xYw) NTinplacez!duplicate name in index/columns: Mr)rRr>)categoryrCzm8[ns]i8)&rrcopynames reset_indexrPromaprrmrur\r]rRkindrL_valuesr#rjtypesis_date pyarrow_dtypeto_numpyobjectrcatch_warningsfilterwarningsrrTasarraydt to_pydatetimeviewrndarrayrS _can_hold_nar) rtemperr column_namesncols data_listra_serryrAvalsmasks r9 insert_datazSQLTable.insert_datas@ :: !::??$D#zzDJJ  U   .::DCT\\23 L!(,fun $TZZ\2 KAx3yy~~$ckk+>?(xx'' (?(?@KK00v0>%446Q$33H}U " 366+?+?+A PAQQ  113A3&{{d$78==rxx/A=BDIIdO**62KK&&v.a, 5d1g 5,Aw$IaL? BY&&Y U #DSE!JKQTT U(QQs%J8AK8 KKKK! c@ | |j}n8|dk(r |j}n&t|r t||}nt d||j \}}t |j}|dk(ry||}n|dk(r t d||zdz}d}|jj5} t|D]I} | |zt| dz|z|  k\rn+t fd|D} || || } | @|| }E|| z }Kddd|S#1swY|SxYw)NmultizInvalid parameter `method`: rz%chunksize argument should be non-zeroc3(K|] }| ywrr).0r{end_istart_is r9 z"SQLTable.insert..`s"K#3wu#5"Ks) rrcallabler rPr"rurrrrtminrs)rrr exec_insertrrnrowschunkstotal_insertedrra chunk_iter num_insertedr(r)s @@r9rzSQLTable.insert<sQ >..K w 44K f !&$/K;F8DE E**,iDJJ A:  I !^DE E9$) [[ ( ( * 7d6] 7i-QUi/7e# "K"KL *4zB +%-)5&,6 7 7 7s1A D= DDc#vKd}|5 |j|} | s|stjg||njd}t| ||||_|j |||j '|jj|j d|j dddy#1swYyxYww)z,Return generator through chunked result set.FTrmrhr8riNr) fetchmanyr! from_recordsr~r_harmonize_columnsrr) rr exit_stackrrmrhr8ri has_read_datarvs r9_query_iteratorzSQLTable._query_iteratorjs  !'' 2('44l $ 9'< '' +=(::)JJ((T(Bjj + ! ! !sB9BB-$ B9-B62B9c ddlm}|t|dkDrx|Dcgc]}|jj|} }|j @|j dddD]+} | j d|jj| -|| } n||j} |jj| } | j} ||j| ||| |||S| j}t|| |||_ |j|||j '|jj|j d|jScc}w)Nr)select)rhr8rir5Tr)rr=rurcrrrrrr;fetchallr~rr8r)rr9rhr8rmrrir=ncolsidx sql_selectrrrvs r9readz SQLTable.readsU &  3w:: C6s Ec|dur|jjj}|1t|ts|g}t ||k7rt d||S|dk(r;d|jjvr#|jjjdgStj|jjjSt|tr|gSt|tr|Sy)NTz@Length of 'index_label' should match number of levels, which is r%r) rrnlevelsrLrorurPrmrcomfill_missing_namesrr)rrrrGs r9rzSQLTable._index_names D=jj&&..G&!+t4#.-K{#w.$,,396#"1 4::#5#55JJ$$))1y --djj.>.>.D.DEEs #7N t $Lr;c g}|jet|jD]M\}}||jjj|}|j t ||dfO|t t|jjDcgc]H}t |jj|||jjdd|fdfJc}z }|Scc}w)NTF) rr\r_get_level_valuesrrrrtrurmiloc)r dtype_mappercolumn_names_and_typesra idx_labelidx_types r9_get_column_names_and_typesz$SQLTable._get_column_names_and_typess!# :: ! )$** 5 P 9' (8(8(J(J1(MN&--s9~x.NO P 3tzz1123# ##A& 'djjooad6K)Le T#   &% # sA C3c ddlm}m}m}ddlm}|j |j}|Dcgc]\}}}||||} }}}|jUt|js|jg} n |j} || d|jdzi} | j| |jxs |jjj} |} ||j| g| d| iScc}}}w)Nr)ColumnPrimaryKeyConstraintr,MetaData)rr_pkr)rrSrTr,rrVrQ_sqlalchemy_typerrrrrrrr)rrSrTr,rVrNrtypis_indexrmrpkcrrs r9rzSQLTable._create_table_setups  /!%!A!A$BWBW!X(>  #c8 4H -  99  * {yy&E499u3DEC NN3 7 0 0 7 7zTYY>w>v>>% sC9c~t|}|jjD]v}|j} |j|}||vr! ||}t |||j|<E|j|j}|tus|tus|tur#|tu}t |||j|<n|dk(r)|tur!|j|d|j|<ntrDt|r9t!|j|r!|j|d|j|<n`|dk(r[t#||j%k(r?|t'j(dus|t*ur |j|d|j|<yy#t $rd}Y_wxYw#t,$rYwxYw)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. Nr[rJrfF)rint64)r:rrmrrrOrY _get_dtyperSr rrfloatrrrrrucountrTrRboolr^) rr8risql_colrbrcrdcol_typerIs r9r8zSQLTable._harmonize_columns s 4K@ zz))( G||H& H-{*#)(3,?vc+RDJJx( ??7<<8(4'?2#o5C+>v3+ODJJx("g-(e2C+1===+NDJJx(&('1' 8(<=+1===+NDJJx("g-#f+2O288G#44D8H/5}}XE}/R 8,M( %#"#:  s<F/F F/'D2F/ F,(F/+F,,F// F<;F<c|jxsi}t|r-tt|}|j|vr||jSt j |d}ddlm}m }m }m }m }m } m} m} m} m} |dvr# |j$j& |dS |S|dk(r&t-j.d t0t3 |S|d k(r!|jd k(r | d S| dS|dk(r|jjj5dvr| S|jjj5dvr| S|jjj5dk(r t7d|S|dk(r|S|dk(r|S|dk(r| S|dk(r t7d| S#t($rt+|dd |dcYSY|SwxYw)NTskipnar) TIMESTAMP BigIntegerBooleanDateDateTimeFloatInteger SmallIntegerTextTime) datetime64r )timezonetz timedelta64lthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.rrUfloat32) precision5rV)int8uint8int16)uint16int32uint64z1Unsigned 64 bit integer datatype is not supportedbooleanrr complexComplex datatypes not supported)rRrrrMrr infer_dtypesqlalchemy.typesrgrhrirjrkrlrmrnrorprrsAttributeErrorgetattrrrrrlowerrP)rrWrRrcrgrhrirjrkrlrmrnrorps r9rXzSQLTable._sqlalchemy_typeEs***  u%Exx5 SXX&??3t4  1 1 46699($d33)O } $ MML+-      #yyI%r**r**  "yy~~##%)CC##%%'+>>%%'83 !TUU!!  "N  K  K  ">? ? M" 43d+7$d338O  4s=F GGcddlm}m}m}m}m}m}m}t||rtSt||rtjdSt||r|jstStSt||rtSt||rtSt||rt St||r$t#rt%tj&St(S)Nr)rgrirjrkrlrmStringr])na_value)rrgrirjrkrlrmrrLr_rTrRrrr rrrarr$nanr) rsqltypergrirjrkrlrmrs r9r^zSQLTable._get_dtypes    gu %L  )88G$ $  +##" "  *O  &K  )K  (!#"BFF33 r;)NTrpandasNNNN) rrrbool | str | list[str] | Noner$Literal['fail', 'replace', 'append']rrrRDtypeArg | NonereturnNonerrrr)r list[str]rint)rz"tuple[list[str], list[np.ndarray]])NN)r int | Noner"Literal['multi'] | Callable | Nonerr)TNrf)r9rrrrhrariDtypeBackend | Literal['numpy'])TNNNrf) r9rrhrarrrirrDataFrame | Iterator[DataFrame])Nrf)rirrr)rWzIndex | Series)__name__ __module__ __qualname____doc__rrrrrrrr"rr;rErrQrr8rXr^rr;r9rrso/3:@ !%";"; - "; 8 ";";"; ";H=E 4 # "2'l!%59,,3,  ,h"9@"!"! "!  "!7"!N" $9@---  -7- )-^@ &?@9@:7:  :xEN r;rc(eZdZdZd dZd dZ d ddZe d ddZe d ddZ edddZ eddd Z e d dd Z y)rz9 Subclasses Should define read_query and to_sql. c|Srrrs r9 __enter__zPandasSQL.__enter__s r;cyrrrargss r9__exit__zPandasSQL.__exit__s r;Nc trr) rrrrhr8rmrrris r9rzPandasSQL.read_tables "!r;c yrr) rrrrhr8rrrRris r9rzPandasSQL.read_querys r;c yrr) rrrrrrrrrRrrrs r9rzPandasSQL.to_sqls r;cyrr)rrrs r9rzPandasSQL.execute r;cyrr)rrrs r9rzPandasSQL.has_tablerr;cyrrrrrrrRrs r9_create_sql_schemazPandasSQL._create_sql_schemas r;)rr3rNTNNNNrfrrrstr | list[str] | Nonerhrar str | NonerrrirrrrrrrrhrarrrRrrirrrrTNNNNNauto)rrrrrrarrrRrrrrrrrrrzstr | Select | TextClauserrrrrraNNN rr!rrrlist[str] | NonerRrrrrr) rrrrrrrrrrrrrrr;r9rrs -1!! $9@ " "* " " " "7 " ) "-1! $!%9@    *        7   )     ;A $!%59  8      3          "&!%!             r;c0eZdZ d ddZy) BaseEngineNc  t|)z: Inserts data into already-prepared table )r) rrrrrrrrrrs r9insert_recordszBaseEngine.insert_recordss"$''r;TNNN rrrrrrrrrr)rrrrrr;r9rrsE04 $((  ( - (( (r;rc8eZdZddZ d ddZy)SQLAlchemyEnginectddy)Nrz'sqlalchemy is required for SQL support.)extrarrs r9rzSQLAlchemyEngine.__init__s"  I r;Nc  ddlm}  |j||S#| j$r@} d} t | j } t j| | r td| | d} ~ wwxYw)Nr)exc)rrzg(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?# )|inf can not be used with MySQLzinf cannot be used with MySQL) rrrStatementErrorrorigresearchrP)rrrrrrrrrrrrmsgerr_texts r9rzSQLAlchemyEngine.insert_recordssj # <<)F<C C!! 0C388}Hyyh' !@AsJI sA*;A%%A*rrr)rrrrrrr;r9rr sJ 04 $   -  r;rc|dk(r td}|dk(r'tg}d}|D] } |cStd||dk(r tSt d#t$r}|dt|zz }Yd}~Ud}~wwxYw) zreturn our implementationrz io.sql.enginez - NzUnable 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:rz*engine must be one of 'auto', 'sqlalchemy')r rrrrP)rengine_classes error_msgs engine_classrs r9 get_enginer.s O, *+ * 1L 1#~% 1  C l    !! A BB! 1gC00  1sA A; A66A;ceZdZdZ d ddZddZedZdddZ d ddZ e d ddZ d dd Z e Z d dd Z dd Z d d d Zed Zdd!dZdd"dZdd#dZ d$ d%dZy)&raa 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. Ncddlm}ddlm}ddlm}t |_t|tr-||}|jj|jt||r)|jj|j}|r9|js)|jj|j||_|||_d|_y)Nr) create_engine)rrUrF)rrsqlalchemy.enginerrrVrr9rLrcallbackdispose enter_contextconnectin_transactionbeginrrreturns_generator)rrrrrrrVs r9rzSQLDatabase.__init__`s -,. $+ c3 $C OO $ $S[[ 1 c6 "//// >C C$6$6$8 OO ) )#))+ 6F+ !&r;cR|js|jjyyr)rr9closers r9rzSQLDatabase.__exit__ws %% OO ! ! #&r;c#K|jjs2|jj5|jdddy|jy#1swYyxYwwr)rrrrs r9rzSQLDatabase.run_transaction{sQxx&&(! hh  ((N  s5A*AA*A'#A*c|gn|g}t|tr|jj|g|S|jj|g|S)z,Simple passthrough to SQLAlchemy connectable)rLrrexec_driver_sqlr)rrrrs r9rzSQLDatabase.executesQ^r& c3 +488++C7$7 7txx+d++r;c |jj|j|gdt||||} |d|_| j |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)ronlyviews)rr)rhr8rmrri)rreflectrrrrEr9) rrrrhr8rmrrrirs r9rzSQLDatabase.read_tablesn~ txxzl$GT6J  %)D "zz OO%#'   r;c #Kd} |5 |j|} | s| stg||||||nd} t| ||||||A dddy#1swYyxYww)+Return generator through chunked result setFTrrhr8rRriN)r6r) rr9rrmrrhr8rRrir:rvs r9r;zSQLDatabase._query_iterators  '' 2(*#&/)5(3"'*7 $ "'!- +"/!   sA AA A AA c |j||} | j} |+d|_|j| |j|| ||||| S| j } 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 Tr)rrrr;r9r@r) rrrrhr8rrrRrirrmrvrs r9rzSQLDatabase.read_querysrc6*++-  %)D "''#)'+(  ??$D #)'+ELr;c ^|rt|s|Dcic]}||}}ntt|}ddlm} |j D]=\} } t | tr t| | r#t | | r0td| dt||||||||} | j| Scc}w)z_ Prepares table in the database for data insertion. Creates it if needed, etc. r) TypeEnginez The type of z is not a SQLAlchemy type)rrrrrrR) rrrMrrr]rLrSrQrPrr) rrrrrrrrRrbrrWmy_typers r9 prep_tablezSQLDatabase.prep_tableWs &:??X5??T5) 3 %  T Wgt,GZ1P4$|C58Q%RSS  T  #     3@s B*c*|js|jsrddlm}||j}|j |xs|j j}||vr+d|d}tj|ttyyyy)zv Checks table name for issues with case-sensitivity. Method is called after data is inserted. rinspectrzThe 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.rN) isdigitislowerrrrget_table_namesrrrrrr)rrrsqlalchemy_inspectinsp table_namesrs r9check_case_sensitivez SQLDatabase.check_case_sensitives||~dlln A%dhh/D..f6P @P@P.QK;&/v6((  /1''5~r;c t| } |j|||||||} | jd| |j|||||| d| }|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 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 `. 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. )rrrrrrrR)rrrrrrrr)rrr)rrrrr)rrrrrrrrrRrrr sql_enginerr0s r9rzSQLDatabase.to_sqls| ' #  322       !!tF!;r;c.|jjSr)rtablesrs r9rzSQLDatabase.tablessyyr;cddlm}||j}|j||xs|jj S)Nrr)rrrrrr)rrrrrs r9rzSQLDatabase.has_tables3<!$((+~~dF$>dii.>.>??r;cddlm}m}|xs|jj}|||j|j |}|j D]*}t|j|sd|j_ ,|S)Nr)Numericr,) autoload_withrF) rrr,rrrrmrLrS asdecimal)rrrrr,tblcolumns r9rzSQLDatabase.get_tablesi +499++J &Qkk .F&++w/(- % . r;c|xs|jj}|j||r|jj|j|g|d|j 5|j ||j|jddd|jjyy#1swY%xYw)NT)rrrrr) rrrrrrrdropclearrrrs r9rzSQLDatabase.drop_tables+499++ >>*f - II  XXZLt  %%' Gz6277TXX7F G IIOO  . G Gs '-B88Cc Xt|||d|||}t|jSNF)rrrrRr)rrrrrrrrRrrs r9rzSQLDatabase._create_sql_schemas9   5##%&&r;NF)rrrrarrrrrrrNTNNrf) r9rrrrhrarRrrirr)rTNNN) rrrrrrrRrrrrrrrrrrrrrrrrarrrrrRrrrrrrrr)rrrrrr,rrrrrrrr)rrrrrrrrrr staticmethodr;rrrrrpropertyrrrrrrr;r9rrNs$HM'%'@D' '.$,-1!! $9@J J *J  J J J 7J  )J X !!%9@&&&  &&7&&V-1! $!%9@T T*T TTT7T )TlH ;A/3!%..8 . - .. .`  B;A! $!%59WW8 W  WWWW3WW Wr  @ "&!%! ''' '  '  ' 'r;rceZdZdZd dZedZd d dZ d ddZ d ddZ e Z d ddZ d dd Z d dd Z y)rz This class enables conversion between DataFrame and SQL databases using ADBC to handle DataBase abstraction. Parameters ---------- con : adbc_driver_manager.dbapi.Connection c||_yrrrrs r9rzADBCDatabase.__init__? r;c#K|jj5} ||jj dddy#t$r|jjwxYw#1swYyxYwwr)rcursorrrollbackcommitrcurs r9rzADBCDatabase.run_transactionBsm XX__  #   HHOO     !!#   s1A:A.AA. A:%A++A..A73A:Nc tt|ts td|gn|g}|jj } |j |g||S#t $r[} |jjn&#t $r}td|d|d}||d}~wwxYwtd|d|}||d}~wwxYwNz/Query must be a string unless using sqlalchemy.zExecution failed on sql:  z unable to rollbackzExecution failed on sql 'z': rLrrOrrrrrrrrrrrr inner_excexs r9rzADBCDatabase.executeL#s#MN N^r&hhoo  CKK #d #J  (!!# ("/uBse;OPi'  ( !:3%s3%HIB#  5A B7A87B28 BBBB22B7c |dur td|r td|r.|r t|} ng} | |z} djd| D} nd} |r d| d|d |} nd| d|} |jj 5} | j | | j }t|| }d d d t|| S#1swYxYw) 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 T2'coerce_float' is not implemented for ADBC drivers/'chunksize' is not implemented for ADBC drivers, c3(K|] }d|d yw)"Nr)r'xs r9r*z*ADBCDatabase.read_table..s#@as!H#@s*zSELECT z FROM .riN)rr8) rr&joinrrrfetch_arrow_tabler)r)rrrrhr8rmrrri index_select to_select select_listrrpa_tabler}s r9rzADBCDatabase.read_table`st t #%D  %&WX X .y9 ! $w.I))#@i#@@KK [Mxq ED[M |>$ 'F" 7:,6G!HIIi'XX__&<#KK+j\ :;<<h& A((&&uU&CCXX__  # __cV-N  %<<** A67S @ A  s0D3D?9E"3D<?E EE"E+c|jj||j}|djD]%}|s|D]}|s|dD]}|d|k(s y'y)N)db_schema_filtertable_name_filtercatalog_db_schemasdb_schema_tablesrTF)radbc_get_objectsread_all to_pylist)rrrrcatalog_schema schema_record table_records r9rzADBCDatabase.has_table` sxx((#t) (* ##78BBD $N!!/ $ $$12D$E$L#L1T9#$  $ $r;ctd)Nznot implemented for adbcrrs r9rzADBCDatabase._create_sql_schemar s""<==r;rrrrrrrrrrr)rrrrrrrrrrrrrrrr;r9rr5s.-1!! $9@X X *X  X X X 7X  )X z-1! $!%9@F F *F  F F F 7F  )F PH ;A! $!%59ZZ8 Z  ZZZZ3ZZ Zx,"&!%! >>> >  >  > >r;rTEXTREALINTEGERrgDATETIME)rlrUrVr rr rc t|jddjd}|S#t$r}t d|d|d}~wwxYw)Nzutf-8strictz%Cannot convert identifier to UTF-8: 'r)rencodedecode UnicodeErrorrP)runamers r9_get_unicode_namerV s[SD   (3::7C L S@aHIsRSs*. A AA ct|}t|s td|jd}|dk\r tdd|j ddzdzS)Nz$Empty table or column name specifiedrz%SQLite identifier cannot contain NULsr,z"")rVrurPfindr)rrU nul_indexs r9_get_valid_sqlite_namer[ s] d #E u:?@@ 6"IA~@AA sD) )C //r;c`eZdZdZd fd Zd dZd dZd dZd dZddZ ddZ d Z d Z xZ S) SQLiteTablezw Patch the SQLTable for fallback support. Instead of a table variable just use the Create Table statement. cDt||i||jyr)superr_register_date_adapters)rrkwargs __class__s r9rzSQLiteTable.__init__ s! $)&) $$&r;cddl}d d}d}d}|jt||jt||jt|d}d}|j d||j d|y) Nrct|jdd|jdd|jdd|jdS)N02d:r/06d)hourminutesecond microsecond)ts r9 _adapt_timez8SQLiteTable._register_date_adapters.._adapt_time s8ffS\188C.!((3qs@ST Tr;c"|jSr isoformatvals r9z5SQLiteTable._register_date_adapters.. s S]]_r;c$|jdS)N rorqs r9rsz5SQLiteTable._register_date_adapters.. ss);r;cHtj|jSr)r fromisoformatrSrqs r9rsz5SQLiteTable._register_date_adapters.. s4#5#5cjjl#Cr;cHtj|jSr)r rwrSrqs r9rsz5SQLiteTable._register_date_adapters.. s(>(>szz|(Lr;r timestampr)rregister_adapterr rr register_converter)rrrmadapt_date_isoadapt_datetime_iso convert_dateconvert_timestamps r9r`z#SQLiteTable._register_date_adapters sw  U5;  {3  ~6  +=>C L""6<8"";0ABr;cJtdj|jS)Nz; )rr1rrs r9rzSQLiteTable.sql_schema s5::djj)**r;c|jj5}|jD]}|j| dddy#1swYyxYwr)rrrr)rrrs r9rzSQLiteTable._execute_create sH [[ ( ( * #d  # T" # # # #s #AActtt|jj}d}t }|j )|j dddD]}|jd||Dcgc] }|| }}dj|}dj|gt|z} djt|D cgc]} d| d c} } d||jd|d | } | Scc}wcc} w) N?r>r,()z INSERT INTO  (z ) VALUES ) ror rrrmr[rrr1rurtr) rnum_rowsrwldescaperCrbracketed_names col_names row_wildcardsr wildcardsinsert_statements r9rzSQLiteTable.insert_statement sSdjj0012' :: !zz$B$' % Q$ %9>>f6&>>>HH_- #U!34 HHE(OLq-2LM 6$)),-R {)I; O  ?Ms /C8 C=ctt|}|j|jd||jS)Nr%r)ro executemanyrr)rrrrrs r9rzSQLiteTable._execute_insert s3O  ...:IF}}r;ct|}|Dcgc] }|D]}| }}}|j|jt|||jScc}}w)Nr)rorrrur)rrrrrrr-flattened_datas r9rz!SQLiteTable._execute_insert_multi sYO '0>#>Q!>!>> T**C N*C^T}}?sAc|j|j}t}|Dcgc]\}}}||dz|z}}}}|jt |jrvt |js|jg}n |j}dj |Dcgc] }|| c}} |jd|jd| d|jr|jdz} nd} d| z||jzd zd j |zd zg} |D cgc] \}}} | s | } }}} t | rd j | }d j | Dcgc] }|| c}} | jd|d|jzd z|zzdz||jzdz| zdz| Scc}}}wcc}wcc} }}wcc}w)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. rur*z CONSTRAINT z_pk PRIMARY KEY (rr/rz CREATE TABLE z ( z, z )rrz CREATE INDEX ix_zON r) rQ_sql_type_namer[rrurr1rrrr)rrNrcnamectypercreate_tbl_stmtsrr? cnames_br schema_name create_stmtsrZix_colscnamess r9rzSQLiteTable._create_table_setup s "&!A!A$BUBU!V'@V  ,;E5!F5MC % '   99 S^ * {yy d";6!9";I   *S069:;#$      Q #<"W">sG$G1 G =G 0G'c~|jxsi}t|r-tt|}|j|vr||jSt j |d}|dk(r'tjdttd}n |dk(rd}n|d k(rd }n|d k(r td |tvrd }t|S) NTrertrurrVrqr emptyrlrr) rRrrrMrrrrrrrrP _SQL_TYPES)rrWrRrcs r9rzSQLiteTable._sql_type_name% s***  u%Exx5 SXX&??3t4 } $ MML+-   !H  %!H  H  ">? ? : %H(##r;rr)rrrr)rr)rrrrrr`rrrrrrr __classcell__)rbs@r9r]r] s7 ' C:+#  &  1f $r;r]ceZdZdZddZedZdddZe d ddZ d ddZ dZ d dd Z ddd Z ddd Zddd Z d dd Zy)rz Version of SQLDatabase to support SQLite connections (fallback without SQLAlchemy). This should only be used internally. Parameters ---------- con : sqlite connection object c||_yrrrs r9rzSQLiteDatabase.__init__S rr;c#K|jj} ||jj |j y#t$r|jj wxYw#|j wxYwwr)rrrrrrrs r9rzSQLiteDatabase.run_transactionV shhhoo I HHOO  IIK   HH      IIKs'B AB %A33A66BB Nc tt|ts td|gn|g}|jj } |j |g||S#t $r[} |jjn&#t $r}td|d|d}||d}~wwxYwtd|d|}||d}~wwxYwrr!r"s r9rzSQLiteDatabase.executeb r%r&c #"Kd} |j|} t| tk(r t| } | sB|j |s/t j g||} |r| j|} | yd}t| ||||||w)rFTr4rN) r6rStuplerorr!r7rr) rrrmrrhr8rRrir:rvrs r9r;zSQLiteDatabase._query_iteratorv s ##I.DDzU"Dz $&33G,F!'u!5 L M#)'+ !sB Bc |j||} | jD cgc]} | d } } ||j| || |||||S|j| } | j t | | |||||} | Scc} w)Nrr)r descriptionr;_fetchall_as_listrr)rrrrhr8rrrRrircol_descrmrvrs r9rzSQLiteDatabase.read_query sc6*/5/A/AB88A;BB  ''#)'+(  ))&1D LLN #)'+EL5Cs A?c\|j}t|ts t|}|Sr)r@rLro)rrrs r9rz SQLiteDatabase._fetchall_as_list s%&$'&\F r;c B|ret|s|D cic]} | |}} ntt|}|jD]&\} }t |t rt | d|dt|||||||}|j|j|| Scc} w)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 `. rz) not a string)rrrrrR) rrrMr]rLrrPr]rr)rrrrrrrrrRrrrrbrWrrs r9rzSQLiteDatabase.to_sql sh &:??X5??T5) %  H W!'3/$uBwi~%FGG H  #   ||Iv..%@s Bcld}d|d}t|j||gjdkDS)Nrz SELECT name FROM sqlite_master WHERE type IN ('table', 'view') AND name=z ; r)rurr@)rrrrquerys r9rzSQLiteDatabase.has_table sGU  4<<v.779:Q>>r;cyrrrs r9rzSQLiteDatabase.get_table) sr;cBdt|}|j|y)Nr9)r[r)rrrdrop_sqls r9rzSQLiteDatabase.drop_table, s! !7!= >? Xr;c Xt|||d|||}t|jSr )r]rrr s r9rz!SQLiteDatabase._create_sql_schema0 s9   5##%&&r;rrrr )rrrhrarRrrirr) rhrarrrRrrirrrr)rrrrrrarrrRrrrrrrrrrrr)rrrRrrrrr)rrrrrrrrrr;rrrrrrrrr;r9rrH s  ( !!%9@$$  $$7$$R! $!%9@& &&&7& )&P  $!%59N/N/ N/  N/N/N/3N/N/ N/` ?!%! ''  '  ' 'r;rcrt|5}|j|||||cdddS#1swYyxYw)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)rrRrN)rr)rrrrrRrrs r9 get_schemarD sA: s # z,, 4d%-    s-6)FN)rIrarKzstr | dict[str, Any] | None)Trf)rhrarirrr!r )rhrarRrrir)r}r!rRrrirrr!r).......)rrrrr8!list[str] | dict[str, str] | NonermrrrriDtypeBackend | lib.NoDefaultrr!)rrrrr8rrmrrrrirrIterator[DataFrame])rrrrrrrhrar8rrmrrrrirrr)rrr$list[Any] | Mapping[str, Any] | Noner8rrrrRrrirrr!)rrrrr8rrrrRrrirrr)rrrhrarrr8rrrrRrrirrr).......N) rrrmrrrrirrRrrr!) rrrmrrrrirrRrrr)rrrhrarmrrrrirrRrrr)NrTNNNNr)rrrrrrrrarzIndexLabel | NonerrrRrrrrrrr)rrrrrrar )rrrrarr)rrrr)rr)NNNN)rrrRrrrrr)pr __future__rabcrr contextlibrrr rr functoolsr rtypingr r rrrrrrfrTpandas._configr pandas._libsrpandas.compat._optionalr pandas.errorsrrpandas.util._exceptionsrpandas.util._validatorsrpandas.core.dtypes.commonrrrrpandas.core.dtypes.dtypesrpandas.core.dtypes.missingrrr pandas.core.apir!r"pandas.core.arraysr#pandas.core.arrays.string_r$pandas.core.baser%pandas.core.commoncorecommonrHr&"pandas.core.internals.constructionr'pandas.core.tools.datetimesr(pandas.io._utilr)collections.abcr*r+rr,sqlalchemy.sql.expressionr-r.pandas._typingr/r0r1r2r3r4r:rYrer~rrrrrrrrr table_existsrrrrrrrrrrVr[r]rrrr;r9rs #  ->57 6+32) .C31 !  CGMM$?MD05< **3 *  *J!5<   34!5<    3 (/F  (+58 #25  &  3   0      (+58 #25  &  3   0    "(,59 $ 25..c?c? c?& c?  c? 3 c?c?c?0c?%c?L ),3658 25 &  1  3    0     ),3658 25 &  1  3    0    ")-3759 !25..e &e  e 1 e 3 e e  e 0e %e P ), 25! &   0       ), 25! &   0      $)-  $ 25..!m&m mmm0m m%mh6<%) !15l l   l 4 l  l #l l  l  /l  l l ^02 "" "" "JP|PfH cH V((&zBC@a')a'NE>9E>V    0$a$(a$Hy'Yy'~  !           r;