U
    b%                    @  sb  d Z ddlmZ ddlmZ ddlmZmZmZ ddlm	Z	 ddl
Z
ddlmZmZmZmZmZ ddlZddlZddlm  mZ ddlmZ dd	lmZ dd
lmZ ddlmZ ddlm Z m!Z!m"Z" ddl#m$Z$ ddl%m&Z& ddl'm(Z( ddl)m*Z*m+Z+ ddl,m-Z- ddl.m/  m0Z1 ddl2m3Z3 ddl4m5Z5 G dd de6Z7ddddZ8dd Z9dd Z:dqddd d!d"Z;d#d$ Z<drdd&d'd(d)Z=dsd*d+Z>edtd-d.d/d0d1Z?edud2d3d/d4d1Z?dvd5d6d7dd8d9d:d;d1Z?edwd-d&d.d<d=d>Z@edxd2d&d3d<d?d>Z@dydd8d&d9d@dAd>Z@edzd-d.d/dBdCZAed{d2d3d/dDdCZAd|d7dd8d9dEdFdCZAd}d5d6d5dd8d&d6d5d8dI	dJdKZBd~d5d6dLdMdNZCeCZDdd6dOdPdQZEG dRdS dSe-ZFG dTdU dUe-ZGG dVdW dWZHG dXdY dYeHZId5dWdZd[d\ZJG d]d^ d^eGZKd_d`dadbdcdddadeZLdfdg ZMdhdi ZNG djdk dkeFZOG dldm dmeGZPdd5d&d6dndodpZQdS )z|
Collection of query wrappers / abstractions to both facilitate data
retrieval and to reduce dependency on DB-specific API.
    )annotations)contextmanager)datedatetimetime)partialN)AnyIteratorSequencecastoverload)DtypeArgimport_optional_dependencyAbstractMethodError)find_stack_level)is_datetime64tz_dtypeis_dict_likeis_list_like)DatetimeTZDtype)isna)
get_option)	DataFrameSeries)PandasObject)to_datetime)Versionc                   @  s   e Zd ZdS )DatabaseErrorN)__name__
__module____qualname__ r"   r"   1/tmp/pip-unpacked-wheel-ck39h295/pandas/io/sql.pyr   4   s   r   boolreturnc                  C  s   ddl } t| jtdkS )ze
    Check if sqlalchemy.__version__ is at least 1.4.0, when several
    deprecations were made.
    r   Nz1.4.0)
sqlalchemyr   __version__)r'   r"   r"   r#   _gt14<   s    r)   c                 C  s6   | g}|dk	r2t |dr$||g7 }n|t|g7 }|S )z9Convert SQL and params args to DBAPI2.0 compliant format.Nkeys)hasattrlist)sqlparamsargsr"   r"   r#   _convert_paramsF   s    
r0   c                 C  s2   | dks| dks| dkrg } nt | ds.| g} | S )z3Process parse_dates argument for read_sql functionsTNF__iter__)r+   parse_datesr"   r"   r#   _process_parse_dates_argumentQ   s
    
r4   zbool | Nonezstr | dict[str, Any] | None)utcformatc                 C  s   t |tr.|dd pd}t| fd|i|S |d krZt| jjtjsVt| jjtj	rZd}|dkrrt| d||dS t
| jrt| ddS t| d||d	S d S )
Nerrorsignores)Ddhmr9   msusnsZcoerce)r7   unitr5   Tr5   )r7   r6   r5   )
isinstancedictpopr   
issubclassdtypetypenpfloatingintegerr   )colr5   r6   errorr"   r"   r#   _handle_date_column\   s    

rN   c              	   C  sf   t |}|  D ]P\}}t|js*||krz|| }W n tk
rN   d}Y nX t||d| |< q| S )zz
    Force non-datetime columns to be read as such.
    Supports both string formatted and integer timestamp columns.
    Nr6   )r4   itemsr   rG   	TypeErrorrN   )Z
data_framer3   col_namedf_colfmtr"   r"   r#   _parse_date_columnsx   s    
rU   TDtypeArg | None)coerce_floatrG   c                 C  sB   t j| ||d}|r||}t||}|dk	r>|j|dd |S )z(Wrap result set of query in a DataFrame.columnsrW   NTZinplace)r   from_recordsastyperU   	set_index)datarY   	index_colrW   r3   rG   framer"   r"   r#   _wrap_result   s    	

ra   c                 C  s   t |}t| |}|j| S )a  
    Execute the given SQL query using the provided connection object.

    Parameters
    ----------
    sql : string
        SQL query to be executed.
    con : SQLAlchemy connectable(engine/connection) or sqlite3 connection
        Using SQLAlchemy makes it possible to use any DB supported by the
        library.
        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
    )pandasSQL_builderr0   execute)r-   conr.   
pandas_sqlr/   r"   r"   r#   rc      s    
rc   .Noner   )	chunksizer&   c                 C  s   d S Nr"   
table_namerd   schemar_   rW   r3   rY   rg   r"   r"   r#   read_sql_table   s    rl   intzIterator[DataFrame]c                 C  s   d S rh   r"   ri   r"   r"   r#   rl      s    str
str | Nonestr | Sequence[str] | None
int | NonezDataFrame | Iterator[DataFrame])rj   rk   r_   rW   rg   r&   c           
      C  s^   t ||d}|| s&td|  d|j| |||||d}	|	dk	rH|	S td|  d|dS )a~  
    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.

    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
    rk   zTable z
 not foundr_   rW   r3   rY   rg   N)rb   	has_table
ValueError
read_table)
rj   rd   rk   r_   rW   r3   rY   rg   re   tabler"   r"   r#   rl      s    A
	)rg   rG   r&   c                 C  s   d S rh   r"   r-   rd   r_   rW   r.   r3   rg   rG   r"   r"   r#   read_sql_query/  s    ry   c                 C  s   d S rh   r"   rx   r"   r"   r#   ry   =  s    )rW   rg   rG   r&   c           	   	   C  s    t |}|j| ||||||dS )u
  
    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 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 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.
    )r_   r.   rW   r3   rg   rG   )rb   
read_query)	r-   rd   r_   rW   r.   r3   rg   rG   re   r"   r"   r#   ry   K  s    Cc                 C  s   d S rh   r"   r-   rd   r_   rW   r.   r3   rY   rg   r"   r"   r#   read_sql  s    r|   c                 C  s   d S rh   r"   r{   r"   r"   r#   r|     s    )r_   rW   rg   r&   c           
      C  s   t |}t|tr(|j| |||||dS z|| }	W n tk
rN   d}	Y nX |	r~|jj|j| gd |j	| |||||dS |j| |||||dS dS )a  
    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 : 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. The user is responsible
        for engine disposal and connection closure for the SQLAlchemy connectable; str
        connections are closed automatically. See
        `here <https://docs.sqlalchemy.org/en/13/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.

    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('test_data', 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

    >>> pd.read_sql('SELECT int_column, date_column FROM test_data',
    ...             conn,
    ...             parse_dates=["date_column"])
       int_column date_column
    0           0  2012-10-11
    1           1  2010-12-11

    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:
    1. Ignore errors while parsing the values of "date_column"

    >>> pd.read_sql('SELECT int_column, date_column FROM test_data',
    ...             conn,
    ...             parse_dates={"date_column": {"errors": "ignore"}})
       int_column date_column
    0           0  2012-10-11
    1           1  2010-12-11

    2. Apply a dayfirst date parsing order on the values of "date_column"

    >>> pd.read_sql('SELECT int_column, date_column FROM test_data',
    ...             conn,
    ...             parse_dates={"date_column": {"dayfirst": True}})
       int_column date_column
    0           0  2012-11-10
    1           1  2010-11-12

    3. Apply custom formatting when date parsing the values of "date_column"

    >>> 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
    )r_   r.   rW   r3   rg   F)bindonlyrs   N)
rb   rC   SQLiteDatabaserz   rt   	Exceptionmetareflectconnectablerv   )
r-   rd   r_   rW   r.   r3   rY   rg   re   Z_is_table_namer"   r"   r#   r|     s@    }
	
	failauto)	namerk   	if_existsindexrg   rG   methodenginer&   c                 K  sp   |dkrt d| dt||d}t| tr8|  } nt| tsJtd|j| |f|||||||	|
d|S )a  
    Write records stored in a DataFrame to a SQL database.

    Parameters
    ----------
    frame : DataFrame, Series
    name : str
        Name of SQL table.
    con : SQLAlchemy connectable(engine/connection) or database string URI
        or sqlite3 DBAPI2 connection
        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 the 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. 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   replaceappend'' is not valid for if_existsrr   z9'frame' argument should be either a Series or a DataFrame)r   r   index_labelrk   rg   rG   r   r   )ru   rb   rC   r   Zto_framer   NotImplementedErrorto_sql)r`   r   rd   rk   r   r   r   rg   rG   r   r   engine_kwargsre   r"   r"   r#   r   Z  s0    S


r   rj   rk   c                 C  s   t ||d}|| S )a-  
    Check if DataBase has named table.

    Parameters
    ----------
    table_name: string
        Name of SQL table.
    con: SQLAlchemy connectable(engine/connection) or sqlite3 DBAPI2 connection
        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
    rr   )rb   rt   )rj   rd   rk   re   r"   r"   r#   rt     s    rt   rr   c                 C  s   ddl }ddl}t| |js$| dkr,t| S tddd}t| tr^|dkrTtdn
|| } |dk	rt| |j	j
rt| |dS |dt t| S )	zm
    Convenience function to return the correct PandasSQL subclass based on the
    provided parameters.
    r   Nr'   r8   )r7   z.Using URI string without sqlalchemy installed.rr   zpandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy)sqlite3warningsrC   
Connectionr   r   rn   ImportErrorZcreate_enginer   ZConnectableSQLDatabasewarnUserWarning)rd   rk   r   r   r'   r"   r"   r#   rb     s     


rb   c                   @  s   e Zd ZdZd3dddd	d
Zdd Zdd Zdd Zdd ZdddddZ	dddddZ
dd Zd4dddddd Zd5dd!d"d#d$Zd6d%d&Zd'd( Zd)d* Zd+d, Zd7d-d.Zd/d0 Zd1d2 ZdS )8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   pandasrn   rV   )r   rG   c                 C  s   || _ || _|| _|| _| ||| _|| _|| _|	| _|
| _	|d k	rR| 
 | _n| j| j | j| _| jd krtd| dd S )NzCould not init table 'r   )r   pd_sqlprefixr`   _index_namer   rk   r   r*   rG   _create_table_setuprw   	get_tableru   )selfr   Zpandas_sql_enginer`   r   r   r   r   rk   r*   rG   r"   r"   r#   __init__  s    
zSQLTable.__init__c                 C  s   | j | j| jS rh   )r   rt   r   rk   r   r"   r"   r#   exists.  s    zSQLTable.existsc                 C  s$   ddl m} t|| j| jjS )Nr   )CreateTable)sqlalchemy.schemar   rn   rw   compiler   r   )r   r   r"   r"   r#   
sql_schema1  s    zSQLTable.sql_schemac                 C  sB   t  r| j| jj| _n| j| jj| _| jj| jjd d S )Nr}   )r)   rw   Zto_metadatar   r   Z
tometadatacreater   r   r"   r"   r#   _execute_create6  s    zSQLTable._execute_createc                 C  sx   |   rl| jdkr&td| j dqt| jdkrL| j| j| j |   qt| jdkrXqttd| j dn|   d S )Nr   zTable 'z' already exists.r   r   r   r   )r   r   ru   r   r   
drop_tablerk   r   r   r"   r"   r#   r   >  s    



zSQLTable.createz	list[str]rm   )r*   r&   c                   s*    fdd|D }| | j |}|jS )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                   s   g | ]}t t |qS r"   rD   zip.0rowr*   r"   r#   
<listcomp>X  s     z,SQLTable._execute_insert.<locals>.<listcomp>)rc   rw   insertrowcount)r   connr*   	data_iterr^   resultr"   r   r#   _execute_insertL  s    zSQLTable._execute_insertc                   s>   ddl m}  fdd|D }|| j|}||}|jS )a  
        Alternative to _execute_insert for DBs support multivalue 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   c                   s   g | ]}t t |qS r"   r   r   r   r"   r#   r   g  s     z2SQLTable._execute_insert_multi.<locals>.<listcomp>)r'   r   rw   valuesrc   r   )r   r   r*   r   r   r^   stmtr   r"   r   r#   _execute_insert_multi\  s
    	
zSQLTable._execute_insert_multic              
   C  s,  | j d k	rd| j }| j |j _z|jdd W qj tk
r` } ztd| |W 5 d }~X Y qjX n| j}ttt|j	}t
|}d g| }t| D ]\}\}}|j}	|	jjdkr|	 }
n(|	jjdkr|	dt}
n
|	t}
t|
tjstt|
|jrt|
}d |
|< |
||< q||fS )NTrZ   z!duplicate name in index/columns: Mr=   i8)r   r`   copynamesZreset_indexru   r,   maprn   rY   len	enumeraterP   Z_valuesrG   kindZto_pydatetimeviewr\   objectrC   rI   ZndarrayAssertionErrorrH   Z_can_hold_nar   )r   temperrcolumn_namesZncols	data_listi_Zservalsr;   maskr"   r"   r#   insert_datal  s0    


$



zSQLTable.insert_datarq   ro   )rg   r   r&   c              	     s  |d kr| j }n2|dkr | j}n"t|r4t|| }ntd| |  \}}t| j}|dkrddS |d krr|}n|dkrtd|| d }d}| j	 x}	t
|D ]h}
|
| t|
d | |  kr qt fdd|D  }||	||}|d krd }q||7 }qW 5 Q R X |S )NZmultizInvalid parameter `method`: r   z%chunksize argument should be non-zero   c                 3  s   | ]}|  V  qd S rh   r"   )r   ZarrZend_iZstart_ir"   r#   	<genexpr>  s     z"SQLTable.insert.<locals>.<genexpr>)r   r   callabler   ru   r   r   r`   r   run_transactionrangeminr   )r   rg   r   Zexec_insertr*   r   Znrowschunkstotal_insertedr   r   Z
chunk_iterZnum_insertedr"   r   r#   r     s:    

zSQLTable.insertr$   )rg   rW   c                 c  sx   d}| |}|s,|sttjg ||dV  qtqd}tj|||d| _| j|d | jdk	rj| jj| jdd | jV  qdS )z,Return generator through chunked result set.FrX   Tr2   NrZ   )	fetchmanyr   r[   r`   _harmonize_columnsr   r]   )r   r   rg   rY   rW   r3   has_read_datar^   r"   r"   r#   _query_iterator  s(    	
    
zSQLTable._query_iteratorc                   s  ddl m} |d k	r~t|dkr~ fdd|D } jd k	rf jd d d D ]}|d jj|  qLt rt|| n||}nt r| jn j } j	|}	|	
 }
|d k	rȈ j|	||
||dS |	 }tj||
|d _ j|d  jd k	r jj jd	d
  jS d S )Nr   )selectc                   s   g | ]} j j| qS r"   )rw   c)r   nr   r"   r#   r     s     z!SQLTable.read.<locals>.<listcomp>)rW   r3   rX   r2   TrZ   )r'   r   r   r   r   rw   r   r)   r   rc   r*   r   fetchallr   r[   r`   r   r]   )r   rW   r3   rY   rg   r   colsidxZ
sql_selectr   r   r^   r"   r   r#   read  s8    
  zSQLTable.readc                 C  s   |dkr| j jj}|d k	rJt|ts*|g}t||krFtd| n|S |dkrrd| j jkrr| j jjd krrdgS t	
| j jjS n"t|tr|gS t|tr|S d S d S )NTz@Length of 'index_label' should match number of levels, which is r   r   )r`   r   nlevelsrC   r,   r   ru   rY   r   comZfill_missing_namesr   rn   )r   r   r   r   r"   r"   r#   r      s.    




zSQLTable._index_namec                   sp   g }j d k	rHtj D ].\}} jj |}|t||df q| fddttjjD 7 }|S )NTc                   s6   g | ].}t jj|  jjd d |f dfqS )NF)rn   r`   rY   Ziloc)r   r   dtype_mapperr   r"   r#   r   (  s   z8SQLTable._get_column_names_and_types.<locals>.<listcomp>)	r   r   r`   Z_get_level_valuesr   rn   r   r   rY   )r   r   column_names_and_typesr   Z	idx_labelZidx_typer"   r   r#   _get_column_names_and_types!  s    
z$SQLTable._get_column_names_and_typesc           
        s   ddl m m}m} ddlm} | | j} fdd|D }| jd k	rt	| js\| jg}n| j}||d| j
d i}|| | jp| jjj}| }	|| j
|	f|d|iS )	Nr   )ColumnPrimaryKeyConstraintTableMetaDatac                   s    g | ]\}}} |||d qS ))r   r"   )r   r   typis_indexr   r"   r#   r   9  s   z0SQLTable._create_table_setup.<locals>.<listcomp>r   Z_pkrk   )r'   r   r   r   r   r   r   _sqlalchemy_typer*   r   r   r   rk   r   r   )
r   r   r   r   r   rY   r*   Zpkcrk   r   r"   r   r#   r   /  s    




zSQLTable._create_table_setupc              	   C  s(  t |}| jjD ]}|j}z| j| }||krlz|| }W n tk
rT   d}Y nX t||d| j|< W q| |j}|t	ks|t
ks|tkr|tk}t||d| j|< n\|tkr|j|dd| j|< n>t|| kr|tdks|tkr|j|dd| j|< W q tk
r    Y qX qdS )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.
        NrO   rB   F)r   int64)r4   rw   rY   r   r`   rQ   rN   
_get_dtyperH   r   r   r   floatr\   r   countrI   rG   r$   KeyError)r   r3   Zsql_colrR   rS   rT   col_typer5   r"   r"   r#   r   M  s8    

zSQLTable._harmonize_columnsc                 C  s  | j pi }t|r0tt|}|j|kr0||j S tj|dd}ddlm}m	}m
}m}m}m}	m}
m}m}m} |dks~|dkrz|jjd k	r|ddW S W n2 tk
r   t|dd d k	r|dd Y S Y nX |S |d	krtjd
tt d |S |dkr|j dkr|	ddS |	ddS n|dkrt|j j dkr<|S |j j dkrR|
S |j j dkrntdn|S n<|dkr|S |dkr|S |dkr|S |dkrtd|S )NTZskipnar   )
	TIMESTAMP
BigIntegerBooleanDateDateTimeFloatIntegerSmallIntegerTextTime
datetime64r   )timezonetztimedelta64lthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.
stacklevelrJ   Zfloat32   )Z	precision5   rK   )Zint8Zuint8Zint16)Zuint16Zint32Zuint64z1Unsigned 64 bit integer datatype is not supportedbooleanr   r   complexComplex datatypes not supported)rG   r   r   rD   r   libinfer_dtypesqlalchemy.typesr   r   r   r   r  r  r  r  r  r  dtr	  AttributeErrorgetattrr   r   r   r   lowerru   )r   rL   rG   r   r   r   r   r   r  r  r  r  r  r  r"   r"   r#   r     sV    



0







zSQLTable._sqlalchemy_typec                 C  s   ddl m}m}m}m}m}m} t||r.tS t||rBt	
dS t||rZ|jsVtS tS t||rhtS t||rvtS t||rtS tS )Nr   )r   r   r   r  r  r  r   )r  r   r   r   r  r  r  rC   r   rI   rG   r  r   r   r   r$   r   )r   Zsqltyper   r   r   r  r  r  r"   r"   r#   r     s      	






zSQLTable._get_dtype)NTr   r   NNNN)NN)TN)TNNN)N)r   r    r!   __doc__r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r"   r"   r"   r#   r     s<           !'   0  
$!
3Gr   c                   @  s*   e Zd ZdZdd Zdddd	d
dZdS )	PandasSQLz7
    Subclasses Should define read_sql and to_sql.
    c                 O  s   t dd S NzMPandasSQL must be created with an SQLAlchemy connectable or sqlite connectionru   r   r/   kwargsr"   r"   r#   r|     s    zPandasSQL.read_sqlr   TNrV   rq   rG   r&   c
           
      C  s   t dd S r  r  )
r   r`   r   r   r   r   rk   rg   rG   r   r"   r"   r#   r     s    zPandasSQL.to_sql)r   TNNNNN)r   r    r!   r  r|   r   r"   r"   r"   r#   r    s   
       r  c                   @  s   e Zd ZddddddZdS )	
BaseEngineTNr   rq   rw   r&   c	           
      K  s   t | dS )z:
        Inserts data into already-prepared table
        Nr   )
r   rw   rd   r`   r   r   rk   rg   r   r   r"   r"   r#   insert_records  s    zBaseEngine.insert_records)TNNN)r   r    r!   r#  r"   r"   r"   r#   r!    s
       r!  c                   @  s&   e Zd Zdd Zd
ddddd	ZdS )SQLAlchemyEnginec                 C  s   t ddd d S )Nr'   z'sqlalchemy is required for SQL support.)extrar   r   r"   r"   r#   r     s     zSQLAlchemyEngine.__init__TNr   rq   r"  c	              
   K  sp   ddl m}
 z|j||dW S  |
jk
rj } z.d}t|j}t||rVtd|n|W 5 d }~X Y nX d S )Nr   )exc)rg   r   zg(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?#
            )|inf can not be used with MySQLzinf cannot be used with MySQL)	r'   r&  r   ZSQLAlchemyErrorrn   origresearchru   )r   rw   rd   r`   r   r   rk   rg   r   r   r&  r   msgZerr_textr"   r"   r#   r#    s    
zSQLAlchemyEngine.insert_records)TNNN)r   r    r!   r   r#  r"   r"   r"   r#   r$    s       r$  )r   r&   c                 C  s   | dkrt d} | dkr|tg}d}|D ]D}z| W   S  tk
rh } z|dt| 7 }W 5 d}~X Y q&X q&td| n| dkrt S tddS )	zreturn our implementationr   zio.sql.engine z
 - 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:r'   z*engine must be one of 'auto', 'sqlalchemy')r   r$  r   rn   ru   )r   Zengine_classesZ
error_msgsZengine_classr   r"   r"   r#   
get_engine7  s     $
r,  c                   @  s  e Zd ZdZd4ddddZedd Zd	d
 Zd5ddddddddZe	d6dddddZ
d7ddddddddZeZd8dddddZd d! Zd9dddd#d$Zed%d& Zd:ddd'd(d)Zd;ddd*d+d,Zd<ddd*d-d.Zd=d/dd0ddd1d2d3ZdS )>r   a  
    This class enables conversion between DataFrame and SQL databases
    using SQLAlchemy to handle DataBase abstraction.

    Parameters
    ----------
    engine : SQLAlchemy connectable
        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).

    Nro   rr   c                 C  s"   ddl m} || _||d| _d S )Nr   r   rr   )r   r   r   r   )r   r   rk   r   r"   r"   r#   r   g  s    zSQLDatabase.__init__c              
   c  sV   ddl m} t| j|rJ| j  }|  |V  W 5 Q R X W 5 Q R X n| jV  d S )Nr   )Engine)Zsqlalchemy.enginer-  rC   r   connectbegin)r   r-  r   r"   r"   r#   r   m  s    
zSQLDatabase.run_transactionc                 O  s   | j  j||S )z,Simple passthrough to SQLAlchemy connectable)r   Zexecution_optionsrc   r  r"   r"   r#   rc   x  s    zSQLDatabase.executeTrn   rp   r$   rq   )rj   r_   rW   rk   rg   c           	      C  s"   t || ||d}|j||||dS )a  
        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.

        Returns
        -------
        DataFrame

        See Also
        --------
        pandas.read_sql_table
        SQLDatabase.read_query

        )r   rk   )rW   r3   rY   rg   )r   r   )	r   rj   r_   rW   r3   rY   rk   rg   rw   r"   r"   r#   rv   |  s    4zSQLDatabase.read_tablerm   rV   )rg   rG   c           	      c  sN   d}|  |}|s.|sJtg ||||dV  qJqd}t||||||dV  qdS )+Return generator through chunked result setF)r_   rW   r3   Tr_   rW   r3   rG   N)r   ra   )	r   rg   rY   r_   rW   r3   rG   r   r^   r"   r"   r#   r     s*    
zSQLDatabase._query_iterator)r-   r_   rW   rg   rG   c              	   C  s`   t ||}| j| }	|	 }
|dk	r<| j|	||
||||dS |	 }t||
||||d}|S dS )u  
        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

        Nr1  )r0   rc   r*   r   r   ra   )r   r-   r_   rW   r3   r.   rg   rG   r/   r   rY   r^   r`   r"   r"   r#   rz     s.    8


zSQLDatabase.read_queryr   r   r   c              
     s    rjt  s  fdd|D  n
tt  ddlm}m}	   D ]&\}
}t|	||sBtd|
 dqBt	|| ||||| d}|
  |S )z_
        Prepares table in the database for data insertion. Creates it if needed, etc.
        c                   s   i | ]
}| qS r"   r"   r   rR   rG   r"   r#   
<dictcomp>E  s      z*SQLDatabase.prep_table.<locals>.<dictcomp>r   )
TypeEngineto_instancezThe type of z is not a SQLAlchemy type)r`   r   r   r   rk   rG   )r   r   rD   r  r5  r6  rP   rC   ru   r   r   )r   r`   r   r   r   r   rk   rG   r5  r6  rL   my_typerw   r"   r3  r#   
prep_table0  s(    

zSQLDatabase.prep_tablec           	   	   C  s   |  s| s| jj}| j L}t rTddlm} ||}|j|pL| j	j
d}n|j|pb| j	j
|d}W 5 Q R X ||krd| d}t|t dS )zv
        Checks table name for issues with case-sensitivity.
        Method is called after data is inserted.
        r   inspectrr   )rk   
connectionzThe 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.N)isdigitislowerr   r   r.  r)   r'   r:  Zget_table_namesr   rk   table_namesr   r   r   )	r   r   rk   r   r   r:  inspr>  r*  r"   r"   r#   check_case_sensitive_  s"    	

 
z SQLDatabase.check_case_sensitiver   c                 K  sV   t |
}| j|||||||d}|jf || j||||||	d|}| 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   rk   rG   )rw   rd   r`   r   r   rk   rg   r   r   rk   )r,  r8  r#  r   r@  )r   r`   r   r   r   r   rk   rg   rG   r   r   r   Z
sql_enginerw   r   r"   r"   r#   r     s0    >
	zSQLDatabase.to_sqlc                 C  s   | j jS rh   )r   tablesr   r"   r"   r#   rB    s    zSQLDatabase.tablesrA  c                 C  sR   t  r0ddlm} || j}|||p,| jjS | j| jjj||pJ| jjS d S )Nr   r9  )	r)   r'   r:  r   rt   r   rk   Zrun_callabledialect)r   r   rk   r:  r?  r"   r"   r#   rt     s    
  
zSQLDatabase.has_tabler   c                 C  sT   ddl m}m} |p| jj}||| j| j|d}|jD ]}t|j|r6d|j_	q6|S )Nr   )Numericr   )Zautoload_withrk   F)
r'   rD  r   r   rk   r   rY   rC   rH   Z	asdecimal)r   rj   rk   rD  r   Ztblcolumnr"   r"   r#   r     s       

zSQLDatabase.get_tablec                 C  sR   |p
| j j}| ||rN| j j| j|g|d | ||j| jd | j   d S )N)r}   r~   rk   r   )r   rk   rt   r   r   r   Zdropclearr   rj   rk   r"   r"   r#   r     s
    zSQLDatabase.drop_tabler   zlist[str] | None)r`   rj   r*   rG   rk   c              	   C  s"   t || |d|||d}t| S NF)r`   r   r*   rG   rk   )r   rn   r   r   r`   rj   r*   rG   rk   rw   r"   r"   r#   _create_sql_schema  s    	zSQLDatabase._create_sql_schema)N)NTNNNN)NTNN)NTNNNN)r   TNNN)r   TNNNNNr   )N)N)N)NNN)r   r    r!   r  r   r   r   rc   rv   staticmethodr   rz   r|   r8  r@  r   propertyrB  rt   r   r   rJ  r"   r"   r"   r#   r   W  sf   

      <    %      S     /&        Y
   r   ZTEXTZREALZINTEGERr   ZDATEZTIME)stringrJ   rK   r   r   r   r  c              
   C  sT   zt | ddd}W n4 tk
rN } ztd|  d|W 5 d }~X Y nX |S )Nzutf-8strictz%Cannot convert identifier to UTF-8: 'r   )rn   encodedecodeUnicodeErrorru   )r   unamer   r"   r"   r#   _get_unicode_name!  s
    $rS  c                 C  sF   t | }t|std|d}|dkr2tdd|dd d S )Nz$Empty table or column name specified r   z%SQLite identifier cannot contain NULs"z"")rS  r   ru   findr   )r   rR  Z	nul_indexr"   r"   r#   _get_valid_sqlite_name)  s    
rW  c                      sj   e Zd ZdZ fddZdd Zdd Zdd	d
dZddddZddddZ	dd Z
dd Z  ZS )SQLiteTablezw
    Patch the SQLTable for fallback support.
    Instead of a table variable just use the Create Table statement.
    c                   s*   dd l }|tdd  t j|| d S )Nr   c                 S  s
   |  dS )Nz%H:%M:%S.%f)strftime)r   r"   r"   r#   <lambda>H      z&SQLiteTable.__init__.<locals>.<lambda>)r   Zregister_adapterr   superr   )r   r/   r  r   	__class__r"   r#   r   A  s    zSQLiteTable.__init__c                 C  s   t d| jS )Nz;
)rn   joinrw   r   r"   r"   r#   r   K  s    zSQLiteTable.sql_schemac              	   C  s0   | j  }| jD ]}|| qW 5 Q R X d S rh   )r   r   rw   rc   )r   r   r   r"   r"   r#   r   N  s    
zSQLiteTable._execute_createrm   num_rowsc          	        s   t tt| jj}d}t | jd k	rF| jd d d D ]}|d| q4 fdd|D }d|}d|gt	| dfddt
|D }d | j d	| d
| }|S )N?r   r   c                   s   g | ]} |qS r"   r"   )r   rE  escaper"   r#   r   \  s     z0SQLiteTable.insert_statement.<locals>.<listcomp>,c                   s   g | ]}d   dqS )()r"   )r   r   )row_wildcardsr"   r#   r   `  s     zINSERT INTO  (z	) VALUES )r,   r   rn   r`   rY   rW  r   r   r_  r   r   r   )	r   ra  r   wldr   Zbracketed_namesZ	col_names	wildcardsinsert_statementr"   )rd  rh  r#   rl  S  s    

zSQLiteTable.insert_statementr%   c                 C  s"   t |}|| jdd| |jS )Nr   r`  )r,   Zexecutemanyrl  r   )r   r   r*   r   r   r"   r"   r#   r   f  s    zSQLiteTable._execute_insertc                 C  s4   t |}dd |D }|| jt|d| |jS )Nc                 S  s   g | ]}|D ]}|qqS r"   r"   )r   r   xr"   r"   r#   r   m  s       z5SQLiteTable._execute_insert_multi.<locals>.<listcomp>r`  )r,   rc   rl  r   r   )r   r   r*   r   r   Zflattened_datar"   r"   r#   r   k  s    z!SQLiteTable._execute_insert_multic           	        s6  |  | j}t  fdd|D }| jdk	rt| jrt| jsJ| jg}n| j}d fdd|D }|d| j d| d | j	r| j	d	 }nd
}d|  | j d d| d g}dd |D }t|r2d|}d fdd|D }|d d| j d |  d  | j d | d  |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                   s"   g | ]\}}} |d  | qS ) r"   )r   cnamectyper   rc  r"   r#   r   z  s    z3SQLiteTable._create_table_setup.<locals>.<listcomp>Nz, c                   s   g | ]} |qS r"   r"   r   r   rc  r"   r#   r     s     zCONSTRAINT z_pk PRIMARY KEY (rg  .r+  zCREATE TABLE z (
z,
  z
)c                 S  s   g | ]\}}}|r|qS r"   r"   )r   ro  r   r   r"   r"   r#   r     s      r   re  c                   s   g | ]} |qS r"   r"   rq  rc  r"   r#   r     s     zCREATE INDEX Zix_zON ri  )
r   _sql_type_namerW  r*   r   r   r_  r   r   rk   )	r   r   Zcreate_tbl_stmtsr*   Z	cnames_brZschema_nameZcreate_stmtsZix_colsZcnamesr"   rc  r#   r   q  s`    


	


zSQLiteTable._create_table_setupc                 C  s   | j pi }t|r0tt|}|j|kr0||j S tj|dd}|dkr^tjdt	t
 d d}n,|dkrld}n|d	krzd
}n|dkrtd|tkrd
}t| S )NTr   r
  r  r  rK   r  r   emptyrM  r  r  )rG   r   r   rD   r   r  r  r   r   r   r   ru   
_SQL_TYPES)r   rL   rG   r   r"   r"   r#   rs    s,    



zSQLiteTable._sql_type_name)r   r    r!   r  r   r   r   rl  r   r   r   rs  __classcell__r"   r"   r]  r#   rX  ;  s   
3rX  c                   @  s   e Zd ZdZdd Zedd Zdd Zed'd
dddddZ	d(ddddddZ
dd Zd)dddddZd*dddddZd+dddd d!Zd,dddd"d#Zd-dddd$d%d&ZdS ).r   z
    Version of SQLDatabase to support SQLite connections (fallback without
    SQLAlchemy). This should only be used internally.

    Parameters
    ----------
    con : sqlite connection object

    c                 C  s
   || _ d S rh   rd   )r   rd   r"   r"   r#   r     s    zSQLiteDatabase.__init__c                 c  sT   | j  }z:z|V  | j   W n  tk
r@   | j    Y nX W 5 |  X d S rh   )rd   cursorclosecommitr   rollback)r   curr"   r"   r#   r     s    

zSQLiteDatabase.run_transactionc                 O  s   | j  }z|j|| |W S  tk
r } ztz| j   W nB tk
r~ } z$td|d  d| d}||W 5 d }~X Y nX td|d  d| }||W 5 d }~X Y nX d S )NzExecution failed on sql: r   
z
unable to rollbackzExecution failed on sql 'z': )rd   rx  rc   r   r{  r   )r   r/   r  r|  r&  Z	inner_excexr"   r"   r#   rc     s    
zSQLiteDatabase.executeNTrm   r$   rV   )rg   rW   rG   c           	      c  sh   d}|  |}t|tkr"t|}|sH|   |sdtjg ||dV  qdqd}t||||||dV  qdS )r0  FrX   Tr1  N)r   rH   tupler,   ry  r   r[   ra   )	rx  rg   rY   r_   rW   r3   rG   r   r^   r"   r"   r#   r     s,    
  zSQLiteDatabase._query_iteratorrq   )rW   rg   rG   c              	   C  sr   t ||}| j| }	dd |	jD }
|d k	rD| j|	||
||||dS | |	}|	  t||
||||d}|S d S )Nc                 S  s   g | ]}|d  qS )r   r"   )r   Zcol_descr"   r"   r#   r   !  s     z-SQLiteDatabase.read_query.<locals>.<listcomp>r1  )r0   rc   descriptionr   _fetchall_as_listry  ra   )r   r-   r_   rW   r.   r3   rg   rG   r/   rx  rY   r^   r`   r"   r"   r#   rz     s0    



zSQLiteDatabase.read_queryc                 C  s   |  }t|tst|}|S rh   )r   rC   r,   )r   r|  r   r"   r"   r#   r  ;  s    
z SQLiteDatabase._fetchall_as_listr   r   c
              	     s    rZt  s  fdd|D  n
tt    D ]&\}}t|ts2t| d| dq2t|| |||| 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 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                   s   i | ]
}| qS r"   r"   r2  r3  r"   r#   r4  |  s      z)SQLiteDatabase.to_sql.<locals>.<dictcomp>ri  z) not a string)r`   r   r   r   rG   )
r   r   rD   rP   rC   rn   ru   rX  r   r   )r   r`   r   r   r   r   rk   rg   rG   r   r  rL   r7  rw   r"   r3  r#   r   A  s$    3

	zSQLiteDatabase.to_sqlrn   ro   rA  c                 C  s*   d}d| d}t | ||g dkS )Nrb  z;SELECT name FROM sqlite_master WHERE type='table' AND name=;r   )r   rc   r   )r   r   rk   rj  queryr"   r"   r#   rt     s    zSQLiteDatabase.has_tabler   c                 C  s   d S rh   r"   rG  r"   r"   r#   r     s    zSQLiteDatabase.get_tablec                 C  s   dt | }| | d S )NzDROP TABLE )rW  rc   )r   r   rk   Zdrop_sqlr"   r"   r#   r     s    zSQLiteDatabase.drop_table)rj   rG   rk   c              	   C  s"   t || |d|||d}t| S rH  )rX  rn   r   rI  r"   r"   r#   rJ    s    	z!SQLiteDatabase._create_sql_schema)NTNN)NTNNNN)r   TNNNNN)N)N)N)NNN)r   r    r!   r  r   r   r   rc   rK  r   rz   r  r   rt   r   r   rJ  r"   r"   r"   r#   r     sD   

    $      '
       O   r   )r   rG   rk   c                 C  s   t |d}|j| ||||dS )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: an open SQL database connection object or a SQLAlchemy connectable
        Using SQLAlchemy makes it possible to use any DB supported by that
        library, default: None
        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.

        .. versionadded:: 1.2.0
    rw  )r*   rG   rk   )rb   rJ  )r`   r   r*   rd   rG   rk   re   r"   r"   r#   
get_schema  s    
    r  )NN)NTNN)N)......)......)NNTNNN)......)......)NTNNNN)......)......)NTNNNN)Nr   TNNNNr   )N)N)NNNN)Rr  
__future__r   
contextlibr   r   r   r   	functoolsr   r(  typingr   r	   r
   r   r   r   ZnumpyrI   Zpandas._libs.libZ_libsr  Zpandas._typingr   Zpandas.compat._optionalr   Zpandas.errorsr   Zpandas.util._exceptionsr   Zpandas.core.dtypes.commonr   r   r   Zpandas.core.dtypes.dtypesr   Zpandas.core.dtypes.missingr   r   r   Zpandas.core.apir   r   Zpandas.core.baser   Zpandas.core.commoncorecommonr   Zpandas.core.tools.datetimesr   Zpandas.util.versionr   OSErrorr   r)   r0   r4   rN   rU   ra   rc   rl   ry   r|   r   rt   Ztable_existsrb   r   r  r!  r$  r,  r   ru  rS  rW  rX  r   r  r"   r"   r"   r#   <module>   s*  
       
                  T                  O                   )         n   f"    C  o    