o ;Di+ @sdZddlZddlZddlmZmZddlmZddlm Z ddl m Z ddl m Z mZmZmZmZmZeeZejZdd d d d d ddddd ZdZdZdZd0ddZ  d1ddZ  d2ddZ  d3ddZd4d d!Zd"d#Z d$d%Z!d&d'Z"d(d)Z#d*d+Z$d,d-Z%d.d/Z&dS)5aC Module providing some convenience functions for working with SQL databases. SQLAlchemy is required, try ``apt-get install python-sqlalchemy`` or ``pip install SQLAlchemy``. Acknowledgments: much of the code of this module is based on the ``csvsql`` utility in the `csvkit `_ package. N)long text_type) ArgumentError)columns)head)_is_dbapi_connection_is_dbapi_cursor_is_sqlalchemy_engine_is_sqlalchemy_session_is_sqlalchemy_connection_quotez access.basezfirebird.kinterbasdbzinformix.informixdbz maxdb.sapdbz mssql.pyodbczmysql.mysqlconnectorzoracle.cx_oraclezpostgresql.psycopg2zsqlite.pysqlitez sybase.pyodbc) accessfirebirdinformixmaxdbmssqlmysqloracle postgresqlsqlitesybaseii TcCsddl}dd|D}i}i}t|dkr|j}|rt|d<ntdd|Dr,|j}ntdd|DrIt|tksAt|t krE|j }no|j }nktd d|DrV|j }n^td d|Drc|j }nQtd d|Drp|j }nDtd d|Dr}|j }n7td d|Dr|j}n*tdd|Dr|j}ntdd|Dr|j}n|j}|rtdd|D|d<|rt|t|k|d<|j||di|fi|S)a7 Infer an appropriate SQLAlchemy column type based on a sequence of values. Keyword arguments: col : sequence A sequence of values to use to infer type, length etc. colname : string Name of column constraints : bool If True use length and nullable constraints rNcSsg|]}|dur|qSN.0vrrJ/var/www/Datamplify/venv/lib/python3.10/site-packages/petl/io/db_create.py Bz*make_sqlalchemy_column..lengthcs|]}t|tVqdSr) isinstanceboolrrrr Kz)make_sqlalchemy_column..csr!r)r"intrrrrr$Nr%csr!r)r"rrrrrr$Ur%cs|] }t|ttfVqdSr)r"r&rrrrrr$Xcss |] }t|tttfVqdSr)r"r&rfloatrrrrr$[scss|] }t|tjVqdSr)r"datetimerrrrr$^cs|] }t|tjVqdSr)r"r*daterrrrr$ar+csr,r)r"r*timerrrrr$dr+csr'r)r"dictlistrrrrr$gr(cSsg|]}tt|qSr)lenrrrrrrmrnullabler) sqlalchemyr1StringNULL_COLUMN_MAX_LENGTHallBooleanmaxSQL_INTEGER_MAXminSQL_INTEGER_MIN BigIntegerIntegerFloatDateTimeDateTimeJSONColumn)colcolname constraintsr3 col_not_nonesql_column_kwargssql_type_kwargssql_column_typerrrmake_sqlalchemy_column1sL   rKc Csbddl}|s |}|j|||d}t|}t|}|D]} t|| | |d} || q|S)a Create an SQLAlchemy table definition based on data in `table`. Keyword arguments: table : table container Table data to use to infer types etc. tablename : text Name of the table schema : text Name of the database schema to create the table in constraints : bool If True use length and nullable constraints metadata : sqlalchemy.MetaData Custom table metadata rN)schema)rF)r3MetaDataTablerr0keysrK append_column) table tablenamerLrFmetadatar3 sql_tablecolsfldsf sql_columnrrrmake_sqlalchemy_tablevs   rYc Cs^ddl}t|||||d}|rtdt|dgd}|} nd} t|j|j| d S)aE Generate a CREATE TABLE statement based on data in `table`. Keyword arguments: table : table container Table data to use to infer types etc. tablename : text Name of the table schema : text Name of the database schema to create the table in constraints : bool If True use length and nullable constraints metadata : sqlalchemy.MetaData Custom table metadata dialect : text One of {'access', 'sybase', 'sqlite', 'informix', 'firebird', 'mysql', 'oracle', 'maxdb', 'postgresql', 'mssql'} rN)rLrFrSzsqlalchemy.dialects.%sdialect)fromlist)rZ) r3rY __import__DIALECTSrZrrL CreateTablecompilestrip) rQrRrLrFrSrZr3rTmodule sql_dialectrrrmake_create_table_statements    rcc Cs8|dkr t||}t||||||d} t| ||ddS)a} Create a database table based on a sample of data in the given `table`. Keyword arguments: table : table container Table data to load dbo : database object DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or SQLAlchemy connection, engine or session tablename : text Name of the table schema : text Name of the database schema to create the table in commit : bool If True commit the changes constraints : bool If True use length and nullable constraints metadata : sqlalchemy.MetaData Custom table metadata dialect : text One of {'access', 'sybase', 'sqlite', 'informix', 'firebird', 'mysql', 'oracle', 'maxdb', 'postgresql', 'mssql'} sample : int Number of rows to sample when inferring types etc., set to 0 to use the whole table r)rLrFrSrZ)commitN)rrc_execute) rQdborRrLrerFrSrZsamplesqlrrr create_tables rjcCs8t|}|durt|d|}d|}t|||dS)ay Drop a database table. Keyword arguments: dbo : database object DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or SQLAlchemy connection, engine or session tablename : text Name of the table schema : text Name of the database schema the table is in commit : bool If True commit the changes N.z DROP TABLE %s)r rf)rgrRrLrerirrr drop_tables rlcCst|t|rtd|t|||dSt|r%tdt|||dSt|r6td|t|||dSt|rGtd|t|||dSt |rXtd|t |||dSt |ritd|t |||dSt d|)Nz-assuming %r is standard DB-API 2.0 connectionz)assuming %r is standard DB-API 2.0 cursorz;assuming %r is an instance of sqlalchemy.engine.base.EnginezsT       E $ ' '.