a
    d                    @  s  d Z ddlmZ ddlZddlmZ ddlZddlmZ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ZddlmZ ddlm  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$m%Z% ddl&m'Z( dd
l)m*Z*m+Z+ ddl,m-Z- ddl.m/Z/ ddl0m1Z1m2Z2m3Z3m4Z4m5Z5m6Z6m7Z7 zddl8Z8dZ9W n e:yV   dZ9Y n0 ej;dd Z<ddddZ=dddddZ>dddddZ?dd Z@ddd d!ZAdd"d#d$d%ZBd"dd&d'd(ZCd)d*d+d,ZDdd-d.d/ZEej;d0d1 ZFej;d2d3 ZGej;d4d5 ZHej;d6d7 ZIej;d8d9 ZJej;d:d; ZKej;d<d= ZLej;d>d? ZMej;d@dA ZNej;dBdC ZOej;dDdE ZPej;dFdG ZQej;dHdI ZRej;dJdK ZSej;dLdM ZTej;dNdO ZUej;dPdQ ZVd;d=gZWd?dAgZXg dRZYg dSZZeWeX eY Z[eWeX eZ Z\e[dOg Z]e\dQg Z^ej_j`ej_adTe]dUdV Zbej_j`ej_adTe]dWdX Zcej_j`ej_adTe]dYdZ Zdej_j`ej_adTe]ej_ad[dd\gd]d^ Zeej_j`ej_adTe]ej_ad_d`dagdbdc Zfej_j`ej_adTe]ddde Zgej_j`ej_adTe^dfdg Zhej_j`ej_adTe^dhdi Ziej_j`ej_adTe\djdk Zjej_j`ej_adTe^dldm Zkej_j`ej_adTe\dndo Zlej_j`ej_adTe\dpdq Zmej_j`ej_adTe[drds Znej_j`ej_adTeWdtdu Zoej_j`ej_adTeWdvdw Zpej_j`ej_adTeXej_adxdydzgd{d| Zqej_j`ej_adTeXd}d~ Zrej_j`ej_adTeWdd Zsej_j`ej_adTeXdd Ztdd Zudd Zvdd ZwG dd dZxG dd dexZyG dd dexZzG dd dZ{G dd de{Z|ej_j}e9 ddG dd deze|Z~G dd deye|Zej_j}e9 ddG dd deze{ZG dd deZej_j`G dd deZej_j`G dd deZG dd deye{Zedjedjejdjedjedjedjeddd ejdjedji	Zdd ZdddZG dd dZd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. The different tested flavors (sqlite3, MySQL,
      PostgreSQL) derive from the base class
    - Tests for the fallback mode (`TestSQLiteFallback`)

    )annotationsN)closing)datedatetimetime	timedelta)StringIOPath)lib)	DataFrameDatetimeTZDtypeIndex
MultiIndexSeries	Timestampconcat
date_rangeisnato_datetimeto_timedelta)ArrowStringArrayStringArray)Version)sql)SQLAlchemyEngineSQLDatabaseSQLiteDatabase
get_enginepandasSQL_builderread_sql_queryread_sql_tableTFc                   C  s$   dddddddddd	d
ddS )Nz1SELECT * 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 r(   r(   r(   a/var/www/html/stable-diffusion-webui/venv/lib/python3.9/site-packages/pandas/tests/io/test_sql.pysql_stringsP   s    r*   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SepalLength
SepalWidthZPetalLengthZ
PetalWidthName   )
sqlalchemyr-   r.   r/   r0   r1   r2   )
r,   r-   r.   r/   r0   r1   r2   dtypemetadatar3   r(   r(   r)   iris_table_metadatam   s     		r;   zsqlite3.Connectionr
   )conn	iris_filec                 C  sj   |   }d}|| |jd dd2}t|}t| d}||| W d    n1 s\0    Y  d S )NzCREATE TABLE iris (
            "SepalLength" REAL,
            "SepalWidth" REAL,
            "PetalLength" REAL,
            "PetalWidth" REAL,
            "Name" TEXT
        )utf-8newlineencodingz&INSERT INTO iris VALUES(?, ?, ?, ?, ?))cursorexecuteopencsvreadernextexecutemany)r<   r=   curstmtcsvfilerF   r(   r(   r)   create_and_load_iris_sqlite3   s    

rL   )r=   r,   c           
   
     sV  ddl m} ddlm} t|}|jd dd}t|}t|  fdd|D }||	|}	t
| |r|  \} |  4 |j| dd	 |j| d
 | |	 W d    n1 s0    Y  W d    n1 s0    Y  nN|  4 |j| dd	 |j| d
 | |	 W d    n1 s(0    Y  W d    n1 sH0    Y  d S )Nr   insertEnginer>   r?   c                   s   g | ]}t t |qS r(   dictzip.0rowheaderr(   r)   
<listcomp>       z(create_and_load_iris.<locals>.<listcomp>TZ
checkfirstbind)r8   rN   sqlalchemy.enginerP   r;   rD   rE   rF   rG   values
isinstanceconnectbegindropcreaterC   )
r<   r=   r,   rN   rP   r3   rK   rF   paramsrJ   r(   rW   r)   create_and_load_iris   s$    



H
rf   c              	   C  s   d}t | tjr$|  }|| nddlm} ddlm} ||}t | |r| 	 B} | 
  | | W d    n1 s0    Y  W d    q1 s0    Y  n2| 
  | | W d    n1 s0    Y  d S )Nz+CREATE VIEW iris_view AS SELECT * FROM irisr   textrO   )r`   sqlite3
ConnectionrB   rC   r8   rh   r^   rP   ra   rb   )r<   rJ   rI   rh   rP   r(   r(   r)   create_and_load_iris_view   s    


H
rk   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/   Integerr0   r2   r"   typesTextColDateCol
IntDateColIntDateOnlyColFloatColIntColBoolColIntColWithNullBoolColWithNullr$   DateColWithTzT)timezone)
r8   rl   rm   r.   rn   r/   ro   r0   r2   Zappend_column)r,   rl   rm   r.   rn   r/   ro   r0   r2   Z	date_typeZ	bool_typer:   rp   r(   r(   r)   types_table_metadata   s(    (r|   z
list[dict])r<   
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(?, ?, ?, ?, ?, ?, ?, ?, ?)
            )rB   rC   rH   )r<   r}   rI   rJ   r(   r(   r)   create_and_load_types_sqlite3   s
    
r~   )r}   r,   c              	   C  s   ddl m} ddlm} t|}|||}t| |r|  \} |  4 |j	| dd |j
| d | | W d    n1 s0    Y  W d    q1 s0    Y  nL|  4 |j	| dd |j
| d | | W d    n1 s0    Y  d S )Nr   rM   rO   Tr[   r\   )r8   rN   r^   rP   r|   r_   r`   ra   rb   rc   rd   rC   )r<   r}   r,   rN   rP   rp   rJ   r(   r(   r)   create_and_load_types   s    


H
r   r   framec                 C  sL   | j jd j}| jd }t|tjs(J t|jg d | j	dv sHJ d S )Nr   ffffff@g      @gffffff?皙?Iris-setosa))      )   r   )
dtypesiloctype
issubclassnpfloatingtmequalContentsr_   shape)r   ZpytyperV   r(   r(   r)   check_iris_frame  s
    
r   )
table_namec                 C  s  d| }t | tjr0|  }|| d S ddlm} ddlm	} t | t
rzT|| }| (} | | W  d    W |  S 1 s0    Y  W |  n
|  0 nTt | | r|  } | | W  d    S 1  s0    Y  n| | S d S )Nz SELECT count(*) AS count_1 FROM r   )create_enginerO   )r`   ri   rj   rB   rC   fetchoner8   r   r^   rP   r+   ra   exec_driver_sqlZ
scalar_onedispose)r<   r   rJ   rI   r   rP   enginer(   r(   r)   
count_rows  s$    



0r   c                 C  s   | dddd}t |S )NiodatarE   ziris.csvr	   )datapath	iris_pathr(   r(   r)   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i2g333333$@   Fz2000-01-01 00:00:00-08:00)
rq   rr   rs   rt   ru   rv   rw   rx   ry   rz   2000-01-04 00:00:00i 'Pi\2z2000-06-01 00:00:00-07:00r(   r(   r(   r(   r)   r}   1  s.    r}   c              
   C  s2   dddddddddd	}t | }||  |S )Nr+   int64float)	rq   rr   rs   rt   ru   rv   rw   rx   ry   )r   keysastype)r}   r   dfr(   r(   r)   types_data_frameO  s    r   c                  C  s   g d} g d}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>ݦ4gёR?)2000-01-06 00:00:00g?{mX?$?gnk3Am?g
S?columnsr   r   r   r(   r(   r)   test_frame1`  s    r   c                  C  s   g d} g d}t || dS )N)r   r   r   ))r   igZ)r   ir   )r   i N  r   )r   i͏r   r   r   r   r(   r(   r)   test_frame3  s    r   c           	   	   c  s   t d}t d}|jdd|jjji|jjd}||}|	dsRt
|| d |	ds||D ]}|d	 q`t||d |V  | L}| $ |d
}|| W d    n1 s0    Y  W d    n1 s0    Y  |  d S )Nr8   pymysqlz*mysql+pymysql://root@localhost:3306/pandasclient_flag)connect_args	poolclassr3   r#   rp   rz    DROP TABLE IF EXISTS test_frame;)pytestimportorskipr   	constantsCLIENTMULTI_STATEMENTSpoolNullPoolinspect	has_tablerf   popr   ra   rb   rh   rC   r   )	r   r}   r8   r   r   inspentryr<   rJ   r(   r(   r)   mysql_pymysql_engine  s(    







Fr   c                 c  s2   |   }|V  W d    n1 s$0    Y  d S Nra   )r   r<   r(   r(   r)   mysql_pymysql_conn  s    
r   c              	   c  s   t d}t d |jd|jjd}||}|dsFt|| d |ds\t||d |V  |	 L}|
 $ |d}|| W d    n1 s0    Y  W d    n1 s0    Y  |  d S )	Nr8   psycopg2z=postgresql+psycopg2://postgres:postgres@localhost:5432/pandasr   r3   r$   rp   r   )r   r   r   r   r   r   r   rf   r   ra   rb   rh   rC   r   )r   r}   r8   r   r   r<   rJ   r(   r(   r)   postgresql_psycopg2_engine  s"    







Fr   c                 c  s2   |   }|V  W d    n1 s$0    Y  d S r   r   )r   r<   r(   r(   r)   postgresql_psycopg2_conn  s    
r   c                  c  s@   t d t } d|  V  W d    n1 s20    Y  d S )Nr8   
sqlite:///)r   r   r   ensure_cleannamer(   r(   r)   
sqlite_str  s    

r   c                 c  s.   t d}|j| |jjd}|V  |  d S )Nr8   r   )r   r   r   r   r   r   )r   r8   r   r(   r(   r)   sqlite_engine  s    
r   c                 c  s2   |   }|V  W d    n1 s$0    Y  d S r   r   )r   r<   r(   r(   r)   sqlite_conn  s    
r   c                 C  s,   t d}|| }t||d |  | S )Nr8   r"   )r   r   r   rf   r   )r   r   r8   r   r(   r(   r)   sqlite_iris_str  s
    

r   c                 C  s   t | |d | S )Nr"   )rf   )r   r   r(   r(   r)   sqlite_iris_engine  s    r   c                 c  s2   |   }|V  W d    n1 s$0    Y  d S r   r   )r   r<   r(   r(   r)   sqlite_iris_conn  s    
r   c               	   c  s^   t td:} | }|V  W d    n1 s20    Y  W d    n1 sP0    Y  d S N:memory:)
contextlibr   ri   ra   )Zclosing_connr<   r(   r(   r)   sqlite_buildin  s    r   c                 C  s   t | | | S r   )rL   )r   r   r(   r(   r)   sqlite_buildin_iris  s    
r   )r   r   r   )r   r   r   r<   c                 C  s    | | } |jd| ddd d S )NtestappendFr   con	if_existsr   )getfixturevalueto_sql)r<   r   requestr(   r(   r)   test_dataframe_to_sql"  s    
r   c              	   C  s   t d ttjdgddtjtdddgddtjtdddgddtjtdgddtjd	gd
dd}|| } t	j
tdd" |jd| ddd W d    n1 s0    Y  d S )Npyarrowr   zint8[pyarrow]r9     timestamp[ns][pyarrow]zdate32[day][pyarrow]zduration[ns][pyarrow]azstring[pyarrow])intr   r   r   stringzthe 'timedelta'match
test_arrowreplaceFr   )r   r   r   pdarrayr   r   r   r   r   assert_produces_warningUserWarningr   r<   r   r   r(   r(   r)   "test_dataframe_to_sql_arrow_dtypes*  s    

r   c                 C  sL   t d tdtjtddd|gddi}|| } |jd| dd	d
 d S )Nr   r   r   r   r   r   r   r   Fr   )r   r   r   r   r   r   r   r   )r<   r   Znulls_fixturer   r(   r(   r)   *test_dataframe_to_sql_arrow_dtypes_missing?  s    

r   methodZmultic                 C  sn   | | } t| dd.}|j|d|d |ds6J W d    n1 sJ0    Y  t| dt|ksjJ d S )NTZneed_transaction
test_framer   r   r   r   r   r   len)r<   r   r   r   	pandasSQLr(   r(   r)   test_to_sqlO  s
    
,r  zmode, num_row_coef)r   r   )r      c                 C  s   | | } t| dd>}|j|ddd |j|d|d |dsFJ W d    n1 sZ0    Y  t| d|t| ks~J d S )NTr   r   failr   r   )r<   modeZnum_row_coefr   r   r  r(   r(   r)   test_to_sql_existZ  s    
,r  c              	   C  s   | | } t| ddp}|j|ddd |ds6J d}tjt|d  |j|ddd W d    n1 sn0    Y  W d    n1 s0    Y  d S )NTr   r   r  r  z!Table 'test_frame' already existsr   )r   r   r   r   r   raises
ValueError)r<   r   r   r  msgr(   r(   r)   test_to_sql_exist_failf  s    
r  c                 C  s\   | | } td| }t| td| }t| td| }|jdksJJ d|jv sXJ d S )NSELECT * FROM irisSELECT * FROM iris where 0=1r   r   r5   )r   r    r   r   read_sqlr   r   r<   r   
iris_framer(   r(   r)   test_read_iris_querys  s    

r  c                 C  st   | | } ttd| dd}t| ttjd| dd}t| ttjd| dd}|jdksbJ d|jv spJ d S )Nr     	chunksizer  r  r5   )r   r   r    r   r   r  r   r   r  r(   r(   r)   test_read_iris_query_chunksize  s    
r  c           
      C  s   | | } ddlm}m}m}m} | }t| tr:|| n| }|d||d}t||| dddd}	t	|	 t| tr~|
  d S )	Nr   )r0   r2   r   selectr3   )Zautoload_withr   r   r   lengthre   )r   r8   r0   r2   r   r  r`   r+   r    r   r   )
r<   r   r0   r2   r   r  r:   Zautoload_conr3   r  r(   r(   r)   .test_read_iris_query_expression_with_parameter  s    

r  c                 C  sV   |d   D ]\}}|| v r q2qtd|  d|| } t|| dd}t| d S )Nr%   zNo part of z( found in sql_strings['read_parameters']r   r   r  )itemsKeyErrorr   r    r   )r<   r   r*   dbqueryr  r(   r(   r)   *test_read_iris_query_string_with_parameter  s    
r!  c                 C  s4   | | } td| }t| td| }t| d S Nr3   )r   r!   r   r   r  r  r(   r(   r)   test_read_iris_table  s
    

r#  c                 C  sD   | | } ttd| dd}t| ttjd| dd}t| d S )Nr3   r  r  )r   r   r!   r   r   r  r  r(   r(   r)   test_read_iris_table_chunksize  s
    
r$  c                   s   | | } g   fdd}t| dd.}|j|d|d |dsFJ W d    n1 sZ0    Y   dgksrJ t| dt|ksJ d S )Nc                   s2    d  fdd|D }|| j | d S )Nr   c                   s   g | ]}t t |qS r(   rQ   rT   r   r(   r)   rY     rZ   z8test_to_sql_callable.<locals>.sample.<locals>.<listcomp>)r   rC   tablerN   )Zpd_tabler<   r   	data_iterr   checkr%  r)   sample  s    
z$test_to_sql_callable.<locals>.sampleTr   r   r   r   r   )r<   r   r   r*  r  r(   r(  r)   test_to_sql_callable  s    
,r+  c                 C  s   | | } td| }t|jjjtjs,J t|j	jjtj
sBJ t|jjjtj
sXJ t|jjjtjsnJ t|jjjtjsJ d S Nrp   )r   r   r!   r   ru   r9   r   r   r   rv   integerrw   rx   ry   r   r(   r(   r)   test_default_type_conversion  s    
r.  c           	   	   C  s   | | } ddlm} ddlm} tg dg dd}|jd| dd	 d
}||}t| |r|  B}|	  |
| W d    n1 s0    Y  W d    q1 s0    Y  n2| 	  | 
| W d    n1 s0    Y  td| }t|| td| }t|| d S )Nr   rg   rO   r   r     皙?r   333333?r   br   Fr   r   r   z}DROP PROCEDURE IF EXISTS get_testdb;

    CREATE PROCEDURE get_testdb ()

    BEGIN
        SELECT * FROM test_frame;
    ENDzCALL get_testdb();)r   r8   rh   r^   rP   r   r   r`   ra   rb   rC   r   r    r   assert_frame_equalr  )	r<   r   rh   rP   r   procZengine_connres1res2r(   r(   r)   test_read_procedure  s"    



H
(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} d u rZ|d u sfJ n| ksfJ 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 d    n1 s0    Y   S )
Nr   z, c                 S  s   g | ]}d | d qS )"r(   )rU   kr(   r(   r)   rY   !  rZ   zVtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<listcomp>.zCOPY z (z) FROM STDIN WITH CSV)r   file)
connectionrB   r   rE   writer	writerowsseekjoinschemar   Zcopy_expert)r&  r<   r   r'  Z
dbapi_connrI   Zs_bufrB  r   r   	sql_queryr<  r(   r)   psql_insert_copy  s    



,zBtest_copy_from_callable_insertion_method.<locals>.psql_insert_copyr   r  r2  r   r   ncol1col2Zcol3r   F)r   r   r   r   )r   r   r   r   r!   r   r7  )r<   r<  r   rI  expectedresult_countresultr(   rH  r)   (test_copy_from_callable_insertion_method  s    
rQ  c              	     s  | | } ddlm  ddlm} ddlm}  fdd}|d}t| |r|  B}|	  |
| W d    n1 s~0    Y  W d    q1 s0    Y  n2| 	  | 
| W d    n1 s0    Y  tg dgtd	d
}|jd| ddd tg dgtd	d
}|jd| dd|d}	td| }
t|
| |	dksRJ tj| dd}|d W d    n1 s0    Y  d S )Nr   rM   rO   rg   c                   s<    fdd|D }| j |jdgd}||}|jS )Nc                   s   g | ]}t t |qS r(   rQ   rT   r%  r(   r)   rY   D  rZ   z\test_insertion_method_on_conflict_do_nothing.<locals>.insert_on_conflict.<locals>.<listcomp>r   )Zindex_elements)r&  r_   Zon_conflict_do_nothingrC   rowcountr&  r<   r   r'  r   rJ   rP  rM   r%  r)   insert_on_conflictC  s    

zHtest_insertion_method_on_conflict_do_nothing.<locals>.insert_on_conflictzx
    CREATE TABLE test_insert_conflict (
        a  integer PRIMARY KEY,
        b  numeric,
        c  text
    );
    r    @r   abcr   test_insert_conflictr   Fr   r   g	@r5  r   r   r   r   r   Tr   )r   Zsqlalchemy.dialects.postgresqlrN   r^   rP   sqlalchemy.sqlrh   r`   ra   rb   rC   r   listr   r   r!   r   r7  r   
drop_table)r<   r   rP   rh   rT  
create_sqlr   rN  Z	df_insertinsertedrP  r  r(   rM   r)   ,test_insertion_method_on_conflict_do_nothing9  s>    

	


H
(r`  c              	     s  | | } ddlm  ddlm} ddlm}  fdd}|d}t| |r|  B}|	  |
| W d    n1 s~0    Y  W d    q1 s0    Y  n2| 	  | 
| W d    n1 s0    Y  tg dgtd	d
}|jd| ddd tg dgtd	d
}|jd| dd|d}	td| }
t|
| |	dksRJ tj| dd}|d W d    n1 s0    Y  d S )Nr   rM   rO   rg   c                   sH    fdd|D }| j |}|j|jj|jjd}||}|jS )Nc                   s   g | ]}t t |qS r(   rQ   rT   r%  r(   r)   rY     rZ   zXtest_insertion_method_on_conflict_update.<locals>.insert_on_conflict.<locals>.<listcomp>)r5  c)r&  r_   Zon_duplicate_key_updater_  r5  ra  rC   rR  rS  rM   r%  r)   rT  ~  s
    
zDtest_insertion_method_on_conflict_update.<locals>.insert_on_conflictzv
    CREATE TABLE test_insert_conflict (
        a INT PRIMARY KEY,
        b FLOAT,
        c VARCHAR(10)
    );
    rU  rW  r   rX  r   Fr   rY  rZ  r  Tr   )r   Zsqlalchemy.dialects.mysqlrN   r^   rP   r[  rh   r`   ra   rb   rC   r   r\  r   r   r!   r   r7  r   r]  )r<   r   rP   rh   rT  r^  r   r   rN  r_  rP  r  r(   rM   r)   (test_insertion_method_on_conflict_updatet  s:    
	


H
(rb  c           
   
   C  s&  | | } ddlm} ddlm} dt j }dt j }|d| d| d| d	| d
	}t| |r| 	 B}|
  || W d    n1 s0    Y  W d    q1 s0    Y  n2| 
  | | W d    n1 s0    Y  t|| }tdgdd}	t||	 d S )Nr   rO   rg   Zgroup_Zgroup_view_z
    CREATE TABLE zF (
        group_id INTEGER,
        name TEXT
    );
    INSERT INTO z- VALUES
        (1, 'name');
    CREATE VIEW z
    AS
    SELECT * FROM z;
    r   r   Zgroup_idr   )r   r^   rP   r[  rh   uuiduuid4hexr`   ra   rb   rC   r!   r   r   r7  )
r<   r   rP   rh   r   	view_nameZsql_stmtr   rP  rN  r(   r(   r)   test_read_view_postgres  s2    



H
(
rh  c                 C  sV   d}d}d}|  | |  | |  | td| }tdgdd}t|| d S )Nz<
CREATE TABLE groups (
   group_id INTEGER,
   name TEXT
);
z,
INSERT INTO groups VALUES
    (1, 'name');
z1
CREATE VIEW group_view
AS
SELECT * FROM groups;
zSELECT * FROM group_viewr   r   rc  )rC   r   r  r   r   r7  )r   create_tableZinsert_intoZcreate_viewrP  rN  r(   r(   r)   test_read_view_sqlite  s    


rj  c              	   C  sl   t jtddJ tjtdd td|  W d    n1 s@0    Y  W d    n1 s^0    Y  d S )Nz+pandas.io.sql.execute requires a connectionr   P`pandas.io.sql.execute` is deprecated and will be removed in the future version.select * from iris)r   r  	TypeErrorr   r   FutureWarningr   rC   )r   r(   r(   r)   test_execute_typeerror  s    ro  c                 C  s>   t jtdd td|  W d    n1 s00    Y  d S )Nrk  r   rl  )r   r   rn  r   rC   )r   r(   r(   r)   test_execute_deprecated  s
    rp  c                   @  s   e Zd Zdd ZdS )	MixInBasec              	   C  s   t | dr| j  z|  }W n tjjtjfy<   Y n^0 |H | |D ]}| 	|| qN| 
|D ]}| || qjW d    n1 s0    Y  d S )Nr<   )hasattrr<   closera   r8   excOperationalErrorri   _get_all_views	drop_view_get_all_tablesr]  )selfr<   viewtblr(   r(   r)   teardown_method  s    

zMixInBase.teardown_methodN)__name__
__module____qualname__r|  r(   r(   r(   r)   rq    s   rq  c                   @  s4   e Zd Zdd Zdd Zdd Zdd Zd	d
 ZdS )SQLiteMixInc                 C  s
   t dS r   ri   ra   ry  r(   r(   r)   ra     s    zSQLiteMixIn.connectc                 C  s"   | dt|  |  d S NzDROP TABLE IF EXISTS rC   r   _get_valid_sqlite_namecommitry  r   r<   r(   r(   r)   r]    s    zSQLiteMixIn.drop_tablec                 C  s   | d}dd | D S )Nz1SELECT name FROM sqlite_master WHERE type='table'c                 S  s   g | ]}|d  qS r   r(   )rU   r&  r(   r(   r)   rY     rZ   z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>rC   fetchallry  r<   ra  r(   r(   r)   rx    s    
zSQLiteMixIn._get_all_tablesc                 C  s"   | dt|  |  d S NzDROP VIEW IF EXISTS r  )ry  rg  r<   r(   r(   r)   rw    s    zSQLiteMixIn.drop_viewc                 C  s   | d}dd | D S )Nz0SELECT name FROM sqlite_master WHERE type='view'c                 S  s   g | ]}|d  qS r  r(   )rU   rz  r(   r(   r)   rY      rZ   z.SQLiteMixIn._get_all_views.<locals>.<listcomp>r  r  r(   r(   r)   rv    s    
zSQLiteMixIn._get_all_viewsN)r}  r~  r  ra   r]  rx  rw  rv  r(   r(   r(   r)   r    s
   r  c                   @  s@   e Zd Zedd Zdd Zdd Zdd Zd	d
 Zdd Z	dS )SQLAlchemyMixInc                 C  s   | j   d S r   )r   r   clsr(   r(   r)   teardown_class$  s    zSQLAlchemyMixIn.teardown_classc                 C  s
   | j  S r   )r   ra   r  r(   r(   r)   ra   (  s    zSQLAlchemyMixIn.connectc                 C  sP   |  r|   |   t|| W d    n1 sB0    Y  d S r   )in_transactionget_transactionrollbackrb   r   r   r]  r  r(   r(   r)   r]  +  s    
zSQLAlchemyMixIn.drop_tablec                 C  s   ddl m} || S Nr   r   )r8   r   Zget_table_namesry  r<   r   r(   r(   r)   rx  1  s    zSQLAlchemyMixIn._get_all_tablesc                 C  s`   |j jj|}| r$|   |   |d|  W d    n1 sR0    Y  d S r  )	r   r,   Zidentifier_preparerZquote_identifierr  r  r  rb   r   )ry  rg  r<   Zquoted_viewr(   r(   r)   rw  6  s    

zSQLAlchemyMixIn.drop_viewc                 C  s   ddl m} || S r  )r8   r   Zget_view_namesr  r(   r(   r)   rv  ?  s    zSQLAlchemyMixIn._get_all_viewsN)
r}  r~  r  classmethodr  ra   r]  rx  rw  rv  r(   r(   r(   r)   r  #  s   
	r  c                   @  sj   e 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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  s>   |  d| j t| jtjr*t| j| nt| j|| j d S r"  )r]  r<   r`   ri   rj   rL   rf   flavor)ry  r   r(   r(   r)   load_iris_dataK  s    zPandasSQLTest.load_iris_datac                 C  s\   | j dkr|D ]}|d qt| jtjrHdd |D }t| j| nt| j|| j  d S )Nr$   rz   c                 S  s   g | ]}t | qS r(   )tupler_   )rU   r   r(   r(   r)   rY   W  rZ   z1PandasSQLTest.load_types_data.<locals>.<listcomp>)r  r   r`   r<   ri   rj   r~   r   )ry  r}   r   r(   r(   r)   load_types_dataR  s    
zPandasSQLTest.load_types_datac                 C  s.   |d | j  }d}| jj||d}t| d S )Nr%   r  r  r  r  
read_queryr   ry  r*   r   re   r  r(   r(   r)   _read_sql_iris_parameter\  s    z&PandasSQLTest._read_sql_iris_parameterc                 C  s4   |d | j  }ddd}| jj||d}t| d S )Nr&   r   r   r  r  r  r  r(   r(   r)   _read_sql_iris_named_parameterb  s    
z,PandasSQLTest._read_sql_iris_named_parameterc                 C  s*   |d | j  }| jj|d d}t| d S )Nr'   r  r  )ry  r*   r   r  r(   r(   r)   (_read_sql_iris_no_parameter_with_percenth  s    z6PandasSQLTest._read_sql_iris_no_parameter_with_percentc                 C  s2   |  d| j | j|jd d ddks.J d S )Nr   r   )r]  r<   r  r   r   ry  r   r(   r(   r)   _to_sql_emptym  s    zPandasSQLTest._to_sql_emptyautoc                 K  sr   |  d| j | jj|dfd|i|dks0J | jds@J t|}t| jd}||ks`J |  d| j dS )z `to_sql` with the `engine` paramr   r      N)r]  r<   r  r   r   r   r   )ry  r   r   Zengine_kwargsnum_entriesnum_rowsr(   r(   r)   _to_sql_with_sql_engineq  s     z%PandasSQLTest._to_sql_with_sql_enginec                 C  sV   |  d| j | j|ddks$J | 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   r7  ry  r   rP  r(   r(   r)   
_roundtrip  s    zPandasSQLTest._roundtripc                 C  s(   | j d}| }t|g d d S Nr  r   )r  rC   r   r   r   )ry  iris_resultsrV   r(   r(   r)   _execute_sql  s    zPandasSQLTest._execute_sqlc                 C  sN   t jddgg ddgd}| j|ddks0J | d}|dggksJJ d S )Nr   rV  Zline1r        ?Zline2r   r   r   r   r   r   Ztest_to_sql_saves_indexr  )r   from_recordsr  r   _get_index_columns)ry  r   ix_colsr(   r(   r)   _to_sql_save_index  s    
z PandasSQLTest._to_sql_save_indexc                 C  sb  | j  J}d}t| j tr(|| nddlm} ||}|| W d    n1 sZ0    Y  G dd dt}d}t| j trddlm} ||}z@| j  "}|| |dW d    n1 s0    Y  W n |y   Y n0 | j 	d}t
|dks
J | j  }|| W d    n1 s60    Y  | j 	d}t
|d	ks^J d S )
Nz'CREATE TABLE test_trans (A INT, B TEXT)r   rg   c                   @  s   e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionN)r}  r~  r  r(   r(   r(   r)   DummyException  s   r  z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')errorzSELECT * FROM test_transr   )r  Zrun_transactionr`   r   rC   r8   rh   	Exceptionr   r  r   )ry  ZtransrJ   rh   r  Zins_sqlresr:  r(   r(   r)   _transaction_test  s0    (
**zPandasSQLTest._transaction_testN)r  )r}  r~  r  __doc__r  r  r  r  r  r  r  r  r  r  r  r(   r(   r(   r)   r  E  s   

r  c                
   @  s  e Zd ZU dZdZded< 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#g d$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d,d- Zd.d/ Zd0d1 Zejd2g d3d4d5 Z d6d7 Z!d8d9 Z"ejd:d;e#e$e#e$d<gd=d> Z%d?d@ Z&dAdB Z'dCdD Z(dEdF Z)dGdH Z*dIdJ Z+dKdL Z,dMdN Z-dOdP Z.dQdR Z/d;S )S_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  TZautousec                 C  s*   |   | _| | | | |   d S r   )ra   r<   r  r  load_test_data_and_sqlry  r   r}   r(   r(   r)   setup_method  s    


z_TestSQLApi.setup_methodc                 C  s   t | j d S r   )rk   r<   r  r(   r(   r)   r    s    z"_TestSQLApi.load_test_data_and_sqlc                 C  s   t d| j}t| d S )NzSELECT * FROM iris_view)r   r    r<   r   ry  r  r(   r(   r)   test_read_sql_view  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.0r   r  )r   r    r<   r   r7  r   )ry  r   Z
with_batchZwithout_batchr(   r(   r)   &test_read_sql_with_chunksize_no_result  s    z2_TestSQLApi.test_read_sql_with_chunksize_no_resultc                 C  s&   t |d| j t d| js"J d S )Nr   )r   r   r<   r   r  r(   r(   r)   r    s    z_TestSQLApi.test_to_sqlc                 C  sp   t j|d| jdd t d| js&J d}tjt|d$ t j|d| jdd W d    n1 sb0    Y  d S )Ntest_frame2r  r  z"Table 'test_frame2' already existsr   )r   r   r<   r   r   r  r	  ry  r   r
  r(   r(   r)   test_to_sql_fail  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:J t|}t| jd}||ksZJ d S )Nr   r  r  r   r   r   r<   r   r   r   ry  r   r  r  r(   r(   r)   test_to_sql_replace  s    z_TestSQLApi.test_to_sql_replacec                 C  sr   t j|d| jdddksJ t j|d| jdddks8J t d| jsJJ dt| }t| jd}||ksnJ d S )Ntest_frame4r  r  r  r   r  r  r  r(   r(   r)   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_frame5Fr   zSELECT * FROM test_frame5)r   r   r<   r  r   r7  )ry  r   rP  r(   r(   r)   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 )
Nr   r   r   Zseriesr   Ztest_seriesFr  zSELECT * FROM test_series)
r   r   aranger   r   r<   r    r   r7  to_frame)ry  ss2r(   r(   r)   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 )Nr  r   r  r  Tr  )r   r   r<   r    r   r  r   r   r   r   r7  r  r(   r(   r)   test_roundtrip!  s    z_TestSQLApi.test_roundtripc                 C  s6   t j|d| jddd t jd| jd}t|| d S )Nr  Fr  )r   r   r  r  r  )r   r   r<   r    r   r7  r  r(   r(   r)   test_roundtrip_chunksize,  s    z$_TestSQLApi.test_roundtrip_chunksizec                 C  sR   t | j}|d}W d    n1 s,0    Y  | }t|g d d S r  )r   r   r<   rC   r   r   r   )ry  
pandas_sqlr  rV   r(   r(   r)   test_execute_sql7  s    (z_TestSQLApi.test_execute_sqlc              	   C  s  t d| j}t|jjjtjr$J t jd| jdgd}t|jjjtjsNJ |j	 t
ddddddt
ddddddgksJ t jd| jdd	id}t|jjjtjsJ |j	 t
ddddddt
ddddddgksJ t jd| jd
gd}t|jjjtjs
J |j	 t
ddddddt
ddddddgks>J t jd| jd
did}t|jjjtjslJ |j	 t
ddddddt
ddddddgksJ t jd| jddid}t|jjjtjsJ |j	 t
dt
dgksJ d S )NSELECT * FROM typesrr   parse_datesi  r   r0  r   r  %Y-%m-%d %H:%M:%Srs   i        i  r  rt   z%Y%m%dz
2010-10-10z
2010-12-12)r   r    r<   r   rr   r9   r   r   
datetime64tolistr   rs   rt   ry  r   r(   r(   r)   test_date_parsing>  s\    



z_TestSQLApi.test_date_parsingr  )ignoreraisecoercezread_sql, text, moder  )r8   fallbackrp   r8   c                 C  s@   | j |v r<|ddi}||| jdd|iid}t|| d S )Nrr   zdatetime64[ns]errors)r   r  )r  r   r<   r   r7  )ry  r  rh   r  r  r   rN  rP  r(   r(   r)   test_custom_dateparsing_erroru  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.J t|j	jjtjsDJ d S )Nr  rr   rs   )	index_colr  )
r   r    r<   r   r   r9   r   r   r  rs   r  r(   r(   r)   test_date_and_index  s    z_TestSQLApi.test_date_and_indexc                 C  s   t tddgdd }tt  |jd| jd}W d    n1 sH0    Y  |dks^J t	d| j}t
|d |d d	 d S )
Nz00:00:01z00:00:03foor   test_timedeltar   r   r  zSELECT * FROM test_timedeltar   )r   r   r  r   r   r   r   r<   r   r    assert_series_equalrz  )ry  r   rO  rP  r(   r(   r)   r    s    .z_TestSQLApi.test_timedeltac                 C  s^   t dddgi}d}tjt|d( |jd| jdd u s<J W d    n1 sP0    Y  d S )Nr   y      ?      ?y               @zComplex datatypes not supportedr   Ztest_complexr  r   r   r  r	  r   r<   )ry  r   r
  r(   r(   r)   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sPJ d S )NrL  r  SELECT * FROM test_index_labeltest_index_label)index_labelr   )	r   ranger   r   r   r   r<   r    r   )ry  r  r  rN  
temp_framer   r   r(   r(   r)   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>J td| j}|jd d	ks^J |jd
 dkspJ tj|d| jdddgd}||ksJ td| j}|jd d 	 ddgksJ ddg|j
_tj|d| jdd}||ksJ td| j}|jd d 	 ddgksJ tj|d| jdddgd}||ksDJ td| j}|jd d 	 ddgksrJ d}tjt|d& tj|d| jddd W d    n1 s0    Y  d S )Nr  rL  )ZA0A1)B0ZB1r  r  r  r   r  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   r<   r    r   r  r   namesr   r  r	  )ry  Zexpected_row_countr  rP  r   r
  r(   r(   r)   "test_to_sql_index_label_multiindex  s\    

  z._TestSQLApi.test_to_sql_index_label_multiindexc                 C  sV   t jddgg dddgd}|jd| jd tjd	| jddgd
}tj||dd d S )Nr  r  r  r   r   r  test_multiindex_roundtripr  z'SELECT * FROM test_multiindex_roundtripr  TZcheck_index_type)r   r  r   r<   r   r    r   r7  ry  r   rP  r(   r(   r)   r    s    z%_TestSQLApi.test_multiindex_roundtripr9   Nr   r   c                 C  s`   t ddgddggddgd}|jd| jd	d
ks4J ||}tjd| j|d}t|| d S )N333333?g333333@ffffff@g333333@r   r   r   test_dtype_argumentr  r  z$SELECT A, B FROM test_dtype_argumentr   r9   )r   r   r<   r   r   r    r   r7  )ry  r9   r   rN  rP  r(   r(   r)   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  r0  r  r   r   Ztest_frame_integer_col_namesr   r  )r   r   r   r<   r  r(   r(   r)   test_integer_col_names#  s    z"_TestSQLApi.test_integer_col_namesc                 C  s"   t j|d| jd}d|v sJ d S )Nr   r  CREATEr   
get_schemar<   ry  r   r^  r(   r(   r)   test_get_schema'  s    z_TestSQLApi.test_get_schemac                 C  s$   t j|d| jdd}d|v s J d S )Nr   pypi)r   rF  zCREATE TABLE pypi.r  r  r(   r(   r)   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|v sZJ d|v sfJ d S )Nr8   r   )ro   INTEGER皙?r	  rV  皙@r4  r   r5  r  r  )r  r8   ro   r   r   r  r<   )ry  ro   r9   Zfloat_framer^  r(   r(   r)   test_get_schema_dtypes0  s    
z"_TestSQLApi.test_get_schema_dtypesc                 C  sf   t ddgddgd}tj|d| jdd}d	}||v s:J tj|d| jd
dgd}d}||v sbJ d S )Nr  r	  rV  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  r<   )ry  r   r   r^  Zconstraint_sentencer(   r(   r)   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	}g d
}tj	d| jddD ]0}t
||gdd}t||| ksJ |d7 }qbt|| | jdkrt  }d	}g d
}tjd| jddD ]0}t
||gdd}t||| ksJ |d7 }qt|| d S )Nr  )   r   Zabcder   Ztest_chunksizeFr6  zselect * from test_chunksizer   )r   r   r   r   r  r   r  TZignore_indexr   r8   )r   r   randomZdefault_rngZstandard_normalr\  r   r<   r   r    r   r   r   r7  r  r!   )ry  r   r9  r:  isizeschunkres3r(   r(   r)   test_chunksize_readJ  s0    


z_TestSQLApi.test_chunksize_readc                 C  s`   t g dg dd}| }|d d|d< |jd| jdd td	| j}t|| d S )
Nr/  )John P. Doez	Jane Dover#  )Z	person_idperson_namer$  categorytest_categoricalFr6  zSELECT * FROM test_categorical)	r   copyr   r   r<   r   r    r   r7  )ry  r   df2r  r(   r(   r)   r&  n  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  r0  r     ér5  r   Ztest_unicodeFr6  )r   r   r<   r  r(   r(   r)   test_unicode_column_name  s    z$_TestSQLApi.test_unicode_column_namec                 C  sJ   t g ddtjdgd}|jd| jdd td| j}t|| d S )	Nr   r   r  r   r
  r  zd1187b08-4943-4c8d-a7f6Fr6  z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r   nanr   r<   r   r    r   r7  ry  r   r  r(   r(   r)   test_escaped_table_name  s    z#_TestSQLApi.test_escaped_table_namec                 C  sj   t g dg ddd}|jd| jdd td| j}t g d	g d
g dgg dd}t|| d S )Nr/  r1  r   r   r5  ra  
test_tableFr6  z*SELECT a, b, a +1 as a, c FROM test_table;)r   r2  r  r   )r  r   r0  r   )r0  r3  r  r   )r   r5  r   ra  r   )r   r   r<   r   r  r   r7  )ry  r   rP  rN  r(   r(   r)   test_read_sql_duplicate_columns  s    z+_TestSQLApi.test_read_sql_duplicate_columns)0r}  r~  r  r  r  __annotations__r   fixturer  r  r  r  r  r  r  r  r  r  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  r(   r(   r(   r)   r    sx   


7


	
9	
$	r  zSQLAlchemy not installedreasonc                   @  s   e Zd ZdZdZ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ejddddddddddddd d!d"eeejd#krd$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"   r8   c                 C  s   t d| _d S Nzsqlite:///:memory:r8   r   r   r  r(   r(   r)   setup_class  s    zTestSQLApi.setup_classc                 C  s@   t |d| j ddg}t jd| j|d}|j |ks<J d S )Nr   r   r   r   )r   r   r<   r!   r   r  )ry  r   colsrP  r(   r(   r)   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s4J t jd| jddgd}|jjddgks^J t jd| jddgddgd}|jjddgksJ |j ddgksJ d S )	Nr   r   r  r   r   r   r   )r  r   )r   r   r<   r!   r   r  r   r  r  r(   r(   r)   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  r3   )r   r    r<   r  r   r7  r!   )ry  iris_frame1iris_frame2r(   r(   r)   test_read_sql_delegate  s    z!TestSQLApi.test_read_sql_delegatec              
   C  s  ddl m} ddlm} |d|dg}|D ]}t| j|r| j B}|  || W d    n1 sp0    Y  W d    q1 s0    Y  q,| j  | j| W d    q,1 s0    Y  q,t	
d , td| j td| j W d    n1 s0    Y  d S )Nr   rg   rO   z,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);Zother_tablezSELECT * FROM other_table)r8   rh   r^   rP   r`   r<   ra   rb   rC   r   r   r   r!   r    )ry  rh   rP   Z
query_listr   r<   r(   r(   r)   test_not_reflect_all_tables  s    
H,z&TestSQLApi.test_not_reflect_all_tablesc                 C  s   t jtdd$ t| jdd W d    n1 s80    Y  t d   |jd| jd W d    n1 sr0    Y  d S )NzThe 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CaseSensitiver  )r   r   r   r   r   r<   Zcheck_case_sensitiver   r  r(   r(   r)   (test_warning_case_insensitive_table_name  s    2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_namesr(   rU   r  r(   r(   r)   rY     rZ   z1TestSQLApi._get_index_columns.<locals>.<listcomp>)r^   rE  Z	InspectorZfrom_enginer<   get_indexes)ry  tbl_namerE  r   ixsr(   r(   r)   r    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|sXJ d S )
Nr   )	TIMESTAMPr   2014-12-12 01:542014-12-11 02:54Tutc	test_typer   )r8   rK  r   r   r   r   r<   SQLTabler`   r&  ra  r   )ry  rK  r   r  r&  r(   r(   r)   test_sqlalchemy_type_mapping  s    z'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)Zint8SMALLINT)ZInt8rS  )Zuint8rS  )ZUInt8rS  )int16rS  )ZInt16rS  )Zuint16r  )ZUInt16r  )int32r  )ZInt32r  )Zuint32BIGINT)ZUInt32rV  )r   rV  )Int64rV  r   rV  r  c                 C  sP   t ddgdg|d}t| j}tjd||d}t|jjjj	}||ksLJ d S )Nr   r   r   r   r9   rP  r   )
r   r   r   r<   rQ  r+   r&  ra  r   r   )ry  r-  rN  r   r  r&  rP  r(   r(   r)   test_sqlalchemy_integer_mapping  s
    z*TestSQLApi.test_sqlalchemy_integer_mappingr-  Zuint64ZUInt64c                 C  sb   t ddgdg|d}t| j}tjtdd  tjd||d W d    n1 sT0    Y  d S )	Nr   r   r   rX  z1Unsigned 64 bit integer datatype is not supportedr   rP  r   )r   r   r   r<   r   r  r	  rQ  )ry  r-  r   r  r(   r(   r)   (test_sqlalchemy_integer_overload_mapping#  s    z3TestSQLApi.test_sqlalchemy_integer_overload_mappingc           	      C  s   t  V}d| }d}|j||ddd t||}t||}d}t||}W d    n1 sd0    Y  t || t || t || d S )Nr   r3   r   Fr   r  )r   r   r   r   r  r!   r    r7  )	ry  r   r   db_urir&  r  r   r   r  r(   r(   r)   test_database_uri_string-  s    
*z#TestSQLApi.test_database_uri_stringpg8000c                 C  sB   d}t jtdd td| W d    n1 s40    Y  d S )Nz)postgresql+pg8000://user:pass@host/dbnamer]  r   zselect * from tabler   r  ImportErrorr   r  )ry  r[  r(   r(   r)   (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sDJ d S )Nr   rg   z#select * from iris where name=:namer   zIris-versicolorr  r6   )r8   rh   r   r  r<   set)ry  rh   Z	name_textiris_df	all_namesr(   r(   r)   test_query_by_text_objF  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sdJ d S )Nr   )	bindparamr  r   r   r  r6   )r8   re  r  r;   r  wherera  r6   r   r  r<   ra  )ry  re  r  r3   Zname_selectrb  rc  r(   r(   r)   test_query_by_select_objO  s    
z#TestSQLApi.test_query_by_select_objc                 C  sF   t g dg dd}|jd| jdd td| j}t|| d S )Nr+  r0  r  r   )r   z%_variationZtest_column_percentageFr6  r   r   r<   r   r!   r   r7  r-  r(   r(   r)   test_column_with_percentage\  s    z&TestSQLApi.test_column_with_percentageN)r}  r~  r  r  r  r  r  r;  r=  r>  rA  rB  rD  r  rR  r   r4  r5  r   r   r9   r   rY  rZ  r\  tdZskip_if_installedr`  rd  rg  rj  r(   r(   r(   r)   r8    sP   
	
	
	
	r8  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  Zdatabaser(   r(   r)   ra   o  s    zTestSQLiteFallbackApi.connectc              	   C  s   t  }t| |*}tj|d|dddks4J W d    n1 sH0    Y  t| |}td|}W d    n1 s0    Y  W d    n1 s0    Y  t || d S )NZtest_frame3_legacyFr  r  z!SELECT * FROM test_frame3_legacy;)r   r   r   ra   r   r   r    r7  )ry  r   r   r<   rP  r(   r(   r)   test_sql_open_closer  s    
$Hz)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installedr6  c                 C  sF   d}d}t jt|d td| W d    n1 s80    Y  d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installedr   r  r^  )ry  r<   r
  r(   r(   r)   test_con_string_import_error  s    z2TestSQLiteFallbackApi.test_con_string_import_errorc              	   C  sv   G dd d}t |dF}tt td| W d    n1 sJ0    Y  W d    n1 sh0    Y  d S )Nc                   @  s*   e Zd ZddddZ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|i || _d S r   )ri   rj   r<   )ry  argskwargsr(   r(   r)   __init__  s    zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__init__c                 S  s   t | j|S r   )getattrr<   )ry  r   r(   r(   r)   __getattr__  s    zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__getattr__c                 S  s   | j   d S r   )r<   rs  r  r(   r(   r)   rs    s    zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.closeN)r}  r~  r  rt  rv  rs  r(   r(   r(   r)   MockSqliteConnection  s   rw  r   zSELECT 1)r   r   r   r   r   r   r  )ry  rw  r<   r(   r(   r)   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  sn   t d| j}t d| j}t|| d}tjt j|d t d| j W d    n1 s`0    Y  d S )Nr  z9Execution failed on sql 'iris': near "iris": syntax errorr   r3   )	r   r    r<   r  r   r7  r   r  DatabaseError)ry  r?  r@  r
  r(   r(   r)   rA    s    z,TestSQLiteFallbackApi.test_read_sql_delegatec                 C  s   t |d}d|v sJ d S )Nr   r  )r   r  r  r(   r(   r)   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   r=  r   zColumn 
 not found)splitstripr	  )ry  rF  columncolr(   r(   r)   _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sPJ d S )	Nr   rL  rM  TrN  rP  r   rK  )r   r   r   r   r<   ZSQLiteTableZ
sql_schemar  )ry  r   r  r&  rF  r(   r(   r)   test_sqlite_type_mapping  s    z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r   )r}  r~  r  r  r  r  ra   rm  r   r4  skipifSQLALCHEMY_INSTALLEDrn  rx  rA  rz  r  r  r(   r(   r(   r)   rl  f  s   


	rl  c                   @  sn  e Zd ZU dZded< edd Zejdd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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-ej./dRdSe0j1gidSe0j1 gie0j1 gdTgdUgdVdW Z2dXdY Z3dZd[ Z4d\d] Z5d^d_ Z6d`da Z7dbdc Z8ej./dddedfgdgdh Z9ej./dddedigdjdk Z:ej./ddg dldmdn Z;dodpdqdrZ<dodpdsdtZ=dudv Z>ej./dwe?j@dxgej./dddedfgdydz ZAd{S )|_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+   r  c                 C  s   |    |   d S r   )setup_driversetup_enginer  r(   r(   r)   r;    s    z_TestSQLAlchemy.setup_classTr  c                 C  sb   z| j  | _t| j| _W n* tjjyH   t	
d| j d Y n0 | | | | d S )NzCan't connect to z server)r   ra   r<   r   r   r  r8   rt  ru  r   skipr  r  r  r  r(   r(   r)   r    s    
z_TestSQLAlchemy.setup_methodc                 C  s
   t  d S r   NotImplementedErrorr  r(   r(   r)   r    s    z_TestSQLAlchemy.setup_driverc                 C  s
   t  d S r   r  r  r(   r(   r)   r    s    z_TestSQLAlchemy.setup_enginec                 C  s   |  | d S r   r  ry  r*   r(   r(   r)   test_read_sql_parameter  s    z'_TestSQLAlchemy.test_read_sql_parameterc                 C  s   |  | d S r   r  r  r(   r(   r)   test_read_sql_named_parameter  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc                 C  s   |  | d S r   r  r  r(   r(   r)   test_to_sql_empty  s    z!_TestSQLAlchemy.test_to_sql_emptyc                 C  s   ddl m} |  }tg dg dd}tj|dd$}||dd	ksNJ W d    n1 sb0    Y  ||}|dsJ tj|dd}|d W d    n1 s0    Y  d S )
Nr   r        ?       @      @      @r  r  r  r  ZoneZtwoTr   r  r  )	r8   r   ra   r   r   r   r   r   r]  ry  r   Z	temp_connr  r  r   r(   r(   r)   test_create_table  s    2z!_TestSQLAlchemy.test_create_tablec                 C  s   ddl m} |  }tg dg dd}t|}||ddksHJ ||}|ds^J |d z|	  W n t
y   Y n0 |drJ d S )Nr   r  r  r  r  r  r  )r8   r   ra   r   r   r   r   r   r]  clear_cacheAttributeErrorr  r(   r(   r)   test_drop_table  s    

z_TestSQLAlchemy.test_drop_tablec                 C  s   |  | d S r   r  r  r(   r(   r)   r    s    z_TestSQLAlchemy.test_roundtripc                 C  s   |    d S r   r  r  r(   r(   r)   r    s    z _TestSQLAlchemy.test_execute_sqlc                 C  s   t jd| jd}t| d S )Nr3   r  )r   r!   r<   r   r  r(   r(   r)   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 )Nr3   r4   )r   r   )r   r!   r<   r   r   r   r_   r  r(   r(   r)   r=    s    z'_TestSQLAlchemy.test_read_table_columnsc                 C  sF   d}t jt|d  tjd| jd W d    n1 s80    Y  d S )Nz!Table this_doesnt_exist not foundr   Zthis_doesnt_existr  )r   r  r	  r   r!   r<   )ry  r
  r(   r(   r)   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$J t|j	jjtj
s:J t|jjjtjsPJ t|jjjtjsfJ t|jjjtszJ d S r,  )r   r!   r<   r   ru   r9   r   r   r   rv   r-  rw   Zbool_rx   ry   objectr  r(   r(   r)   r.  $  s    z,_TestSQLAlchemy.test_default_type_conversionc                 C  sH   t ddgid}|jd| jdddks*J td| j}t|| d S )Ni64l            )r   test_bigintFr6  r   ri  r  r(   r(   r)   r  0  s    z_TestSQLAlchemy.test_bigintc                 C  s(   t d| j}t|jjjtjs$J d S r,  	r   r!   r<   r   rr   r9   r   r   r  r  r(   r(   r)   test_default_date_load8  s    z&_TestSQLAlchemy.test_default_date_loadc                 C  sH  dd }t d| j}t|ds4|jtjjdd |j}t	|j
tsJJ t d| jdgd}t|ds||jtjjdd |j}t	|j
tsJ t|jjdksJ ||j ttt d| jd	d
dd}|j}t	|j
tsJ t|jjdksJ td| j}|j}t	|j
tsJ t|j|j td| j}||j d S )Nc                 S  s   t | jdr8| d tdks"J | d tdksJ ndt| jtrt| jjdksXJ tdddtdddg}t	|| j
d}t| | ntd	| j d S )
NMr   z2000-01-01 08:00:00r   z2000-06-01 07:00:00UTCtzr   z&DateCol loaded with incorrect type -> )r   Zis_np_dtyper9   r   r`   r   r+   dtr  r   r   r   r  AssertionError)r  Zexpected_datarN  r(   r(   r)   r)  D  s    


z:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkzselect * from typesrz   z&no column with datetime with time zoner6  r  r  r   r  Tr  rp   )r    r<   rr  node
add_markerr   r4  xfailrz   r`   r9   r   r+   r  r  r   r\  r   r!   r   r  )ry  r   r)  r   r  rN  r(   r(   r)   test_datetime_with_timezone?  s@    !



z+_TestSQLAlchemy.test_datetime_with_timezonec                 C  s   t dtddddi}|jd| jdddks0J | jd	krP|d jd
|d< n|d jd |d< t	d| j}t
|| td| j}| jdkrt|jd tsJ t|d |d< t
|| d S )Nr   2013-01-01 09:00:00r0  
US/Pacificperiodsr  Ztest_datetime_tzFr6  r$   r  zSELECT * FROM test_datetime_tzr"   r   r   )r   r   r   r<   r  r  Z
tz_convertZtz_localizer   r!   r   r7  r    r`   locr+   r   )ry  rN  rP  r(   r(   r)   %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s4J td| j}t tjgdgd	}t	|| d S )
Nr   i'  r   r   r  Ztest_datetime_obbFr6  r   )
r   r   r   r<   r   r!   r   ZNaTr   r7  )ry  r   rP  rN  r(   r(   r)   test_out_of_bounds_datetime  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sBJ tjd| jdd
}tj	||dd d S )Nz
2018-01-01r   Z6H)r  freqnumsr  Z	foo_tableZ	info_date)r   r   r  r  F)Zcheck_names)
r   Z
_with_freqr   r  r   r<   r   r!   r   r7  )ry  datesrN  rP  r(   r(   r)   "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
|s6J t jd| jdgd}t|jj	j
tjs`J t jd| jddid}t|jj	j
tjsJ t jd| jdddiid}t|jj	j
tjsJ t jd| jdgd}t|jj	j
tjsJ t jd| jddid}t|jj	j
tjsJ t jd| jdd	diid}t|jj	j
tjsFJ d S )
Nrp   r"   rr   r  r  formatrs   r  unit)r   r!   r<   r  r  r   r  r   rr   r9   r   rs   )ry  r   expected_typer(   r(   r)   r    s.    
z!_TestSQLAlchemy.test_date_parsingc                 C  s   t tdddtdd}|jd| jddks4J td| j}|jdd	d
}t	
|| td| j}|jdd	d
}| jdkrt|jd tsJ t|d |d< t	
|| nt	
|| d S )Nr  r0  r  r  r  test_datetimer  r   r   )ZaxisSELECT * FROM test_datetimer"   r  r   )r   r   r   r  r   r<   r   r!   rc   r   r7  r    r  r`   r  r+   r   r  r(   r(   r)   r    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sBJ t	d| j}t
|| td
| j}| jdkrt|jd tsJ t|d dd|d< t
|| nt
|| d S )Nr  r0  r  r  r  r   r   r  Fr6  r  r"   r  r   r  )r  )r   r   r   r  r,  r  r   r<   r   r!   r   r7  r    r  r`   r+   r   r  r(   r(   r)   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<J td| j}|d }t|d }t|| d S )	N  r   r  r   r   	test_dateFr6  )r   r   r   r<   r!   r   r   r  )ry  r   r  rP  rN  r(   r(   r)   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<J td| j}t|| |}tj|d|ddd
ksrJ td|}|	dd }t|| tj|d| jddd
ksJ | j
dkrtd| j}|	dd }t|| td| j}t|| d S )N	   r   r      r   r   	test_timeFr6  r  Z
test_time2r  zSELECT * FROM test_time2c                 S  s
   |  dS Nz%H:%M:%S.%fstrftime_r(   r(   r)   <lambda>	  rZ   z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>Z
test_time3r"   zSELECT * FROM test_time3c                 S  s
   |  dS r  r  r  r(   r(   r)   r  #	  rZ   )r   r   r   r<   r!   r   r7  r   r    mapr  )ry  r   r   r  r   refr(   r(   r)   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sDJ td| j}t	j
||dd	d
 d S )Ni  r   g        )s1r  Ztest_read_writeFr6  r   T)Zcheck_dtypeZcheck_exact)r   r   rU  float32r   r   r<   r   r!   r   r7  )ry  r  r  r   r(  r(   r(   r)   test_mixed_dtype_insert(	  s    z'_TestSQLAlchemy.test_mixed_dtype_insertc                 C  sl   t g ddtjdgd}|jd| jdddks4J td| j}t|| t	d	| j}t|| d S )
Nr+  r   r
  r  test_nanFr6  r0  SELECT * FROM test_nan)
r   r   r,  r   r<   r   r!   r   r7  r    r  r(   r(   r)   test_nan_numeric4	  s    z _TestSQLAlchemy.test_nan_numericc                 C  s   t g dtjtjtjgd}|jd| jdddks8J td| j}t|| |d 	d|d< d |d< t
d	| j}t|| d S )
Nr+  r  r  Fr6  r0  r   r  r  )r   r   r,  r   r<   r   r!   r   r7  r   r    r  r(   r(   r)   test_nan_fullcolumnA	  s    z#_TestSQLAlchemy.test_nan_fullcolumnc                 C  sv   t g dddtjgd}|jd| jdddks4J d |jd	< td| j}t	|| t
d
| j}t	|| d S )Nr+  r   r5  r  r  Fr6  r0  r  r   r  )r   r   r,  r   r<   r  r   r!   r   r7  r    r  r(   r(   r)   test_nan_stringP	  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 rF  r(   rG  r(   r(   r)   rY   f	  rZ   z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)r8   r   r<   rH  )ry  rI  r   r   rJ  r(   r(   r)   r  `	  s
    

z"_TestSQLAlchemy._get_index_columnsc                 C  s   |    d S r   r  r  r(   r(   r)   test_to_sql_save_indexi	  s    z&_TestSQLAlchemy.test_to_sql_save_indexc                 C  s   |    d S r   r  r  r(   r(   r)   test_transactionsl	  s    z!_TestSQLAlchemy.test_transactionsc           	   	   C  s"  ddl m} ddlm} d}tj||| jd}|jd d }| || j ||}t	| j|r| j
 B}|  || W d    n1 s0    Y  W d    q1 s0    Y  n6| j  | j| W d    n1 s0    Y  t|| j}tj||dd | || j d S )Nr   rg   rO   test_get_schema_create_tabler  Fr  )r8   rh   r^   rP   r   r  r<   r   r]  r`   ra   rb   rC   r!   r   r7  )	ry  r   rh   rP   r{  r^  Zblank_test_dfr<   Zreturned_dfr(   r(   r)   r  o	  s     
H*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}|jd	| jd
dksPJ |jd| jd|iddksnJ | }|j| jd |j	d j
d j}t||sJ d}	tjt|	d& |jd| jdtid W d    n1 s0    Y  |jd| jd|diddksJ |j| jd |j	d j
d j}t||s>J |jdksNJ |jd| j|ddksjJ |j| jd |j	d j
d j}
|j	d j
d j}t|
|sJ t||sJ d S )Nr   )rl   r1   r0   r   r   g?Tg?Nr   
dtype_testr  r  dtype_test2r   r   r9   r\   z&The type of B is not a SQLAlchemy typer   r  Zdtype_test3
   single_dtype_test)r8   rl   r1   sqlalchemy.schemar0   r   r   r<   reflecttablesr   r   r`   r   r  r	  r+   r  )ry  rl   r1   r0   r<  r   r   metaZsqltyper
  ZsqltypeaZsqltypebr(   r(   r)   
test_dtype	  s4    4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
|| jddksJ t|| j}	| }
|
j| jd | jdkr|n|}|
j| j}t|d j|sJ t|d j|sJ t|d j|sJ t|d j|sJ d S )Nr   )rm   rn   r/   ro   r  T  r   r   r  r   r  BoolDateIntr/   notna_dtype_testr  r  r\   r#   r  r  r  r/   )r8   rm   rn   r/   ro   r  r0   r   r   r   r   r<   r   r!   r  r  r  r   r`   r   )ry  rm   rn   r/   ro   r0   r<  r   r{  r  r  Zmy_typecol_dictr(   r(   r)   test_notna_dtype	  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sJ t
d| j	}t|d jd dt|d jd dksJ | }|j| j	d |jd j}	t|	d jt|	d jksJ t|	d j|s(J t|	d j|s>J t|	d j|sTJ t|	d j|sjJ d S )Nr   )
BigIntegerr/   ro   r  gYB?r  r   float64r   rU  r   )f32f64
f64_as_f32i32r  Ztest_dtypesFr   r     )Z	precision)r   r   r   r   r9   r   r     r\   r  r  r  )r8   r  r/   ro   r  r0   r   r   r   r<   r   r!   r   roundr   r  r  r   r+   r   r`   )
ry  r  r/   ro   r0   Vr   r  r  r  r(   r(   r)   test_double_precision	  s>    
0"z%_TestSQLAlchemy.test_double_precisionc                   sj   ddl m  dd dd fdd fd	d
}tdg dijd| jddks\J || j d S )Nr   rO   c                 S  s   d}t j|| dS )Nz'SELECT test_foo_data FROM test_foo_datar  )r   r    )rA  r   r(   r(   r)   test_select	  s    zC_TestSQLAlchemy.test_connectable_issue_example.<locals>.test_selectc                 S  s   |j d| dd d S )Ntest_foo_datar   )r   r   r   )r   )rA  r   r(   r(   r)   test_append	  s    zC_TestSQLAlchemy.test_connectable_issue_example.<locals>.test_appendc                   s   | } | | d S r   r(   )r<   Zfoo_data)r  r  r(   r)   test_connectable
  s    zH_TestSQLAlchemy.test_connectable_issue_example.<locals>.test_connectablec              	     sp   t |  rd|  @}|  | W d    n1 s:0    Y  W d    ql1 sX0    Y  n|  d S r   )r`   ra   rb   )Zconnectabler<   )rP   r  r(   r)   main
  s
    


Fz<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr  r+  r  r0  )r^   rP   r   r   r<   )ry  r  r(   )rP   r  r  r  r)   test_connectable_issue_example	  s    	z._TestSQLAlchemy.test_connectable_issue_exampleinputr  bar)r  infe0c                 C  s   t |}| jdkrtd}t|jtdk rRd|jv rRtjjdd}|j	
| d}tjt|d" |jd	| jd
d W d    q1 s0    Y  n4|jd	| jd
ddksJ td	| j}t|| d S )Nr#   r   z1.0.3r
  zGH 36465r6  zinf cannot be used with MySQLr   ZfoobarFr6  r   )r   r  r   r   r   __version__r   r4  r  r  r  r  r	  r   r<   r   r!   r   Zassert_equal)ry  r  r   r   r   r4  r
  r  r(   r(   r)   test_to_sql_with_negative_npinf
  s    

2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	x}|
 P | }	|j|	 |||d |  tj||j|	d	}
W d    n1 s0    Y  W d    n1 s0    Y  t|
| d S )
Nr   )r.   ro   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_keyr  F)ZnullableN)r}  r~  r  __tablename__Z__table_args__idr  r(   r.   ro   r  r(   r)   	TemporaryG
  s   
r  )r  )r   r   )r8   r.   ro   r  r  sqlalchemy.ormr  r  r   r<   rb   rA  Z	__table__rd   addflushr   r    r  r   r7  )ry  r  r  r  Z	test_datarN  ZBaser  sessionr<   r   r(   r  r)   test_temporary_table7
  s    
Pz$_TestSQLAlchemy.test_temporary_tablec                 C  sB   d}t jt|d | |d W d    n1 s40    Y  d S )Nz*engine must be one of 'auto', 'sqlalchemy'r   Z
bad_engine)r   r  r	  r  r  r(   r(   r)   test_invalid_engineW
  s    z#_TestSQLAlchemy.test_invalid_enginec                 C  s:   t dd | | W d    n1 s,0    Y  d S )Nio.sql.enginer8   r   option_contextr  r  r(   r(   r)   test_options_sqlalchemy\
  s    z'_TestSQLAlchemy.test_options_sqlalchemyc                 C  s:   t dd | | W d    n1 s,0    Y  d S )Nr  r  r  r  r(   r(   r)   test_options_autoa
  s    z!_TestSQLAlchemy.test_options_autoc                 C  s   t tdtsJ tdd4 t tdts2J t tdtsDJ W d    n1 sX0    Y  tdd4 t tdtsJ t tdtsJ W d    n1 s0    Y  d S )Nr8   r  r  )r`   r   r   r   r   r  r(   r(   r)   test_options_get_enginef
  s    0z'_TestSQLAlchemy.test_options_get_enginec                 C  s   d S r   r(   r  r(   r(   r)   "test_get_engine_auto_error_messageq
  s    z2_TestSQLAlchemy.test_get_engine_auto_error_messagefuncr  r    c           	      C  s   d}|   }|j|| jddd td|, tt|d| | j|d}W d    n1 s^0    Y  | ||}t|| td|P tt|d| | j|dd	}| ||}|D ]}t|| qW d    n1 s0    Y  d S )
Nr   Fr   r   r   r   r   mode.string_storageSelect * from dtype_backendr0  )r   r*  r  	dtype_backend_datar   r<   r   r   ru  dtype_backend_expectedr   r7  	ry  string_storager%  r*  r&  r   rP  rN  iteratorr(   r(   r)   test_read_sql_dtype_backendw
  s&    $z+_TestSQLAlchemy.test_read_sql_dtype_backendr!   c           	      C  s   d}|   }|j|| jddd td|& tt||| j|d}W d    n1 sX0    Y  | ||}t|| td|J tt||| j|dd}| ||}|D ]}t|| qW d    n1 s0    Y  d S )	Nr   Fr   r&  r'  r)  r0  )r*  r  r+  r.  r(   r(   r)   !test_read_sql_dtype_backend_table
  s"    4z1_TestSQLAlchemy.test_read_sql_dtype_backend_table)r  r!   r    c                 C  sl   d}|   }|j|| jddd d}tjt|d& tt||| jdd W d    n1 s^0    Y  d S )	Nr   Fr   r&  zPdtype_backend numpy is invalid, only 'numpy_nullable' and 'pyarrow' are allowed.r   numpyr)  )r,  r   r<   r   r  r	  ru  r   )ry  r%  r&  r   r
  r(   r(   r)   )test_read_sql_invalid_dtype_backend_table
  s    z9_TestSQLAlchemy.test_read_sql_invalid_dtype_backend_tabler   rp  c              
   C  sb   t tdtjdgddtg dddtdtjdgddtg d	ddg d
g dg dg ddS )Nr   r0  rW  r   r/  r        @Float64r  r  r5  )TFNTFTr/  r   r5  Nr   r5  ra  defgh)r   r   r   r,  r  r(   r(   r)   r,  
  s    z"_TestSQLAlchemy.dtype_backend_datac              
     s  |dkr>t tjg dtjd}t tjddtjgtjd}n.tdtg d}tg d}t	t
dtjd	gd
dt
g dd
dt
dtjdgddt
g dddt
ddtjgddt
g ddd||d|dkrtdddlm  t	 fddjD S )Npythonr/  r   r   r5  r   r9  r   r0  rW  r/  r  r5  r6  r7  TFbooleanr8  r:  r   )ArrowExtensionArrayc                   s$   i | ]}| j | d dqS )T)Zfrom_pandas)r   )rU   r  rB  r   par(   r)   
<dictcomp>
  s   z:_TestSQLAlchemy.dtype_backend_expected.<locals>.<dictcomp>)r   r   r   Zobject_r   ZNAr   r   r   r   r   r,  Zpandas.arraysrB  r   )ry  storager*  Zstring_arrayZstring_array_nar(   rC  r)   r-  
  s4    


z&_TestSQLAlchemy.dtype_backend_expectedc                 C  sb   ddd}t ddgd|}| }|jd| jdd	d
 td| j|ddD ]}t|| qLd S )Nr   r  r4  r   r5  r   r   Fr   r&  zSELECT * FROM testr   )r9   r  )r   r   r'  r   r<   r    r   r7  )ry  r   r   rN  rP  r(   r(   r)   test_chunksize_empty_dtypes
  s    

z+_TestSQLAlchemy.test_chunksize_empty_dtypesr*  numpy_nullablec                 C  s   d}t g ddd}|j|| jddd tt|d| | jd	tji|d
}t tg dtjdtg d|dkstdnddd}t	|| d S )Nr   r/  r   r4  Fr   r&  r(  r   )r9   r*  r   )r   r   r   rH  r   rW  )
r   r   r<   ru  r   r   r  r   r   r7  )ry  r%  r*  r&  r   rP  rN  r(   r(   r)   test_read_sql_dtype
  s$    	z#_TestSQLAlchemy.test_read_sql_dtypeN)Br}  r~  r  r  r2  r  r;  r   r3  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  r  r  r  r  r  r  r4  r5  r   infr  r  r  r!  r"  r#  r$  r1  r2  r4  r,  r-  rG  r   Z
no_defaultrI  r(   r(   r(   r)   r    s   



	

O 
	%-"(
 


%r  c                      s   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d Zdd Zdd fddZejdddgdd Zdd Z  ZS )TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r"   c                 C  s   t d| _d S r9  r:  r  r(   r(   r)   r    s    zTestSQLiteAlchemy.setup_enginec                 C  s
   d | _ d S r   )driverr  r(   r(   r)   r    s    zTestSQLiteAlchemy.setup_driverc                 C  st   d}t ddddddddg}|d| j tjt|d$ |jd| jd dd	 W d    n1 sf0    Y  d S )
NzxStarting with pandas version 3.0 all arguments of to_sql except for the arguments 'name' and 'con' will be keyword-only.r   r  r0  r  Zexampler   r   r  )r   r   r<   r   r   rn  )ry  r
  r   r(   r(   r)   test_keyword_deprecation!  s    z*TestSQLiteAlchemy.test_keyword_deprecationc                 C  s   t d| j}t|jjjtjs$J t|j	jjtj
s:J t|jjjtj
sPJ t|jjjtjsfJ t|jjjtjs|J d S r,  )r   r!   r<   r   ru   r9   r   r   r   rv   r-  rw   rx   ry   r  r(   r(   r)   r.  -  s    z.TestSQLiteAlchemy.test_default_type_conversionc                 C  s(   t d| j}t|jjjtjr$J d S r,  r  r  r(   r(   r)   r  <  s    z(TestSQLiteAlchemy.test_default_date_loadc                 C  sj   t dddgidd}|jd| jdddks.J td  td| j W d    n1 s\0    Y  d S )	Nr   r   r  r   r   Ztest_bigintwarningFr6  )r   r   r<   r   r   r   r!   r  r(   r(   r)   test_bigint_warningB  s    z%TestSQLiteAlchemy.test_bigint_warningc                 C  s\   t ddgddgd}tjtdd$ |jd| jd	d
d W d    n1 sN0    Y  d S )Nr   r  r0  r  rL  rM  zEmpty table name specifiedr   rC  r   Fr   r  r  r(   r(   r)   test_valueerror_exceptionJ  s    z+TestSQLiteAlchemy.test_valueerror_exceptionc           	        s  ddl m mm ddlm}m} | }G  fddd|}| j  |j	
| j W d    n1 sp0    Y  || jd}| b}tddgdd	gd
}|jd| jddddksJ |  ||j|j}t|}W d    n1 s0    Y  t|jddgksJ d S )Nr   r.   ro   r1   )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  string_columnr(   rQ  r(   r)   Test`  s   rU  r\   r   ZhelloZworld)r  rT  r   Fr   r&  r  r  rT  )r8   r.   ro   r1   r  r  rR  r<   rb   r:   Z
create_allr   r   r  r   r  rT  r\  r   )	ry  r  rR  Z	BaseModelrU  r  r  r   Z
test_queryr(   rQ  r)   test_row_object_is_named_tupleO  s&    ,
&z0TestSQLiteAlchemy.test_row_object_is_named_tupler   rp  c                   s<   t  ||}|dkr(|ddd}n|ddd}|S NrH  rW  )r<  r=  zint64[pyarrow]superr-  r   ry  rF  r*  r   	__class__r(   r)   r-  v  s
    z(TestSQLiteAlchemy.dtype_backend_expectedr%  r  r!   c                 C  s   d S r   r(   )ry  r/  r%  r(   r(   r)   r2    s    z3TestSQLiteAlchemy.test_read_sql_dtype_backend_tablec                 C  s   t d d}tdddgi}|j|| jddd td	d
 t|| j}W d    n1 s`0    Y  d}tdddgi|tdg|dd}t	
|| d S )Nr   r   r   xyFr   )r   r   r   zfuture.infer_stringTzstring[pyarrow_numpy]r   )r9   r   )r   r   r   r   r<   r   r   r!   r   r   r7  )ry  r&  r   rP  r9   rN  r(   r(   r)   test_read_sql_string_inference  s    
*z0TestSQLiteAlchemy.test_read_sql_string_inference)r}  r~  r  r  r  r  r  r  rM  r.  r  rN  rP  rV  r-  r   r4  r5  r2  r_  __classcell__r(   r(   r[  r)   rK    s    

'	
rK  c                      sN   e Zd ZdZdZdZedd Zedd Zdd	 Z	d
d fddZ
  ZS )TestMySQLAlchemyzA
    Test the sqlalchemy backend against an MySQL database.

    r#   i  c                 C  s(   t jd| j d| j d| jd| _d S )Nzmysql+z://root@localhost:/pandas)r   )r8   r   rL  portr   r   r  r(   r(   r)   r    s    zTestMySQLAlchemy.setup_enginec                 C  s$   t d}d| _d|jjji| _d S )Nr   r   )r   r   rL  r   r   r   r   )r  r   r(   r(   r)   r    s    
zTestMySQLAlchemy.setup_driverc                 C  s   d S r   r(   r  r(   r(   r)   r.    s    z-TestMySQLAlchemy.test_default_type_conversionr   rp  c                   s<   t  ||}|dkr(|ddd}n|ddd}|S rW  rX  rZ  r[  r(   r)   r-    s
    z'TestMySQLAlchemy.dtype_backend_expected)r}  r~  r  r  r  rc  r  r  r  r.  r-  r`  r(   r(   r[  r)   ra    s   

ra  c                   @  s@   e Zd ZdZdZdZedd Zedd Z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| _d S )Nzpostgresql+z://postgres:postgres@localhost:rb  )r8   r   rL  rc  r   r  r(   r(   r)   r    s    z"TestPostgreSQLAlchemy.setup_enginec                 C  s   t d d| _d S )Nr   )r   r   rL  r  r(   r(   r)   r    s    
z"TestPostgreSQLAlchemy.setup_driverc                 C  s  ddl m} tddgddgddgd	}| j ( | jd
 | jd W d    n1 s`0    Y  |jd| jdddksJ |jd| jddddksJ |jd| jddddksJ 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 d    n1 sh0    Y  | j ( | jd
 | jd W d    n1 s0    Y  |jd| jddddksJ |jd| jdddd |jd| jdddddks
J tjd| jdd}t	
t||gdd| t| j|r|  }	tj|	dd}
|
j|ddddksrJ |
j|dddddksJ |
j|dddddksJ tjd| jdd}|
d}t	
|| d S ) Nr   rO   r   r  r2  r   r   rJ  rK  z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;Ztest_schema_publicFr6  Ztest_schema_public_explicitpublic)r   r   r   rF  Ztest_schema_otherother)rF  z!Table test_schema_other not foundr   )r   r   rF  r   r   )r   r   rF  r   r   r   Tr  Ztest_schema_other2r  )r   r   )r^   rP   r   r<   rb   r   r   r   r!   r   r7  r   r  r	  r   r`   ra   r   Z
read_table)ry  rP   r   r9  r:  r!  Zres4r
  r  Zengine2Zpdsqlr(   r(   r)   test_schema_support  s    *

2,


z)TestPostgreSQLAlchemy.test_schema_supportc           	   	   C  sD  ddl m} ddlm} |d}t| j|r| j B}|  || W d    n1 s`0    Y  W d    q1 s~0    Y  n6| j  | j| W d    n1 s0    Y  d}t	
|| j}tdtddd	gd
 gddgd
 d}t|| tj| jdd}|d W d    n1 s60    Y  d S )Nr   rO   rg   z
        CREATE TABLE person
        (
            id serial constraint person_pkey primary key,
            created_dt timestamp with time zone
        );

        INSERT INTO person
            VALUES (1, '2021-01-01T00:00:00Z');
        zHSELECT * FROM "person" AS p1 INNER JOIN "person" AS p2 ON p1.id = p2.id;r   Z2021r  r  r  r  Z
created_dtr   Tr   Zperson)r^   rP   r[  rh   r`   r<   ra   rb   rC   r   r  r   r   r   r7  r   r   r]  )	ry  rP   rh   ri  r   rG  rP  rN  r  r(   r(   r)   test_self_join_date_columns2  s(    
H*z1TestPostgreSQLAlchemy.test_self_join_date_columnsN)r}  r~  r  r  r  rc  r  r  r  rg  rh  r(   r(   r(   r)   rd    s   

crd  c                   @  s   e Zd ZdZ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 )(TestSQLiteFallbackzG
    Test the fallback mode against an in-memory sqlite database.

    r"   Tr  c                 C  s0   |   | _| | | | t| j| _d S r   )ra   r<   r  r  r   r   r  r  r(   r(   r)   r  e  s    


zTestSQLiteFallback.setup_methodc                 C  s   |  | d S r   r  r  r(   r(   r)   r  l  s    z*TestSQLiteFallback.test_read_sql_parameterc                 C  s   |  | d S r   r  r  r(   r(   r)   r  o  s    z0TestSQLiteFallback.test_read_sql_named_parameterc                 C  s   |  | d S r   r  r  r(   r(   r)   r  r  s    z$TestSQLiteFallback.test_to_sql_emptyc                 C  s\   t g dg dd}| j|ddks,J | jds<J | jd | jdrXJ d S )Nr  r  r  Zdrop_test_framer  )r   r  r   r   r]  )ry  r  r(   r(   r)   test_create_and_drop_tableu  s    z-TestSQLiteFallback.test_create_and_drop_tablec                 C  s   |  | d S r   r  r  r(   r(   r)   r    s    z!TestSQLiteFallback.test_roundtripc                 C  s   |    d S r   r  r  r(   r(   r)   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<J td	| j}| jd
krft||t	 n| jdkr|t|| d S )Nr  r   r  r   r   r  Fr6  zSELECT * FROM test_dater"   r#   )
r   r   r   r<   r    r  r   r7  r   r+   r-  r(   r(   r)   r    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sjJ td| j}| j	dkr|dd
 }t
|| d S )Nr  r   r   r  r  r  r  r  c                 S  s   |   S r   )timetz)r  r(   r(   r)   r    rZ   z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>r   r   r  Fr6  zSELECT * FROM test_timer"   c                 S  s
   |  dS r  r  r  r(   r(   r)   r    rZ   )r   r   r   Zto_pydatetimer  r   r   r<   r    r  r   r7  )ry  rk  Ztz_timesZtz_dtr   r  rN  r(   r(   r)   r    s    
z%TestSQLiteFallback.test_datetime_timec                 C  sP   t d| d| j}g }|jD ]*}t d| d| j}||j  q |S )NzASELECT * FROM sqlite_master WHERE type = 'index' AND tbl_name = ''zPRAGMA index_info())r   r    r<   r   r   r  )ry  rI  rJ  r  Zix_nameZix_infor(   r(   r)   r    s    
z%TestSQLiteFallback._get_index_columnsc                 C  s   |    d S r   r  r  r(   r(   r)   r    s    z)TestSQLiteFallback.test_to_sql_save_indexc                 C  s   |    d S r   r  r  r(   r(   r)   r    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(rn  zTable z	, column r|  )r<   rC   r	  )
ry  r&  r  ZrecsZcidr   ctypeZnot_nulldefaultpkr(   r(   r)   r    s
    
z*TestSQLiteFallback._get_sqlite_column_typec                 C  s   | j dkrtd ddg}ddg}t||d}|jd| jd	d
ksHJ |jd| jddidd
ksfJ | dddkszJ | dddksJ d}tjt|d& |jd| jdt	id W d    n1 s0    Y  |jd| jddd
ksJ | dddksJ | dddksJ d S )Nr#   Not applicable to MySQL legacyr   r   r  r  r   r  r  r  r  STRINGr  r  z!B \(<class 'bool'>\) not a stringr   r  r  )
r  r   r  r   r   r<   r  r  r	  bool)ry  r<  r   r   r
  r(   r(   r)   r    s    

4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|| jddksrJ | |ddksJ | |ddksJ | |ddksJ | |ddksJ d S )Nr#   rr  Tr  r   r   r  r   r  r  r  r  r  r  r  r  rK  r  r/   r-   )	r  r   r  r   r   r   r   r<   r  )ry  r<  r   r{  r(   r(   r)   r    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	  |jd
| jd W d    n1 sT0    Y  tg dD ]|\}}|j|| jddksJ t|| j t ddgddggd|gd}d|d}|j|| jddksJ t|| j qjd S )Nr   r  r0  r  r   r5  r   z$Empty table or column name specifiedr   rC  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_namer;  )	r   r   r  r	  r   r<   	enumerater   Ztable_exists)ry  r   r
  ZndxZ
weird_namer(  Zc_tblr(   r(   r)   test_illegal_names  s    .z%TestSQLiteFallback.test_illegal_namesN)r}  r~  r  r  r  r   r3  r  r  r  r  rj  r  r  r  r4  r5  r  r  r  r  r  r  r  rv  r(   r(   r(   r)   ri  ]  s(   


ri  z'{}'z{:.8f}z{:d}c                 C  s   dS )NZNULLr(   )r]  r(   r(   r)   r    rZ   r  z{:.10f}z'{!s}'c                 G  sJ   g }|D ]4}t |tr"t|r"d }tt| }||| q| t| S r   )r`   r   r   _formattersr   r   r  )r   rr  Zprocessed_argsarg	formatterr(   r(   r)   format_query  s    rz  c                 C  sL   t |}||  }W d   n1 s.0    Y  |du rDdS t|S )z#Replace removed sql.tquery functionN)r   r   rC   r  r\  )r   r   r  r  r(   r(   r)   tquery$  s    ,r{  c                   @  s\   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S )TestXSQLitec                 C  s*   |  }|dt|  |  d S r  )rB   rC   r   r  r  )ry  r   r<   rI   r(   r(   r)   r]  ,  s    zTestXSQLite.drop_tablec                 C  s   t  }tj|d|dddks"J td|}|j|_|}t || dgt| |d< | }t	t
t|t
jdd	 }| |d
< tj|d|dddksJ tjd|d
d}| }||_d
|j_t || d S )Nr0  Fr6  r  zselect * from test_tabler   txtr   r  ZIdxZtest_table2zselect * from test_table2r  )r   makeTimeDataFramer   r   r  r   r7  r   r'  r   r   r  r   r   )ry  r   r   rP  rN  Zframe2Znew_idxr(   r(   r)   
test_basic1  s,    zTestXSQLite.test_basicc           
      C  s   t  }tj|jd< t|d}| }|| d}|	 D ]$\}}t
|g|R  }t||d q>|  tjd|d}	|j|	_t j|	|dd d S )N)r   r   r   z(INSERT INTO test VALUES (%s, %s, %s, %s)r  select * from testgMbP?)Zrtol)r   r~  r   r,  r   r   r  rB   rC   Ziterrowsrz  r{  r  r  r   r7  )
ry  r   r   r^  rI   insr  rV   Zfmt_sqlrP  r(   r(   r)   test_write_row_by_rowN  s    
z!TestXSQLite.test_write_row_by_rowc           	      C  s   t  }t|d}| }|| d}|jd }t| }||t| W d    n1 sd0    Y  |	  t
d|}|jd d |_t ||d d  d S )Nr   z$INSERT INTO test VALUES (?, ?, ?, ?)r   r  r   )r   r~  r   r  rB   rC   r   r   r  r  r  r   r7  )	ry  r   r   r^  rI   r  rV   r  rP  r(   r(   r)   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 J q tj|dddgd	}| }d
|v sJ | }|| d S )Nr    r  r   r   r   ZDATETIMEr   r%  zPRIMARY KEY ("A", "B"))	r   r~  r   r  
splitlinesr}  r   rB   rC   )ry  r   r   r^  lineslinetokensrI   r(   r(   r)   test_schemap  s    
zTestXSQLite.test_schemac              	   C  s   d}|  }|| t|^}|d |d tjtjdd |d W d    n1 sf0    Y  W d    n1 s0    Y  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))rB   rC   r   r   r   r  ry  )ry  r   r^  rI   r  r(   r(   r)   test_execute_fail  s    	


zTestXSQLite.test_execute_failc              	   C  s   d}t tdV}| }|| t|}|d W d    n1 sR0    Y  W d    n1 sp0    Y  d}tj	tj
|d td|d W d    n1 s0    Y  d S )Nr  r   r  z$Cannot operate on a closed database.r   r  r  )r   r   ri   ra   rB   rC   r   r   r   r  ZProgrammingErrorr{  )ry  r^  r<   rI   r  r
  r(   r(   r)   test_execute_closed_connection  s    	
Fz*TestXSQLite.test_execute_closed_connectionc                 C  s0   t dtdi}tj||ddddks,J d S )NFromr   ZtestkeywordsFr   r   r   )r   r   Zonesr   r   )ry  r   r   r(   r(   r)   test_keyword_as_column_names  s    z(TestXSQLite.test_keyword_as_column_namesc                 C  sp   t ddgdgd}tj||ddddks,J |}tdd	 |d
D }|dksTJ td
|}t|| d S )Nr   r  Zc0r   mono_dfFr  c                 s  s   | ]}|d  V  qdS )r   Nr(   )rU   Zmy_c0r(   r(   r)   	<genexpr>  rZ   z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr0  )r   r   r   sumrC   r  r   r7  )ry  r   r  Zcon_xZthe_sumrP  r(   r(   r)   test_onecolumn_of_integer  s    z%TestXSQLite.test_onecolumn_of_integerc                 C  s  t ddgddgd}t g dg dd}d}d	| }d
}tjt|d" tj|||dd W d    n1 st0    Y  | || tj|||dd d}tjt|d" tj|||dd W d    n1 s0    Y  tj|||ddd t||dddgksJ tj|||ddddks,J t||dg dksFJ | || tj|||ddddkspJ t||dddgksJ tj|||ddddksJ t||dg dksJ | || d S )Nr   r  r   r   rO  rh  )r   r   EZtable_if_existszSELECT * FROM z*'notvalidvalue' is not valid for if_existsr   Znotvalidvalue)r   r   r   r   r  z&Table 'table_if_exists' already existsr   F)r   r   r   r   r   r  r  r  r0  )r0  r   r  r   r   r  r   )r  r  r  r  r  )r   r   r  r	  r   r   r]  r{  )ry  r   Zdf_if_exists_1Zdf_if_exists_2r   Z
sql_selectr
  r(   r(   r)   test_if_exists  s    
$$


zTestXSQLite.test_if_existsN)r}  r~  r  r]  r  r  r  r  r  r  r  r  r  r(   r(   r(   r)   r|  +  s   r|  )N)r  
__future__r   r   r   rE   r   r   r   r   r   r   pathlibr
   ri   rd  r3  r   r   Zpandas._libsr   Zpandas.util._test_decoratorsutilZ_test_decoratorsrk  Zpandasr   r   r   r   r   r   r   r   r   r   r   r   Zpandas._testingZ_testingr   Zpandas.core.arraysr   r   Zpandas.util.versionr   Z	pandas.ior   Zpandas.io.sqlr   r   r   r   r   r    r!   r8   r  r_  r3  r*   r;   rL   rf   rk   r|   r~   r   r   r   r   r}   r   r   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_irisr4  r  r5  r   r   r   r  r  r  r  r  r  r!  r#  r$  r+  r.  r;  rQ  r`  rb  rh  rj  ro  rp  rq  r  r  r  r  r  r8  rl  r  rK  ra  rd  ri  r  r+   Zstr_bytesr   r   r   r  rt  rw  rz  r{  r|  r(   r(   r(   r)   <module>   sz  4$


!



#















		$%94#

"    U LX      W # # 0
