ɯeiZqddlmZddlZddlZddlZddlmZddlm Z ddl m Z ddl m Z mZmZmZmZmZmZmZddlmZddlmZdd lmZmZd d lmZmZmZd d l m!Z!d d l"m#Z#e r d dl$m%Z% ddl&Z&edejPj&Z)e e*Z+ ddZ, ddZ-ddZ. d dZ/ d! d"dZ0 d#dZ1 d! d$dZ2d%dZ3d&dZ4 d' d(dZ5 d)dZ6 d*dZ7y#e'$rdZ&YwxYw)+) annotationsN)partial) getLogger)TemporaryDirectory) TYPE_CHECKINGAnyCallableIterableIteratorLiteralSequenceTypeVar)ProgrammingError)pandas) TelemetryDataTelemetryField)TempObjectTypeget_temp_type_for_objectrandom_name_for_temp_object)'_PARAM_USE_SCOPED_TEMP_FOR_PANDAS_TOOLS)SnowflakeCursor)SnowflakeConnectionT)boundc#Kt|dk(rd|fytdt||D]$}t||z |j|||zf&yw)zmHelper generator to chunk a sequence efficiently with current index like if enumerate was called on sequence.rN)lenrangeintiloc)lstnis b/var/www/html/glpi_dashboard/venv/lib/python3.12/site-packages/snowflake/connector/pandas_tools.py chunk_helperr%-s] 3x1}f  1c#h ".!a%j#((1q1u---.sAAcn|rt||dznd|rt||dzndzt||z}|S)z4Helper to format table/stage/file format's location..)_escape_part_location)databaseschemanamequote_identifierslocations r$build_location_helperr/8sT FN x): ;c ASUEK ): ;c AQS U &7 8 9 Ocnd|vrd}|r,|jdsd|z}|jds|dz}|S)N'T") startswithendswith)part should_quotes r$r)r)Ds@ d{ s#:D}}S!#:D Kr0cdt|d||s|rdndd}|f}tjd|d||j|dd|d y) NCREATE zx STAGE /* Python:snowflake.connector.pandas_tools.write_pandas() */ identifier(?) FILE_FORMAT=(TYPE=PARQUET COMPRESSION= BINARY_AS_TEXT=FALSEr()zcreating stage with ' '. params: %sTr _is_internal_force_qmark_paramstyleparamsnum_statementsrloggerdebugexecute)cursorstage_location compressionauto_create_table overwriteuse_scoped_temp_objectcreate_stage_sqlr@s r$_do_create_temp_stagerMPs!!9:P!Q RSKLWKXtEIRYpXZX[[\] F LL()9(:-H&Q NN $ r0c ttj}t||||} t || ||||| S#t $rB} t jd| dt| d|} t || ||||Yd} ~ | Sd} ~ wwxYwNr*r+r,r-zcreating stage z failed. Exception z!. Fall back to use current schema) rrSTAGEr/rMrrCrDstr) rFr*r+r-rHrIrJrK stage_namerGes r$_create_temp_stagerUds-^-A-ABJ* + N        "  0 !    n--@QHi j $      "    ! s< B7BBcdt|d||}|f}tjd|d||j|dd|dy)Nr9zq FILE FORMAT identifier(?) /* Python:snowflake.connector.pandas_tools.write_pandas() */ TYPE=PARQUET COMPRESSION=zcreating file format with 'r<Trr=rB)rFfile_format_locationrHsql_use_logical_typerKfile_format_sqlr@s r$_do_create_temp_file_formatrZsr *+ABCD$$/=1E0F H # $F LL..?}MvV NN $ r0c ttj}t||||} t ||||||S#t $rA} t jd|dt| d|}t |||||Yd} ~ |Sd} ~ wwxYwrO) rr FILE_FORMATr/rZrrCrDrR) rFr*r+r-rHrXrKfile_format_namerWrTs r$_create_temp_file_formatr^s3>3M3MN0 +   #   "  ,      233Fs1vhNo p  0#   "      s; B6BBct|trJt|dkDr$|jdr|j dr|S|j dd}d|dSt|S)Nrr2z'') isinstancerRrr4r5replace)values r$_convert_value_to_sql_optionrcsa% u:>e..s3s8KLMMTEugQ< 5zr0cbhd}|jDcic]!\}}||jt|#}}}t|j |z x}r&t ddj t|dj d|jDScc}}w)N>CATALOG CATALOG_SYNC BASE_LOCATIONEXTERNAL_VOLUMESTORAGE_SERIALIZATION_POLICYz2Invalid iceberg configurations option(s) provided ,  c30K|]\}}|d|yw)=N).0kvs r$ z3_iceberg_config_statement_helper..s>41aqc1#J>s)itemsupperrcsetkeysrjoinsorted)iceberg_configALLOWED_CONFIGSrprq normalizedinvalid_configss r$ _iceberg_config_statement_helperr}sO#((* Aq =  /22J joo/0?BBB@6RaKbAc@d e   88>:+;+;+=> >>s&B+c *234| | tdddd}||jvrtd|d|j|jr |jjtdnd}| rt j d td d }|r|jd vr td|jdvrt|j}| t|}t|jtjr2d|jj k(rd|jj"k(sjA|d|d}|jB|fd|i||rB|jEddjEdd}3jGd |dd!|z|d"d#$t=jH||r@|jEddjEdd}3jGd |d%d!|z|d"d#$ddd| r5d&4|j,Dcgc]}t%|jEd&d'}}nd4tK|j,}44d(4jA|z4z}dK3fd) } | s| s| rotM3||| ||||}!d*}"d!||!f}#tNjQd+|"d,|#tS3jU|"d-d-|#d.jW2d/jAtY||j,D$%cgc]\}$}%4|$4d02|%c}%}$}&t[||| r| rt]t^j`n|| }'| s| re|rd1nd}(tc|xsi})d2|jed0|(d3|&d4|)d5 }*|'f}#tNjQd6|*d,|#3jU|*d-d-|#d.d7d8jA24fd9tY||j,Dz}+n,t[|||| :}'d7d8jA4fd;|Dz}+ | r7| s5d<},|'f}#tNjQd=|,d,|#3jU|,d-d-|#d.d!|jEddz}-d>|d?|+d@|-dA|dB||| s| s| rdCnd|dD }.|'|f}#tNjQdE|.d,|#3jU|.d-d-|#d.jW}/| rP| rNt[|||| :}0| |0dFdG}1|'|0f}#tNjQdH|1d,|#3jU|1d-d-|#d.3jgthjjtljn3jqtsdI|/Dt|/tudJ|/D|/fScc}w#1swYxYwcc}wcc}%}$w#t$r| r | r | |'dFwxYw#3jgthjjtljn3jqwxYw)LaAllows users to most efficiently write back a pandas DataFrame to Snowflake. It works by dumping the DataFrame into Parquet files, uploading them and finally copying their data into the table. Returns whether all files were ingested correctly, number of chunks uploaded, and number of rows ingested with all of the COPY INTO command's output for debugging purposes. Example usage: import pandas from snowflake.connector.pandas_tools import write_pandas df = pandas.DataFrame([('Mark', 10), ('Luke', 20)], columns=['name', 'balance']) success, nchunks, nrows, _ = write_pandas(cnx, df, 'customers') Args: conn: Connection to be used to communicate with Snowflake. df: Dataframe we'd like to write back. table_name: Table name where we want to insert into. database: Database schema and table is in, if not provided the default one will be used (Default value = None). schema: Schema table is in, if not provided the default one will be used (Default value = None). chunk_size: Number of elements to be inserted once, if not provided all elements will be dumped once (Default value = None). compression: The compression used on the Parquet files, can only be gzip, or snappy. Gzip gives supposedly a better compression, while snappy is faster. Use whichever is more appropriate (Default value = 'gzip'). on_error: Action to take when COPY INTO statements fail, default follows documentation at: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#copy-options-copyoptions (Default value = 'abort_statement'). use_vectorized_scanner: Boolean that specifies whether to use a vectorized scanner for loading Parquet files. See details at `copy options `_. parallel: Number of threads to be used when uploading chunks, default follows documentation at: https://docs.snowflake.com/en/sql-reference/sql/put.html#optional-parameters (Default value = 4). quote_identifiers: By default, identifiers, specifically database, schema, table and column names (from df.columns) will be quoted. If set to False, identifiers are passed on to Snowflake without quoting. I.e. identifiers will be coerced to uppercase by Snowflake. (Default value = True) infer_schema: Perform explicit schema inference on the data in the DataFrame and use the inferred data types when selecting columns from the DataFrame. (Default value = False) auto_create_table: When true, will automatically create a table with corresponding columns for each column in the passed in DataFrame. The table will not be created if it already exists create_temp_table: (Deprecated) Will make the auto-created table as a temporary table overwrite: When true, and if auto_create_table is true, then it drops the table. Otherwise, it truncates the table. In both cases it will replace the existing contents of the table with that of the passed in Pandas DataFrame. table_type: The table type of to-be-created table. The supported table types include ``temp``/``temporary`` and ``transient``. Empty means permanent table as per SQL convention. use_logical_type: Boolean that specifies whether to use Parquet logical types. With this file format option, Snowflake can interpret Parquet logical types during data loading. To enable Parquet logical types, set use_logical_type as True. Set to None to use Snowflakes default. For more information, see: https://docs.snowflake.com/en/sql-reference/sql/create-file-format iceberg_config: A dictionary that can contain the following iceberg configuration values: * external_volume: specifies the identifier for the external volume where the Iceberg table stores its metadata files and data in Parquet format * catalog: specifies either Snowflake or a catalog integration to use for this table * base_location: the base directory that snowflake can write iceberg metadata and files to * catalog_sync: optionally sets the catalog integration configured for Polaris Catalog * storage_serialization_policy: specifies the storage serialization policy for the table bulk_upload_chunks: If set to True, the upload will use the wildcard upload method. This is a faster method of uploading but instead of uploading and cleaning up each chunk separately it will upload all chunks at once and then clean up locally stored chunks. Returns: Returns the COPY INTO command's results to verify ingestion in the form of a tuple of whether all chunks were ingested correctly, # of chunks, # of ingested rows, and ingest's output. NzESchema has to be provided to write_pandas when a database is providedautosnappy)gziprzInvalid compression 'z', only acceptable values are: Fzcreate_temp_table is deprecated, we still respect this parameter when it is True but please consider using `table_type="temp"` instead) stackleveltemp)r temporary transientzGUnsupported table type. Expected table types: temp/temporary, transient)rrrrz0Pandas Dataframe has non-standard index of type z which will not be written. Consider changing the index to pd.RangeIndex(start=0,...,step=1) or call reset_index() to keep index as column(s)zJDataframe contains a datetime with timezone column, but 'use_logical_type=zr'. This can result in datetimes being incorrectly written to Snowflake. Consider setting 'use_logical_type = True'r(z USE_LOGICAL_TYPE = TRUEz USE_LOGICAL_TYPE = FALSEfilez.txtrH\z\\r2z\'z'file://@ auto_detect)parallelsource_compression)local_file_namerGoptionsz/*'r3z"",cd|jd}|f}tjd|d|d|j|dd|dy) NzDROP zU IF EXISTS identifier(?) /* Python:snowflake.connector.pandas_tools.write_pandas() */z dropping z with 'r<Trr=)rtrCrDrE)r, object_typedrop_sqlr@rFs r$ drop_objectz!write_pandas..drop_objectsf;,,./0EF y WXJmLfU $(  r0zNSELECT COLUMN_NAME, TYPE FROM table(infer_schema(location=>?, file_format=>?))zinferring schema with 'r<Tr=rjrkzICEBERG r9z#TABLE IF NOT EXISTS identifier(?) (z) z> /* Python:snowflake.connector.pandas_tools.write_pandas() */ zauto creating table with 'z$1:z,$1:c3@K|]\}}|d|yw)z::Nrn)ro snowflake_colcolcolumn_type_mappingquotes r$rrzwrite_pandas..+s7. " sgm_UG2.A#.F-G H. srPc3.K|] }|ywNrn)rorrs r$rrzwrite_pandas..6s#. 1>ugm_UG ,. szYTRUNCATE TABLE identifier(?) /* Python:snowflake.connector.pandas_tools.write_pandas() */ztruncating table with 'zVCOPY INTO identifier(?) /* Python:snowflake.connector.pandas_tools.write_pandas() */ (z) FROM (SELECT z FROM 'z4') FILE_FORMAT=(TYPE=PARQUET USE_VECTORIZED_SCANNER=z COMPRESSION=r:z) PURGE=TRUE ON_ERROR=?zcopying into with 'tableznALTER TABLE identifier(?) RENAME TO identifier(?) /* Python:snowflake.connector.pandas_tools.write_pandas() */zrename table with 'c3,K|] }|ddk(yw)rLOADEDNrnrorTs r$rrzwrite_pandas..~s 3AaDH  3sc38K|]}t|dyw)N)rrs r$rrzwrite_pandas..s ,!C!I ,s)r,rRrrRreturnNone);rrv_session_parametersgetrwarningswarnDeprecationWarninglower ValueErrorrrr`indexr RangeIndexstepstartrRtype UserWarninganycolumnsapitypesis_datetime64tz_dtyperFrUrr%ospathrw to_parquetra_uploadremovelistr^rCrDdictrEfetchallzipr/rrTABLEr}rt_log_telemetry_job_datar PANDAS_WRITErTRUEcloseallsum)5conndf table_namer*r+ chunk_sizerHon_errorrr- infer_schemarIcreate_temp_tablerJ table_typeuse_logical_typerybulk_upload_chunksuse_vectorized_scannerkwargscompression_map_use_scoped_temp_objectcrXrG tmp_folderr#chunk chunk_pathrsnowflake_column_namesrrrWinfer_schema_sqlr@rrcreate_table_columnstarget_table_locationicebergiceberg_config_statementcreate_table_sqlparquet_columns truncate_sqlcopy_stage_location copy_into_sql copy_resultsoriginal_table_locationrename_table_sqlrrFrs5 @@@r$ write_pandasrs@P S  &:O/..00#K=0OP_PdPdPfOg h    # #   $$%LeT    @    j&&(0RR U  22-.EFLLN W  288V../     >s4>?R>ST< =    @B K1   / /1 6K!   " "#( (   ! 9: [[]F' N  $R4 &HAujD4.AJ E  Z K[ KF K%!))$7??UK&.tfA$6#&#7)1W *% & %%dF3;;CGD NN"*4& 4"^3%-]S  )8FHZZ!P#a&..d";!P!P!%bjj!1q(--.DEEMG  I7     K ( # ln%&(<= ./?.@ NPVW" NN !(,   hj  $yy+..Dbjj*Q &M3'-q1DS1I0JK  !6  "3,N,@,@A  !   $2jG'G$"( $ ***,-Qwi8(),D+EPR  ,-F LL,-=,>mLf  NN !(,   &++. &)*@"**&M. #   !6/ !   &++. BX. #  A /vL+-F LL2<. NPV W NN!(,  "N$:$:3$FFy+,G4G3HI&'=%=>*;78*;yL&^`a#$$ %  "    *=/GP~~ $( & (*  *&;!"3 ' # / 9 P +-DEF LL./?.@ NPV W NN !(,   &&~'B'BMDVDVW  3l 33 L ,| ,,  M L<@"QZ d  * -w 7  &&~'B'BMDVDVW s@3Z'AZ6BZ)"Z,Z1 *DZ7Z)7[[A\c btfddDr tdttfiS)aFThis returns a pd_writer with the desired arguments. Example usage: import pandas as pd from snowflake.connector.pandas_tools import pd_writer sf_connector_version_df = pd.DataFrame([('snowflake-connector-python', '1.0')], columns=['NAME', 'NEWEST_VERSION']) sf_connector_version_df.to_sql('driver_versions', engine, index=False, method=make_pd_writer()) # to use parallel=1, quote_identifiers=False, from functools import partial sf_connector_version_df.to_sql( 'driver_versions', engine, index=False, method=make_pd_writer(parallel=1, quote_identifiers=False))) This function takes arguments used by 'pd_writer' (excluding 'table', 'conn', 'keys', and 'data_iter') Please refer to 'pd_writer' for documentation. c3&K|]}|v ywrrnroargrs r$rrz!make_pd_writer.. KS3&= K)rrrv data_iterzcArguments 'table', 'conn', 'keys', and 'data_iter' are not supported parameters for make_pd_writer.)rrr pd_writer)rs`r$make_pd_writerrs6: K$J KK q   9 ' ''r0c tfddDr td|jj}tj||}t d|||j j|jdy)aThis is a wrapper on top of write_pandas to make it compatible with to_sql method in pandas. Notes: Please note that when column names in the pandas DataFrame are consist of strictly lower case letters, column names need to be enquoted, otherwise `ProgrammingError` will be raised. This is because `snowflake-sqlalchemy` does not enquote lower case column names when creating the table, but `pd_writer` enquotes the columns by default. the copy into command looks for enquoted column names. Future improvements will be made in the snowflake-sqlalchemy library. Example usage: import pandas as pd from snowflake.connector.pandas_tools import pd_writer sf_connector_version_df = pd.DataFrame([('snowflake-connector-python', '1.0')], columns=['NAME', 'NEWEST_VERSION']) sf_connector_version_df.to_sql('driver_versions', engine, index=False, method=pd_writer) # when the column names are consist of only lower case letters, enquote the column names sf_connector_version_df = pd.DataFrame([('snowflake-connector-python', '1.0')], columns=['"name"', '"newest_version"']) sf_connector_version_df.to_sql('driver_versions', engine, index=False, method=pd_writer) Args: table: Pandas package's table object. conn: SQLAlchemy engine object to talk to Snowflake. keys: Column names that we are trying to insert. data_iter: Iterator over the rows. More parameters can be provided to be used by 'write_pandas' (excluding 'conn', 'df', 'table_name', and 'schema'), Please refer to 'write_pandas' for documentation on other available parameters. c3&K|]}|v ywrrnrs r$rrzpd_writer..rr)rrrr+z^Arguments 'conn', 'df', 'table_name', and 'schema' are not supported parameters for pd_writer.)rNrn) rr connectionr DataFramerr,rtr+)rrrvrr sf_connectionrs ` r$rrs|L K$J KK l  OO..M   )T 2B  ::##%||   r0)r!pandas.DataFramer"rrz&Iterator[tuple[int, pandas.DataFrame]]) r* str | Noner+rr,rRr-boolrrR)r6rRr7rrrR)rFrrGrRrHrRrIrrJrrKrrr)F)rFrr*rr+rr-rrHrRrIrrJrrKrrrR) rFrrWrRrHrRrXrRrKrrr)rFrr*rr+rr-rrHrRrXrRrKrrrR)rbzUnion[str, bool, int, float]rrR)ryzdict[str, str]rrR)NNNrabort_statementTFFFFr(NNFF)*rrrrrrRr*rr+rrz int | NonerHrRrrRrrr-rrrrIrrrrJrrz-Literal['', 'temp', 'temporary', 'transient']rz bool | Noneryzdict[str, str] | Nonerrrrrrrzttuple[bool, int, int, Sequence[tuple[str, str, int, int, int, int, str | None, int | None, int | None, str | None]]])rzzCallable[[pandas.io.sql.SQLTable, sqlalchemy.engine.Engine | sqlalchemy.engine.Connection, Iterable, Iterable, Any], None]) rzpandas.io.sql.SQLTablerz7sqlalchemy.engine.Engine | sqlalchemy.engine.Connectionrvr rr rr)8 __future__rcollections.abc collectionsrr functoolsrloggingrtempfilertypingrrr r r r r rsnowflake.connectorrsnowflake.connector.optionsrsnowflake.connector.telemetryrr_utilsrrr constantsrrFrrr sqlalchemy ImportErrorabcr__name__rCr%r/r)rMrUrZr^rcr}rrrrnr0r$r s" '   1.G ?#/ C{//0 8 . .!.+.  ", 47 LP        !  8$)* ** * *  *  **!* *Z   !   <$)' ' '  '  '  '  ' !'  ' T ?6 !%"##@B$(,0$#('L LLL L  L  LLLLLLLLL>L "!L"*#L$%L&!'L()L*+L^ "( "(J4 !4 A4 4 4  4K  s,EE%$E%