U
|
¬ý°dÊR ã @ s4 d Z ddlmZ ddlmZmZ ddlmZmZ ddl m
|
Z
|
m Z m Z ddl m Z ddlZddlmZmZmZmZmZmZ ddlZddlZdd lmZ dd
|
lm Z m Z m Z m Z dd l m!Z! dd l"m#Z#m$Z$ dd l%m&Z& ddl'm(Z( ddl)m*Z*m+Z+m,Z,m-Z- ddl.m/Z/ ddl0m1Z1 ddl2m3Z3 ddl4m5Z5m6Z6 ddl7m8Z8 ddl9m:Z: ddl;m< m=Z> ddl?m@Z@ ddlAmBZB erddlCmDZD ddlEmFZFmGZG dd ZHd|d d d d d!ZId"d# ZJd}d d&d'd(d)d*ZKd~d d+d&d,d-d.ZLdd/d0ZMedd2d3d4d5d6d'd7d8d9ZNedd2d3d4d:d6d;d7d<d9ZNddd$dddejOfd=d>d2d d3d4d?d6d@dA dBd9ZNedd2d3d3d5d+d6d'dCdDdEZPedd2d3d3d:d+d6d;dCdFdEZPdd$ddddejOfd2d d3d3d?d+d6d@dGdHdEZPedd2dId5d6d+d'dJdKdLZQed
d2dId:d6d+d;dJdMdLZQdd$ddddejOdfd2d d4d?d6d+d@dNdOdLZQdd=d>dRd dSd?d+d>d=d?dT
|
dUdVZRdd=d>d dWdXdYZSeSZTdd>d dZd[d\d]ZUG d^d_ d_e:ZVG d`dZ dZe:eZWG dadb dbZXG dcdd ddeXZYd=dbdedfdgZZG dhdi dieWZ[djdkdldmdndodldpZ\dqdr Z]dsdt Z^G dudv dveVZ_G dwdx dxeWZ`dd=d+d>d=dydzd{ZadS )z|
|
Collection of query wrappers / abstractions to both facilitate data
|
retrieval and to reduce dependency on DB-specific API.
|
é )Ú annotations)ÚABCÚabstractmethod)Ú ExitStackÚcontextmanager)ÚdateÚdatetimeÚtime)ÚpartialN)Ú TYPE_CHECKINGÚAnyÚIteratorÚLiteralÚcastÚoverload)Úlib)ÚDateTimeErrorChoicesÚDtypeArgÚ DtypeBackendÚ
|
IndexLabel©Úimport_optional_dependency)ÚAbstractMethodErrorÚ DatabaseError)Úfind_stack_level)Úcheck_dtype_backend)Úis_datetime64tz_dtypeÚ is_dict_likeÚ
|
is_integerÚ is_list_like)ÚDatetimeTZDtype)Úisna)Ú
|
get_option)Ú DataFrameÚSeries)ÚArrowExtensionArray)Ú PandasObject)Úconvert_object_array)Ú to_datetime)ÚTable)ÚSelectÚ
|
TextClausec C s2 | dks| dks| dkr g } nt | ds.| g} | S )z3Process parse_dates argument for read_sql functionsTNFÚ__iter__)Úhasattr)Ú parse_dates© r/ úDd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\pandas/io/sql.pyÚ _process_parse_dates_argumentR s
|
|
r1 FÚboolzstr | dict[str, Any] | None)ÚutcÚformatc C s t |tr.| dd ¡pd}t| fd|i|S |d krZt| jjtjsVt| jjtj rZd}|dkrrt| d||dS t
|
| jrt| ddS t| d||d S d S )
|
NÚerrorsÚignoreÚs)ÚDÚdÚhÚmr7 ÚmsÚusÚnsZcoerce)r5 Úunitr3 T©r3 )r5 r4 r3 ) Ú
|
isinstanceÚdictÚpopr( Ú
|
issubclassÚdtypeÚtypeÚnpÚfloatingÚintegerr )Úcolr3 r4 Úerrorr/ r/ r0 Ú_handle_date_column] s
|
ÿþ
|
rL c C sf t |}| ¡ D ]P\}}t|js*||krz || }W n tk
|
rN d}Y nX t||d| |< q| S )zz
|
Force non-datetime columns to be read as such.
|
Supports both string formatted and integer timestamp columns.
|
N©r4 )r1 Úitemsr rE Ú TypeErrorrL )Z
|
data_framer. Úcol_nameÚdf_colÚfmtr/ r/ r0 Ú_parse_date_columnsy s
|
rS TÚnumpyú DtypeBackend | Literal['numpy']r# )Ú coerce_floatÚ dtype_backendÚreturnc sf t | ¡}tt|jd ||d}|dkrBtd fdd|D }|rXttt||S t|dS d S )N)rE rV rW Zpyarrowc s g | ]}t j|d dqS )T)Z from_pandas)r% Úarray©Ú.0Zarr©Úpar/ r0 Ú
|
<listcomp> s z0_convert_arrays_to_dataframe.<locals>.<listcomp>)Úcolumns) r Zto_object_array_tuplesr' ÚlistÚTr r# rB Úzip)Údatar_ rV rW ÚcontentZarraysr/ r\ r0 Ú _convert_arrays_to_dataframe s
|
ü
|
ÿre úDtypeArg | None)rV rE rW c C s< t | |||}|r | |¡}t||}|dk r8| |¡}|S )z(Wrap result set of query in a DataFrame.N)re ÚastyperS Ú set_index)rc r_ Ú index_colrV r. rE rW Úframer/ r/ r0 Ú _wrap_result¦ s
|
|
|
|
rk c
|
C sp t jdtt d tddd}|dk r@t|t|jjfr@t dt
|
|dd }| | |¡W 5 Q R £ S Q R X dS )
|
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Ú
|
sqlalchemyr6 ©r5 Nz+pandas.io.sql.execute requires a connectionT)Úneed_transaction) ÚwarningsÚwarnÚ FutureWarningr r rA ÚstrÚengineÚEnginerO ÚpandasSQL_builderÚexecute)ÚsqlÚconÚparamsrn Ú
|
pandas_sqlr/ r/ r0 rx ½ s ü rx .ústr | list[str] | Nonez!list[str] | dict[str, str] | Noneúlist[str] | NoneÚNonez DtypeBackend | lib.NoDefault)ri r. r_ Ú chunksizerW rX c C s d S ©Nr/ © Ú
|
table_namerz Úschemari rV r. r_ r rW r/ r/ r0 Úread_sql_tableà s r
ÚintzIterator[DataFrame]c C s d S r r/ r r/ r/ r0 r
ï s rt ú
|
str | Noneú
|
int | Noneú DataFrame | Iterator[DataFrame]) r r ri rV r. r_ r rW rX c
|
C s t | |tjkrd}t||dd8} | | ¡s@td| d| j| ||||||d}
|
W 5 Q R X |
|
dk rn|
|
S td| d|dS )a6
|
|
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"}, defaults to NumPy backed DataFrames
|
Which dtype_backend to use, e.g. whether a DataFrame should have NumPy
|
arrays, nullable dtypes are used for all dtypes that have a nullable
|
implementation when "numpy_nullable" is set, pyarrow is used for all
|
dtypes if "pyarrow" is set.
|
|
The dtype_backends are still experimential.
|
|
.. 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
|
rT T©r rp zTable z
|
not found©ri rV r. r_ r rW N)r r Ú
|
no_defaultrw Ú has_tableÚ
|
ValueErrorÚ
|
read_table) r rz r ri rV r. r_ r rW r| Útabler/ r/ r0 r
þ s$ L
|
|
ù
|
)ri r{ r. r rE rW rX c C s d S r r/ © ry rz ri rV r{ r. r rE rW r/ r/ r0 Úread_sql_queryb s r c C s d S r r/ r r/ r/ r0 r q s )ri rV r{ r. r rE rW rX c
|
C sP t | |tjkrd}t|(} | j| |||||||dW 5 Q R £ S Q R X dS )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
|
dtype_backend : {"numpy_nullable", "pyarrow"}, defaults to NumPy backed DataFrames
|
Which dtype_backend to use, e.g. whether a DataFrame should have NumPy
|
arrays, nullable dtypes are used for all dtypes that have a nullable
|
implementation when "numpy_nullable" is set, pyarrow is used for all
|
dtypes if "pyarrow" is set.
|
|
The dtype_backends are still experimential.
|
|
.. 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.
|
rT )ri r{ rV r. r rE rW N)r r r rw Ú
|
read_query)
|
ry rz ri rV r{ r. r rE rW r| r/ r/ r0 r s N
|
|
øú list[str])ri r_ r rW rE rX c
|
|
C s d S r r/ ©
|
ry rz ri rV r{ r. r_ r rW rE r/ r/ r0 Úread_sqlß s r c
|
|
C s d S r r/ r r/ r/ r0 r ï s )ri rV r_ r rW rE rX c
|
C sÔ t | |tjkrd}t|¬}
|
t|
|
trP|
|
j| ||||||| dW 5 Q R £ S z|
|
| ¡} W n tk
|
rv d} Y nX | r |
|
j | ||||||dW 5 Q R £ S |
|
j| ||||||| dW 5 Q R £ S W 5 Q R X dS )u¤
|
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.
|
dtype_backend : {"numpy_nullable", "pyarrow"}, defaults to NumPy backed DataFrames
|
Which dtype_backend to use, e.g. whether a DataFrame should have NumPy
|
arrays, nullable dtypes are used for all dtypes that have a nullable
|
implementation when "numpy_nullable" is set, pyarrow is used for all
|
dtypes if "pyarrow" is set.
|
|
The dtype_backends are still experimential.
|
|
.. 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('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
|
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
|
rT )ri r{ rV r. r rW rE Fr N)
|
r r r rw rA ÚSQLiteDatabaser r Ú Exceptionr ) ry rz ri rV r{ r. r_ r rW rE r| Z_is_table_namer/ r/ r0 r ÿ sN t
|
|
|
ø
|
ù
|
øÚfailÚautoú&Literal[('fail', 'replace', 'append')]r )
|
Únamer Ú if_existsÚindexÚ index_labelr rE Úmethodru rX c K s |dkrt d| dt| tr,| ¡ } nt| ts>tdt||dd4} | j| |f||||||| |
|
d| W 5 Q R £ S Q R X dS ) 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 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. 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_existsz9'frame' argument should be either a Series or a DataFrameTr )r r r r r rE r ru N)r rA r$ Zto_framer# ÚNotImplementedErrorrw Úto_sql) rj r rz r r r r r rE r ru Ú engine_kwargsr| r/ r/ r0 r¦ ¡ s0 S
|
|
|
ÿþö õr¦ ©r r rX c
|
C s. t ||d}| | ¡W 5 Q R £ S Q R X dS )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
|
©r N)rw r )r rz r r| r/ r/ r0 r s r Ú PandasSQL©r rp rX c C s ddl }t| |js | dkr$t| S tddd}t| trJ|dkrJtd|dk rpt| t|jjfrpt | ||S t
|
j dt t d 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.
|
r Nrn r6 ro z.Using URI string without sqlalchemy installed.z»pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.rl )Úsqlite3rA Ú
|
Connectionr r rt Ú ImportErrorru Z ConnectableÚ SQLDatabaserq rr Ú UserWarningr )rz r rp r¬ rn r/ r/ r0 rw ) s
|
ûrw c @ s e Zd ZdZd>ddddd d
|
d d d Zdd ZddddZd
|
dddZd
|
dddZdddddZ dddd d Z
|
d dd d Z d?d!d"d!d#d$d%Z d@d'd"d(d)d*d+d,Z dAd'd(d)d-d.d/d0Zd1d2 Zd3d4 Zd5d6 ZdBd)d
|
d7d8d9Zd:d; Zd<d= ZdS )CÚSQLTablezî
|
For mapping Pandas tables to SQL tables.
|
Uses fact that table is reflected by SQLAlchemy to
|
do better type conversions.
|
Also holds various flags needed to avoid having to
|
pass them between functions all the time.
|
NTr Úpandasrt ú bool | str | list[str] | Noner rf r )r r r ÚprefixrE rX c C s || _ || _|| _|| _| ||¡| _|| _|| _| | _|
|
| _ |d k rR|
|
¡ | _ n| j | j | j¡| _ | j d krt d| dd S )NzCould not init table 'r£ )r Úpd_sqlr´ rj Ú _index_namer r r ÚkeysrE Ú_create_table_setupr Ú get_tabler ) Úselfr Zpandas_sql_enginerj r r r´ r r r· rE r/ r/ r0 Ú__init__U s
|
zSQLTable.__init__c C s | j | j| j¡S r )rµ r r r ©rº r/ r/ r0 Úexistsv s zSQLTable.exists©rX c C s$ ddl m} t|| j | jj¡S )Nr )Ú CreateTable)Úsqlalchemy.schemar¿ rt r Úcompilerµ rz )rº r¿ r/ r/ r0 Ú
|
sql_schemay s zSQLTable.sql_schemac C s> | j | jj¡| _ | j ¡ | j j| jjd W 5 Q R X d S )N©Úbind)r Z to_metadatarµ ÚmetaÚrun_transactionÚcreaterz r¼ r/ r/ r0 Ú_execute_create~ s zSQLTable._execute_createc C sv | ¡ rj| jdkr$td| j d| jdkrJ| j | j| j¡ | ¡ qr| jdkrVqrtd| j dn| ¡ d S )Nr zTable 'z' already exists.r¡ r¢ r£ r¤ )r½ r r r rµ Ú
|
drop_tabler rÈ r¼ r/ r/ r0 rÇ s
|
|
|
|
zSQLTable.creater r )r· rX c s* fdd|D }| | j ¡ |¡}|jS )a<
|
Execute SQL statement inserting data
|
|
Parameters
|
----------
|
conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection
|
keys : list of str
|
Column names
|
data_iter : generator of list
|
Each item contains a list of values to be inserted
|
c s g | ]}t t |qS r/ ©rB rb ©r[ Úrow©r· r/ r0 r^ s z,SQLTable._execute_insert.<locals>.<listcomp>)rx r ÚinsertÚrowcount)rº Úconnr· Ú data_iterrc Úresultr/ rÍ r0 Ú_execute_insert s zSQLTable._execute_insertc s> ddl m} fdd|D }|| j |¡}| |¡}|jS )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 |qS r/ rÊ rË rÍ r/ r0 r^ s z2SQLTable._execute_insert_multi.<locals>.<listcomp>)rn rÎ r Úvaluesrx rÏ )rº rÐ r· rÑ rÎ rc ÚstmtrÒ r/ rÍ r0 Ú_execute_insert_multi¢ s
|
|
z SQLTable._execute_insert_multiz"tuple[list[str], list[np.ndarray]]c
|
C sL | j d k rd| j ¡ }| j |j _z|jdd W qj tk
|
r` } ztd| |W 5 d }~X Y qjX n| j}ttt|j }t
|
|}d g| }t | ¡ D ]ª\}\}}|j jdkr¼|j ¡ } nL|j jdkrü|j}
|
t|
|
trê|
|
jt d¡d}
|
|
|
d¡ t¡} n |j t¡} t| tjs"tt| |j r:t | } d | | < | ||< q||fS ) NT©Zinplacez!duplicate name in index/columns: ÚMr; zm8[ns]©rE Úi8) r rj ÚcopyÚnamesZ reset_indexr r` Úmaprt r_ ÚlenÚ enumeraterN rE ÚkindÚdtZ to_pydatetimeZ_valuesrA r% Zto_numpyrG Úviewrg ÚobjectZndarrayÚAssertionErrorrF Z _can_hold_nar! ) rº ÚtempÚerrÚ column_namesZncolsÚ data_listÚiÚ_Zserr9 ÚvalsÚmaskr/ r/ r0 Ú insert_data² s4
|
|
|
$
|
|
|
zSQLTable.insert_datar r )r r rX c s& |d kr| j }n2|dkr | j}n"t|r4t|| }ntd| | ¡ \}}t| j}|dkrddS |d krr|}n|dkrtd|| d }d }| j ¡ } t
|
|D ]p}
|
|
|
| t |
|
d | | krÒ qt fdd|D } || || } t | r¦|d kr| }q¦|| 7 }q¦W 5 Q R X |S )NZmultiz Invalid parameter `method`: r z%chunksize argument should be non-zeroé c 3 s | ]}|
V qd S r r/ rZ ©Zend_iZstart_ir/ r0 Ú <genexpr>ü s z"SQLTable.insert.<locals>.<genexpr>)rÓ rÖ Úcallabler
|
r rí rÞ rj rµ rÆ ÚrangeÚminrb r ) rº r r Z exec_insertr· rè ZnrowsÚchunksÚtotal_insertedrÐ ré Z
|
chunk_iterZ num_insertedr/ rï r0 rÎ Ú s<
|
|
zSQLTable.insertrT r r2 rU )Ú
|
exit_stackr rV rW c
|
c s d}|t | |¡} | s0|sxtjg ||dV qxd}t| |||| _| j||d | jdk rn| jj| jdd | jV q
|
W 5 Q R X dS )z,Return generator through chunked result set.F©r_ rV T©r. rW Nr× )Ú fetchmanyr# Ú from_recordsre rj Ú_harmonize_columnsr rh )
|
rº rÒ rö r r_ rV r. rW Ú has_read_datarc r/ r/ r0 Ú_query_iterator s2
|
ÿ ÿ ÿ
|
zSQLTable._query_iteratorr )rö rV rW rX c sú ddl m} |d k rpt|dkrp fdd|D } jd k rf jd d d
D ]} | d jj| ¡ qL|| }
|
n
|
| j}
|
j |
|
¡} | ¡ } |d k r® j
|
| ||| |||dS | ¡ } t | | || _ j||d jd k rð j j jdd j S d S )
|
Nr )Úselectc s g | ]} j j| qS r/ )r Úc)r[ Únr¼ r/ r0 r^ 6 s z!SQLTable.read.<locals>.<listcomp>éÿÿÿÿ)rV r. rW rø Tr× )rn rþ rÞ r rÎ r rÿ rµ rx r· rý Úfetchallre rj rû rh )rº rö rV r. r_ r rW rþ ÚcolsÚidxZ
|
sql_selectrÒ rç rc r/ r¼ r0 Úread* sD
|
|
|
ù
|
ÿ ÿ
|
z SQLTable.readc C s¨ |dkr| j jj}|d k rHt|ts*|g}t||krDtd| |S |dkrpd| j jkrp| j jjd krpdgS t
|
| j jj ¡S n"t|t r|gS t|tr |S d S d S )NTz@Length of 'index_label' should match number of levels, which is rî r ) rj r ÚnlevelsrA r` rÞ r r_ r ÚcomZfill_missing_namesrÜ rt )rº r r r r/ r/ r0 r¶ Y s.
|
|
ÿÿ
|
þ ý
|
|
zSQLTable._index_namec sp g }j d k rHtj D ].\}} jj |¡}| t||df¡ q| fddttjjD 7 }|S )NTc s6 g | ].}t jj| jjd d
|f dfqS )NF)rt rj r_ Ziloc)r[ ré ©Ú dtype_mapperrº r/ r0 r^ s ÿz8SQLTable._get_column_names_and_types.<locals>.<listcomp>) r rß rj Z_get_level_valuesr¢ rt rò rÞ r_ )rº r Úcolumn_names_and_typesré Z idx_labelZidx_typer/ r r0 Ú_get_column_names_and_typesy s
|
þz$SQLTable._get_column_names_and_typesc
|
s® ddl m m}m} ddlm} | | j¡} fdd|D }| jd k rt | js\| jg}n| j}||d| j
|
d i}| |¡ | j p| j jj }| } || j
|
| f|d|iS ) Nr )ÚColumnÚPrimaryKeyConstraintr) ©ÚMetaDatac s g | ]\}}} |||d qS ))r r/ )r[ r ÚtypÚis_index©r r/ r0 r^ s ÿz0SQLTable._create_table_setup.<locals>.<listcomp>r Z_pkr )rn r r r) rÀ r r Ú_sqlalchemy_typer· r r r¢ r rµ rÅ )
|
rº r r) r r
|
r_ r· Zpkcr rÅ r/ r r0 r¸ s
|
þ
|
|
|
|
z SQLTable._create_table_setup)rW rX c C s> t |}| jjD ]&}|j}z| j| }||krnz || }W n tk
|
rV d}Y nX t||d| j|< W q| |j¡}|t ks|t
|
ks|t kr®|t k}t||d| j|< np|dkrÔ|t krÔ|j |dd| j|< nJ|dkr t|| ¡ kr |t d¡ks
|
|tkr |j |dd| j|< W q tk
|
r6 Y qX qdS )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.
|
NrM r@ rT F)rÛ Úint64)r1 r r_ r rj rO rL Ú
|
_get_dtyperF r r r Úfloatrg rÞ ÚcountrG rE r2 ÚKeyError) rº r. rW Zsql_colrP rQ rR Úcol_typer3 r/ r/ r0 rû ¥ s8
|
|
ÿþý zSQLTable._harmonize_columnsc C s¬ | j pi }t|r0tt|}|j|kr0||j S tj|dd}ddlm}m }m
|
}m }m }m } m}
|
m} m} m} |dkrÊz |jjd k r|ddW S W n2 tk
|
rÄ t|dd d k rÀ|dd Y S Y nX |S |dkrètjd tt d
|
|S |d kr|j d kr| d dS | ddS n|dkrl|j j ¡ dkr4| S |j j ¡ dkrJ|
|
S |j j ¡ dkrftdn|S n<|dkrz|S |dkr|S |dkr| S |dkr¨td| S )NT©Zskipnar )
|
Ú TIMESTAMPÚ
|
BigIntegerÚBooleanÚDateÚDateTimeÚFloatÚIntegerÚ SmallIntegerÚTextÚTime)Ú
|
datetime64r )ÚtimezoneÚtzÚ timedelta64últhe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.rl rH Úfloat32é )Z precisioné5 rI )Zint8Zuint8Úint16)Zuint16Úint32Zuint64z1Unsigned 64 bit integer datatype is not supportedÚbooleanr r Úcomplexú Complex datatypes not supported) rE r r rB r r Ú infer_dtypeÚsqlalchemy.typesr r r r r r r! r" r# r$ rá r' ÚAttributeErrorÚgetattrrq rr r° r Úlowerr )rº rJ rE r r r r r r r r! r" r# r$ r/ r/ r0 r Ü sV
|
|
|
|
0 ü
|
|
|
|
|
|
|
|
zSQLTable._sqlalchemy_typec C s ddl m}m}m}m}m}m} t||r.tS t||rBt
|
d¡S t||rZ|j sVt S t S t||rht S t||rvtS t||rtS tS )Nr )r r r r r r! r )r3 r r r r r r! rA r rG rE r& r r r r2 rã )rº Zsqltyper r r r r r! r/ r/ r0 r " s
|
|
|
|
|
|
|
zSQLTable._get_dtype)NTr r² NNNN)NN)TNrT )TNNNrT )NrT )Ú__name__Ú
|
__module__Ú __qualname__Ú__doc__r» r½ r rÈ rÇ rÓ rÖ rí rÎ rý r r¶ r r¸ rû r r r/ r/ r/ r0 r± J sL õ!) ÿ2 ø' ù/ ý7Fr± c
|
@ sÆ e Zd ZdZdd ZddddZd+d d d dddddddZed,d d d dddddddZed-dd dd dd d d Z ed.d d d!d"Z
|
ed/d dd d#d$d%Z ed0d&d d'ddd(d)d*Z dS )1rª z9
|
Subclasses Should define read_query and to_sql.
|
c C s | S r r/ r¼ r/ r/ r0 Ú __enter__E s zPandasSQL.__enter__r r¾ c G s d S r r/ ©rº Úargsr/ r/ r0 Ú__exit__H s zPandasSQL.__exit__NTrT rt r} r2 r r rU r ©r ri rV r r rW rX c C s t d S r )r¥ ) rº r ri rV r. r_ r r rW r/ r/ r0 r K s zPandasSQL.read_tablerf ©ry ri rV r rE rW rX c C s d S r r/ ) rº ry ri rV r. r{ r rE rW r/ r/ r0 r X s zPandasSQL.read_queryr r r ©r r rE ru rX c K s d S r r/ ) rº rj r r r r r r rE r ru r§ r/ r/ r0 r¦ f s zPandasSQL.to_sqlústr | Select | TextClause©ry c C s d S r r/ )rº ry r{ r/ r/ r0 rx w s zPandasSQL.execute©r r rX c C s d S r r/ )rº r r r/ r/ r0 r { s zPandasSQL.has_tabler# r~ ©rj r r· rE r c C s d S r r/ )rº rj r r· rE r r/ r/ r0 Ú_create_sql_schema s z PandasSQL._create_sql_schema)NTNNNNrT )NTNNNNrT )r TNNNNNr )N)N)NNN) r7 r8 r9 r: r; r> r r r r¦ rx r rF r/ r/ r/ r0 rª @ sN ÷ ÷ õ úc @ s e Zd Zd ddddddZdS )
|
Ú
|
BaseEngineTNr± r³ r ©r r rX c
|
K s t | dS )z:
|
Inserts data into already-prepared table
|
N)r )
|
rº r rz rj r r r r r r§ r/ r/ r0 Úinsert_records s zBaseEngine.insert_records)TNNN)r7 r8 r9 rI r/ r/ r/ r0 rG s
|
÷rG c @ s. e Zd ZddddZd ddd d
|
d d ZdS )ÚSQLAlchemyEnginer r¾ c C s t ddd d S )Nrn z'sqlalchemy is required for SQL support.)Úextrar r¼ r/ r/ r0 r» s ÿzSQLAlchemyEngine.__init__TNr± r³ r rH c
|
K sn ddl m}
|
z|j||dW S |
|
jk
|
rh } z,d} t| j} t | | ¡rTtd| | W 5 d } ~ X Y nX d S )Nr )Úexc)r r zg(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?#
|
)|inf can not be used with MySQLz inf cannot be used with MySQL) rn rL rÎ ZStatementErrorrt ÚorigÚreÚsearchr )rº r rz rj r r r r r r§ rL ræ ÚmsgZerr_textr/ r/ r0 rI ¤ s
|
|
z SQLAlchemyEngine.insert_records)TNNN)r7 r8 r9 r» rI r/ r/ r/ r0 rJ s ÷rJ )ru rX c C s | dkrt d} | dkrztg}d}|D ]D}z | W S tk
|
rh } z|dt| 7 }W 5 d}~X Y q&X q&td| | dkrt S tddS ) zreturn our implementationr z io.sql.engineÚ z
|
- Nz±Unable to find a usable engine; tried using: 'sqlalchemy'.
|
A suitable version of sqlalchemy is required for sql I/O support.
|
Trying to import the above resulted in these errors:rn z*engine must be one of 'auto', 'sqlalchemy')r" rJ r® rt r )ru Zengine_classesZ
|
error_msgsZ engine_classræ r/ r/ r0 Ú
|
get_engine¿ s $ÿ
|
rR c @ sJ e Zd ZdZdCdddddd Zdd
|
d d Zed d ZdDddddZdEdddddddddd Z e
|
dFd d dd dd d!d"Z dGddddd ddd#d$d%Z e Z dHd'd(d d)d*d+d,Zdddd-d.d/ZdIdd'ddd ddd1d2d3Zed4d5 ZdJdddd-d6d7ZdKddd8d9d:d;ZdLdddd9d<d=ZdMd>dd?d dd@dAdBZdS )Nr¯ aa
|
This class enables conversion between DataFrame and SQL databases
|
using SQLAlchemy to handle DataBase abstraction.
|
|
Parameters
|
----------
|
con : SQLAlchemy Connectable or URI string.
|
Connectable to connect with the database. Using SQLAlchemy makes it
|
possible to use any DB supported by that library.
|
schema : string, default None
|
Name of SQL schema in database to write to (if database flavor
|
supports this). If None, use default schema (default).
|
need_transaction : bool, default False
|
If True, SQLDatabase will create a transaction.
|
|
NFr r2 r r« c C s ddl m} ddlm} ddlm} t | _t|t rL||}| j
|
|j ¡ t||rf| j | ¡ ¡}|r| ¡ s| j | ¡ ¡ || _||d| _d| _d S )Nr )Ú create_engine)rv r r© F)rn rS Zsqlalchemy.enginerv rÀ r r rö rA rt ÚcallbackZdisposeÚ enter_contextÚconnectÚin_transactionÚbeginrz rÅ Úreturns_generator)rº rz r rp rS rv r r/ r/ r0 r» ñ s
|
|
zSQLDatabase.__init__r¾ c G s | j s| j ¡ d S r )rY rö Úcloser< r/ r/ r0 r> s zSQLDatabase.__exit__c c s6 | j ¡ s*| j ¡ | j V W 5 Q R X n| j V d S r )rz rW rX r¼ r/ r/ r0 rÆ s
|
zSQLDatabase.run_transactionrB rC c C s@ |dkr g n|g}t |tr.| jj|f| S | jj|f| S )z,Simple passthrough to SQLAlchemy connectableN)rA rt rz Zexec_driver_sqlrx )rº ry r{ r= r/ r/ r0 rx s
|
zSQLDatabase.executeTrT rt r} r rU r r? c
|
C sJ | j j| j|gd t|| ||d} |dk r2d| _| j| j|||||dS )ai
|
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"}}, defaults to NumPy dtypes
|
Which dtype_backend to use, e.g. whether a DataFrame should have NumPy
|
arrays, nullable dtypes are used for all dtypes that have a nullable
|
implementation when "numpy_nullable" is set, pyarrow is used for all
|
dtypes if "pyarrow" is set.
|
|
The dtype_backends are still experimential.
|
|
.. versionadded:: 2.0
|
|
Returns
|
-------
|
DataFrame
|
|
See Also
|
--------
|
pandas.read_sql_table
|
SQLDatabase.read_query
|
|
)rÄ Úonly)r r NT)rV r. r_ r rW )rÅ Úreflectrz r± rY r rö )
|
rº r ri rV r. r_ r r rW r r/ r/ r0 r s >úzSQLDatabase.read_tabler r rf )rö r rV rE rW c
|
c sb d} |P | |¡}
|
|
|
s6| sTtg ||||||dV qTd} t|
|
||||||dV q
|
W 5 Q R X dS )ú+Return generator through chunked result setF©ri rV r. rE rW TN)rù rk ) rÒ rö r r_ ri rV r. rE rW rü rc r/ r/ r0 rý f s2
|
ù ùzSQLDatabase._query_iteratorr@ c C sf | ||¡} | ¡ }
|
|dk r@d| _| j| | j||
|
|||||d S | ¡ } t| |
|
|||||d} | S dS )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
|
|
NTr^ )rx r· rY rý rö r rk ) rº ry ri rV r. r{ r rE rW rÒ r_ rc rj r/ r/ r0 r s4 9 ÷ ù zSQLDatabase.read_queryr r r³ r± )r r rE rX c
|
s rzt s fdd|D n
|
tt ddlm} ¡ D ]:\} }
|
t|
|
tr\t|
|
|r\q>t|
|
|rhq>t d| dq>t
|
|| ||||| d} | ¡ | S )z_
|
Prepares table in the database for data insertion. Creates it if needed, etc.
|
c s i | ]
|
}| qS r/ r/ ©r[ rP rÙ r/ r0 Ú
|
<dictcomp>ü s z*SQLDatabase.prep_table.<locals>.<dictcomp>r )Ú
|
TypeEnginez The type of z is not a SQLAlchemy type)rj r r r r rE ) r r rB r3 ra rN rA rF rD r r± rÇ ) rº rj r r r r r rE ra rJ Úmy_typer r/ rÙ r0 Ú
|
prep_tableç s.
|
|
ø
|
zSQLDatabase.prep_tablerD c C sd | ¡ s`| ¡ s`ddlm} || j}|j|p4| jjd}||kr`d| d}tj |t
|
t d dS )zv
|
Checks table name for issues with case-sensitivity.
|
Method is called after data is inserted.
|
r ©Úinspectr© zThe provided table name 'z' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.rl N) ÚisdigitÚislowerrn re rz Zget_table_namesrÅ r rq rr r° r )rº r r Úsqlalchemy_inspectÚinspZ table_namesrP r/ r/ r0 Úcheck_case_sensitive s
|
|
ÿýz SQLDatabase.check_case_sensitiver )r r r r rE ru rX c K sV t |
|
} | j|||||||d} | jf | | 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.
|
)rj r r r r r rE )r rz rj r r r r r )r r )rR rc rI rz rj )rº rj r r r r r r rE r ru r§ Z
|
sql_enginer rõ r/ r/ r0 r¦ 4 s0 >ù
|
ø ÷ zSQLDatabase.to_sqlc C s | j jS r )rÅ Útablesr¼ r/ r/ r0 rk s zSQLDatabase.tablesc C s* ddl m} || j}| ||p&| jj¡S )Nr rd )rn re rz r rÅ r )rº r r rh ri r/ r/ r0 r s
|
zSQLDatabase.has_tabler) r¨ c C sT ddl m}m} |p| jj}||| j| j|d}|jD ]}t|j|r6d|j_ q6|S )Nr )ÚNumericr) )Z autoload_withr F)
|
rn rl r) rÅ r rz r_ rA rF Z asdecimal)rº r r rl r) ZtblÚcolumnr/ r/ r0 r¹ s
|
|
zSQLDatabase.get_tablec C sf |p
|
| j j}| ||¡rb| j j| j|g|d | ¡ | ||¡j| jd W 5 Q R X | j ¡ d S )N)rÄ r[ r rà ) rÅ r r r\ rz rÆ r¹ ZdropÚclear©rº r r r/ r/ r0 rÉ ¤ s
|
zSQLDatabase.drop_tabler# r~ rE c C s" t || |d|||d}t| ¡ S ©NF)rj r r· rE r )r± rt r ©rº rj r r· rE r r r/ r/ r0 rF ¬ s ù z SQLDatabase._create_sql_schema)NF)N)NTNNNNrT )NTNNrT )NTNNNNrT )r TNNN)r TNNNNNr )N)N)N)NNN)r7 r8 r9 r: r» r> r rÆ rx r Ú staticmethodrý r r rc rj r¦ Úpropertyrk r r¹ rÉ rF r/ r/ r/ r0 r¯ ß sr ÿ
|
|
÷ K ÷+ ÷ V ø0! õ Y
|
úr¯ ZTEXTZREALZINTEGERr ZDATEZTIME)ÚstringrH rI r r r r/ c
|
C sT zt | dd¡ d¡}W n4 tk
|
rN } ztd| d|W 5 d }~X Y nX |S )Nzutf-8Ústrictz%Cannot convert identifier to UTF-8: 'r£ )rt ÚencodeÚdecodeÚ UnicodeErrorr )r Úunameræ r/ r/ r0 Ú_get_unicode_nameÎ s
|
$rz c C sF t | }t|std| d¡}|dkr2tdd| dd¡ d S )Nz$Empty table or column name specifiedú r z%SQLite identifier cannot contain NULsú"z"")rz rÞ r Úfindr¡ )r ry Z nul_indexr/ r/ r0 Ú_get_valid_sqlite_nameÖ s
|
r~ c s~ e Zd ZdZdd fdd ZddddZddd d
|
Zd dd d dZd dddZd dddZ dd Z
|
dd Z Z S )Ú SQLiteTablezw
|
Patch the SQLTable for fallback support.
|
Instead of a table variable just use the Create Table statement.
|
r r¾ c s4 dd l }dddd}| t|¡ t j|| d S )Nr rt r¾ c S s* | j dd| jdd| jdd| jdS )NZ02dú:Ú.Z06d)ÚhourÚminuteÚsecondÚ microsecond)Útr/ r/ r0 Ú _adapt_timeõ s z)SQLiteTable.__init__.<locals>._adapt_time)r¬ Zregister_adapterr Úsuperr» )rº r= Úkwargsr¬ r ©Ú __class__r/ r0 r» î s zSQLiteTable.__init__rt c C s t d | j¡S )Nz;
|
)rt Újoinr r¼ r/ r/ r0 r ü s zSQLiteTable.sql_schemac C s0 | j ¡ }| jD ]}| |¡ qW 5 Q R X d S r )rµ rÆ r rx )rº rÐ rÕ r/ r/ r0 rÈ ÿ s
|
zSQLiteTable._execute_creater )Únum_rowsrX c s² t tt| jj}d}t | jd k rF| jd d d
D ]}| d|¡ q4 fdd|D }d |¡}d |gt | ¡d fddt
|
|D ¡}d | j d | d
|
| }|S ) Nú?r r c s g | ] } |qS r/ r/ )r[ rm ©Úescaper/ r0 r^ s z0SQLiteTable.insert_statement.<locals>.<listcomp>ú,c s g | ]}d dqS )ú(ú)r/ )r[ rê )Ú row_wildcardsr/ r0 r^ s z INSERT INTO ú (z ) VALUES ) r` rÝ rt rj r_ r~ r rÎ r rÞ rò r ) rº r rÜ Úwldr Zbracketed_namesZ col_namesÚ wildcardsÚinsert_statementr/ )r r r0 r s
|
|
ÿz SQLiteTable.insert_statementc C s" t |}| | jdd|¡ |jS )Nrî ©r )r` Z executemanyr rÏ )rº rÐ r· rÑ rè r/ r/ r0 rÓ s zSQLiteTable._execute_insertc C s4 t |}dd |D }| | jt|d|¡ |jS )Nc S s g | ]}|D ]}|q qS r/ r/ )r[ rÌ Úxr/ r/ r0 r^ s z5SQLiteTable._execute_insert_multi.<locals>.<listcomp>r )r` rx r rÞ rÏ )rº rÐ r· rÑ rè Zflattened_datar/ r/ r0 rÖ s z!SQLiteTable._execute_insert_multic s6 | | j¡}t fdd|D }| jdk rt| jrt| jsJ| jg}n| j}d fdd|D ¡}| d| j d| d¡ | j r| j d }nd
|
}d | | j d d |¡ d g}dd |D }t|r2d |¡}d fdd|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 | qS )ú r/ )r[ ÚcnameÚctyperê r r/ r0 r^ + s z3SQLiteTable._create_table_setup.<locals>.<listcomp>Nz, c s g | ] } |qS r/ r/ ©r[ rÿ r r/ r0 r^ 4 s z CONSTRAINT z_pk PRIMARY KEY (r r rQ z CREATE TABLE z (
|
z,
|
z
|
)c S s g | ]\}}}|r|qS r/ r/ )r[ r rê r r/ r/ r0 r^ E s rê r c s g | ] } |qS r/ r/ r r r/ r0 r^ H s z CREATE INDEX Zix_zON r )
|
r Ú_sql_type_namer~ r· rÞ r r r¢ r r ) rº r
|
Zcreate_tbl_stmtsr· Z cnames_brZ schema_nameZ create_stmtsZix_colsZcnamesr/ r r0 r¸ " s`
|
ÿ
|
|
ÿ ÿþýüûÿ
|
|
ÿþýüûúÿ
|
z SQLiteTable._create_table_setupc C s | j pi }t|r0tt|}|j|kr0||j S tj|dd}|dkr^tjdt t
|
d d}n,|dkrld}n |d krzd
|
}n|d krt d |t krd
|
}t | S ) NTr r( r) rl rI r% r Úemptyrt r0 r1 ) rE r r rB r r r2 rq rr r° r r Ú
|
_SQL_TYPES)rº rJ rE r r/ r/ r0 r U s,
|
|
|
|
üzSQLiteTable._sql_type_name) r7 r8 r9 r: r» r rÈ r rÓ rÖ r¸ r Ú __classcell__r/ r/ r r0 r è s 3r c @ sÜ e Zd ZdZddddZedd Zd/d d
|
d d Zed0dddddddZ d1ddddddddZ
|
dd Z d2d ddd dd d!d"Z d3d d#dd$d%d&Z d4d d#dd'd(d)Zd5d d#dd$d*d+Zd6d dd#d,d-d.ZdS )7r zÉ
|
Version of SQLDatabase to support SQLite connections (fallback without
|
SQLAlchemy). This should only be used internally.
|
|
Parameters
|
----------
|
con : sqlite connection object
|
|
r r¾ c C s
|
|| _ d S r ©rz )rº rz r/ r/ r0 r» s zSQLiteDatabase.__init__c c sT | j ¡ }z:z|V | j ¡ W n tk
|
r@ | j ¡ Y nX W 5 | ¡ X d S r )rz ÚcursorrZ Úcommitr Úrollback)rº Úcurr/ r/ r0 rÆ s
|
|
z SQLiteDatabase.run_transactionNrB rC c C sÔ t |tstd|d kr g n|g}| j ¡ }z|j|f| |W S tk
|
rÎ } zlz| j ¡ W n> tk
|
r¢ } z td| d| d}||W 5 d }~X Y nX td| d| }||W 5 d }~X Y nX d S )Nz/Query must be a string unless using sqlalchemy.zExecution failed on sql: Ú
|
z
|
unable to rollbackzExecution failed on sql 'z': ) rA rt rO rz r¤ rx r r¦ r )rº ry r{ r= r§ rL Z inner_excÚexr/ r/ r0 rx s"
|
|
ÿzSQLiteDatabase.executeTrT r r2 rf rU )r rV rE rW c c sz d}| |¡} t| tkr"t| } | sX| ¡ |svtjg ||d}
|
|rP|
|
|¡}
|
|
|
V qvd}t| ||||||dV qdS )r] Fr÷ Tr^ N) rù rF Útupler` rZ r# rú rg rk ) r¤ r r_ ri rV r. rE rW rü rc rÒ r/ r/ r0 rý ¦ s4
|
ÿ
|
ùz SQLiteDatabase._query_iteratorr r )rV r rE rW rX c
|
C sn | ||¡} dd | jD }
|
|d k r>| j| ||
|
|||||dS | | ¡} | ¡ t| |
|
|||||d} | S d S )Nc S s g | ] }|d qS )r r/ )r[ Zcol_descr/ r/ r0 r^ Ù s z-SQLiteDatabase.read_query.<locals>.<listcomp>r^ )rx Ú descriptionrý Ú_fetchall_as_listrZ rk ) rº ry ri rV r. r{ r rE rW r¤ r_ rc rj r/ r/ r0 r Í s2 ø
|
ù zSQLiteDatabase.read_queryc C s | ¡ }t|tst|}|S r )r rA r` )rº r§ rÒ r/ r/ r0 r¬ õ s
|
z SQLiteDatabase._fetchall_as_listr r rt rA c s rZt s fdd|D n
|
tt ¡ D ]&\} } t| ts2t| d| dq2t|| |||| 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 | ]
|
}| qS r/ r/ r_ rÙ r/ r0 r` 7 s z)SQLiteDatabase.to_sql.<locals>.<dictcomp>r z) not a string)rj r r r rE )
|
r r rB rN rA rt r r rÇ rÎ )rº rj r r r r r r rE r ru r§ rJ rb r r/ rÙ r0 r¦ û s$ 4
|
|
ù zSQLiteDatabase.to_sqlr rD c C s* d}d| d}t | ||g¡ ¡ dkS )Nr z;SELECT name FROM sqlite_master WHERE type='table' AND name=ú;r )rÞ rx r )rº r r r Úqueryr/ r/ r0 r K s zSQLiteDatabase.has_tabler¨ c C s d S r r/ ro r/ r/ r0 r¹ Q s zSQLiteDatabase.get_tablec C s dt | }| |¡ d S )Nz DROP TABLE )r~ rx )rº r r Zdrop_sqlr/ r/ r0 rÉ T s zSQLiteDatabase.drop_table)r rE r c C s" t || |d|||d}t| ¡ S rp )r rt r rq r/ r/ r0 rF X s ù z!SQLiteDatabase._create_sql_schema)N)NTNNrT )NTNNNNrT )r TNNNNNr )N)N)N)NNN)r7 r8 r9 r: r» r rÆ rx rr rý r r¬ r¦ r r¹ rÉ rF r/ r/ r/ r0 r x sJ
|
|
ø) ÷(
|
õP úr )r rE r rX c
|
C s6 t |d"}|j| ||||dW 5 Q R £ S Q R X dS )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
|
r£ )r· rE r N)rw rF )rj r r· rz rE r r| r/ r/ r0 Ú
|
get_schemal s ÿr¯ )FN)TrT )NTNNrT )N).......).......).......).......).......N).......N)Nr TNNNNr )N)NF)NNNN)br: Ú
|
__future__r Úabcr r Ú
|
contextlibr r r r r Ú functoolsr
|
rN Útypingr r r r r r rq rT rG Z pandas._libsr Zpandas._typingr r r r Zpandas.compat._optionalr Z pandas.errorsr r Zpandas.util._exceptionsr Zpandas.util._validatorsr Zpandas.core.dtypes.commonr r r r Zpandas.core.dtypes.dtypesr Zpandas.core.dtypes.missingr! r² r" Zpandas.core.apir# r$ Zpandas.core.arraysr% Zpandas.core.baser& Zpandas.core.commonÚcoreÚcommonr Z"pandas.core.internals.constructionr' Zpandas.core.tools.datetimesr( rn r) Zsqlalchemy.sql.expressionr* r+ r1 rL rS re rk rx r
r r r r¦ r Z table_existsrw r± rª rG rJ rR r¯ r¡ rz r~ r r r¯ r/ r/ r/ r0 Ú<module> sT
|
ÿ ü ù
|
# ÷ ÷ ÷ d ÷ ÷ ÷ _ ö ö ö ' õ"m ý! yK! hù x ú
|
