U
    ÎZj4� ã                   @  s  d Z ddlmZ ddlZddlmZmZmZ ddlmZ ddl	m
Z
 ddlZddlZddlZddlm  mZ ddlmZmZ ddlZddlmZmZmZmZmZmZmZmZm Z m!Z! ddl"m#Z$ ddl%m  m&Z& dd	l%m'Z'm(Z(m)Z)m*Z*m+Z+m,Z,m-Z- zddl.Z.d
Z/W n e0k
�r"   dZ/Y nX ddddœddddœddddœdœZ1ddœdd„Z2dddœdd„Z3ddd œd!d"„Z4d#d$„ Z5ddœd%d&„Z6dd'd(œd)d*„Z7d'dd+œd,d-„Z8d.d/œd0d1„Z9dd2œd3d4„Z:ej;d5d6„ ƒZ<ej;d7d8„ ƒZ=ej;d9d:„ ƒZ>ej;d;d<„ ƒZ?ej;d=d>„ ƒZ@ej;d?d@„ ƒZAej;dAdB„ ƒZBej;dCdD„ ƒZCej;dEdF„ ƒZDej;dGdH„ ƒZEej;dIdJ„ ƒZFej;dKdL„ ƒZGej;dMdN„ ƒZHej;dOdP„ ƒZIej;dQdR„ ƒZJd@dBgZKdDdFgZLdHdJgZMdLdNgZNeKeL eM ZOeKeL eN ZPeOdPg ZQePdRg ZRejSjTejS UdSeQ¡ejS UdTddUg¡dVdW„ ƒƒƒZVejSjTejS UdSeQ¡ejS UdXdYdZg¡d[d\„ ƒƒƒZWejSjTejS UdSeQ¡d]d^„ ƒƒZXejSjTejS UdSeR¡d_d`„ ƒƒZYejSjTejS UdSeO¡dadb„ ƒƒZZejSjTejS UdSeK¡dcdd„ ƒƒZ[ejSjTejS UdSeK¡dedf„ ƒƒZ\ejSjTejS UdSeL¡ejS Udgdhdig¡djdk„ ƒƒƒZ]G dldm„ dmƒZ^G dndo„ doe^ƒZ_G dpdq„ dqe^ƒZ`G drds„ dsƒZaG dtdu„ dueaƒZbejSjce/ dvdw�G dxdy„ dye`ebƒƒZdG dzd{„ d{ƒZeG d|d}„ d}eeedƒZfG d~d„ de_ebƒZgG d€d�„ d�e`eaƒZhG d‚dƒ„ dƒeeehƒZiG d„d…„ d…ƒZjG d†d‡„ d‡ƒZkG dˆd‰„ d‰ƒZlejSjTG dŠd‹„ d‹ekehƒƒZmejSjTG dŒd�„ d�ekeiƒƒZnejSjTG dŽd�„ d�elehƒƒZoejSjTG d�d‘„ d‘eleiƒƒZpG d’d“„ d“ejehƒZqG d”d•„ d•ejeiƒZrG d–d—„ d—e_eaƒZsed˜jteud˜jtejvd˜jtewd˜jtexd™jteydšjtezdƒd›dœ„ ej{d�jte|džjti	Z}dŸd „ Z~d¥d¡d¢„ZG d£d¤„ d¤ƒZ€dS )¦a  SQL io tests

The SQL tests are broken down in different classes:

- `PandasSQLTest`: base class with common methods for all test classes
- Tests for the public API (only tests with sqlite3)
    - `_TestSQLApi` base class
    - `TestSQLApi`: test the public API with sqlalchemy engine
    - `TestSQLiteFallbackApi`: test the public API with a sqlite DBAPI
      connection
- Tests for the different SQL flavors (flavor specific type conversions)
    - Tests for the sqlalchemy mode: `_TestSQLAlchemy` is the base class with
      common methods, `_TestSQLAlchemyConn` tests the API with a SQLAlchemy
      Connection object. The different tested flavors (sqlite3, MySQL,
      PostgreSQL) derive from the base class
    - Tests for the fallback mode (`TestSQLiteFallback`)

é    )ÚannotationsN)ÚdateÚdatetimeÚtime)ÚStringIO©ÚPath)Úis_datetime64_dtypeÚis_datetime64tz_dtype)
Ú	DataFrameÚIndexÚ
MultiIndexÚSeriesÚ	TimestampÚconcatÚ
date_rangeÚisnaÚto_datetimeÚto_timedelta)ÚSQLAlchemyEngineÚSQLDatabaseÚSQLiteDatabaseÚ
get_engineÚpandasSQL_builderÚread_sql_queryÚread_sql_tableTFz1SELECT * FROM iris WHERE Name=? AND SepalLength=?z7SELECT * FROM iris WHERE `Name`=%s AND `SepalLength`=%sz7SELECT * FROM iris WHERE "Name"=%s AND "SepalLength"=%s)ÚsqliteÚmysqlÚ
postgresqlz]
                SELECT * FROM iris WHERE Name=:name AND SepalLength=:length
                zw
                SELECT * FROM iris WHERE
                `Name`=%(name)s AND `SepalLength`=%(length)s
                zw
                SELECT * FROM iris WHERE
                "Name"=%(name)s AND "SepalLength"=%(length)s
                z&SELECT * FROM iris WHERE Name LIKE '%'z(SELECT * FROM iris WHERE `Name` LIKE '%'z(SELECT * FROM iris WHERE "Name" LIKE '%')Úread_parametersÚread_named_parametersÚread_no_parameters_with_percentÚstr)Údialectc           
      C  sp   ddl m}m}m}m}m}m} | dkr,|n|}|ƒ }|d||d|ƒ|d|ƒ|d|ƒ|d|ƒ|d	|d
ƒƒƒ}	|	S )Nr   )ÚREALÚColumnÚFloatÚMetaDataÚStringÚTabler   ÚirisÚSepalLengthZ
SepalWidthZPetalLengthZ
PetalWidthÚNameéÈ   )Ú
sqlalchemyr$   r%   r&   r'   r(   r)   )
r#   r$   r%   r&   r'   r(   r)   ÚdtypeÚmetadatar*   © r1   ú[/var/www/html/TRUCKING_PROJECT/venv/lib/python3.8/site-packages/pandas/tests/io/test_sql.pyÚiris_table_metadatae   s     	ù	r3   zsqlite3.Connectionr   )ÚconnÚ	iris_filec              	   C  sT   |   ¡ }d}| |¡ |jd d��(}t |¡}t|ƒ d}| ||¡ W 5 Q R X d S )Nz³CREATE TABLE iris (
            "SepalLength" REAL,
            "SepalWidth" REAL,
            "PetalLength" REAL,
            "PetalWidth" REAL,
            "Name" TEXT
        )©Únewlinez&INSERT INTO iris VALUES(?, ?, ?, ?, ?))ÚcursorÚexecuteÚopenÚcsvÚreaderÚnextÚexecutemany)r4   r5   ÚcurÚstmtÚcsvfiler<   r1   r1   r2   Úcreate_and_load_iris_sqlite3}   s    

rB   )r5   r#   c           
        sÐ   ddl m} ddlm} t|ƒ}|j| dd� |j| d� |jd d��€}t 	|¡}t
|ƒ‰ ‡ fdd	„|D ƒ}||ƒ |¡}	t| |ƒr¸|  ¡ �$} |  ¡ � |  |	¡ W 5 Q R X W 5 Q R X n
|  |	¡ W 5 Q R X d S )
Nr   ©Úinsert©ÚEngineT©Z
checkfirst©Úbindr6   c                   s    g | ]}d d„ t ˆ |ƒD ƒ‘qS )c                 S  s   i | ]\}}||“qS r1   r1   )Ú.0ÚkeyÚvaluer1   r1   r2   Ú
<dictcomp>™   s      z3create_and_load_iris.<locals>.<listcomp>.<dictcomp>)Úzip©rJ   Úrow©Úheaderr1   r2   Ú
<listcomp>™   s     z(create_and_load_iris.<locals>.<listcomp>)r.   rD   Úsqlalchemy.enginerF   r3   ÚdropÚcreater:   r;   r<   r=   ÚvaluesÚ
isinstanceÚconnectÚbeginr9   )
r4   r5   r#   rD   rF   r*   rA   r<   Úparamsr@   r1   rQ   r2   Úcreate_and_load_irisŽ   s    



 r\   c              
   C  s�   d}t | tjƒr$|  ¡ }| |¡ nhddlm} ddlm} ||ƒ}t | |ƒr‚|  	¡ �$} |  
¡ � |  |¡ W 5 Q R X W 5 Q R X n
|  |¡ d S )Nz+CREATE VIEW iris_view AS SELECT * FROM irisr   ©ÚtextrE   )rX   Úsqlite3Ú
Connectionr8   r9   r.   r^   rT   rF   rY   rZ   )r4   r@   r?   r^   rF   r1   r1   r2   Úcreate_and_load_iris_view£   s    


 ra   c                 C  sÂ   ddl m}m}m}m}m}m}m}m} | dkr4|n|}	| dkrD|n|}
|ƒ }|d||d|ƒ|d|	ƒ|d|ƒ|d|ƒ|d	|ƒ|d
|ƒ|d|
ƒ|d|ƒ|d|
ƒƒ}| dkr¾| 	|d|dd�ƒ¡ |S )Nr   )ÚTEXTÚBooleanr%   ÚDateTimer&   ÚIntegerr'   r)   r   ÚtypesÚTextColÚDateColÚ
IntDateColÚIntDateOnlyColÚFloatColÚIntColÚBoolColÚIntColWithNullÚBoolColWithNullr   ÚDateColWithTzT)Útimezone)
r.   rb   rc   r%   rd   r&   re   r'   r)   Úappend_column)r#   rb   rc   r%   rd   r&   re   r'   r)   Z	date_typeZ	bool_typer0   rf   r1   r1   r2   Útypes_table_metadataµ   s(    (õrs   z
list[dict])r4   Ú
types_datac                 C  s*   |   ¡ }d}| |¡ d}| ||¡ d S )Na”  CREATE TABLE types (
                    "TextCol" TEXT,
                    "DateCol" TEXT,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" REAL,
                    "IntCol" INTEGER,
                    "BoolCol" INTEGER,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" INTEGER
                )zY
            INSERT INTO types
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
            )r8   r9   r>   )r4   rt   r?   r@   r1   r1   r2   Úcreate_and_load_types_sqlite3Ö   s
    
ru   )rt   r#   c              
   C  s”   ddl m} ddlm} t|ƒ}|j| dd� |j| d� ||ƒ |¡}t| |ƒr†|  	¡ �$} |  
¡ � |  |¡ W 5 Q R X W 5 Q R X n
|  |¡ d S )Nr   rC   rE   TrG   rH   )r.   rD   rT   rF   rs   rU   rV   rW   rX   rY   rZ   r9   )r4   rt   r#   rD   rF   rf   r@   r1   r1   r2   Úcreate_and_load_typesì   s    


 rv   r   ©Úframec                 C  sB   | j d j}| jd }t|tjƒs&t‚t |j	dddddg¡ d S )Nr   çffffff@ç      @çffffffö?çš™™™™™É?úIris-setosa)
ÚdtypesÚtypeÚilocÚ
issubclassÚnpÚfloatingÚAssertionErrorÚtmÚequalContentsrW   )rx   ZpytyperP   r1   r1   r2   Úcheck_iris_frameý   s    
r‡   )Ú
table_namec              	   C  sŠ   d|› �}t | tjƒr*|  ¡ }| |¡}nTddlm} ddlm} ||ƒ}t | |ƒrt|  	¡ �} |  |¡}W 5 Q R X n
|  |¡}| 
¡ d S )Nz SELECT count(*) AS count_1 FROM r   r]   rE   )rX   r_   r`   r8   r9   r.   r^   rT   rF   rY   Úfetchone)r4   rˆ   r@   r?   Úresultr^   rF   r1   r1   r2   Ú
count_rows  s    



r‹   c                 C  s   | ddddƒ}t |ƒS )NÚioÚdatar;   ziris.csvr   )ÚdatapathÚ	iris_pathr1   r1   r2   r�     s    r�   c                   C  s4   ddddddddddd	œ
dd
dddddd d dd	œ
gS )NÚfirstú2000-01-03 00:00:00i wði’·2g333333$@é   Fz2000-01-01 00:00:00-08:00)
rg   rh   ri   rj   rk   rl   rm   rn   ro   rp   ú2000-01-04 00:00:00i 'âPi\¸2z2000-06-01 00:00:00-07:00r1   r1   r1   r1   r2   rt     s.    ööórt   c              
   C  s2   ddddddddddœ	}t | ƒ}|| ¡   |¡S )Nr"   Úint64Úfloat)	rg   rh   ri   rj   rk   rl   rm   rn   ro   )r   ÚkeysÚastype)rt   r~   Údfr1   r1   r2   Útypes_data_frame:  s    ÷r™   c                  C  s&   dddddg} dddd	g}t || d
�S )NÚindexÚAÚBÚCÚD)r‘   gNÿ\^ï?g9\›x`|@g%Ú TO×¿gá(nIŽò¿)r“   gî}ÒCÄð?çc×^ÜV¥¿g~q"B¶Ä¿gˆ”æÐ4Ë?)ú2000-01-05 00:00:00gGà Í¿èß?ç2rÂ¹eç?gˆŽ>Ý¦4á¿gÑ‘öÙRŠõ?)ú2000-01-06 00:00:00gÔ?{mXìñ?ç$ôó­ùù?gnk3¥AÓm?gØ
S«›å?©Úcolumns©r   ©r¥   r�   r1   r1   r2   Útest_frame1K  s    êr¨   c                  C  s"   dddg} ddddg}t || d�S )	Nrš   r›   rœ   )r‘   iÿÿÿgZØÓÍÿ¿)r“   iãÿÿÿrŸ   )r    i N  r¡   )r¢   iÍ�ûÿr£   r¤   r¦   r§   r1   r1   r2   Útest_frame3o  s    
ür©   c           	   
   c  sÄ   t  d¡}t  d¡}|jdd|jjjid�}| |¡}| d¡sLt|| dƒ | d¡sv|D ]}| 	d	¡ qZt
||dƒ |V  | ¡ �.}| ¡ � | d
¡}| |¡ W 5 Q R X W 5 Q R X | ¡  d S )Nr.   Úpymysqlz*mysql+pymysql://root@localhost:3306/pandasÚclient_flag©Úconnect_argsr*   r   rf   rp   ú DROP TABLE IF EXISTS test_frame;)ÚpytestÚimportorskipÚcreate_engineÚ	constantsÚCLIENTÚMULTI_STATEMENTSÚinspectÚ	has_tabler\   Úpoprv   rY   rZ   r^   r9   Údispose)	r�   rt   r.   rª   ÚengineÚinspÚentryr4   r@   r1   r1   r2   Úmysql_pymysql_engine{  s&    

þ





r¼   c                 c  s   |   ¡ V  d S ©N©rY   )r¼   r1   r1   r2   Úmysql_pymysql_conn’  s    r¿   c              
   c  s¢   t  d¡}t  d¡ | d¡}| |¡}| d¡s>t|| dƒ | d¡sTt||dƒ |V  | ¡ �.}| ¡ � | 	d¡}| 
|¡ W 5 Q R X W 5 Q R X | ¡  d S )Nr.   Úpsycopg2z=postgresql+psycopg2://postgres:postgres@localhost:5432/pandasr*   r   rf   r®   )r¯   r°   r±   rµ   r¶   r\   rv   rY   rZ   r^   r9   r¸   )r�   rt   r.   r¹   rº   r4   r@   r1   r1   r2   Úpostgresql_psycopg2_engine—  s     

ÿ





rÁ   c                 c  s   |   ¡ V  d S r½   r¾   )rÁ   r1   r1   r2   Úpostgresql_psycopg2_conn«  s    rÂ   c                  c  s&   t  d¡} |  d¡}|V  | ¡  d S )Nr.   z	sqlite://)r¯   r°   r±   r¸   )r.   r¹   r1   r1   r2   Úsqlite_engine°  s    

rÃ   c                 c  s   |   ¡ V  d S r½   r¾   )rÃ   r1   r1   r2   Úsqlite_conn¸  s    rÄ   c                 C  s   t | |dƒ | S )Nr   )r\   )rÃ   r�   r1   r1   r2   Úsqlite_iris_engine½  s    rÅ   c                 c  s   |   ¡ V  d S r½   r¾   )rÅ   r1   r1   r2   Úsqlite_iris_connÃ  s    rÆ   c                  c  s   t  d¡} | V  |  ¡  d S ©Nú:memory:)r_   rY   Úclose)r4   r1   r1   r2   Úsqlite_buildinÈ  s    
rÊ   c                 C  s   t | |ƒ | S r½   )rB   )rÊ   r�   r1   r1   r2   Úsqlite_buildin_irisÏ  s    
rË   r4   ÚmethodÚmultic                 C  sJ   |  | ¡} t| ƒ}|j|d|d� | d¡s0t‚t| dƒt|ƒksFt‚d S )NÚ
test_frame©rÌ   ©Úgetfixturevaluer   Úto_sqlr¶   r„   r‹   Úlen)r4   rÌ   r¨   ÚrequestÚ	pandasSQLr1   r1   r2   Útest_to_sqlõ  s
    
rÖ   zmode, num_row_coef)Úreplacer’   )Úappendé   c                 C  s^   |  | ¡} t| ƒ}|j|ddd� |j|d|d� | d¡s@t‚t| dƒ|t|ƒ ksZt‚d S )NrÎ   Úfail©Ú	if_existsrÐ   )r4   ÚmodeZnum_row_coefr¨   rÔ   rÕ   r1   r1   r2   Útest_to_sql_exist   s    
rÞ   c              	   C  sb   |  | ¡} t| ƒ}|j|ddd� | d¡s0t‚d}tjt|d�� |j|ddd� W 5 Q R X d S )NrÎ   rÚ   rÛ   z!Table 'test_frame' already exists©Úmatch)rÑ   r   rÒ   r¶   r„   r¯   ÚraisesÚ
ValueError)r4   r¨   rÔ   rÕ   Úmsgr1   r1   r2   Útest_to_sql_exist_fail  s    
rä   c                 C  s(   |  | ¡} t| ƒ}| d¡}t|ƒ d S )NúSELECT * FROM iris)rÑ   r   Ú
read_queryr‡   )r4   rÔ   rÕ   Ú
iris_framer1   r1   r2   Útest_read_iris  s    

rè   c                   sh   |  | ¡} t| ƒ}g ‰ ‡ fdd„}|j|d|d� | d¡s@t‚ˆ dgksNt‚t| dƒt|ƒksdt‚d S )Nc                   s2   ˆ  d¡ ‡ fdd„|D ƒ}| | j ¡ |¡ d S )Nr’   c                   s   g | ]}t tˆ |ƒƒ‘qS r1   )ÚdictrN   rO   ©r–   r1   r2   rS   ,  s     z8test_to_sql_callable.<locals>.sample.<locals>.<listcomp>)rØ   r9   ÚtablerD   )Zpd_tabler4   r–   Ú	data_iterr�   ©Úcheckrê   r2   Úsample*  s    
z$test_to_sql_callable.<locals>.samplerÎ   rÏ   r’   rÐ   )r4   r¨   rÔ   rÕ   rï   r1   rí   r2   Útest_to_sql_callable"  s    
rð   c                 C  sˆ   |  | ¡} t d| ¡}t|jjjtjƒs,t	‚t|j
jjtjƒsBt	‚t|jjjtjƒsXt	‚t|jjjtjƒsnt	‚t|jjjtjƒs„t	‚d S ©Nrf   )rÑ   Úsqlr   r�   rk   r/   r   r‚   rƒ   r„   rl   Úintegerrm   rn   ro   )r4   rÔ   r˜   r1   r1   r2   Útest_default_type_conversion5  s    
rô   c           	   
   C  sÔ   |  | ¡} ddlm} ddlm} tdddgddd	gd
œƒ}|jd| dd� d}||ƒ}t| |ƒr–|  ¡ �$}| 	¡ � | 
|¡ W 5 Q R X W 5 Q R X n
|  
|¡ t d| ¡}t ||¡ t d| ¡}t ||¡ d S )Nr   r]   rE   r’   rÙ   é   çš™™™™™¹?r|   g333333Ó?©ÚaÚbrÎ   F©rš   z}DROP PROCEDURE IF EXISTS get_testdb;

    CREATE PROCEDURE get_testdb ()

    BEGIN
        SELECT * FROM test_frame;
    ENDzCALL get_testdb();)rÑ   r.   r^   rT   rF   r   rÒ   rX   rY   rZ   r9   rò   r   r…   Úassert_frame_equalÚread_sql)	r4   rÔ   r^   rF   r˜   ÚprocZengine_connÚres1Úres2r1   r1   r2   Útest_read_procedureH  s     



 
r   Úexpected_countrÙ   zSuccess!c                   s„   ‡ fdd„}|  | ¡} tddgddgddgd	œƒ}|jd
| d|d�}tˆ tƒs\|d ksht‚n|ˆ ksht‚t d
| ¡}t 	||¡ d S )Nc              	     s–   |j }| ¡ �~}tƒ }t |¡}| |¡ | d¡ d dd„ |D ƒ¡}| jrb| j› d| j	› �}	n| j	}	d|	› d|› d�}
|j
|
|d	� W 5 Q R X ˆ S )
Nr   z, c                 S  s   g | ]}d |› d �‘qS )ú"r1   )rJ   Úkr1   r1   r2   rS   }  s     zVtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<listcomp>Ú.zCOPY z (z) FROM STDIN WITH CSV)rò   Úfile)Ú
connectionr8   r   r;   ÚwriterÚ	writerowsÚseekÚjoinÚschemaÚnameZcopy_expert)rë   r4   r–   rì   Z
dbapi_connr?   Zs_bufr  r¥   rˆ   Z	sql_query©r  r1   r2   Úpsql_insert_copyt  s    



zBtest_copy_from_callable_insertion_method.<locals>.psql_insert_copyr’   rÙ   rö   r|   rø   Ún©Úcol1Úcol2Zcol3rÎ   F)rš   rÌ   )
rÑ   r   rÒ   rX   Úintr„   rò   r   r…   rû   )r4   r  rÔ   r  ÚexpectedÚresult_countrŠ   r1   r  r2   Ú(test_copy_from_callable_insertion_methodm  s    
   ÿ
r  c                   @  s   e Zd Zdd„ ZdS )Ú	MixInBasec                 C  s.   t | dƒr*|  ¡ D ]}|  |¡ q|  ¡  d S )Nr4   )ÚhasattrÚ_get_all_tablesÚ
drop_tableÚ_close_conn)ÚselfÚtblr1   r1   r2   Úteardown_method–  s    
zMixInBase.teardown_methodN)Ú__name__Ú
__module__Ú__qualname__r  r1   r1   r1   r2   r  •  s   r  c                   @  s$   e Zd Zdd„ Zdd„ Zdd„ ZdS )ÚSQLiteMixInc                 C  s&   | j  dt |¡› �¡ | j  ¡  d S ©NzDROP TABLE IF EXISTS )r4   r9   rò   Ú_get_valid_sqlite_nameÚcommit©r  rˆ   r1   r1   r2   r  Ÿ  s    ÿzSQLiteMixIn.drop_tablec                 C  s   | j  d¡}dd„ | ¡ D ƒS )Nz1SELECT name FROM sqlite_master WHERE type='table'c                 S  s   g | ]}|d  ‘qS )r   r1   )rJ   rë   r1   r1   r2   rS   §  s     z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r4   r9   Úfetchall)r  Úcr1   r1   r2   r  ¥  s    zSQLiteMixIn._get_all_tablesc                 C  s   | j  ¡  d S r½   ©r4   rÉ   ©r  r1   r1   r2   r  ©  s    zSQLiteMixIn._close_connN©r  r   r!  r  r  r  r1   r1   r1   r2   r"  ž  s   r"  c                   @  s$   e Zd Zdd„ Zdd„ Zdd„ ZdS )ÚSQLAlchemyMixInc                 C  s   t  | j¡ |¡ d S r½   )rò   r   r4   r  r&  r1   r1   r2   r  ®  s    zSQLAlchemyMixIn.drop_tablec                 C  s   ddl m} || jƒ ¡ S )Nr   ©rµ   )r.   rµ   r4   Zget_table_names)r  rµ   r1   r1   r2   r  ±  s    zSQLAlchemyMixIn._get_all_tablesc                 C  s   | j  ¡  d S r½   )r4   r¸   r*  r1   r1   r2   r  ¶  s    zSQLAlchemyMixIn._close_connNr+  r1   r1   r1   r2   r,  ­  s   r,  c                   @  sv   e Zd ZdZejdd„ ƒZejdd„ ƒZdd„ Zdd	„ Z	d
d„ Z
dd„ Zddd„Zdd„ Zdd„ Zdd„ Zdd„ ZdS )ÚPandasSQLTestzT
    Base class with common private methods for SQLAlchemy and fallback cases.

    c                 C  sL   t | dƒs|  ¡  |  d¡ t| jtjƒr8t| j|ƒ nt| j|| j	ƒ d S )Nr4   r*   )
r  Úsetup_connectr  rX   r4   r_   r`   rB   r\   Úflavor)r  r�   r1   r1   r2   Úload_iris_dataÁ  s    

zPandasSQLTest.load_iris_datac                 C  sn   t | dƒs|  ¡  | jdkr0|D ]}| d¡ q t| jtjƒrZdd„ |D ƒ}t| j|ƒ nt	| j|| jƒ d S )Nr4   r   rp   c                 S  s   g | ]}t | ¡ ƒ‘qS r1   )ÚtuplerW   )rJ   r»   r1   r1   r2   rS   Ó  s     z1PandasSQLTest.load_types_data.<locals>.<listcomp>)
r  r/  r0  r·   rX   r4   r_   r`   ru   rv   )r  rt   r»   r1   r1   r2   Úload_types_dataË  s    

zPandasSQLTest.load_types_datac                 C  s2   t d | j }ddg}| jj||d�}t|ƒ d S )Nr   r}   ry   ©r[   ©ÚSQL_STRINGSr0  rÕ   ræ   r‡   ©r  Úqueryr[   rç   r1   r1   r2   Ú_read_sql_iris_parameterØ  s    z&PandasSQLTest._read_sql_iris_parameterc                 C  s4   t d | j }dddœ}| jj||d�}t|ƒ d S )Nr    r}   ry   )r  Úlengthr4  r5  r7  r1   r1   r2   Ú_read_sql_iris_named_parameterÞ  s    
z,PandasSQLTest._read_sql_iris_named_parameterc                 C  s*   t d | j }| jj|d d�}t|ƒ d S )Nr!   r4  r5  )r  r8  rç   r1   r1   r2   Ú(_read_sql_iris_no_parameter_with_percentä  s    z6PandasSQLTest._read_sql_iris_no_parameter_with_percentc                 C  s.   |   d¡ | j |jd d… d¡dks*t‚d S )Nr¨   r   )r  rÕ   rÒ   r€   r„   ©r  r¨   r1   r1   r2   Ú_to_sql_emptyé  s    
zPandasSQLTest._to_sql_emptyÚautoc                 K  sj   |   d¡ | jj|dfd|i|—Ždks,t‚| j d¡s<t‚t|ƒ}t| jdƒ}||ks\t‚|   d¡ dS )z `to_sql` with the `engine` paramr¨   r¹   é   N)r  rÕ   rÒ   r„   r¶   rÓ   r‹   r4   )r  r¨   r¹   Zengine_kwargsÚnum_entriesÚnum_rowsr1   r1   r2   Ú_to_sql_with_sql_engineí  s"    
 ÿÿÿýÿz%PandasSQLTest._to_sql_with_sql_enginec                 C  sR   |   d¡ | j |d¡dks t‚| j d¡}|jddd� d |j_t 	||¡ d S )NÚtest_frame_roundtripr@  ú"SELECT * FROM test_frame_roundtripÚlevel_0T©Zinplace)
r  rÕ   rÒ   r„   ræ   Ú	set_indexrš   r  r…   rû   ©r  r¨   rŠ   r1   r1   r2   Ú
_roundtrip  s    
zPandasSQLTest._roundtripc                 C  s.   | j  d¡}| ¡ }t |dddddg¡ d S )Nrå   ry   rz   r{   r|   r}   )rÕ   r9   r‰   r…   r†   ©r  Ziris_resultsrP   r1   r1   r2   Ú_execute_sql  s    zPandasSQLTest._execute_sqlc                 C  sP   t jddgdddgdgd�}| j |d¡dks2t‚|  d¡}|dggksLt‚d S )	N©r’   çÍÌÌÌÌÌ @Úline1©rÙ   g      ø?Úline2r›   rœ   r�   ©r¥   rš   Ztest_to_sql_saves_indexrÙ   )r   Úfrom_recordsrÕ   rÒ   r„   Ú_get_index_columns)r  r˜   Úix_colsr1   r1   r2   Ú_to_sql_save_index  s      ÿ
z PandasSQLTest._to_sql_save_indexc              	   C  s$  | j  ¡ �@}d}t| j tƒr(| |¡ nddlm} ||ƒ}| |¡ W 5 Q R X G dd„ dtƒ}d}t| j tƒr„ddlm} ||ƒ}z,| j  ¡ �}| |¡ |dƒ‚W 5 Q R X W n |k
rÄ   Y nX | j  	d¡}t
|ƒdksât‚| j  ¡ �}| |¡ W 5 Q R X | j  	d¡}t
|ƒd	k�s t‚d S )
Nz'CREATE TABLE test_trans (A INT, B TEXT)r   r]   c                   @  s   e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionN©r  r   r!  r1   r1   r1   r2   ÚDummyException&  s   rX  z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')ÚerrorzSELECT * FROM test_transr’   )rÕ   Zrun_transactionrX   r   r9   r.   r^   Ú	Exceptionr   ræ   rÓ   r„   )r  Ztransr@   r^   rX  Zins_sqlÚresrÿ   r1   r1   r2   Ú_transaction_test  s0    
zPandasSQLTest._transaction_testN)r?  )r  r   r!  Ú__doc__r¯   Úfixturer1  r3  r9  r;  r<  r>  rC  rJ  rL  rV  r\  r1   r1   r1   r2   r.  »  s   
	

r.  c                
   @  sª  e Zd ZU dZdZded< dd„ Zejdd�d	d
„ ƒZ	dd„ Z
dd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd „ Zd!d"„ Zd#d$„ Zej d%d&d'd(g¡ej d)ejd*d+fejd,d-fejd*d+fejd,d-fg¡d.d/„ ƒƒZd0d1„ Zd2d3„ Zd4d5„ Z ej d6d7d8d9d:d;d<g¡d=d>„ ƒZ!d?d@„ Z"dAdB„ Z#ej dCdDe$e%e$e%dEœg¡dFdG„ ƒZ&dHdI„ Z'dJdK„ Z(dLdM„ Z)dNdO„ Z*dPdQ„ Z+dRdS„ Z,dTdU„ Z-dVdW„ Z.dXdY„ Z/dDS )ZÚ_TestSQLApiañ  
    Base class to test the public API.

    From this two classes are derived to run these tests for both the
    sqlalchemy mode (`TestSQLApi`) and the fallback mode
    (`TestSQLiteFallbackApi`).  These tests are run with sqlite3. Specific
    tests for the different sql flavours are included in `_TestSQLAlchemy`.

    Notes:
    flavor can always be passed even in SQLAlchemy mode,
    should be correctly ignored.

    we don't use drop_table because that isn't part of the public api

    r   r"   rÝ   c                 C  s   |   ¡ | _d S r½   ©rY   r4   r*  r1   r1   r2   r/  X  s    z_TestSQLApi.setup_connectT©Úautousec                 C  s   |   ¡  d S r½   )Úload_test_data_and_sql©r  r1  r3  r1   r1   r2   Úsetup_method[  s    z_TestSQLApi.setup_methodc                 C  s   t | jƒ d S r½   )ra   r4   r*  r1   r1   r2   rc  _  s    z"_TestSQLApi.load_test_data_and_sqlc                 C  s   t  d| j¡}t|ƒ d S )NzSELECT * FROM iris_view)rò   r   r4   r‡   ©r  rç   r1   r1   r2   Útest_read_sql_viewb  s    z_TestSQLApi.test_read_sql_viewc                 C  s8   d}t j|| jdd�}t  || j¡}t t|ƒ|¡ d S )Nz/SELECT * FROM iris_view WHERE SepalLength < 0.0é   ©Ú	chunksize)rò   r   r4   r…   rû   r   )r  r8  Z
with_batchZwithout_batchr1   r1   r2   Ú&test_read_sql_with_chunksize_no_resultf  s    z2_TestSQLApi.test_read_sql_with_chunksize_no_resultc                 C  s&   t  |d| j¡ t  d| j¡s"t‚d S )Nr¨   )rò   rÒ   r4   r¶   r„   r=  r1   r1   r2   rÖ   l  s    z_TestSQLApi.test_to_sqlc              	   C  s\   t j|d| jdd� t  d| j¡s&t‚d}tjt|d�� t j|d| jdd� W 5 Q R X d S )NÚtest_frame2rÚ   rÛ   z"Table 'test_frame2' already existsrß   )rò   rÒ   r4   r¶   r„   r¯   rá   râ   ©r  r¨   rã   r1   r1   r2   Útest_to_sql_failp  s
    z_TestSQLApi.test_to_sql_failc                 C  s^   t j|d| jdd� t j|d| jdd� t  d| j¡s:t‚t|ƒ}t| jdƒ}||ksZt‚d S )Nr©   rÚ   rÛ   r×   )rò   rÒ   r4   r¶   r„   rÓ   r‹   ©r  r¨   rA  rB  r1   r1   r2   Útest_to_sql_replacex  s    z_TestSQLApi.test_to_sql_replacec                 C  sr   t j|d| jdd�dkst‚t j|d| jdd�dks8t‚t  d| j¡sJt‚dt|ƒ }t| jdƒ}||ksnt‚d S )NÚtest_frame4rÚ   rÛ   r@  rØ   rÙ   )rò   rÒ   r4   r„   r¶   rÓ   r‹   ro  r1   r1   r2   Útest_to_sql_appendƒ  s    ÿz_TestSQLApi.test_to_sql_appendc                 C  s2   t j|d| jdd� t  d| j¡}t ||¡ d S )NZtest_frame5Frú   zSELECT * FROM test_frame5)rò   rÒ   r4   rü   r…   rû   )r  r©   rŠ   r1   r1   r2   Útest_to_sql_type_mapping‘  s    z$_TestSQLApi.test_to_sql_type_mappingc                 C  sL   t tjddd�dd�}tj|d| jdd� t d	| j¡}t | 	¡ |¡ d S )
Nrh  r”   ©r/   Zseries©r  Ztest_seriesFrú   zSELECT * FROM test_series)
r   r‚   Úarangerò   rÒ   r4   r   r…   rû   Úto_frame)r  ÚsÚs2r1   r1   r2   Útest_to_sql_series—  s    z_TestSQLApi.test_to_sql_seriesc                 C  s\   t j|d| jd� t jd| jd�}|j|_|jddd� |j t¡ d |j_t	 
||¡ d S )NrD  ©ÚconrE  rF  TrG  )rò   rÒ   r4   r   rš   rH  r—   r  r  r…   rû   rI  r1   r1   r2   Útest_roundtrip�  s    z_TestSQLApi.test_roundtripc                 C  s6   t j|d| jddd� t jd| jd�}t ||¡ d S )NrD  FrÙ   )r|  rš   rj  rE  r{  )rò   rÒ   r4   r   r…   rû   rI  r1   r1   r2   Útest_roundtrip_chunksize¨  s    ûz$_TestSQLApi.test_roundtrip_chunksizec                 C  s2   t jd| jd�}| ¡ }t |dddddg¡ d S )Nrå   r{  ry   rz   r{   r|   r}   )rò   r9   r4   r‰   r…   r†   rK  r1   r1   r2   Útest_execute_sql³  s    z_TestSQLApi.test_execute_sqlc              	   C  sò  t  d| j¡}t|jjjtjƒr$t	‚t jd| jdgd�}t|jjjtjƒsNt	‚|j 
¡ tddddddƒtddddddƒgks€t	‚t jd| jdd	id�}t|jjjtjƒs¬t	‚|j 
¡ tddddddƒtddddddƒgksÞt	‚t jd| jd
gd�}t|jjjtjƒ�s
t	‚|j 
¡ tddddddƒtddddddƒgk�s>t	‚t jd| jd
did�}t|jjjtjƒ�slt	‚|j 
¡ tddddddƒtddddddƒgk�s t	‚t jd| jddid�}t|jjjtjƒ�sÎt	‚|j 
¡ tdƒtdƒgk�sît	‚d S )NúSELECT * FROM typesrh   ©Úparse_datesiÐ  r’   rõ   r   r@  ú%Y-%m-%d %H:%M:%Sri   iÂ  é   é   iÝ  rx  rj   z%Y%m%dz
2010-10-10z
2010-12-12)rò   r   r4   r�   rh   r/   r   r‚   Ú
datetime64r„   Útolistr   ri   rj   ©r  r˜   r1   r1   r2   Útest_date_parsing¹  sh      ÿþ
ýþ
  ÿþ  ÿþýþz_TestSQLApi.test_date_parsingrY  ÚignoreÚraiseÚcoercezread_sql, text, moder€  )r.   Úfallbackrf   r.   c                 C  s@   | j |kr<| ddi¡}||| jdd|iid�}t ||¡ d S )Nrh   zdatetime64[ns]Úerrors)r|  r‚  )rÝ   r—   r4   r…   rû   )r  rü   r^   rÝ   rY  r™   r  rŠ   r1   r1   r2   Útest_custom_dateparsing_errorð  s    
 ÿýz)_TestSQLApi.test_custom_dateparsing_errorc                 C  sH   t jd| jdddgd�}t|jjjtjƒs.t	‚t|j
jjtjƒsDt	‚d S )Nr€  rh   ri   )Ú	index_colr‚  )rò   r   r4   r�   rš   r/   r   r‚   r†  r„   ri   rˆ  r1   r1   r2   Útest_date_and_index  s    üz_TestSQLApi.test_date_and_indexc              	   C  st   t tddgdd�ƒ ¡ }t t¡� | d| j¡}W 5 Q R X |dksHt‚t	 
d| j¡}t |d |d  d¡¡ d S )	Nz00:00:01z00:00:03Úfooru  Útest_timedeltarÙ   zSELECT * FROM test_timedeltar”   )r   r   rw  r…   Úassert_produces_warningÚUserWarningrÒ   r4   r„   rò   r   Úassert_series_equalÚview)r  r˜   r  rŠ   r1   r1   r2   r“    s    z_TestSQLApi.test_timedeltac              	   C  sH   t dddgiƒ}d}tjt|d�� | d| j¡d ks:t‚W 5 Q R X d S )Nrø   y      ð?      ð?y               @zComplex datatypes not supportedrß   Ztest_complex)r   r¯   rá   râ   rÒ   r4   r„   )r  r˜   rã   r1   r1   r2   Útest_complex_raises%  s    z_TestSQLApi.test_complex_raiseszindex_name,index_label,expected)NNrš   )NÚother_labelr™  )Ú
index_nameNrš  )rš  r™  r™  )r   NÚ0)Nr   r›  c                 C  sT   t dtdƒiƒ}||j_d}tj|d| j|d� t || j¡}|jd |ksPt	‚d S )Nr  r@  úSELECT * FROM test_index_labelÚtest_index_label©Úindex_labelr   )
r   Úrangerš   r  rò   rÒ   r4   r   r¥   r„   )r  rš  rŸ  r  Ú
temp_framer8  rx   r1   r1   r2   Útest_to_sql_index_label+  s    z#_TestSQLApi.test_to_sql_index_labelc              	   C  sª  d}t dtdƒit ddg¡d�}t |d| j¡}||ks>t‚t d| j¡}|j	d d	ks^t‚|j	d
 dkspt‚tj|d| jdddgd�}||ks–t‚t d| j¡}|j	d d…  
¡ ddgksÂt‚ddg|j_tj|d| jdd�}||ksît‚t d| j¡}|j	d d…  
¡ ddgk�st‚tj|d| jdddgd�}||k�sDt‚t d| j¡}|j	d d…  
¡ ddgk�srt‚d}tjt|d�� tj|d| jddd� W 5 Q R X d S )Nr@  r  )ZA0ÚA1)ÚB0ZB1rú   r�  rœ  r   rF  r’   Zlevel_1r×   r›   rœ   )rÜ   rŸ  rÙ   rÛ   r�   rž   zALength of 'index_label' should match number of levels, which is 2rß   )r   r   r   Zfrom_productrò   rÒ   r4   r„   r   r¥   r‡  rš   Únamesr¯   rá   râ   )r  Zexpected_row_countr¡  rŠ   rx   rã   r1   r1   r2   Ú"test_to_sql_index_label_multiindexD  sb    
þû   ÿ û ûz._TestSQLApi.test_to_sql_index_label_multiindexc                 C  sV   t jddgdddgddgd�}| d| j¡ tjd| jddgd	�}tj||d
d� d S )NrM  rP  r›   rœ   r�   rR  Útest_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtrip©r�  T©Zcheck_index_type)r   rS  rÒ   r4   rò   r   r…   rû   ©r  r˜   rŠ   r1   r1   r2   r§  }  s    ý  ÿz%_TestSQLApi.test_multiindex_roundtripr/   N©r›   rœ   c                 C  s^   t ddgddggddgd�}| d| j¡d	ks2t‚| |¡}tjd
| j|d�}t ||¡ d S )Nç333333ó?g333333@çffffff@g333333@r›   rœ   r¤   Útest_dtype_argumentrÙ   z$SELECT A, B FROM test_dtype_argument©r|  r/   )	r   rÒ   r4   r„   r—   rò   r   r…   rû   )r  r/   r˜   r  rŠ   r1   r1   r2   r®  Š  s    
  ÿz_TestSQLApi.test_dtype_argumentc                 C  s4   t ddgddggddgd�}tj|d| jdd	� d S )
Nr’   rÙ   rõ   r@  r   r¤   Ztest_frame_integer_col_namesr×   rÛ   )r   rò   rÒ   r4   rˆ  r1   r1   r2   Útest_integer_col_namesŸ  s    z"_TestSQLApi.test_integer_col_namesc                 C  s"   t j|d| jd�}d|kst‚d S )NÚtestr{  ÚCREATE©rò   Ú
get_schemar4   r„   ©r  r¨   Ú
create_sqlr1   r1   r2   Útest_get_schema£  s    z_TestSQLApi.test_get_schemac                 C  s$   t j|d| jdd�}d|ks t‚d S )Nr±  Úpypi)r|  r  zCREATE TABLE pypi.r³  rµ  r1   r1   r2   Útest_get_schema_with_schema§  s    z'_TestSQLApi.test_get_schema_with_schemac                 C  sj   | j dkrddlm} |}nd}tddgddgd	œƒ}tj|d
| jd|id�}d|ksZt‚d|ksft‚d S )Nr.   r   )re   ÚINTEGERçš™™™™™ñ?r¬  rN  çš™™™™™@r÷   r±  rù   r¯  r²  )rÝ   r.   re   r   rò   r´  r4   r„   )r  re   r/   Zfloat_framer¶  r1   r1   r2   Útest_get_schema_dtypes¬  s    
   ÿz"_TestSQLApi.test_get_schema_dtypesc                 C  sf   t ddgddgdœƒ}tj|d| jdd�}d	}||ks:t‚tj|d| jd
dgd�}d}||ksbt‚d S )Nr»  r¬  rN  r¼  )ÚCol1ZCol2r±  r¾  )r|  r–   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")r›   rœ   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   rò   r´  r4   r„   )r  r¨   rx   r¶  Zconstraint_sentencer1   r1   r2   Útest_get_schema_keys»  s    z _TestSQLApi.test_get_schema_keysc                 C  s  t tj dd¡tdƒd�}|jd| jdd� t d| j¡}t ƒ }d	}ddddd
g}tjd| jdd�D ]0}t	||gdd�}t
|ƒ|| ksŒt‚|d7 }qdt ||¡ | jdk�rt ƒ }d	}ddddd
g}tjd| jdd�D ]2}t	||gdd�}t
|ƒ|| k�st‚|d7 }qØt ||¡ d S )Né   rh  Zabcder¤   Ztest_chunksizeFrú   zselect * from test_chunksizer   rÙ   ri  T©Zignore_indexr’   r.   )r   r‚   ÚrandomZrandnÚlistrÒ   r4   rò   r   r   rÓ   r„   r…   rû   rÝ   r   )r  r˜   rþ   rÿ   ÚiÚsizesÚchunkÚres3r1   r1   r2   Útest_chunksize_readÆ  s0      ÿ


z_TestSQLApi.test_chunksize_readc                 C  sd   t dddgdddgdœƒ}| ¡ }|d  d¡|d< |jd	| jd
d� t d| j¡}t ||¡ d S )Nr’   rÙ   rõ   zJohn P. Doez	Jane Dove)Z	person_idÚperson_namerÉ  ÚcategoryÚtest_categoricalFrú   zSELECT * FROM test_categorical)	r   Úcopyr—   rÒ   r4   rò   r   r…   rû   )r  r˜   Údf2r[  r1   r1   r2   rË  è  s    þÿz_TestSQLApi.test_categoricalc                 C  s2   t ddgddggddgd�}|jd| jd	d
� d S )Nr’   rÙ   rõ   r@  õ   Ã©rù   r¤   Ztest_unicodeFrú   )r   rÒ   r4   rˆ  r1   r1   r2   Útest_unicode_column_nameù  s    z$_TestSQLApi.test_unicode_column_namec                 C  sL   t dddgdtjdgdœƒ}|jd| jdd	� t d
| j¡}t ||¡ d S )Nr   r’   rÙ   r|   r­  r«  zd1187b08-4943-4c8d-a7f6Frú   z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r‚   ÚnanrÒ   r4   rò   r   r…   rû   ©r  r˜   r[  r1   r1   r2   Útest_escaped_table_nameþ  s    z#_TestSQLApi.test_escaped_table_name)0r  r   r!  r]  r0  Ú__annotations__r/  r¯   r^  re  rc  rg  rk  rÖ   rn  rp  rr  rs  rz  r}  r~  r  r‰  ÚmarkÚparametrizerò   rü   r   r   r�  r‘  r“  r˜  r¢  r¦  r§  r  r•   r®  r°  r·  r¹  r½  r¿  rÈ  rË  rÏ  rÒ  r1   r1   r1   r2   r_  D  s„   


7

ý
øþ
ôþ
9üþ	
"r_  úSQLAlchemy not installed©Úreasonc                   @  sð   e Zd ZdZdZdZdd„ Zdd„ Zdd	„ Zd
d„ Z	dd„ Z
dd„ Zdd„ Zdd„ Zej ddddddddddddd d!d"ee e¡jd#krŽd$nd%fg¡d&d'„ ƒZej d(d)d*g¡d+d,„ ƒZd-d.„ Ze d/¡d0d1„ ƒZd2d3„ Zd4d5„ Zd6d7„ Zd8S )9Ú
TestSQLApizŸ
    Test the public API as it would be used directly

    Tests for `read_sql_table` are included here, as this is specific for the
    sqlalchemy mode.

    r   r.   c                 C  s
   t  d¡S ©Nzsqlite:///:memory:©r.   r±   r*  r1   r1   r2   rY     s    zTestSQLApi.connectc                 C  s@   t  |d| j¡ ddg}t jd| j|d�}|j ¡ |ks<t‚d S )NrÎ   r›   rœ   r¤   )rò   rÒ   r4   r   r¥   r‡  r„   )r  r¨   ÚcolsrŠ   r1   r1   r2   Útest_read_table_columns  s    z"TestSQLApi.test_read_table_columnsc                 C  s¨   t  |d| j¡ t jd| jdd�}|jjdgks4t‚t jd| jddgd�}|jjddgks^t‚t jd| jddgddgd�}|jjddgksŽt‚|j ¡ ddgks¤t‚d S )	NrÎ   rš   r¨  r›   rœ   r�   rž   )r�  r¥   )	rò   rÒ   r4   r   rš   r¥  r„   r¥   r‡  rI  r1   r1   r2   Útest_read_table_index_col   s       ÿz$TestSQLApi.test_read_table_index_colc                 C  sT   t  d| j¡}t  d| j¡}t ||¡ t  d| j¡}t  d| j¡}t ||¡ d S )Nrå   r*   )rò   r   r4   rü   r…   rû   r   )r  Úiris_frame1Úiris_frame2r1   r1   r2   Útest_read_sql_delegate0  s    z!TestSQLApi.test_read_sql_delegatec                 C  s¶   ddl m} ddlm} |dƒ|dƒg}|D ]R}t| j|ƒrr| j ¡ �$}| ¡ � | |¡ W 5 Q R X W 5 Q R X q,| j |¡ q,t	 
d ¡�" t d| j¡ t d| j¡ W 5 Q R X d S )Nr   r]   rE   z,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);Zother_tablezSELECT * FROM other_table)r.   r^   rT   rF   rX   r4   rY   rZ   r9   r…   r”  rò   r   r   )r  r^   rF   Z
query_listr8  r4   r1   r1   r2   Útest_not_reflect_all_tables9  s    þ
 z&TestSQLApi.test_not_reflect_all_tablesc              	   C  sV   t jtdd�� t | j¡ dd¡ W 5 Q R X t  d ¡� | d| j¡ W 5 Q R X d S )Nz¶The provided table name 'TABLE1' 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ß   ZTABLE1Ú ZCaseSensitive)r…   r”  r•  rò   r   r4   Zcheck_case_sensitiverÒ   r=  r1   r1   r2   Ú(test_warning_case_insensitive_table_nameN  s    ýz3TestSQLApi.test_warning_case_insensitive_table_namec                 C  s6   ddl m} |j | j¡}| d¡}dd„ |D ƒ}|S )Nr   )Ú
reflectionZtest_index_savedc                 S  s   g | ]}|d  ‘qS ©Zcolumn_namesr1   ©rJ   rÄ  r1   r1   r2   rS   c  s     z1TestSQLApi._get_index_columns.<locals>.<listcomp>)rT   rå  Z	InspectorZfrom_enginer4   Úget_indexes)r  Útbl_namerå  rº   Úixsr1   r1   r2   rT  ^  s
    
zTestSQLApi._get_index_columnsc                 C  s\   ddl m} tdtddgdd�iƒ}t | j¡}tjd||d	�}t|j	j
d j|ƒsXt‚d S )
Nr   )Ú	TIMESTAMPr   Ú201412120154Ú201412110254T©ÚutcÚ	test_typerw   )r.   rë  r   r   rò   r   r4   ÚSQLTablerX   rë   r(  r   r„   )r  rë  r˜   Údbrë   r1   r1   r2   Útest_sqlalchemy_type_mappingf  s    ÿz'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)Zint8ÚSMALLINT)ZInt8rô  )Zuint8rô  )ZUInt8rô  )Zint16rô  )ZInt16rô  )Zuint16rº  )ZUInt16rº  )Úint32rº  )ZInt32rº  )Zuint32ÚBIGINT)ZUInt32rö  )r”   rö  )ZInt64rö  r”   rö  rº  c                 C  sP   t ddgdg|d�}t | j¡}tjd||d�}t|jjjj	ƒ}||ksLt
‚d S )Nr   r’   rø   ©r¥   r/   rð  rw   )r   rò   r   r4   rñ  r"   rë   r(  rø   r   r„   )r  ró   r  r˜   rò  rë   rŠ   r1   r1   r2   Útest_sqlalchemy_integer_mappingr  s
    z*TestSQLApi.test_sqlalchemy_integer_mappingró   Zuint64ZUInt64c              	   C  sN   t ddgdg|d�}t | j¡}tjtdd�� tjd||d� W 5 Q R X d S )	Nr   r’   rø   r÷  z1Unsigned 64 bit integer datatype is not supportedrß   rð  rw   )r   rò   r   r4   r¯   rá   râ   rñ  )r  ró   r˜   rò  r1   r1   r2   Ú(test_sqlalchemy_integer_overload_mapping�  s     ÿz3TestSQLApi.test_sqlalchemy_integer_overload_mappingc           	   	   C  s‚   t  ¡ �L}d| }d}|j||ddd� t ||¡}t ||¡}d}t ||¡}W 5 Q R X t  ||¡ t  ||¡ t  ||¡ d S )Nz
sqlite:///r*   r×   F)rÜ   rš   rå   )r…   Úensure_cleanrÒ   rò   rü   r   r   rû   )	r  r¨   r  Údb_urirë   rl  r©   r8  rq  r1   r1   r2   Útest_database_uri_string™  s    
z#TestSQLApi.test_database_uri_stringÚpg8000c              	   C  s.   d}t jtdd�� t d|¡ W 5 Q R X d S )Nz)postgresql+pg8000://user:pass@host/dbnamerý  rß   zselect * from table©r¯   rá   ÚImportErrorrò   rü   )r  rû  r1   r1   r2   Ú(test_pg8000_sqlalchemy_passthrough_errorª  s    z3TestSQLApi.test_pg8000_sqlalchemy_passthrough_errorc                 C  sH   ddl m} |dƒ}tj|| jddid�}t|d ƒ}|dhksDt‚d S )Nr   r]   z#select * from iris where name=:namer  zIris-versicolorr4  r,   )r.   r^   rò   rü   r4   Úsetr„   )r  r^   Z	name_textÚiris_dfÚ	all_namesr1   r1   r2   Útest_query_by_text_obj²  s
    z!TestSQLApi.test_query_by_text_objc                 C  sh   ddl m}m} t| jƒ}||ƒ |jj|dƒk¡}tj	|| j
ddid�}t|d ƒ}|dhksdt‚d S )Nr   )Ú	bindparamÚselectr  r}   r4  r,   )r.   r  r  r3   r0  Úwherer(  r,   rò   rü   r4   r  r„   )r  r  r  r*   Zname_selectr  r  r1   r1   r2   Útest_query_by_select_obj»  s    
z#TestSQLApi.test_query_by_select_objc                 C  sJ   t dddgdddgdœƒ}|jd| jd	d
� t d| j¡}t ||¡ d S )Nr   r’   rÙ   rõ   r@  rh  )r›   z%_variationZtest_column_percentageFrú   )r   rÒ   r4   rò   r   r…   rû   rÑ  r1   r1   r2   Útest_column_with_percentageÈ  s    z&TestSQLApi.test_column_with_percentageN)r  r   r!  r]  r0  rÝ   rY   rÝ  rÞ  rá  râ  rä  rT  ró  r¯   rÔ  rÕ  r  r‚   r/   r  rø  rù  rü  ÚtdZskip_if_installedr   r  r  r	  r1   r1   r1   r2   rÙ    sN   	ñþ
	
	
	rÙ  c                      s,   e Zd ZdZejdd�‡ fdd„ƒZ‡  ZS )Ú_EngineToConnMixinzS
    A mixin that causes setup_connect to create a conn rather than an engine.
    Tra  c                 #  sn   t ƒ  ¡  | j}| ¡ }| ¡ | _t |¡| _|| _	|| _d V  | j 
¡  | j ¡  | j	| _t | j	¡| _d S r½   )Úsuperrc  r4   rY   rZ   Z_EngineToConnMixin__txrò   r   rÕ   Z_EngineToConnMixin__engineÚrollbackrÉ   )r  r1  r3  r¹   r4   ©Ú	__class__r1   r2   re  ×  s    



z_EngineToConnMixin.setup_method)r  r   r!  r]  r¯   r^  re  Ú__classcell__r1   r1   r  r2   r  Ò  s   
r  c                   @  s   e Zd ZdS )ÚTestSQLApiConnNrW  r1   r1   r1   r2   r  é  s   r  c                   @  sz   e Zd ZdZdZdZddd„Zdd„ Zej	j
ed	d
�dd„ ƒZej	j
ed	d
�dd„ ƒZdd„ Zdd„ Zdd„ Zdd„ ZdS )ÚTestSQLiteFallbackApiz9
    Test the public sqlite connection fallback API

    r   r�  rÈ   c                 C  s
   t  |¡S r½   ©r_   rY   )r  Zdatabaser1   r1   r2   rY   ö  s    zTestSQLiteFallbackApi.connectc              	   C  sn   t  ¡ �P}|  |¡}tj|d|dd�dks.t‚| ¡  |  |¡}t d|¡}| ¡  W 5 Q R X t  ||¡ d S )NZtest_frame3_legacyFrú   r@  z!SELECT * FROM test_frame3_legacy;)	r…   rú  rY   rò   rÒ   r„   rÉ   r   rû   )r  r©   r  r4   rŠ   r1   r1   r2   Útest_sql_open_closeù  s    


z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installedr×  c              	   C  s2   d}d}t jt|d�� t d|¡ W 5 Q R X d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installedrß   rå   rþ  )r  r4   rã   r1   r1   r2   Útest_con_string_import_error	  s    z2TestSQLiteFallbackApi.test_con_string_import_errorc              	   C  s<   G dd„ dƒ}|dƒ}t  t¡� t d|¡ W 5 Q R X d S )Nc                   @  s"   e Zd Zddœdd„Zdd„ ZdS )z~TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnectionÚNone)Úreturnc                 _  s   t j||Ž| _d S r½   )r_   r`   r4   )r  ÚargsÚkwargsr1   r1   r2   Ú__init__  s    z‡TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__init__c                 S  s   t | j|ƒS r½   )Úgetattrr4   )r  r  r1   r1   r2   Ú__getattr__  s    zŠTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__getattr__N)r  r   r!  r  r  r1   r1   r1   r2   ÚMockSqliteConnection  s   r  rÈ   zSELECT 1)r…   r”  r•  rò   rü   )r  r  r4   r1   r1   r2   ÚJtest_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed  s    z`TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installedc              	   C  sZ   t  d| j¡}t  d| j¡}t ||¡ d}tjt j|d�� t  d| j¡ W 5 Q R X d S )Nrå   z9Execution failed on sql 'iris': near "iris": syntax errorrß   r*   )	rò   r   r4   rü   r…   rû   r¯   rá   ÚDatabaseError)r  rß  rà  rã   r1   r1   r2   rá    s    z,TestSQLiteFallbackApi.test_read_sql_delegatec                 C  s   t  |d¡}d|kst‚d S )Nr±  r²  )rò   r´  r„   rµ  r1   r1   r2   Útest_get_schema2(  s    z&TestSQLiteFallbackApi.test_get_schema2c                 C  sJ   |  d¡D ]*}|  ¡ d  d¡|kr
|  ¡ d   S q
td|› d�ƒ‚d S )NÚ
r   z""r’   zColumn ú
 not found)ÚsplitÚstriprâ   )r  r  ÚcolumnÚcolr1   r1   r2   Ú_get_sqlite_column_type-  s    z-TestSQLiteFallbackApi._get_sqlite_column_typec                 C  sT   t dtddgdd�iƒ}t | j¡}tjd||d�}| ¡ }|  |d¡dksPt‚d S )	Nr   rì  rí  Trî  rð  rw   rë  )	r   r   rò   r   r4   ZSQLiteTableZ
sql_schemar'  r„   )r  r˜   rò  rë   r  r1   r1   r2   Útest_sqlite_type_mapping4  s    ÿz.TestSQLiteFallbackApi.test_sqlite_type_mappingN)rÈ   )r  r   r!  r]  r0  rÝ   rY   r  r¯   rÔ  ÚskipifÚSQLALCHEMY_INSTALLEDr  r  rá  r   r'  r(  r1   r1   r1   r2   r  í  s   


	r  c                   @  sô  e Zd ZU dZded< ejddd�dd„ ƒZd	d
„ Zejdd�dd„ ƒZ	e
dd„ ƒZe
dd„ ƒZe
dd„ ƒZdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zd d!„ Zd"d#„ Zd$d%„ Zd&d'„ Zd(d)„ Zd*d+„ Zd,d-„ Zd.d/„ Zd0d1„ Zd2d3„ Zd4d5„ Zd6d7„ Zd8d9„ Z d:d;„ Z!d<d=„ Z"d>d?„ Z#d@dA„ Z$dBdC„ Z%dDdE„ Z&dFdG„ Z'dHdI„ Z(dJdK„ Z)dLdM„ Z*dNdO„ Z+dPdQ„ Z,dRdS„ Z-dTdU„ Z.dVdW„ Z/dXdY„ Z0ej1 2dZd[e3j4gid[e3j4 gie3j4 gd\gd]œg¡d^d_„ ƒZ5d`da„ Z6dbdc„ Z7ddde„ Z8dfdg„ Z9dhdi„ Z:djdk„ Z;dlS )mÚ_TestSQLAlchemyz¶
    Base class for testing the sqlalchemy backend.

    Subclasses for specific database types are created below. Tests that
    deviate for each flavor are overwritten there.

    r"   r0  TÚclass)rb  Úscopec                 C  s*   |   ¡  |  ¡  |  ¡  }| _| ¡  d S r½   )Úsetup_importÚsetup_driverrY   r4   )Úclsr4   r1   r1   r2   Úsetup_classO  s    z_TestSQLAlchemy.setup_classc                 C  s   d S r½   r1   r*  r1   r1   r2   rc  V  s    z&_TestSQLAlchemy.load_test_data_and_sqlra  c                 C  s   d S r½   r1   rd  r1   r1   r2   re  Y  s    z_TestSQLAlchemy.setup_methodc                 C  s   t st d¡ d S )NrÖ  )r*  r¯   Úskip©r0  r1   r1   r2   r.  ]  s    z_TestSQLAlchemy.setup_importc                 C  s
   t ƒ ‚d S r½   ©ÚNotImplementedErrorr3  r1   r1   r2   r/  c  s    z_TestSQLAlchemy.setup_driverc                 C  s
   t ƒ ‚d S r½   r4  r3  r1   r1   r2   rY   g  s    z_TestSQLAlchemy.connectc                 C  sX   z&|   ¡ | _t | j¡| _| j  ¡  W n, tjjk
rR   t 	d| j
› d�¡ Y nX d S )NzCan't connect to z server)rY   r4   rò   r   rÕ   r.   ÚexcZOperationalErrorr¯   r2  r0  r*  r1   r1   r2   r/  k  s    
z_TestSQLAlchemy.setup_connectc                 C  s   |   ¡  d S r½   ©r9  r*  r1   r1   r2   Útest_read_sql_parametert  s    z'_TestSQLAlchemy.test_read_sql_parameterc                 C  s   |   ¡  d S r½   ©r;  r*  r1   r1   r2   Útest_read_sql_named_parameterw  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc                 C  s   |   |¡ d S r½   ©r>  r=  r1   r1   r2   Útest_to_sql_emptyz  s    z!_TestSQLAlchemy.test_to_sql_emptyc                 C  sj   ddl m} |  ¡ }tddddgddddgdœƒ}t |¡}| |d¡d	ksPt‚||ƒ}| d¡sft‚d S ©
Nr   r-  ç      ð?ç       @ç      @ç      @©ÚoneÚtwor¡  r@  )	r.   rµ   rY   r   rò   r   rÒ   r„   r¶   ©r  rµ   Z	temp_connr¡  rÕ   rº   r1   r1   r2   Útest_create_table}  s    ÿ
z!_TestSQLAlchemy.test_create_tablec                 C  s‚   ddl m} |  ¡ }tddddgddddgdœƒ}t |¡}| |d¡d	ksPt‚||ƒ}| d¡sft‚| 	d¡ | d¡r~t‚d S r=  )
r.   rµ   rY   r   rò   r   rÒ   r„   r¶   r  rE  r1   r1   r2   Útest_drop_tableŠ  s    ÿ

z_TestSQLAlchemy.test_drop_tablec                 C  s   |   |¡ d S r½   ©rJ  r=  r1   r1   r2   r}  š  s    z_TestSQLAlchemy.test_roundtripc                 C  s   |   ¡  d S r½   ©rL  r*  r1   r1   r2   r  �  s    z _TestSQLAlchemy.test_execute_sqlc                 C  s   t jd| jd�}t|ƒ d S )Nr*   r{  )rò   r   r4   r‡   rf  r1   r1   r2   Útest_read_table   s    z_TestSQLAlchemy.test_read_tablec                 C  s.   t jd| jddgd�}t |jjddg¡ d S )Nr*   r+   )r|  r¥   )rò   r   r4   r…   r†   r¥   rW   rf  r1   r1   r2   rÝ  ¤  s      ÿz'_TestSQLAlchemy.test_read_table_columnsc              	   C  s2   d}t jt|d�� tjd| jd� W 5 Q R X d S )Nz!Table this_doesnt_exist not foundrß   Zthis_doesnt_existr{  )r¯   rá   râ   rò   r   r4   )r  rã   r1   r1   r2   Útest_read_table_absent_raisesª  s    z-_TestSQLAlchemy.test_read_table_absent_raisesc                 C  s~   t  d| j¡}t|jjjtjƒs$t	‚t|j
jjtjƒs:t	‚t|jjjtjƒsPt	‚t|jjjtjƒsft	‚t|jjjtƒszt	‚d S rñ   )rò   r   r4   r�   rk   r/   r   r‚   rƒ   r„   rl   ró   rm   Zbool_rn   ro   Úobjectrˆ  r1   r1   r2   rô   ¯  s    z,_TestSQLAlchemy.test_default_type_conversionc                 C  sH   t ddgid�}|jd| jdd�dks*t‚t d| j¡}t ||¡ d S )NÚi64l            )r�   Útest_bigintFrú   r’   )r   rÒ   r4   r„   rò   r   r…   rû   rª  r1   r1   r2   rN  »  s    z_TestSQLAlchemy.test_bigintc                 C  s(   t  d| j¡}t|jjjtjƒs$t	‚d S rñ   ©
rò   r   r4   r�   rh   r/   r   r‚   r†  r„   rˆ  r1   r1   r2   Útest_default_date_loadÃ  s    z&_TestSQLAlchemy.test_default_date_loadc                 C  s@  dd„ }t d| jƒ}t|dƒs4|j tjjdd�¡ |j}t	|j
ƒsHt‚t d| jdgd�}t|dƒsz|j tjjdd�¡ |j}t	|j
ƒsŽt‚t|jjƒdks¢t‚||jƒ ttt d| jd	d
�ƒdd�}|j}t	|j
ƒsÜt‚t|jjƒdksðt‚t d| j¡}|j}t	|j
ƒ�st‚t |j|j¡ t d| j¡}||jƒ d S )Nc                 S  sš   t | jƒr4| d tdƒkst‚| d tdƒks–t‚nbt| jƒr†t| jjƒdksRt‚tddd�tddd�g}t|| j	d�}t
 | |¡ ntd| j› �ƒ‚d S )	Nr   z2000-01-01 08:00:00r’   z2000-06-01 07:00:00ÚUTC)Útzru  z&DateCol loaded with incorrect type -> )r	   r/   r   r„   r
   r"   ÚdtrR  r   r  r…   r–  )r&  Zexpected_datar  r1   r1   r2   rî   Ï  s    



þ
ÿz:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkzselect * from typesrp   z&no column with datetime with time zoner×  r�  rQ  r’   ri  TrÁ  rf   )r   r4   r  ÚnodeÚ
add_markerr¯   rÔ  Úxfailrp   r
   r/   r„   r"   rS  rR  r   rÃ  rò   r   r…   r–  )r  rÔ   rî   r˜   r&  r  r1   r1   r2   Útest_datetime_with_timezoneÊ  sD    "
ÿ  ÿ
ÿ
þz+_TestSQLAlchemy.test_datetime_with_timezonec                 C  sÊ   t dtdddd�iƒ}|jd| jdd�dks0t‚| jd	krP|d j d
¡|d< n|d j d ¡|d< t	 
d| j¡}t ||¡ t	 d| j¡}| jdkrºt|jd tƒsªt‚t|d ƒ|d< t ||¡ d S )Nr›   ú2013-01-01 09:00:00rõ   ú
US/Pacific©ÚperiodsrR  Ztest_datetime_tzFrú   r   rQ  zSELECT * FROM test_datetime_tzr   ©r   r›   )r   r   rÒ   r4   r„   r0  rS  Z
tz_convertZtz_localizerò   r   r…   rû   r   rX   Úlocr"   r   )r  r  rŠ   r1   r1   r2   Ú%test_datetime_with_timezone_roundtrip  s    ÿ

z5_TestSQLAlchemy.test_datetime_with_timezone_roundtripc                 C  sd   t dtdddƒidgd�}|jd| jdd�dks4t‚t d| j¡}t tjgdgd�}t	 
||¡ d S )	Nr   i'  r’   r   rú   Ztest_datetime_obbFr¤   )r   r   rÒ   r4   r„   rò   r   ÚpdZNaTr…   rû   )r  r�   rŠ   r  r1   r1   r2   Útest_out_of_bounds_datetime5  s
    z+_TestSQLAlchemy.test_out_of_bounds_datetimec                 C  sh   t dddd� d ¡}tdtdƒi|d�}|jd| jdd	�dksBt‚tjd| jdd
�}t	j
||dd� d S )Nz
2018-01-01rh  Z6H)r[  ÚfreqZnumsrú   Z	foo_tableZ	info_daterž  r¨  F)Zcheck_names)r   Z
_with_freqr   r   rÒ   r4   r„   rò   r   r…   rû   )r  Údatesr  rŠ   r1   r1   r2   Ú"test_naive_datetimeindex_roundtrip=  s
    z2_TestSQLAlchemy.test_naive_datetimeindex_roundtripc                 C  sJ  t  d| j¡}| jdkrtntj}t|jj	j
|ƒs6t‚t jd| jdgd�}t|jj	j
tjƒs`t‚t jd| jddid�}t|jj	j
tjƒsŒt‚t jd| jdddiid�}t|jj	j
tjƒs¼t‚t jd| jdgd�}t|jj	j
tjƒsæt‚t jd| jddid�}t|jj	j
tjƒ�st‚t jd| jdd	diid�}t|jj	j
tjƒ�sFt‚d S )
Nrf   r   rh   r�  rƒ  Úformatri   rx  Úunit)rò   r   r4   r0  rL  r‚   r†  r�   rh   r/   r   r„   ri   )r  r˜   Úexpected_typer1   r1   r2   r‰  G  s6      ÿ
ý  
ÿz!_TestSQLAlchemy.test_date_parsingc                 C  sÂ   t tddd�t d¡dœƒ}| d| j¡dks2t‚t d| j¡}|j	ddd	�}t
 ||¡ t d
| j¡}|j	ddd	�}| jdkr²t|jd tƒs”t‚t|d ƒ|d< t
 ||¡ nt
 ||¡ d S )NrX  rõ   ©r[  r@  r«  Útest_datetimerš   r’   )ZaxisúSELECT * FROM test_datetimer   r\  r›   )r   r   r‚   rv  rÒ   r4   r„   rò   r   rU   r…   rû   r   r0  rX   r]  r"   r   rª  r1   r1   r2   rh  g  s    ÿ
z_TestSQLAlchemy.test_datetimec                 C  sº   t tddd�t d¡dœƒ}tj|jd< |jd| jdd	�dksBt‚t	 
d| j¡}t ||¡ t	 d
| j¡}| jdkrªt|jd tƒsˆt‚t|d dd�|d< t ||¡ nt ||¡ d S )NrX  rõ   rg  r@  r«  ©r’   r›   rh  Frú   ri  r   r\  r›   rŒ  )rŽ  )r   r   r‚   rv  rÐ  r]  rÒ   r4   r„   rò   r   r…   rû   r   r0  rX   r"   r   rª  r1   r1   r2   Útest_datetime_NaT|  s    ÿ
z!_TestSQLAlchemy.test_datetime_NaTc                 C  sl   t tdddƒtdddƒgdgd�}|jd| jdd�dks<t‚td| jƒ}|d }t|d ƒ}t ||¡ d S )	NéÞ  r’   rÙ   rø   r¤   Ú	test_dateFrú   )	r   r   rÒ   r4   r„   r   r   r…   r–  )r  r˜   r[  rŠ   r  r1   r1   r2   Útest_datetime_date�  s    "z"_TestSQLAlchemy.test_datetime_datec                 C  s  t tdddƒtdddƒgdgd�}|jd| jdd	�d
ks<t‚td| jƒ}t ||¡ t 	¡ }t
j|d|dd	�d
ksvt‚t
 d|¡}| dd„ ¡}t ||¡ t
j|d| jdd	�d
ks¸t‚| jdkrêt
 d| j¡}| dd„ ¡}t ||¡ t
 d| j¡}t ||¡ d S )Né	   r   r’   é   rø   r¤   Ú	test_timeFrú   rÙ   Z
test_time2zSELECT * FROM test_time2c                 S  s
   |   d¡S ©Nz%H:%M:%S.%f©Ústrftime©Ú_r1   r1   r2   Ú<lambda>¦  ó    z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>Z
test_time3r   zSELECT * FROM test_time3c                 S  s
   |   d¡S rr  rs  ru  r1   r1   r2   rw  ¬  rx  )r   r   rÒ   r4   r„   r   r…   rû   ÚTestSQLiteFallbackrY   rò   r   Úapplymapr0  )r  r˜   r[  rÄ   Úrefr1   r1   r2   Útest_datetime_timeš  s     "
z"_TestSQLAlchemy.test_datetime_timec                 C  sh   t dtjd�}t dtjd�}t||dœƒ}|jd| jdd�dksDt‚t 	d| j¡}t
j||dd	d
� d S )Ni  rt  g        )Ús1ry  Ztest_read_writeFrú   r’   T)Zcheck_dtypeZcheck_exact)r   r‚   rõ  Úfloat32r   rÒ   r4   r„   rò   r   r…   rû   )r  r}  ry  r˜   rÍ  r1   r1   r2   Útest_mixed_dtype_insert±  s    z'_TestSQLAlchemy.test_mixed_dtype_insertc                 C  sn   t dddgdtjdgdœƒ}|jd| jdd	�d
ks6t‚t d| j¡}t 	||¡ t 
d| j¡}t 	||¡ d S )Nr   r’   rÙ   r|   r­  r«  Útest_nanFrú   rõ   úSELECT * FROM test_nan)r   r‚   rÐ  rÒ   r4   r„   rò   r   r…   rû   r   rª  r1   r1   r2   Útest_nan_numeric½  s    z _TestSQLAlchemy.test_nan_numericc                 C  sŒ   t dddgtjtjtjgdœƒ}|jd| jdd�dks:t‚t d| j¡}t 	||¡ |d	  
d
¡|d	< d |d	< t d| j¡}t 	||¡ d S )Nr   r’   rÙ   r«  r€  Frú   rõ   rœ   rL  r�  )r   r‚   rÐ  rÒ   r4   r„   rò   r   r…   rû   r—   r   rª  r1   r1   r2   Útest_nan_fullcolumnÊ  s     z#_TestSQLAlchemy.test_nan_fullcolumnc                 C  sx   t dddgddtjgdœƒ}|jd| jdd	�d
ks6t‚d |jd< t d| j¡}t	 
||¡ t d| j¡}t	 
||¡ d S )Nr   r’   rÙ   rø   rù   r«  r€  Frú   rõ   ©rÙ   rœ   r�  )r   r‚   rÐ  rÒ   r4   r„   r]  rò   r   r…   rû   r   rª  r1   r1   r2   Útest_nan_stringÙ  s    
z_TestSQLAlchemy.test_nan_stringc                 C  s2   ddl m} || jƒ}| |¡}dd„ |D ƒ}|S )Nr   r-  c                 S  s   g | ]}|d  ‘qS ræ  r1   rç  r1   r1   r2   rS   ï  s     z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)r.   rµ   r4   rè  )r  ré  rµ   rº   rê  r1   r1   r2   rT  é  s
    

z"_TestSQLAlchemy._get_index_columnsc                 C  s   |   ¡  d S r½   ©rV  r*  r1   r1   r2   Útest_to_sql_save_indexò  s    z&_TestSQLAlchemy.test_to_sql_save_indexc                 C  s   |   ¡  d S r½   ©r\  r*  r1   r1   r2   Útest_transactionsõ  s    z!_TestSQLAlchemy.test_transactionsc           	   
   C  sÈ   ddl m} ddlm} d}tj||| jd�}|jd d… }|  |¡ ||ƒ}t	| j|ƒr�| j 
¡ �$}| ¡ � | |¡ W 5 Q R X W 5 Q R X n| j |¡ t || j¡}tj||dd� |  |¡ d S )Nr   r]   rE   Útest_get_schema_create_tabler{  Fr©  )r.   r^   rT   rF   rò   r´  r4   r€   r  rX   rY   rZ   r9   r   r…   rû   )	r  r©   r^   rF   r  r¶  Zblank_test_dfr4   Zreturned_dfr1   r1   r2   rŠ  ø  s    

 z,_TestSQLAlchemy.test_get_schema_create_tablec              	   C  s¨  ddl m}m} ddlm} ddg}ddg}t||d�}| d	| j¡d
ksNt‚|jd| jd|id�d
kslt‚|ƒ }|j	| jd� |j
d jd j}t||ƒs t‚d}	tjt|	d�� |jd| jdtid� W 5 Q R X |jd| jd|dƒid�d
ksöt‚|j	| jd� |j
d jd j}t||ƒ�s&t‚|jdk�s6t‚|jd| j|d�d
k�sRt‚|j	| jd� |j
d jd j}
|j
d jd j}t|
|ƒ�s”t‚t||ƒ�s¤t‚d S )Nr   )rb   r(   ©r'   r›   rœ   ©gš™™™™™é?T©gÍÌÌÌÌÌì?Nr¤   Ú
dtype_testrÙ   Údtype_test2rt  rH   z&The type of B is not a SQLAlchemy typerß   rY  Zdtype_test3é
   Úsingle_dtype_test)r.   rb   r(   Úsqlalchemy.schemar'   r   rÒ   r4   r„   ÚreflectÚtablesr¥   r   rX   r¯   rá   râ   r"   r:  )r  rb   r(   r'   rÜ  r�   r˜   ÚmetaZsqltyperã   ZsqltypeaZsqltypebr1   r1   r2   Ú
test_dtype  s2     "z_TestSQLAlchemy.test_dtypec                 C  s  ddl m}m}m}m} ddlm} tdd gƒttdddƒd gƒtdd gdd	�td
d gƒdœ}t	|ƒ}d}| 
|| j¡dks€t‚t || j¡}	|ƒ }
|
j| jd� | jdkr°|n|}|
j| j}t|d j|ƒsÔt‚t|d j|ƒsèt‚t|d j|ƒsüt‚t|d j|ƒ�st‚d S )Nr   )rc   rd   r&   re   r‹  TéÜ  rh  r’   rL  rt  r»  ©ÚBoolÚDateÚIntr&   Únotna_dtype_testrÙ   rH   r   r™  rš  r›  r&   )r.   rc   rd   r&   re   r’  r'   r   r   r   rÒ   r4   r„   rò   r   r“  r0  r”  r¥   rX   r   )r  rc   rd   r&   re   r'   rÜ  r˜   r  rv  r•  Zmy_typeÚcol_dictr1   r1   r2   Útest_notna_dtype2  s&    

üz _TestSQLAlchemy.test_notna_dtypec           
   	   C  sn  ddl m}m}m} ddlm} d}tt|gdd�t|gdd�t|gdd�tdgd	d�tdgd
d�dœƒ}|jd| j	ddd|dd�id�dks’t
‚t d| j	¡}t |d jd d¡t |d jd d¡ksÐt
‚|ƒ }|j| j	d� |jd j}	t|	d jƒt|	d jƒk�st
‚t|	d j|ƒ�s(t
‚t|	d j|ƒ�s>t
‚t|	d j|ƒ�sTt
‚t|	d j|ƒ�sjt
‚d S )Nr   )Ú
BigIntegerr&   re   r‹  gñYÉBÊÀó?r~  rt  Úfloat64rh  rõ  r”   )Úf32Úf64Ú
f64_as_f32Úi32rM  Ztest_dtypesFr×   r£  é   )Ú	precision)rš   rÜ   r/   r’   r¢  é   rH   r¡  r¤  rM  )r.   rŸ  r&   re   r’  r'   r   r   rÒ   r4   r„   rò   r   r‚   Úroundr€   r“  r”  r¥   r"   r   rX   )
r  rŸ  r&   re   r'   ÚVr˜   r[  r•  r�  r1   r1   r2   Útest_double_precisionO  s>    ûÿûùÿ
0"z%_TestSQLAlchemy.test_double_precisionc                   sj   ddl m‰  dd„ ‰dd„ ‰‡‡fdd„‰‡ ‡fd	d
„}tddddgiƒ d| j¡dks\t‚|| jƒ d S )Nr   rE   c                 S  s   d}t j|| d�S )Nz'SELECT test_foo_data FROM test_foo_datar{  )rò   r   )r  r8  r1   r1   r2   r’  �  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.fooc                 S  s   |j d| dd� d S )NÚtest_foo_datarØ   )r  r|  rÜ   )rÒ   )r  r�   r1   r1   r2   Úbar…  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.barc                   s   ˆ| ƒ}ˆ | |ƒ d S r½   r1   )r4   Zfoo_data)r¬  r’  r1   r2   Úbazˆ  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.bazc              
     sH   t | ˆ ƒr<|  ¡ �"}| ¡ � ˆ|ƒ W 5 Q R X W 5 Q R X nˆ| ƒ d S r½   )rX   rY   rZ   )Zconnectabler4   )rF   r­  r1   r2   ÚmainŽ  s
    


z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr«  r’   rÙ   rõ   )rT   rF   r   rÒ   r4   r„   )r  r®  r1   )rF   r¬  r­  r’  r2   Útest_connectable_issue_example|  s    	ÿÿz._TestSQLAlchemy.test_connectable_issue_exampleÚinputr’  r¬  )r’  Úinfe0c              	   C  sº   t |ƒ}| jdkr‚dd l}|jdd… dkrPd|jkrPtjjdd�}|j 	|¡ d}tj
t|d	�� |jd
| jdd� W 5 Q R X n4|jd
| jdd�dksœt‚t d
| j¡}t ||¡ d S )Nr   r   rõ   )r   r�  r   r±  zGH 36465r×  zinf cannot be used with MySQLrß   ZfoobarFrú   r’   )r   r0  rª   ÚVERSIONr¥   r¯   rÔ  rV  rT  rU  rá   râ   rÒ   r4   r„   rò   r   r…   Zassert_equal)r  r°  rÔ   r˜   rª   rÔ  rã   r[  r1   r1   r2   Útest_to_sql_with_negative_npinfœ  s    
z/_TestSQLAlchemy.test_to_sql_with_negative_npinfc              
     sÒ   ddl m‰ m‰m‰m} ddlm}m} d}td|giƒ}|ƒ }G ‡ ‡‡fdd„d|ƒ}|| j	ƒ�Z}| 
¡ �F | ¡ }	|j |	¡ | ||d�¡ | ¡  tj||jƒ|	d	�}
W 5 Q R X W 5 Q R X t |
|¡ d S )
Nr   )r%   re   ÚUnicoder  )ÚSessionÚdeclarative_basezHello, World!Úspamc                      s6   e Zd ZdZddgiZ” ”dd�Z” ”dƒdd�Zd	S )
z7_TestSQLAlchemy.test_temporary_table.<locals>.TemporaryZ	temp_testÚprefixesÚ	TEMPORARYT©Zprimary_keyrp  F)ZnullableN)r  r   r!  Ú__tablename__Z__table_args__Úidr·  r1   ©r%   re   r´  r1   r2   Ú	TemporaryÈ  s   
r¾  )r·  )rò   r|  )r.   r%   re   r´  r  Úsqlalchemy.ormrµ  r¶  r   r4   rZ   r  Z	__table__rV   ÚaddÚflushrò   r   r·  r…   rû   )r  r  rµ  r¶  Z	test_datar  ZBaser¾  Úsessionr4   r˜   r1   r½  r2   Útest_temporary_table¸  s    
(z$_TestSQLAlchemy.test_temporary_tablec              	   C  s.   d}t jt|d�� |  |d¡ W 5 Q R X d S )Nz*engine must be one of 'auto', 'sqlalchemy'rß   Z
bad_engine)r¯   rá   râ   rC  rm  r1   r1   r2   Útest_invalid_engineØ  s    z#_TestSQLAlchemy.test_invalid_enginec              	   C  s&   t  dd¡� |  |¡ W 5 Q R X d S )Núio.sql.enginer.   ©r_  Úoption_contextrC  r=  r1   r1   r2   Útest_options_sqlalchemyÝ  s    z'_TestSQLAlchemy.test_options_sqlalchemyc              	   C  s&   t  dd¡� |  |¡ W 5 Q R X d S )NrÅ  r?  rÆ  r=  r1   r1   r2   Útest_options_autoâ  s    z!_TestSQLAlchemy.test_options_autoc              	   C  sŽ   t tdƒtƒst‚t dd¡�* t tdƒtƒs2t‚t tdƒtƒsDt‚W 5 Q R X t dd¡�* t tdƒtƒsnt‚t tdƒtƒs€t‚W 5 Q R X d S )Nr.   rÅ  r?  )rX   r   r   r„   r_  rÇ  r*  r1   r1   r2   Útest_options_get_engineç  s    z'_TestSQLAlchemy.test_options_get_enginec                 C  s   d S r½   r1   r*  r1   r1   r2   Ú"test_get_engine_auto_error_messageò  s    z2_TestSQLAlchemy.test_get_engine_auto_error_messageN)<r  r   r!  r]  rÓ  r¯   r^  r1  rc  re  Úclassmethodr.  r/  rY   r/  r8  r:  r<  rF  rG  r}  r  rJ  rÝ  rK  rô   rN  rP  rW  r^  r`  rc  r‰  rh  rk  rn  r|  r  r‚  rƒ  r…  rT  r‡  r‰  rŠ  r–  rž  rª  r¯  rÔ  rÕ  r‚   Úinfr³  rÃ  rÄ  rÈ  rÉ  rÊ  rË  r1   r1   r1   r2   r+  D  sv   






	P
 
	#- (þ
 r+  c                      s*   e Zd Zejjdd�‡ fdd„ƒZ‡  ZS )Ú_TestSQLAlchemyConnz4Nested transactions rollbacks don't work with Pandasr×  c                   s   t ƒ  ¡  d S r½   )r  r‰  r*  r  r1   r2   r‰  ú  s    z%_TestSQLAlchemyConn.test_transactions)r  r   r!  r¯   rÔ  rV  r‰  r  r1   r1   r  r2   rÎ  ù  s   rÎ  c                   @  sL   e Zd ZdZdZedd„ ƒZedd„ ƒZdd„ Zd	d
„ Z	dd„ Z
dd„ ZdS )Ú_TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r   c                 C  s
   t  d¡S rÚ  rÛ  r3  r1   r1   r2   rY   	  s    z_TestSQLiteAlchemy.connectc                 C  s
   d | _ d S r½   )Údriverr3  r1   r1   r2   r/  	  s    z_TestSQLiteAlchemy.setup_driverc                 C  s€   t  d| j¡}t|jjjtjƒs$t	‚t|j
jjtjƒs:t	‚t|jjjtjƒsPt	‚t|jjjtjƒsft	‚t|jjjtjƒs|t	‚d S rñ   )rò   r   r4   r�   rk   r/   r   r‚   rƒ   r„   rl   ró   rm   rn   ro   rˆ  r1   r1   r2   rô   	  s    z/_TestSQLiteAlchemy.test_default_type_conversionc                 C  s(   t  d| j¡}t|jjjtjƒr$t	‚d S rñ   rO  rˆ  r1   r1   r2   rP  	  s    z)_TestSQLiteAlchemy.test_default_date_loadc              	   C  sV   t dddgidd�}|jd| jdd�dks.t‚t d ¡� t d| j¡ W 5 Q R X d S )	Nrø   r’   rÙ   r”   rt  Ztest_bigintwarningFrú   )r   rÒ   r4   r„   r…   r”  rò   r   rˆ  r1   r1   r2   Útest_bigint_warning%	  s    z&_TestSQLiteAlchemy.test_bigint_warningc           	        sÖ   ddl m‰ m‰m‰ ddlm}m} |ƒ }G ‡ ‡‡fdd„d|ƒ}|j | j	¡ || j	d�}|ƒ }t
ddgdd	gd
œƒ}|jd| j	ddd�dks”t‚| ¡  | |j|j¡}t
|ƒ}| ¡  t|jƒddgksÒt‚d S )Nr   ©r%   re   r(   )r¶  Úsessionmakerc                      s(   e Zd ZdZ” ”dd�Z” ”dƒƒZdS )z?_TestSQLiteAlchemy.test_row_object_is_named_tuple.<locals>.TestrÎ   Trº  é2   N)r  r   r!  r»  r¼  r’  r1   rÒ  r1   r2   ÚTest>	  s   rÕ  rH   r’   ZhelloZworld)r¼  r’  rÎ   Fr×   )r|  rš   rÜ   rÙ   r¼  r’  )r.   r%   re   r(   r¿  r¶  rÓ  r0   Z
create_allr4   r   rÒ   r„   r%  r8  r¼  r’  rÉ   rÃ  r¥   )	r  r¶  rÓ  Z	BaseModelrÕ  rµ  rÂ  r˜   r’  r1   rÒ  r2   Útest_row_object_is_named_tuple-	  s"    ÿÿz1_TestSQLiteAlchemy.test_row_object_is_named_tupleN)r  r   r!  r]  r0  rÌ  rY   r/  rô   rP  rÑ  rÖ  r1   r1   r1   r2   rÏ  ÿ  s   

rÏ  c                   @  s8   e Zd ZdZdZdZedd„ ƒZedd„ ƒZdd	„ Z	d
S )Ú_TestMySQLAlchemyzA
    Test the sqlalchemy backend against an MySQL database.

    r   iê  c                 C  s"   t jd| j› d| j› d�| jd�S )Nzmysql+z://root@localhost:ú/pandasr¬   )r.   r±   rÐ  Úportr­   r3  r1   r1   r2   rY   ^	  s    þz_TestMySQLAlchemy.connectc                 C  s$   t  d¡}d| _d|jjji| _d S )Nrª   r«   )r¯   r°   rÐ  r²   r³   r´   r­   )r0  rª   r1   r1   r2   r/  e	  s    
z_TestMySQLAlchemy.setup_driverc                 C  s   d S r½   r1   r*  r1   r1   r2   rô   k	  s    z._TestMySQLAlchemy.test_default_type_conversionN)
r  r   r!  r]  r0  rÙ  rÌ  rY   r/  rô   r1   r1   r1   r2   r×  U	  s   

r×  c                   @  s8   e Zd ZdZdZdZedd„ ƒZedd„ ƒZdd	„ Z	d
S )Ú_TestPostgreSQLAlchemyzF
    Test the sqlalchemy backend against an PostgreSQL database.

    r   i8  c                 C  s   t  d| j› d| j› d�¡S )Nzpostgresql+z://postgres:postgres@localhost:rØ  )r.   r±   rÐ  rÙ  r3  r1   r1   r2   rY   x	  s    ÿz_TestPostgreSQLAlchemy.connectc                 C  s   t  d¡ d| _d S )NrÀ   )r¯   r°   rÐ  r3  r1   r1   r2   r/  ~	  s    
z#_TestPostgreSQLAlchemy.setup_driverc              	   C  sj  ddl m} tddgddgddgd	œƒ}| j d
¡ | j d¡ |jd| jdd�dksZt‚|jd| jddd�dksvt‚|jd| jddd�dks’t‚t d| j¡}t	 
||¡ t d| j¡}t	 
||¡ tjd| jdd�}t	 
||¡ tjd| jdd�}t	 
||¡ d}tjt|d�� tjd| jdd� W 5 Q R X | j d
¡ | j d¡ |jd| jddd�dk�sht‚|jd| jdddd� |jd| jdddd�dk�sžt‚tjd| jdd�}t	 
t||gdd�|¡ t| j|ƒ�rf|  ¡ }	tj|	dd�}
|
j|ddd�dk�st‚|
j|dddd�dk�s"t‚|
j|dddd�dk�s>t‚tjd| jdd�}|
 d¡}t	 
||¡ d S )Nr   rE   r’   rÙ   rö   r|   rø   r  r  z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;Ztest_schema_publicFrú   Ztest_schema_public_explicitÚpublic)rš   r  Ztest_schema_otherÚother)r  z!Table test_schema_other not foundrß   )r  rš   r×   )r  rš   rÜ   rØ   TrÁ  Ztest_schema_other2)rš   rÜ   )rT   rF   r   r4   r9   rÒ   r„   rò   r   r…   rû   r¯   rá   râ   r   rX   rY   r   Z
read_table)r  rF   r˜   rþ   rÿ   rÇ  Zres4rã   r[  Zengine2Zpdsqlr1   r1   r2   Útest_schema_supportƒ	  sŠ       ÿýÿÿ  ÿÿûûùÿ
ÿÿÿÿ
z*_TestPostgreSQLAlchemy.test_schema_supportN)
r  r   r!  r]  r0  rÙ  rÌ  rY   r/  rÝ  r1   r1   r1   r2   rÚ  o	  s   

rÚ  c                   @  s   e Zd ZdS )ÚTestMySQLAlchemyNrW  r1   r1   r1   r2   rÞ  Ü	  s   rÞ  c                   @  s   e Zd ZdS )ÚTestMySQLAlchemyConnNrW  r1   r1   r1   r2   rß  á	  s   rß  c                   @  s   e Zd ZdS )ÚTestPostgreSQLAlchemyNrW  r1   r1   r1   r2   rà  æ	  s   rà  c                   @  s   e Zd ZdS )ÚTestPostgreSQLAlchemyConnNrW  r1   r1   r1   r2   rá  ë	  s   rá  c                   @  s   e Zd ZdS )ÚTestSQLiteAlchemyNrW  r1   r1   r1   r2   râ  ð	  s   râ  c                   @  s   e Zd ZdS )ÚTestSQLiteAlchemyConnNrW  r1   r1   r1   r2   rã  ô	  s   rã  c                   @  sÆ   e Zd ZdZdZedd„ ƒZdd„ Zej	dd�d	d
„ ƒZ
dd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zej dddg¡dd„ ƒZdd„ Zdd „ Zd!d"„ Zd#d$„ Zd%d&„ Zd'd(„ Zd)d*„ Zd+S ),ry  zG
    Test the fallback mode against an in-memory sqlite database.

    r   c                 C  s
   t  d¡S rÇ   r  r3  r1   r1   r2   rY   
  s    zTestSQLiteFallback.connectc                 C  s   |   ¡ | _d S r½   r`  r*  r1   r1   r2   r/  
  s    z TestSQLiteFallback.setup_connectTra  c                 C  s   t  | j¡| _d S r½   )rò   r   r4   rÕ   rd  r1   r1   r2   re  
  s    zTestSQLiteFallback.setup_methodc                 C  s   |   ¡  d S r½   r7  r*  r1   r1   r2   r8  
  s    z*TestSQLiteFallback.test_read_sql_parameterc                 C  s   |   ¡  d S r½   r9  r*  r1   r1   r2   r:  
  s    z0TestSQLiteFallback.test_read_sql_named_parameterc                 C  s   |   |¡ d S r½   r;  r=  r1   r1   r2   r<  
  s    z$TestSQLiteFallback.test_to_sql_emptyc                 C  sd   t ddddgddddgdœƒ}| j |d¡dks4t‚| j d¡sDt‚| j d¡ | j d¡r`t‚d S )Nr>  r?  r@  rA  rB  Zdrop_test_framer@  )r   rÕ   rÒ   r„   r¶   r  )r  r¡  r1   r1   r2   Útest_create_and_drop_table
  s    ÿz-TestSQLiteFallback.test_create_and_drop_tablec                 C  s   |   |¡ d S r½   rH  r=  r1   r1   r2   r}  %
  s    z!TestSQLiteFallback.test_roundtripc                 C  s   |   ¡  d S r½   rI  r*  r1   r1   r2   r  (
  s    z#TestSQLiteFallback.test_execute_sqlc                 C  s€   t tdddƒtdddƒgdgd�}|jd| jdd�dks<t‚td	| jƒ}| jd
krft || 	t
¡¡ n| jdkr|t ||¡ d S )Nrl  r’   rÙ   rø   r¤   rm  Frú   zSELECT * FROM test_dater   r   )r   r   rÒ   r4   r„   r   r0  r…   rû   r—   r"   rÑ  r1   r1   r2   rn  +
  s    "

z%TestSQLiteFallback.test_datetime_dateÚtz_awareFc                 C  sž   |st dddƒt dddƒg}n$tdddd�}t| ¡ ƒ d	d
„ ¡}t|dgd�}|jd| jdd�dksjt‚t	d| jƒ}| j
dkrš| dd
„ ¡}t ||¡ d S )Nro  r   r’   rp  rX  rÙ   rY  rZ  c                 S  s   |   ¡ S r½   )Útimetz)rS  r1   r1   r2   rw  =
  rx  z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>rø   r¤   rq  Frú   zSELECT * FROM test_timer   c                 S  s
   |   d¡S rr  rs  ru  r1   r1   r2   rw  E
  rx  )r   r   r   Zto_pydatetimeÚmapr   rÒ   r4   r„   r   r0  rz  r…   rû   )r  rå  Ztz_timesZtz_dtr˜   r[  r  r1   r1   r2   r|  6
  s    
z%TestSQLiteFallback.test_datetime_timec                 C  sT   t  dd|› d� | j¡}g }|jD ]*}t  d|› d�| j¡}| |j ¡ ¡ q$|S )Nz1SELECT * FROM sqlite_master WHERE type = 'index' zAND tbl_name = 'ú'zPRAGMA index_info(ú))rò   r   r4   r  rØ   r‡  )r  ré  rê  rU  Zix_nameZix_infor1   r1   r2   rT  H
  s    
ÿý
z%TestSQLiteFallback._get_index_columnsc                 C  s   |   ¡  d S r½   r†  r*  r1   r1   r2   r‡  T
  s    z)TestSQLiteFallback.test_to_sql_save_indexc                 C  s   |   ¡  d S r½   rˆ  r*  r1   r1   r2   r‰  W
  s    z$TestSQLiteFallback.test_transactionsc           
      C  sT   | j  d|› d�¡}|D ] \}}}}}}	||kr|  S qtd|› d|› d�ƒ‚d S )NzPRAGMA table_info(ré  zTable z	, column r"  )r4   r9   râ   )
r  rë   r%  ZrecsZcidr  ÚctypeZnot_nullÚdefaultÚpkr1   r1   r2   r'  Z
  s
    
z*TestSQLiteFallback._get_sqlite_column_typec              	   C  s  | j dkrt d¡ ddg}ddg}t||d�}| d| j¡d	ksFt‚|jd
| jddid�d	ksdt‚|  dd¡dksxt‚|  d
d¡dksŒt‚d}tjt	|d�� |jd| jdt
id� W 5 Q R X |jd| jdd�d	ksÚt‚|  dd¡dksît‚|  dd¡dk�st‚d S )Nr   úNot applicable to MySQL legacyr›   rœ   rŒ  r�  r¤   rŽ  rÙ   r�  ÚSTRINGrt  rº  z!B \(<class 'bool'>\) not a stringrß   rY  r‘  )r0  r¯   r2  r   rÒ   r4   r„   r'  rá   râ   Úbool)r  rÜ  r�   r˜   rã   r1   r1   r2   r–  a
  s    

 zTestSQLiteFallback.test_dtypec                 C  sÄ   | j dkrt d¡ tdd gƒttdddƒd gƒtdd gdd�td	d gƒd
œ}t|ƒ}d}| || j¡dkspt‚|  	|d¡dks„t‚|  	|d¡dks˜t‚|  	|d¡dks¬t‚|  	|d¡dksÀt‚d S )Nr   rí  Tr—  rh  r’   rL  rt  r»  r˜  rœ  rÙ   r™  rº  rš  rë  r›  r&   r$   )
r0  r¯   r2  r   r   r   rÒ   r4   r„   r'  )r  rÜ  r˜   r  r1   r1   r2   rž  w
  s    



üz#TestSQLiteFallback.test_notna_dtypec                 C  sâ   t ddgddggddgd�}d}tjt|d	�� | d
| j¡ W 5 Q R X tddddddddddg
ƒD ]x\}}| || j¡dks‚t‚t 	|| j¡ t ddgddggd|gd�}d|d›�}| || j¡dksÎt‚t 	|| j¡ qdd S )Nr’   rÙ   rõ   r@  rø   rù   r¤   z$Empty table or column name specifiedrß   rã  ztest_weird_name]ztest_weird_name[ztest_weird_name`ztest_weird_name"ztest_weird_name'z_b.test_weird_name_01-30z"_b.test_weird_name_01-30"Z99beginswithnumberZ12345rÎ  Ztest_weird_col_nameÚd)
r   r¯   rá   râ   rÒ   r4   Ú	enumerater„   rò   Ztable_exists)r  r˜   rã   ZndxZ
weird_namerÍ  Zc_tblr1   r1   r2   Útest_illegal_names‹
  s.    öÿz%TestSQLiteFallback.test_illegal_namesN)r  r   r!  r]  r0  rÌ  rY   r/  r¯   r^  re  r8  r:  r<  rä  r}  r  rn  rÔ  rÕ  r|  rT  r‡  r‰  r'  r–  rž  rò  r1   r1   r1   r2   ry  ü	  s.   



ry  z'{}'z{:.8f}z{:d}c                 C  s   dS )NZNULLr1   )Úxr1   r1   r2   rw  µ
  rx  rw  z{:.10f}z'{!s}'c                 G  sJ   g }|D ]4}t |tƒr"t|ƒr"d }tt|ƒ }| ||ƒ¡ q| t|ƒ S r½   )rX   r•   r   Ú_formattersr   rØ   r2  )rò   r  Zprocessed_argsÚargÚ	formatterr1   r1   r2   Úformat_query»
  s    r÷  c                 C  s&   t j| |d� ¡ }|dkrdS t|ƒS )z#Replace removed sql.tquery functionr{  N)rò   r9   r'  rÃ  )r8  r|  r[  r1   r1   r2   ÚtqueryÇ
  s    rø  c                   @  sl   e Zd Zdd„ Zdd„ Zdd„ Zdd„ Zd	d
„ Zdd„ Zdd„ Z	dd„ Z
dd„ Zdd„ Zdd„ Zdd„ ZdS )ÚTestXSQLitec                 C  s   t  d¡| _d S rÇ   )r_   rY   r4   r*  r1   r1   r2   re  Î
  s    zTestXSQLite.setup_methodc                 C  s   | j  ¡  d S r½   r)  r*  r1   r1   r2   r  Ñ
  s    zTestXSQLite.teardown_methodc                 C  s.   | j  ¡ }| dt |¡› �¡ | j  ¡  d S r#  )r4   r8   r9   rò   r$  r%  )r  rˆ   r?   r1   r1   r2   r  Ô
  s    
zTestXSQLite.drop_tablec                 C  sÚ   t  ¡ }tj|d| jdd�dks$t‚t d| j¡}|j|_|}t  ||¡ dgt	|ƒ |d< | 
¡ }tt t	|ƒ¡ƒd }| 
¡ |d	< tj|d
| jdd�dks¢t‚tjd| jd	d�}| 
¡ }||_d	|j_t  ||¡ d S )NZ
test_tableF)r  r|  rš   rp  zselect * from test_tablerø   Útxtr�  ZIdxZtest_table2zselect * from test_table2r¨  )r…   ÚmakeTimeDataFramerò   rÒ   r4   r„   rü   rš   rû   rÓ   rÌ  r   r‚   rv  r  )r  rx   rŠ   r  Zframe2Znew_idxr1   r1   r2   Ú
test_basicÙ
  s     zTestXSQLite.test_basicc           	      C  sœ   t  ¡ }tj|jd< t |d¡}| j ¡ }| 	|¡ d}| 
¡ D ]$\}}t|f|žŽ }t|| jd� q@| j ¡  tjd| jd�}|j|_t j||dd� d S )N)r   r   r±  z(INSERT INTO test VALUES (%s, %s, %s, %s)r{  úselect * from testgü©ñÒMbP?)Zrtol)r…   rû  r‚   rÐ  r€   rò   r´  r4   r8   r9   Ziterrowsr÷  rø  r%  rü   rš   rû   )	r  rx   r¶  r?   Úinsrv  rP   Zfmt_sqlrŠ   r1   r1   r2   Útest_write_row_by_rowï
  s    


z!TestXSQLite.test_write_row_by_rowc                 C  sŒ   t  ¡ }t |d¡}| j ¡ }| |¡ d}|jd }tj|| jt|ƒd� | j 	¡  t 
d| j¡}|jd d… |_t  ||d d… ¡ d S )Nr±  z$INSERT INTO test VALUES (?, ?, ?, ?)r   r4  rý  r’   )r…   rû  rò   r´  r4   r8   r9   r€   r2  r%  rü   rš   rû   )r  rx   r¶  r?   rþ  rP   rŠ   r1   r1   r2   Útest_execute  s    



zTestXSQLite.test_executec                 C  s˜   t  ¡ }t |d¡}| ¡ }|D ]6}| d¡}t|ƒdkr |d dkr |d dks t‚q tj|dddgd	�}| ¡ }d
|ks€t‚| j 	¡ }| 
|¡ d S )Nr±  ú rÙ   r   r›   r’   ZDATETIMErœ   rê   zPRIMARY KEY ("A", "B"))r…   rû  rò   r´  Ú
splitlinesr#  rÓ   r„   r4   r8   r9   )r  rx   r¶  ÚlinesÚlineÚtokensr?   r1   r1   r2   Útest_schema  s    

zTestXSQLite.test_schemac              	   C  sb   d}| j  ¡ }| |¡ t d| j ¡ t d| j ¡ tjtjdd�� t d| j ¡ W 5 Q R X d S )Núƒ
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a, b)
        );
        ú,INSERT INTO test VALUES("foo", "bar", 1.234)z,INSERT INTO test VALUES("foo", "baz", 2.567)zExecution failed on sqlrß   z(INSERT INTO test VALUES("foo", "bar", 7))r4   r8   r9   rò   r¯   rá   r  )r  r¶  r?   r1   r1   r2   Útest_execute_fail  s    	

zTestXSQLite.test_execute_failc              	   C  sb   d}| j  ¡ }| |¡ t d| j ¡ | j  ¡  d}tjtj|d�� t	d| j d� W 5 Q R X d S )Nr  r  z$Cannot operate on a closed database.rß   rý  r{  )
r4   r8   r9   rò   rÉ   r¯   rá   r_   ZProgrammingErrorrø  )r  r¶  r?   rã   r1   r1   r2   Útest_execute_closed_connection2  s    	


z*TestXSQLite.test_execute_closed_connectionc                 C  s2   t dt d¡iƒ}tj|| jddd�dks.t‚d S )NÚFromrh  ZtestkeywordsF©r|  r  rš   )r   r‚   Zonesrò   rÒ   r4   r„   rˆ  r1   r1   r2   Útest_keyword_as_column_namesF  s    z(TestXSQLite.test_keyword_as_column_namesc                 C  st   t ddgdgd�}tj|| jddd�dks.t‚| j}tdd	„ | d
¡D ƒƒ}|dksXt‚t d
|¡}t 	||¡ d S )Nr’   rÙ   Zc0r¤   Úmono_dfFr  c                 s  s   | ]}|d  V  qdS )r   Nr1   )rJ   Zmy_c0r1   r1   r2   Ú	<genexpr>R  s     z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfrõ   )
r   rò   rÒ   r4   r„   Úsumr9   rü   r…   rû   )r  r  Zcon_xZthe_sumrŠ   r1   r1   r2   Útest_onecolumn_of_integerJ  s    z%TestXSQLite.test_onecolumn_of_integerc              	   C  sÄ  t ddgddgdœƒ}t dddgd	d
dgdœƒ}d}d|› �}d}tjt|d�� tj|| j|dd� W 5 Q R X |  |¡ tj|| j|dd� d}tjt|d�� tj|| j|dd� W 5 Q R X tj|| j|ddd� t|| jd�ddgksðt	‚tj|| j|ddd�dk�st	‚t|| jd�dddgk�s.t	‚|  |¡ tj|| j|ddd�dk�sXt	‚t|| jd�ddgk�stt	‚tj|| j|ddd�dk�s”t	‚t|| jd�dddddgk�s¶t	‚|  |¡ d S )Nr’   rÙ   r›   rœ   )r  r  rõ   r@  rh  r�   rž   ÚEZtable_if_existszSELECT * FROM z*'notvalidvalue' is not valid for if_existsrß   Znotvalidvalue)rx   r|  r  rÜ   rÚ   z&Table 'table_if_exists' already existsr×   F)rx   r|  r  rÜ   rš   r{  rj  r„  )rõ   r�   )r@  rž   )rh  r  rØ   )
r   r¯   rá   râ   rò   rÒ   r4   r  rø  r„   )r  Zdf_if_exists_1Zdf_if_exists_2rˆ   Z
sql_selectrã   r1   r1   r2   Útest_if_existsY  s˜    
ü
   ÿ   ÿûûùÿ

ûùÿ
ûùÿ
ûzTestXSQLite.test_if_existsN)r  r   r!  re  r  r  rü  rÿ  r   r  r	  r
  r  r  r  r1   r1   r1   r2   rù  Í
  s   rù  )N)�r]  Ú
__future__r   r;   r   r   r   rŒ   r   Úpathlibr   r_   Únumpyr‚   r¯   Zpandas.util._test_decoratorsÚutilZ_test_decoratorsr
  Zpandas.core.dtypes.commonr	   r
   Zpandasr_  r   r   r   r   r   r   r   r   r   r   Zpandas._testingZ_testingr…   Zpandas.io.sqlrò   r   r   r   r   r   r   r   r.   r*  rÿ  r6  r3   rB   r\   ra   rs   ru   rv   r‡   r‹   r^  r�   rt   r™   r¨   r©   r¼   r¿   rÁ   rÂ   rÃ   rÄ   rÅ   rÆ   rÊ   rË   Zmysql_connectableZpostgresql_connectableZsqlite_connectableZsqlite_iris_connectableZsqlalchemy_connectableZsqlalchemy_connectable_irisZall_connectableZall_connectable_irisrÔ  rò  rÕ  rÖ   rÞ   rä   rè   rð   rô   r   r  r  r"  r,  r.  r_  r)  rÙ  r  r  r  r+  rÎ  rÏ  r×  rÚ  rÞ  rß  rà  rá  râ  rã  ry  rd  r"   Zstr_Úbytesr•   r  r   r   rï  rô  r÷  rø  rù  r1   r1   r1   r2   Ú<module>   s^  0$

ýøýí!



#










þþþþ
ÿ

	#%	 
   G JW     :Vm 4         ÷
