B Y0d ' @sxUdZddlmZddlmZddlmZmZmZddlm Z ddl Z ddl m Z m Z mZmZmZddlZddlZddlmmZddlmZdd lmZdd lmZdd lmZmZm Z dd l!m"Z"dd l#m$Z$ddl%m&Z&ddl'm(Z(m)Z)ddl*m+Z+ddl,m-Z-ddl.m/Z/Gddde0Z1Gddde2Z3da4de5d<ddZ6ddddZ7dd Z8d!d"Z9dydd#d$d%d&Z:d'd(Z;dzdd*d+d,d-Zed}d6d7d2d8d4Z>d~d9d:d;ddd?d4Z>edd0d*d1d@dAdBZ?edd6d*d7d@dCdBZ?ddd|j|dd|S)z(Wrap result set of query in a DataFrame.)columnsrUNT)inplace)r from_recordsastyperT set_index)datarV index_colrUr5rGframerrr _wrap_results   r^cCs2|dkrt|}n t|dd}t||}|j|S)aY 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. cur : deprecated, cursor is obtained from connection, default: None params : list or tuple, optional, default: None List of parameters to pass to execute method. Returns ------- Results Iterable NT) is_cursor)pandasSQL_builderr3execute)r0r'curr1 pandas_sqlr2rrr ras    raNoner) chunksizer*cCsdS)Nr) table_namer'schemar\rUr5rVrerrr read_sql_tables rhintzIterator[DataFrame]cCsdS)Nr)rfr'rgr\rUr5rVrerrr rhs strz str | Nonezstr | Sequence[str] | Nonez int | NonezDataFrame | Iterator[DataFrame])rfrgr\rUrer*c Cst|}t|stdddl}ddlm} | ||d} y| j|gddWn8|jjk r} zt d|d | Wdd} ~ XYnXt || d } | j ||||||d } | dk r| St d|d |dS) 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 z9read_sql_table only supported for SQLAlchemy connectable.rN)MetaData)rgT)onlyZviewszTable z not found)meta)r\rUr5rVre) _engine_builderr(NotImplementedErrorr#sqlalchemy.schemarlreflectexcZInvalidRequestError ValueError SQLDatabase read_table)rfr'rgr\rUr5rVrer#rlrnerrrctablerrr rhs,A  $ )rerGr*cCsdS)Nr)r0r'r\rUr1r5rerGrrr read_sql_queryTs rycCsdS)Nr)r0r'r\rUr1r5rerGrrr rybs )rUrerGr*c Cs t|}|j|||||||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 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\r1rUr5rerG)r` read_query) r0r'r\rUr1r5rerGrcrrr rypsCcCsdS)Nr)r0r'r\rUr1r5rVrerrr read_sqls r{cCsdS)Nr)r0r'r\rUr1r5rVrerrr r{s )r\rUrer*c Cst|}t|tr(|j||||||dSy||} Wntk rNd} YnX| rz|jj|gd|j||||||dS|j||||||dSdS)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 `_. 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) >>> 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\r1rUr5reF)rm)r\rUr5rVreN) r`r%SQLiteDatabaserz has_table Exceptionrnrrrv) r0r'r\rUr1r5rVrercZ_is_table_namerrr r{s:|  failauto) namerg if_existsindexrerGmethodr&r*c Kst|dkrtd|dt||d} t|tr8|}nt|tsJtd| j||f||||||| | d| dS)aX 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)``. Details and a sample callable implementation can be found in the section :ref:`insert method `. engine : {'auto', 'sqlalchemy'}, default 'auto' SQL engine library to use. If 'auto', then the option ``io.sql.engine`` is used. The default ``io.sql.engine`` behavior is 'sqlalchemy' .. versionadded:: 1.3.0 **engine_kwargs Any additional kwargs are passed to the engine. )rreplaceappend'z' is not valid for if_exists)rgz9'frame' argument should be either a Series or a DataFrame)rr index_labelrgrerGrr&N)rtr`r%rZto_framerrpto_sql) r]rr'rgrrrrerGrr& engine_kwargsrcrrr r~s(C    r)rfrgcCst||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 )rg)r`r})rfr'rgrcrrr r}s r}cCsBt|tr>y ddl}Wntk r.daYnX||}|S|S)zw Returns a SQLAlchemy engine from a URI (if con is a string) else it just return con without modifying it. rNF)r%rkr#r$r"Z create_engine)r'r#rrr ros    roF)rgr_cCsBt|}t|rt|||dSt|tr2tdn t||dSdS)zm Convenience function to return the correct PandasSQL subclass based on the provided parameters. )rgrnz.Using URI string without sqlalchemy installed.)r_N)ror(rur%rkr$r|)r'rgrnr_rrr r` s   r`c@seZdZdZd2dddd d Zd d Zd dZddZddZddddZ ddddZ ddZ d3dddddZ d4dd d!d"d#Z d5d$d%Zd&d'Zd(d)Zd*d+Zd6d,d-Zd.d/Zd0d1ZdS)7SQLTablez 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. NTrpandasrkzDtypeArg | None)rrGc Cs||_||_||_||_||||_||_||_| |_| |_ |dk rR| |_ n|j |j|j|_ |j dkrt d|ddS)NzCould not init table 'r)rpd_sqlprefixr] _index_namerrgrr-rG_create_table_setuprx get_tablert) selfrZpandas_sql_enginer]rrrrrgr-rGrrr __init__&s   zSQLTable.__init__cCs|j|j|jS)N)rr}rrg)rrrr existsGszSQLTable.existscCs$ddlm}t||j|jjS)Nr) CreateTable)rqrrkrxcompiler connectable)rrrrr sql_schemaJs zSQLTable.sql_schemacCs:tr|j|jj|_n|j|jj|_|jdS)N)r,rxZ to_metadatarrnZ tometadatacreate)rrrr _execute_createOszSQLTable._execute_createcCsx|rl|jdkr&td|jdqt|jdkrL|j|j|j|qt|jdkrXqttd|jdn|dS)NrzTable 'z' already exists.rrrz' is not valid for if_exists)rrrtrr drop_tablergr)rrrr rWs    zSQLTable.createz list[str])r-cs(fdd|D}||j|dS)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 csg|]}tt|qSr)rDzip).0row)r-rr qsz,SQLTable._execute_insert..N)rarxinsert)rconnr- data_iterr[r)r-r _execute_insertes zSQLTable._execute_insertcs(fdd|D}||j|dS)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. csg|]}tt|qSr)rDr)rr)r-rr r|sz2SQLTable._execute_insert_multi..N)rarxr)rrr-rr[r)r-r _execute_insert_multitszSQLTable._execute_insert_multic Cs0|jdk rd|j}|j|j_y|jddWqjtk r`}ztd||Wdd}~XYqjXn|j}ttt|j }t |}dg|}xt | D]\}\}}|j } | jjdkr| } n(| jjdkr| dt} n | t} t| tjstt| |jrt| } d| | <| ||<qW||fS)NT)rWz!duplicate name in index/columns: Mr?i8)rr]copynamesZ reset_indexrtr/maprkrVlen enumeraterOZ_valuesrGkindZ to_pydatetimeviewrYobjectr%rIZndarrayAssertionErrorrHZ _can_hold_nar) rtemprw column_namesZncols data_listi_Zservalsr=maskrrr insert_datas0   $      zSQLTable.insert_dataz int | Nonez str | None)rerc s|dkr|j}n2|dkr |j}n"t|r4t||}ntd||\}}t|j}|dkrddS|dkrr|}n|dkrtd||d}|j `}xXt |D]L} | |t | d||krPt fdd|D} |||| qWWdQRXdS)NmultizInvalid parameter `method`: rz%chunksize argument should be non-zeroricsg|]}|qSrr)rZarr)end_istart_irr rsz#SQLTable.insert..) rrcallablerrtrrr]rrun_transactionrangeminr) rrerZ exec_insertr-rZnrowschunksrrZ chunk_iterr)rrr rs0     zSQLTable.insertr))rerUccs|d}xr||}|s.|s*tjg||dVPqd}tj|||d|_|j|d|jdk rl|jj|jdd|jVqWdS)z,Return generator through chunked result set.F)rVrUT)r5N)rW) fetchmanyrrXr]_harmonize_columnsrrZ)rresultrerVrUr5 has_read_datar[rrr _query_iterators    zSQLTable._query_iteratorc s|dk rtt|dkrtddlm}fdd|D}jdk rjx,jdddD]}|djj|qNW||}n j}j|} | } |dk rj | || ||dS| } t j | | |d_j|djdk rjjjd d jSdS) Nr)selectcsg|]}jj|qSr)rxc)rn)rrr rsz!SQLTable.read..)rUr5)rVrU)r5T)rW)rr#rrrrxrrrar-rfetchallrrXr]rrZ) rrUr5rVrercolsidxZ sql_selectrrr[r)rr reads0       z SQLTable.readcCs|dkr|jjj}|dk rJt|ts*|g}t||krFtd|n|S|dkrrd|jjkrr|jjjdkrrdgSddt |jjj DSn"t|t r|gSt|tr|SdSdS)NTz@Length of 'index_label' should match number of levels, which is rircSs&g|]\}}|dk r|nd|qS)NZlevel_r)rrlrrr r%sz(SQLTable._index_name..) r]rnlevelsr%r/rrtrVrrrrk)rrrrrrr r s(      zSQLTable._index_namecstg}jdk rLx.) rrr]Z_get_level_valuesrrkrrrV)rrcolumn_names_and_typesrZ idx_labelZidx_typer)rrr _get_column_names_and_types1s  z$SQLTable._get_column_names_and_typesc sddlmm}m}||j}fdd|D}|jdk rtt|jsP|jg}n|j}||d|jdi}| ||j p|j j j }ddl m}||j |d} ||j| f|d |iS) Nr)ColumnPrimaryKeyConstraintTablecs g|]\}}}|||dqS))rr)rrtypis_index)rrr rIsz0SQLTable._create_table_setup..rZ_pk)rl)rgrg)r#rrrr_sqlalchemy_typer-rrrrgrrnrqrl) rrrrrVr-Zpkcrgrlrnr)rr r?s       zSQLTable._create_table_setupc Cs,t|}x|jjD]}|j}y|j|}||krny ||}Wntk rXd}YnXt||d|j|<w||j}|t ks|t ks|t kr|t k}t||d|j|<n\|t kr|j |dd|j|<n>t||kr |tdks|tkr |j |dd|j|<Wqtk r"YqXqWdS)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. N)r8)r7F)rint64)r6rxrVrr]rPrN _get_dtyperHrrrfloatrYrcountrIrGr)KeyError)rr5Zsql_colrQrRrScol_typer7rrr r_s2     zSQLTable._harmonize_columnscCs|jpi}t|r0tt|}|j|kr0||jStj|dd}ddlm}m }m }m }m }m } m} m} m} m} |dks~|dkry|jjdk r|ddSWn.tk rt|dddk r|ddSYnX|S|d krtjd td d |S|d kr|jdkr| ddS| ddSn|dkrl|jjdkr4| S|jjdkrJ| S|jjdkrftdn|Sn<|dkrz|S|dkr|S|dkr| S|dkrtd| S)NT)skipnar) TIMESTAMP BigIntegerBooleanDateDateTimeFloatInteger SmallIntegerTextTime datetime64r)timezonetz timedelta64zlthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.) stacklevelrJZfloat32) precision5rK)Zint8Zuint8Zint16)Zuint16Zint32Zuint64z1Unsigned 64 bit integer datatype is not supportedbooleanrrcomplexzComplex datatypes not supported)rGrr rDrlib infer_dtypesqlalchemy.typesrrrrrrrrrrdtrAttributeErrorgetattrwarningswarn UserWarninglowerrt)rrLrGrrrrrrrrrrrrrr rsT    0            zSQLTable._sqlalchemy_typecCsddlm}m}m}m}m}m}t||r.tSt||rBt dSt||rZ|j sVt St St||rht St||rvtSt||rtStS)Nr)rrrrrrr)rrrrrrrr%rrIrGrrrrr)r)rZsqltyperrrrrrrrr rs        zSQLTable._get_dtype)NTrrNNNN)NN)TN)TNNN)N)rrr__doc__rrrrrrrrrrrrrrrrrrrrr rs4 &) %$ 3Grc@s(eZdZdZddZd ddd d ZdS) PandasSQLz7 Subclasses Should define read_sql and to_sql. cOs tddS)NzMPandasSQL must be created with an SQLAlchemy connectable or sqlite connection)rt)rr2kwargsrrr r{szPandasSQL.read_sqlrTNzDtypeArg | None)rGc Cs tddS)NzMPandasSQL must be created with an SQLAlchemy connectable or sqlite connection)rt) rr]rrrrrgrerGrrrr rs zPandasSQL.to_sql)rTNNNNN)rrrrr{rrrrr rs rc@seZdZdddddZdS) BaseEngineTNr)rxc Ks t|dS)z: Inserts data into already-prepared table N)r) rrxr'r]rrrgrerrrrr insert_recordsszBaseEngine.insert_records)TNNN)rrrrrrrr rsrc@s$eZdZddZd ddddZdS) SQLAlchemyEnginecCstddddS)Nr#z'sqlalchemy is required for SQL support.)extra)r)rrrr r(szSQLAlchemyEngine.__init__TNr)rxc  Ksrddlm} y|j||dWnN| jk rl} z.d} t| j} t| | rXtd| n| Wdd} ~ XYnXdS)Nr)rs)rerzg(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?# )|inf can not be used with MySQLzinf cannot be used with MySQL) r#rsrZSQLAlchemyErrorrkorigresearchrt)rrxr'r]rrrgrerrrsrwmsgZerr_textrrr r-s    zSQLAlchemyEngine.insert_records)TNNN)rrrrrrrrr r's  r)r&r*c Cs|dkrtd}|dkrztg}d}xF|D]>}y|Stk rd}z|dt|7}Wdd}~XYq(Xq(Wtd|n|dkrtStddS) zreturn our implementationrz io.sql.enginez - NzUnable to find a usable engine; tried using: 'sqlalchemy'. A suitable version of sqlalchemy is required for sql I/O support. Trying to import the above resulted in these errors:r#z*engine must be one of 'auto', 'sqlalchemy')rrr$rkrt)r&Zengine_classesZ error_msgsZ engine_classrwrrr get_engineIs & r c@seZdZdZd5ddddZeddZd d Zd6d d ddddddZe d7dddddZ d8d dddddddZ e Z d9dddddZ d d!Zd:dd#d$d%Zed&d'Zd;d dd(d)d*Zdd0d d1ddd2d3d4ZdS)?rua 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). meta : SQLAlchemy MetaData object, default None If provided, this MetaData object is used instead of a newly created. This allows to specify database flavor specific arguments in the MetaData object. Nz str | None)rgcCs.||_|s$ddlm}||j|d}||_dS)Nr)rl)rg)rrqrlrn)rr&rgrnrlrrr r}s  zSQLDatabase.__init__c cs4|j }t|dr|Vn|jVWdQRXdS)Nra)rbeginr.)rZtxrrr rs  zSQLDatabase.run_transactioncOs|jj||S)z,Simple passthrough to SQLAlchemy connectable)rZexecution_optionsra)rr2rrrr raszSQLDatabase.executeTrkzstr | Sequence[str] | Noner)z int | None)rfr\rUrgrec Cs"t||||d}|j||||dS)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 )rrg)rUr5rVre)rr) rrfr\rUr5rVrgrerxrrr rvs 4zSQLDatabase.read_tablerjzDtypeArg | None)rerGc csRd}xH||}|s0|s,tg||||dVPqd}t||||||dVqWdS)z+Return generator through chunked result setF)r\rUr5T)r\rUr5rGN)rr^) rrerVr\rUr5rGrr[rrr rs(   zSQLDatabase._query_iterator)r0r\rUrerGc Cs`t||}|j|} | } |dk r<|j| || ||||dS| } t| | ||||d} | SdS)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 N)r\rUr5rG)r3rar-rrr^) rr0r\rUr5r1rerGr2rrVr[r]rrr rzs*8  zSQLDatabase.read_queryrr)rGr*c srnts fdd|Dn ttddlm}m} x2D]&\} } t| | |sDtd| dqDWt |||||||d} | | S)z_ Prepares table in the database for data insertion. Creates it if needed, etc. csi|] }|qSrr)rrQ)rGrr [sz*SQLDatabase.prep_table..r) TypeEngine to_instancez The type of z is not a SQLAlchemy type)r]rrrrgrG) rr rDrr rrOr%rtrr) rr]rrrrrgrGr rrLmy_typerxr)rGr prep_tableFs&  zSQLDatabase.prep_tablec Cs|s|s|jj}|jL}trTddlm}||}|j|pL|j j d}n|j |pb|j j |d}WdQRX||krd|d}t |tdS)zv Checks table name for issues with case-sensitivity. Method is called after data is inserted. r)inspect)rg)rg connectionNzThe 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.)isdigitislowerrr&connectr,r#rZget_table_namesrnrg table_namesrrr) rrrgr&rrinsprrrrr check_case_sensitiveus    z SQLDatabase.check_case_sensitiver)rGc  KsVt| } |j|||||||d} | jf| |j|||||| d| |j||ddS)a Write records stored in a DataFrame to a SQL database. Parameters ---------- frame : DataFrame name : string Name of SQL table. if_exists : {'fail', 'replace', 'append'}, default 'fail' - fail: If table exists, do nothing. - replace: If table exists, drop it, recreate it, and insert data. - append: If table exists, insert data. Create if does not exist. index : boolean, default True Write DataFrame index as a column. index_label : string or sequence, default None Column label for index column(s). If None is given (default) and `index` is True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex. schema : string, default None Name of SQL schema in database to write to (if database flavor supports this). If specified, this overwrites the default schema of the SQLDatabase object. chunksize : int, default None If not None, then rows will be written in batches of this size at a time. If None, all rows will be written at once. dtype : single type or dict of column name to SQL type, default None Optional specifying the datatype for columns. The SQL type should be a SQLAlchemy type. If all columns are of the same type, one single value can be used. method : {None', 'multi', callable}, default None Controls the SQL insertion clause used: * None : Uses standard SQL ``INSERT`` clause (one per row). * 'multi': Pass multiple values in a single ``INSERT`` clause. * callable with signature ``(pd_table, conn, keys, data_iter)``. Details and a sample callable implementation can be found in the section :ref:`insert method `. engine : {'auto', 'sqlalchemy'}, default 'auto' SQL engine library to use. If 'auto', then the option ``io.sql.engine`` is used. The default ``io.sql.engine`` behavior is 'sqlalchemy' .. versionadded:: 1.3.0 **engine_kwargs Any additional kwargs are passed to the engine. )r]rrrrrgrG)rxr'r]rrrgrer)rrgN)r rrrr)rr]rrrrrgrerGrr&rZ sql_enginerxrrr rs(>zSQLDatabase.to_sqlcCs|jjS)N)rntables)rrrr rszSQLDatabase.tables)rrgcCsPtr.ddl}||j}|||p*|jjS|j|jjj||pH|jjSdS)Nr) r,r#rrr}rnrgZ run_callabledialect)rrrgsarrrr r}s  zSQLDatabase.has_table)rfrgcCsl|p |jj}|r*|jjd||g}n|jj|}ddlm}x"|jD]}t|j |rLd|j _ qLW|S)N.r)NumericF) rnrgrgetjoinr#rrVr%rHZ asdecimal)rrfrgtblrcolumnrrr rs     zSQLDatabase.get_tablecCsH|p |jj}|||rD|jj|g|d||||jdS)N)rmrg)rnrgr}rrrZdropclear)rrfrgrrr rs   zSQLDatabase.drop_tablerzlist[str] | None)r]rfr-rGrgc Cs"t|||d|||d}t|S)NF)r]rr-rGrg)rrkr)rr]rfr-rGrgrxrrr _create_sql_schemaszSQLDatabase._create_sql_schema)NN)NTNNNN)NTNN)NTNNNN)rTNNN)rTNNNNNr)N)N)N)NNN)rrrrrrrrarv staticmethodrrzr{rrrpropertyrr}rrr#rrrr ruisV 4K'&M   ruZTEXTZREALZINTEGERrZDATEZTIME)stringrJrKrrrrc CsTyt|ddd}Wn4tk rN}ztd|d|Wdd}~XYnX|S)Nzutf-8strictz%Cannot convert identifier to UTF-8: 'r)rkencodedecode UnicodeErrorrt)runamerwrrr _get_unicode_name7s $r,cCsFt|}t|std|d}|dkr2tdd|dddS)Nz$Empty table or column name specifiedrz%SQLite identifier cannot contain NULs"z"")r,rrtfindr)rr+Z nul_indexrrr _get_valid_sqlite_name?s r0zvThe spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.cs^eZdZdZfddZddZddZdd d d Zd d ZddZ ddZ ddZ Z S) SQLiteTablezw Patch the SQLTable for fallback support. Instead of a table variable just use the Create Table statement. cs*ddl}|tddtj||dS)NrcSs |dS)Nz %H:%M:%S.%f)strftime)rrrr dz&SQLiteTable.__init__..)sqlite3Zregister_adapterrsuperr)rr2rr5) __class__rr r]szSQLiteTable.__init__cCstd|jS)Nz; )rkrrx)rrrr rgszSQLiteTable.sql_schemac Cs4|j }x|jD]}||qWWdQRXdS)N)rrrxra)rrstmtrrr rjs  zSQLiteTable._execute_createrj)num_rowsc sttt|jj}d}t|jdk rJx$|jdddD]}|d|q6Wfdd|D}d|}d|gt |dfddt |D}d |j d |d |}|S) N?rrcsg|] }|qSrr)rr!)escaperr rxsz0SQLiteTable.insert_statement..,c3s|]}ddVqdS)()Nr)rr) row_wildcardsrr |sz/SQLiteTable.insert_statement..z INSERT INTO z (z ) VALUES ) r/rrkr]rVr0rrrrrr) rr9rwldrZbracketed_namesZ col_names wildcardsinsert_statementr)r;r?r rCos  zSQLiteTable.insert_statementcCs t|}||jdd|dS)Nri)r9)r/Z executemanyrC)rrr-rrrrr rszSQLiteTable._execute_insertcCs2t|}dd|D}||jt|d|dS)NcSsg|]}|D]}|q qSrr)rrxrrr rsz5SQLiteTable._execute_insert_multi..)r9)r/rarCr)rrr-rrZflattened_datarrr rsz!SQLiteTable._execute_insert_multic sl||j}td}dd|D}tt|j|rBtjt ddt fdd|D}|j dk rt |j rt |j s|j g}n|j }dfd d |D}|d |jd |d |jr|jd}nd}d||jdd|dg}dd|D} t | rhd| } dfdd | D}|dd|jd| d|jd|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. z\s+cSsg|]\}}}|qSrr)rrQrrrr rsz3SQLiteTable._create_table_setup..)rcs"g|]\}}}|d|qS) r)rcnamectyper)r;rr rsNz, c3s|]}|VqdS)Nr)rr)r;rr r@sz2SQLiteTable._create_table_setup..z CONSTRAINT z_pk PRIMARY KEY (r>rr z CREATE TABLE z ( z, z )cSsg|]\}}}|r|qSrr)rrGrrrrr rsrr<c3s|]}|VqdS)Nr)rr)r;rr r@sz CREATE INDEX Zix_zON z ()r_sql_type_namerranyrrrr_SAFE_NAMES_WARNINGr0r-rrrrrrg) rrpatrZcreate_tbl_stmtsr-Z cnames_brZ schema_nameZ create_stmtsZix_colsZcnamesr)r;r rs2     &  6zSQLiteTable._create_table_setupcCs|jpi}t|r0tt|}|j|kr0||jStj|dd}|dkr\tjdt ddd}n,|dkrjd }n|d krxd }n|d krt d |t krd }t |S)NT)rrzlthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.r)rrKrremptyr&rzComplex datatypes not supported) rGrr rDrrrrrrrt _SQL_TYPES)rrLrGrrrr rIs*    zSQLiteTable._sql_type_name) rrrrrrrrCrrrrI __classcell__rr)r7r r1Ws 9r1c@seZdZdZd)ddddZeddZd d Zed*d dddddZ d+ddddddZ ddZ d,ddddZ d-ddddd Z d.ddd!d"d#Zd/dddd$d%Zd0dddd&d'd(Zd S)1r|z Version of SQLDatabase to support SQLite connections (fallback without SQLAlchemy). This should only be used internally. Parameters ---------- con : sqlite connection object Fr))r_cCs||_||_dS)N)r_r')rr'r_rrr rszSQLiteDatabase.__init__ccsT|j}z:y|V|jWn tk r@|jYnXWd|XdS)N)r'cursorcommitr~rollbackclose)rrbrrr rs   zSQLiteDatabase.run_transactionc Os|jr|j}n |j}y|j|||Stk r}zty|jWnBtk r}z$td|dd|d}||Wdd}~XYnXtd|dd|}||Wdd}~XYnXdS)NzExecution failed on sql: r z unable to rollbackzExecution failed on sql 'z': )r_r'rPrar~rRr!)rr2rrbrsZ inner_excexrrr ras  zSQLiteDatabase.executeNTrjzDtypeArg | None)rerUrGc csld}xb||}t|tkr$t|}|sJ||sFtjg||dVPqd}t||||||dVqWdS)z+Return generator through chunked result setF)rVrUT)r\rUr5rGN)rrHtupler/rSrrXr^) rPrerVr\rUr5rGrr[rrr rs&   zSQLiteDatabase._query_iteratorz int | None)rUrerGc Csrt||}|j|} dd| jD} |dk rD|j| || ||||dS|| } | t| | ||||d} | SdS)NcSsg|] }|dqS)rr)rZcol_descrrr rEsz-SQLiteDatabase.read_query..)r\rUr5rG)r3ra descriptionr_fetchall_as_listrSr^) rr0r\rUr1r5rerGr2rPrVr[r]rrr rz8s,   zSQLiteDatabase.read_querycCs|}t|tst|}|S)N)rr%r/)rrbrrrr rX_s z SQLiteDatabase._fetchall_as_listr)rGc  sr^ts fdd|Dn ttx2D]&\} } t| ts4t| d| dq4Wt||||||d} | | || dS)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 `. csi|] }|qSrr)rrQ)rGrr r sz)SQLiteDatabase.to_sql..z (z) not a string)r]rrrrGN) rr rDrOr%rkrtr1rr)rr]rrrrrgrerGrrrLrrxr)rGr res"3  zSQLiteDatabase.to_sqlrkz str | None)rrgcCs*d}d|d}t|||gdkS)Nr:z;SELECT name FROM sqlite_master WHERE type='table' AND name=;r)rrar)rrrgrAqueryrrr r}s zSQLiteDatabase.has_table)rfrgcCsdS)Nr)rrfrgrrr rszSQLiteDatabase.get_tablecCsdt|}||dS)Nz DROP TABLE )r0ra)rrrgZdrop_sqlrrr rszSQLiteDatabase.drop_table)rfrGrgc Cs"t|||d|||d}t|S)NF)r]rr-rGrg)r1rkr)rr]rfr-rGrgrxrrr r#sz!SQLiteDatabase._create_sql_schema)F)NTNN)NTNNNN)rTNNNNN)N)N)N)NNN)rrrrrrrrar$rrzrXrr}rrr#rrrr r|s:   E r|)rrGrgcCst|d}|j|||||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-rGrg)r`r#)r]rr-r'rGrgrcrrr get_schemas r[)NN)NTNN)NN)NNTNNN)NNTNNri)NNTNNN)NTNNNN)NTNNriN)NTNNNN)NTNNNN)NTNNNri)NTNNNN)NrTNNNNr)N)NNF)NNNN)Sr __future__r contextlibrrrr functoolsrrtypingrr r r r rnumpyrIZpandas._libs.libZ_libsrZpandas._typingr Zpandas.compat._optionalrZ pandas.errorsrZpandas.core.dtypes.commonrrrZpandas.core.dtypes.dtypesrZpandas.core.dtypes.missingrrrZpandas.core.apirrZpandas.core.baserZpandas.core.tools.datetimesrZpandas.util.versionrr$rIOErrorr!r"__annotations__r(r,r3r6rNrTr^rarhryr{rr}Z table_existsror`rrrrr rurNr,r0rKr1r|r[rrrr s                 WG  S_" Gv