B []@sdZddlmZddlmZmZmZddlmZddlZddl Z ddl Z ddl m mZddlmZddlmZmZmZddlmZdd lmZdd lmZmZdd lmZdd lmZGd dde Z!Gddde"Z#da$ddZ%ddZ&ddZ'dHddZ(ddZ)dIddZ*dJddZ+dKd d!Z,dLd"d#Z-dMd$d%Z.dNd'd(Z/dOd)d*Z0e0Z1d+d,Z2dPd.d/Z3Gd0d1d1eZ4Gd2d3d3eZ5Gd4d5d5e5Z6d6d7d8d9d:d;d8d<Z7d=d>Z8d?d@Z9dAZ:GdBdCdCe4Z;GdDdEdEe5Zc Csht|}xZ|D]N\}}t|s*||kry ||}Wntk rNd}YnXt||d||<qW|S)zz Force non-datetime columns to be read as such. Supports both string formatted and integer timestamp columns. N)r5)r(itemsr TypeErrorr>)Z data_framer'col_namedf_colfmtrrr_parse_date_columnses  rDTcCs4tj|||d}t||}|dk r0|j|dd|S)z(Wrap result set of query in a DataFrame.)columns coerce_floatNT)inplace)r from_recordsrD set_index)datarE index_colrFr'framerrr _wrap_resultzs  rMcCs2|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_builderr%execute)r"rcurr# pandas_sqlr$rrrrPs    rPc Cst|}t|stdddl}ddlm} | ||d} y| j|gddWn(|jjk rtt dj |d YnXt || d } | j ||||||d } | dk r| St dj |d |dS) aj 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 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 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)schemaT)onlyZviewszTable {name} not found)name)meta)rKrFr'rE chunksize) _engine_builderrNotImplementedErrorrsqlalchemy.schemarSreflectexcZInvalidRequestError ValueErrorr5 SQLDatabase read_table) table_namerrTrKrFr'rErXrrSrWrRtablerrrread_sql_tables,B   rccCst|}|j||||||dS)a, Read SQL query into a DataFrame. Returns a DataFrame corresponding to the result set of the query string. Optionally provide an `index_col` parameter to use one of the columns as the index, otherwise default integer index will be used. Parameters ---------- sql : string SQL query or SQLAlchemy Selectable (select or text object) SQL query to be executed. con : SQLAlchemy connectable(engine/connection), 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. index_col : string or list of strings, optional, default: None Column(s) to set as index(MultiIndex). coerce_float : boolean, 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. Returns ------- DataFrame See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql Notes ----- Any datetime values with time zone information parsed via the `parse_dates` parameter will be converted to UTC. )rKr#rFr'rX)rO read_query)r"rrKrFr#r'rXrRrrrread_sql_querys>rec Cst|}t|tr(|j||||||dSy||} Wntk rNd} YnX| rz|jj|gd|j||||||dS|j||||||dSdS)aK Read SQL query or database table into a DataFrame. This function is a convenience wrapper around ``read_sql_table`` and ``read_sql_query`` (for backward compatibility). It will delegate to the specific function depending on the provided input. A SQL query will be routed to ``read_sql_query``, while a database table name will be routed to ``read_sql_table``. Note that the delegated function might have more specific notes about their functionality not listed here. Parameters ---------- sql : string or SQLAlchemy Selectable (select or text object) SQL query to be executed or a table name. con : SQLAlchemy connectable (engine/connection) or database string URI or DBAPI2 connection (fallback mode) Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. index_col : string or list of strings, optional, default: None Column(s) to set as index(MultiIndex). coerce_float : boolean, 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 See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql_query : Read SQL query into a DataFrame. )rKr#rFr'rXF)rU)rKrFr'rErXN) rOrSQLiteDatabaserd has_table ExceptionrWr\r`) r"rrKrFr#r'rErXrRZ_is_table_namerrrread_sqlPs:A  rifailc Csh|dkrtd|t||d} t|tr6|}nt|tsHtd| j||||||||| d dS)aK Write records stored in a DataFrame to a SQL database. Parameters ---------- frame : DataFrame, Series name : string 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 : string, default None 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 : 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. 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 SQLtype or 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. 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 `. .. versionadded:: 0.24.0 )rjreplaceappendz '{0}' is not valid for if_exists)rTz9'frame' argument should be either a Series or a DataFrame) if_existsindex index_labelrTrXr8methodN) r^r5rOrrZto_framer rZto_sql) rLrVrrTrmrnrorXr8rprRrrrrqs$9    rqcCst||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 )rT)rOrg)rarrTrRrrrrg s rgcCsBt|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)rstrrrrZ create_engine)rrrrrrY%s    rYFcCsBt|}t|rt|||dSt|tr2tdn t||dSdS)zm Convenience function to return the correct PandasSQL subclass based on the provided parameters. )rTrWz.Using URI string without sqlalchemy installed.)rNN)rYrr_rrrrrf)rrTrWrNrrrrO7s   rOc@seZdZdZd(ddZdd Zd d Zd d ZddZddZ ddZ ddZ d)ddZ d*ddZ d+ddZddZddZd d!Zd,d"d#Zd$d%Zd&d'ZdS)-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. NTrjpandasc Cs||_||_||_||_||||_||_||_| |_| |_ |dk rR| |_ n|j |j|j|_ |j dkrt dj|ddS)NzCould not init table '{name}')rV)rVpd_sqlprefixrL _index_namernrTrmrr8_create_table_setuprb get_tabler^r5) selfrVZpandas_sql_enginerLrnrmrvrorTrr8rrr__init__Rs   zSQLTable.__init__cCs|j|j|jS)N)rurgrVrT)rzrrrexistssszSQLTable.existscCs$ddlm}t||j|jjS)Nr) CreateTable)r[r}rrrbcompileru connectable)rzr}rrr sql_schemavs zSQLTable.sql_schemacCs |j|jj|_|jdS)N)rbZ tometadatarurWcreate)rzrrr_execute_create{szSQLTable._execute_createcCsv|rj|jdkr&tdj|jdqr|jdkrL|j|j|j|qr|jdkrXqrtd|jn|dS)NrjzTable '{name}' already exists.)rVrkrlz '{0}' is not valid for if_exists) r|rmr^r5rVru drop_tablerTr)rzrrrrs    zSQLTable.createcs(fdd|D}||j|dS)a3Execute 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)r6zip).0row)rrr sz,SQLTable._execute_insert..N)rPrbinsert)rzconnr data_iterrJr)rr_execute_inserts zSQLTable._execute_insertcs(fdd|D}||j|dS)zAlternative 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)r6r)rr)rrrrsz2SQLTable._execute_insert_multi..N)rPrbr)rzrrrrJr)rr_execute_insert_multiszSQLTable._execute_insert_multic Cs*|jdk rb|j}|j|j_y|jddWqhtk r^}ztd|Wdd}~XYqhXn|j}ttt |j }t |}dg|}|j j }x|D]}|jr|jr|j}t|}q|jdt}ntj|td}|jrt|} d|| <x"t|j|D]\} } | || <qWqW||fS)NT)rGz$duplicate name in index/columns: {0}zM8[us])r8)rnrLcopynamesZ reset_indexr^r5r!maprrrElen_datablocksZ is_datetimeZ is_datetimetzvaluesZ to_pydatetimer:Z atleast_2dastypeobjectarrayZ get_valuesZ _can_hold_nar rZmgr_locs) rztemperr column_namesZncols data_listrbr-maskZcol_locr=rrr insert_datas2   "    zSQLTable.insert_datac s|dkr|j}n2|dkr |j}n"t|r4t||}ntd||\}}t|j}|dkrddS|dkrr|}n|dkrtdt ||d}|j `}xXt |D]L} | |t | d||krPtfdd|D} |||| qWWdQRXdS)NmultizInvalid parameter `method`: {}rz%chunksize argument should be non-zerocsg|]}|qSrr)rZarr)end_istart_irrrsz#SQLTable.insert..)rrcallablerr^r5rrrLintrurun_transactionrangeminr) rzrXrpZ exec_insertrrZnrowschunksriZ chunk_iterr)rrrrs0    zSQLTable.insertccs^xX||}|sPqtj|||d|_|j|d|jdk rN|jj|jdd|jVqWdS)z,Return generator through chunked result set.)rErF)r'NT)rG) fetchmanyr rHrL_harmonize_columnsrnrI)rzresultrXrErFr'rJrrr_query_iterators   zSQLTable._query_iteratorc s|dk rft|dkrfddlm}fdd|Djdk r\fddjdddD|}n j}j|}|}|dk rj|||||dS| } t j | ||d_ j |d jdk rj jjd d j SdS) Nr)selectcsg|]}jj|qSr)rbc)rn)rzrrrsz!SQLTable.read..cs g|]}djj|qS)r)rrbr)ridx)colsrzrrrs)rFr')rErF)r'T)rG)rrrrnrbrurPrrfetchallr rHrLrrI) rzrFr'rErXrZ sql_selectrrrJr)rrzrread s.        z SQLTable.readcCs|dkr|jjj}|dk rJt|ts*|g}t||krFtd|n|S|dkrrd|jjkrr|jjj dkrrdgSddt |jjj DSn"t|t r|gSt|tr|SdSdS)NTzCLength of 'index_label' should match number of levels, which is {0}rrncSs&g|]\}}|dk r|nd|qS)Nz level_{0})r5)rrlrrrrGsz(SQLTable._index_name..) rLrnnlevelsrr!rr^r5rErV enumeraterrr)rzrnrorrrrrw/s*       zSQLTable._index_namecstg}jdk rLx.) rnrrLZ_get_level_valuesrlrrrrrE)rzrcolumn_names_and_typesrZ idx_labelZidx_typer)rrzr_get_column_names_and_typesSs  z$SQLTable._get_column_names_and_typesc sddlm}mm}||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)TableColumnPrimaryKeyConstraintcs g|]\}}}|||dqS))rnr)rrVtypis_index)rrrrgsz0SQLTable._create_table_setup..rVZ_pk)rS)rTrT)rrrrr_sqlalchemy_typerr rVrlrTrurWr[rS) rzrrrrErZpkcrTrSrWr)rrrxas       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)r5)r4F)rint64)r(rbrErVrLr@r> _get_dtyper9rrr floatrrcountr:r8boolKeyError)rzr'Zsql_colrArBrCcol_typer4rrrr}s2     zSQLTable._harmonize_columnsc CsV|jpi}|j|kr |j|jStj|dd}ddlm}m}m}m}m }m } m } m } m } |dksj|dkry|jjdk r| ddSWn(tk r|jdk r| ddSYnX| S|dkrtjd td d |S|d kr|jd kr|ddS|ddSn\|dkr|jdkr|S|Sn<|dkr$|S|dkr2| S|dkr@| S|dkrRtd|S)NT)skipnar) BigIntegerIntegerFloatTextBooleanDateTimeDateTime TIMESTAMP datetime64r)timezone timedelta64zlthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.) stacklevelr;Zfloat32)Z precision5r<Zint32booleanrrcomplexzComplex datatypes not supported)r8rVlib infer_dtypesqlalchemy.typesrrrrrrrrrdttzAttributeErrorwarningswarn UserWarningr^) rzr=r8rrrrrrrrrrrrrrsH   ,            zSQLTable._sqlalchemy_typecCsddlm}m}m}m}m}m}t||r.tSt||rBt dSt||rZ|j sVt St St||rht St||rvtSt||rtStS)Nr)rrrrrrr)rrrrrrrrrr:r8rrr rrr)rzZsqltyperrrrrrrrrrs         zSQLTable._get_dtype)NTrjrtNNNN)NN)TN)TNNN)N)rrr__doc__r{r|rrrrrrrrrrwrrxrrrrrrrrsGs2  ( %  $$ 3?rsc@s eZdZdZddZddZdS) PandasSQLz7 Subclasses Should define read_sql and to_sql. cOs tddS)NzMPandasSQL must be created with an SQLAlchemy connectable or sqlite connection)r^)rzr$kwargsrrrri szPandasSQL.read_sqlcOs tddS)NzMPandasSQL must be created with an SQLAlchemy connectable or sqlite connection)r^)rzr$rrrrrqszPandasSQL.to_sqlN)rrrrrirqrrrrrsrc@seZdZdZdddZeddZddZdd d Ze dd d Z d ddZ e Z d!ddZ eddZd"ddZd#ddZd$ddZd%ddZdS)&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). 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. NcCs.||_|s$ddlm}||j|d}||_dS)Nr)rS)rT)rr[rSrW)rzrrTrWrSrrrr{,s  zSQLDatabase.__init__c cs4|j }t|dr|Vn|jVWdQRXdS)NrP)rbeginr )rzZtxrrrr5s  zSQLDatabase.run_transactioncOs|jj||S)z,Simple passthrough to SQLAlchemy connectable)rrP)rzr$rrrrrP=szSQLDatabase.executeTc Cs"t||||d}|j||||dS)aRead SQL database table into a DataFrame. Parameters ---------- table_name : string Name of SQL table in database. index_col : string, optional, default: None Column to set as index. coerce_float : boolean, 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 )rnrT)rFr'rErX)rsr) rzrarKrFr'rErTrXrbrrrr`As 3zSQLDatabase.read_tableccs0x*||}|sPqt|||||dVqWdS)z+Return generator through chunked result set)rKrFr'N)rrM)rrXrErKrFr'rJrrrr|s zSQLDatabase._query_iteratorc Cs\t||}|j|}|} |dk r:|j||| |||dS|} t| | |||d} | SdS)aRead SQL query into a DataFrame. Parameters ---------- sql : string SQL query to be executed. index_col : string, optional, default: None Column name to use as index for the returned DataFrame object. coerce_float : boolean, 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. Returns ------- DataFrame See Also -------- read_sql_table : Read SQL database table into a DataFrame. read_sql N)rKrFr')r%rPrrrrM) rzr"rKrFr'r#rXr$rrErJrLrrrrds&1  zSQLDatabase.read_queryrjc  srtsfdd|Ddk rjddlm} m} x2D]&\} } t| | | s@tdj| dq@Wt|||||||d}| |j || d | s| s|j j}|j }|j|p|jj|d }WdQRX||krd |}t|tdS) 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 `. .. versionadded:: 0.24.0 csi|] }|qSrr)rrA)r8rr sz&SQLDatabase.to_sql..Nr) to_instance TypeEnginez.The type of {column} is not a SQLAlchemy type )column)rLrnrmrorTr8)rp)rT connectionzThe provided table name '{0}' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.)r rrrr?rr^r5rsrrisdigitislowerrrconnect table_namesrWrTrrr)rzrLrVrmrnrorTrXr8rprrr=my_typerbrrrmsgr)r8rrqs:5  zSQLDatabase.to_sqlcCs|jjS)N)rWtables)rzrrrr:szSQLDatabase.tablescCs|j|jjj||p|jjS)N)rZ run_callabledialectrgrWrT)rzrVrTrrrrg>szSQLDatabase.has_tablecCsl|p |jj}|r*|jjd||g}n|jj|}ddlm}x"|jD]}t|j |rLd|j _ qLW|S)N.r)NumericF) rWrTrgetjoinrrrErr9Z asdecimal)rzrarTtblrrrrrryCs     zSQLDatabase.get_tablecCsH|p |jj}|||rD|jj|g|d||||jdS)N)rUrT)rWrTrgr\ryZdropclear)rzrarTrrrrSs   zSQLDatabase.drop_tablecCs t|||d||d}t|S)NF)rLrnrr8)rsrrr)rzrLrarr8rbrrr_create_sql_schemaZszSQLDatabase._create_sql_schema)NN)NTNNNN)NTN)NTNNN)rjTNNNNN)N)N)N)NN)rrrrr{rrrPr` staticmethodrrdrirqpropertyrrgryrrrrrrr_s<  3  C U    r_ZTEXTZREALZINTEGERrZDATEZTIME)stringr;r<rrrrcCsDyt|ddd}Wn$tk r>tdj|dYnX|S)Nzutf-8strictz,Cannot convert identifier to UTF-8: '{name}')rV)rrencodedecode UnicodeErrorr^r5)rVunamerrr_get_unicode_nameos rcCsFt|}t|std|d}|dkr2tdd|dddS)Nz$Empty table or column name specifiedrz%SQLite identifier cannot contain NULs"z"")rrr^findrk)rVrZ nul_indexrrr_get_valid_sqlite_nameys rzvThe spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.csPeZdZdZfddZddZddZdd Zd d Zd d Z 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)_rrrz&SQLiteTable.__init__..)sqlite3Zregister_adapterrsuperr{)rzr$rr) __class__rrr{szSQLiteTable.__init__cCstd|jS)Nz; )rrrrb)rzrrrrszSQLiteTable.sql_schemac Cs4|j }x|jD]}||qWWdQRXdS)N)rurrbrP)rzrZstmtrrrrs  zSQLiteTable._execute_createcsttt|jjd}t|jdk rBfdd|jdddDfddD}d|}d|gt}dj |j ||d}|S) N?csg|]}d|qS)r)r)rr)rrrrsz0SQLiteTable.insert_statement..rcsg|] }|qSrr)rr)escaperrrs,z.INSERT INTO {table} ({columns}) VALUES ({wld}))rbrEwld) r!rrrrLrErrnrrr5rV)rzrZbracketed_namesZ col_names wildcardsinsert_statementr)rrrrs  zSQLiteTable.insert_statementcCst|}|||dS)N)r!Z executemanyr)rzrrrrrrrrszSQLiteTable._execute_insertc sN||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 j|j|d d |jdd|dg}dd|D}t |rJd|} 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)rrAr rrrrsz3SQLiteTable._create_table_setup..)rcs"g|]\}}}|d|qS) r)rcnamectyper )rrrrsNz, c3s|]}|VqdS)Nr)rr)rrr sz2SQLiteTable._create_table_setup..z-CONSTRAINT {tbl}_pk PRIMARY KEY ({cnames_br}))r cnames_brz CREATE TABLE z ( z, z )cSsg|]\}}}|r|qSrr)rrr rrrrrsr rc3s|]}|VqdS)Nr)rr)rrrrsz CREATE INDEX Zix_zON z ())r_sql_type_namerer~anyrsearchrr_SAFE_NAMES_WARNINGrrrr rrlr5rV) rzrpatrZcreate_tbl_stmtsrrZ create_stmtsZix_colsZcnamesr)rrrxs.     "  6zSQLiteTable._create_table_setupcCs|jpi}|j|kr||jStj|dd}|dkrJtjdtddd}n,|dkrXd }n|d krfd }n|d krvtd |tkrd }t|S)NT)rrzlthe 'timedelta' type is not supported, and will be written as integer values (ns frequency) to the database.r)rr<rremptyrrzComplex datatypes not supported) r8rVrrrrrr^ _SQL_TYPES)rzr=r8rrrrrs&   zSQLiteTable._sql_type_name) rrrrr{rrrrrxr __classcell__rr)rrr s 7r c@seZdZdZdddZeddZddZedd d Z dd dZ ddZ dddZ d ddZ d!ddZd"ddZd#ddZd S)$rfz Version of SQLDatabase to support SQLite connections (fallback without SQLAlchemy). This should only be used internally. Parameters ---------- con : sqlite connection object FcCs||_||_dS)N)rNr)rzrrNrrrr{"szSQLiteDatabase.__init__ccsT|j}z:y|V|jWn tk r@|jYnXWd|XdS)N)rcursorcommitrhrollbackclose)rzrQrrrr&s   zSQLiteDatabase.run_transactionc Os|jr|j}n |j}y |r,|j||n |j||Stk r}zdy|jWn2tk rtdj|d|d}t|YnXtdj|d|d}t|Wdd}~XYnXdS)Nz7Execution failed on sql: {sql} {exc} unable to rollbackr)r"r]z&Execution failed on sql '{sql}': {exc}) rNrr'rPrhr)rr5r)rzr$rrQr]exrrrrP2s&  zSQLiteDatabase.executeNTccsLxF||}t|tkr t|}|s0|Pqt|||||dVqWdS)z+Return generator through chunked result set)rKrFr'N)rr9tupler!r*rM)r'rXrErKrFr'rJrrrrLs  zSQLiteDatabase._query_iteratorc Csnt||}|j|}dd|jD} |dk rB|j||| |||dS||} |t| | |||d} | SdS)NcSsg|] }|dqS)rr)rZcol_descrrrrnsz-SQLiteDatabase.read_query..)rKrFr')r%rP descriptionr_fetchall_as_listr*rM) rzr"rKrFr#r'rXr$r'rErJrLrrrrdbs(   zSQLiteDatabase.read_querycCs|}t|tst|}|S)N)rrr!)rzrQrrrrr.s z SQLiteDatabase._fetchall_as_listrjc srtsfdd|Ddk rXx0D]$\} } t| ts0tdj| | dq0Wt||||||d} | | || dS)ai 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 : 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 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 `. .. versionadded:: 0.24.0 csi|] }|qSrr)rrA)r8rrrsz)SQLiteDatabase.to_sql..Nz {column} ({type!s}) not a string)rr9)rLrnrmror8) r r?rrrr^r5r rr) rzrLrVrmrnrorTrXr8rpr=rrbr)r8rrqs$4  zSQLiteDatabase.to_sqlcCs*d}dj|d}t|||gdkS)NrzASELECT name FROM sqlite_master WHERE type='table' AND name={wld};)rr)r5rrPr)rzrVrTrqueryrrrrgszSQLiteDatabase.has_tablecCsdS)Nr)rzrarTrrrryszSQLiteDatabase.get_tablecCsdjt|d}||dS)NzDROP TABLE {name})rV)r5rrP)rzrVrTZdrop_sqlrrrrszSQLiteDatabase.drop_tablecCs t|||d||d}t|S)NF)rLrnrr8)r rrr)rzrLrarr8rbrrrrsz!SQLiteDatabase._create_sql_schema)F)NTN)NTNNN)rjTNNNNN)N)N)N)NN)rrrrr{rrrPrrrdr.rqrgryrrrrrrrfs.       B  rfcCst|d}|j||||dS)a Get the SQL db table schema for the given frame. Parameters ---------- frame : DataFrame name : string 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. )r)rr8)rOr)rLrVrrr8rRrrr get_schemas r0)NN)NTN)NN)NNTNNN)NTNNN)NTNNNN)NrjTNNNN)N)NNF)NNN)>r contextlibrrrr functoolsrrrZnumpyr:Zpandas._libs.libZ_libsrZ pandas.compatrZpandas.core.dtypes.commonrr r Zpandas.core.dtypes.dtypesr Zpandas.core.dtypes.missingr Zpandas.core.apir rZpandas.core.baserZpandas.core.tools.datetimesrrrIOErrorrrrr%r(r>rDrMrPrcrerirqrgZ table_existsrYrOrsrr_r%rrr"r rfr0rrrrs          # [ E d H  BO \