o
    Ti!                    @  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:y   dZ9Y nw ej;dd Z<dddZ=dddZ>dddZ?dd Z@dd d!ZAdd$d%ZBdd&d'ZCdd*d+ZDdd-d.ZEej;d/d0 ZFej;d1d" ZGej;d2d3 ZHej;d4d5 ZIej;d6d7 ZJej;d8d9 ZKej;d:d; ZLej;d<d= ZMej;d>d? ZNej;d@dA ZOej;dBdC ZPej;dDdE ZQej;dFdG ZRej;dHdI ZSej;dJdK ZTej;dLdM ZUej;dNdO ZVd9d;gZWd=d?gZXg dPZYg dQZZeWeX eY Z[eWeX eZ Z\e[dMg Z]e\dOg Z^ej_j`ej_ade]dRdS Zbej_j`ej_ade]dTdU Zcej_j`ej_ade]dVdW Zdej_j`ej_ade]ej_adXddYgdZd[ Zeej_j`ej_ade]ej_ad\d]d^gd_d` Zfej_j`ej_ade]dadb Zgej_j`ej_ade^dcdd Zhej_j`ej_ade^dedf Ziej_j`ej_ade\dgdh Zjej_j`ej_ade^didj Zkej_j`ej_ade\dkdl Zlej_j`ej_ade\dmdn Zmej_j`ej_ade[dodp Znej_j`ej_adeWdqdr Zoej_j`ej_adeWdsdt Zpej_j`ej_adeXej_adudvdwgdxdy Zqej_j`ej_adeXdzd{ Zrej_j`ej_adeWd|d} Zsej_j`ej_adeXd~d 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(   X/var/www/html/evchargy.com/venv/lib/python3.10/site-packages/pandas/tests/io/test_sql.pysql_stringsP   s   r*   dialectstrc           
      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;   connsqlite3.Connection	iris_filer
   c                 C  sl   |   }d}|| |jd dd}t|}t| d}||| W d    d S 1 s/w   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csvfilerG   r(   r(   r)   create_and_load_iris_sqlite3   s   

"rM   c           
   
     sr  ddl m} ddlm} t|}|jd dd}t|}t|  fdd|D }||	|}	t
| |rq|  .} |   |j| dd	 |j| d
 | |	 W d    n1 s\w   Y  W d    n1 skw   Y  n.|   |j| dd	 |j| d
 | |	 W d    n1 sw   Y  W d    d S W d    d S W d    d S 1 sw   Y  d S )Nr   insertEnginer?   r@   c                      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   rO   sqlalchemy.enginerQ   r;   rE   rF   rG   rH   values
isinstanceconnectbegindropcreaterD   )
r<   r>   r+   rO   rQ   r3   rL   rG   paramsrK   r(   rY   r)   create_and_load_iris   s8   




 "rh   c              	   C  s   d}t | tjr|  }|| d S ddlm} ddlm} ||}t | |r`| 	 *} | 
  | | W d    n1 sAw   Y  W d    d S W d    d S 1 sYw   Y  d S | 
  | | W d    d S 1 suw   Y  d S )Nz+CREATE VIEW iris_view AS SELECT * FROM irisr   textrP   )rb   sqlite3
ConnectionrC   rD   r8   rj   r`   rQ   rc   rd   )r<   rK   rJ   rj   rQ   r(   r(   r)   create_and_load_iris_view   s$   


"
"rm   c                 C  s   ddl m}m}m}m}m}m}m}m} | dkr|n|}	| dkr"|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   rn   ro   r.   rp   r/   rq   r0   r2   append_column)r+   rn   ro   r.   rp   r/   rq   r0   r2   Z	date_typeZ	bool_typer:   rr   r(   r(   r)   types_table_metadata   s(   (r   
types_data
list[dict]c                 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(?, ?, ?, ?, ?, ?, ?, ?, ?)
            )rC   rD   rI   )r<   r   rJ   rK   r(   r(   r)   create_and_load_types_sqlite3   s
   
r   c              	   C  s  ddl m} ddlm} t|}|||}t| |ra|  7} |   |j	| dd |j
| d | | W d    n1 sBw   Y  W d    d S W d    d S 1 sZw   Y  d S |   |j	| dd |j
| d | | W d    d S 1 sw   Y  d S )Nr   rN   rP   Tr]   r^   )r8   rO   r`   rQ   r   ra   rb   rc   rd   re   rf   rD   )r<   r   r+   rO   rQ   rr   rK   r(   r(   r)   create_and_load_types   s&   


"
"r   framer   c                 C  sL   | j jd j}| jd }t|tjsJ t|jg d | j	dv s$J d S )Nr   ffffff@g      @gffffff?皙?Iris-setosa))      )   r   )
dtypesiloctype
issubclassnpfloatingtmequalContentsra   shape)r   ZpytyperX   r(   r(   r)   check_iris_frame  s
   
r   
table_namec                 C  s  d| }t | tjr|  }|| d S ddlm} ddlm	} t | t
rZz+|| }| } | | W  d    W |  S 1 sIw   Y  W |  d S |  w t | |r||  } | | W  d    S 1 suw   Y  d S | | S )Nz SELECT count(*) AS count_1 FROM r   )create_enginerP   )rb   rk   rl   rC   rD   fetchoner8   r   r`   rQ   r,   rc   exec_driver_sqlZ
scalar_onedispose)r<   r   rK   rJ   r   rQ   enginer(   r(   r)   
count_rows  s(   





$r   c                 C  s   | dddd}t |S )NiodatarF   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)
rs   rt   ru   rv   rw   rx   ry   rz   r{   r|   2000-01-04 00:00:00i 'Pi\2z2000-06-01 00:00:00-07:00r(   r(   r(   r(   r)   r   1  s.   c              
   C  s2   dddddddddd	}t | }||  |S )Nr,   int64float)	rs   rt   ru   rv   rw   rx   ry   rz   r{   )r   keysastype)r   r   dfr(   r(   r)   types_data_frameO  s   r   c                  C     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  r   )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s*t
|| d |	ds?|D ]}|d	 q1t||d |V  | &}|  |d
}|| W d    n1 s`w   Y  W d    n1 sow   Y  |  d S )Nr8   pymysqlz*mysql+pymysql://root@localhost:3306/pandasclient_flag)connect_args	poolclassr3   r#   rr   r|    DROP TABLE IF EXISTS test_frame;)pytestimportorskipr   	constantsCLIENTMULTI_STATEMENTSpoolNullPoolinspect	has_tablerh   popr   rc   rd   rj   rD   r   )	r   r   r8   r   r   inspentryr<   rK   r(   r(   r)   mysql_pymysql_engine  s0   







r   c                 c  6    |   }|V  W d    d S 1 sw   Y  d S Nrc   )r   r<   r(   r(   r)   mysql_pymysql_conn     
"r   c              	   c  s    t d}t d |jd|jjd}||}|ds$t|| d |ds/t||d |V  |	 &}|
  |d}|| W d    n1 sPw   Y  W d    n1 s_w   Y  |  d S )	Nr8   psycopg2z=postgresql+psycopg2://postgres:postgres@localhost:5432/pandasr   r3   r$   rr   r   )r   r   r   r   r   r   r   rh   r   rc   rd   rj   rD   r   )r   r   r8   r   r   r<   rK   r(   r(   r)   postgresql_psycopg2_engine  s*   







r   c                 c  r   r   r   )r   r<   r(   r(   r)   postgresql_psycopg2_conn  r   r   c                  c  sD    t d t } d|  V  W d    d S 1 sw   Y  d S )Nr8   
sqlite:///)r   r   r   ensure_cleannamer(   r(   r)   
sqlite_str  s
   

"r   c                 c  s0    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  r   r   r   )r   r<   r(   r(   r)   sqlite_conn  r   r   c                 C  s,   t d}|| }t||d |  | S )Nr8   r"   )r   r   r   rh   r   )r   r   r8   r   r(   r(   r)   sqlite_iris_str  s
   

r   c                 C  s   t | |d | S )Nr"   )rh   )r   r   r(   r(   r)   sqlite_iris_engine  s   r   c                 c  r   r   r   )r   r<   r(   r(   r)   sqlite_iris_conn  r   r   c               	   c  sr    t td&} | }|V  W d    n1 sw   Y  W d    d S W d    d S 1 s2w   Y  d S N:memory:)
contextlibr   rk   rc   )Zclosing_connr<   r(   r(   r)   sqlite_buildin  s   "r   c                 C  s   t | | | S r   )rM   )r   r   r(   r(   r)   sqlite_buildin_iris  s   
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    d S 1 sXw   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   methodmultic                 C  sn   | | } t| dd}|j|d|d |dsJ W d    n1 s%w   Y  t| dt|ks5J 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s#J W d    n1 s-w   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A}|j|ddd |dsJ d}tjt|d |j|ddd W d    n1 s7w   Y  W d    d S W d    d S 1 sOw   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s%J d|jv s,J 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s1J d|jv s8J 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rA|
  d S d S )	Nr   )r0   r2   r   selectr3   )Zautoload_withr   r   r   lengthrg   )r   r8   r0   r2   r   r  rb   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 n	q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s#J W d    n1 s-w   Y   dgks9J t| dt|ksDJ d S )Nc                   s2    d  fdd|D }|| j | d S )Nr   c                   rR   r(   rS   rV   r   r(   r)   r[     r\   z8test_to_sql_callable.<locals>.sample.<locals>.<listcomp>)r   rD   tablerO   )Zpd_tabler<   r   	data_iterr   checkr,  r)   sample  s   
z$test_to_sql_callable.<locals>.sampleTr  r  r  r   r  )r<   r   r   r1  r  r(   r/  r)   test_to_sql_callable  s   
r2  c                 C  s   | | } td| }t|jjjtjsJ t|j	jjtj
s!J t|jjjtj
s,J t|jjjtjs7J t|jjjtjsBJ d S Nrr   )r   r   r!   r   rw   r9   r   r   r   rx   integerry   rz   r{   r   r(   r(   r)   test_default_type_conversion  s   
r5  c           	   	   C  s   | | } ddlm} ddlm} tg dg dd}|jd| dd	 d
}||}t| |r]|  !}|	  |
| W d    n1 sHw   Y  W d    n1 sWw   Y  n| 	  | 
| W d    n1 sqw   Y  td| }t|| td| }t|| d S )Nr   ri   rP   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   rj   r`   rQ   r   r   rb   rc   rd   rD   r   r    r   assert_frame_equalr  )	r<   r   rj   rQ   r   procZengine_connres1res2r(   r(   r)   test_read_procedure  s,   




rB  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 r-|d u s,J n| ks3J td
| }t|| d S )Nc                   s   |j }| E}t }t|}|| |d ddd |D }| jr1| j d| j	 }	n| j	}	d|	 d| d}
|j
|
|d	 W d     S 1 sOw   Y   S )
Nr   z, c                 S  s   g | ]}d | d qS )"r(   )rW   kr(   r(   r)   r[   !      zVtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<listcomp>.zCOPY z (z) FROM STDIN WITH CSV)r   file)
connectionrC   r   rF   writer	writerowsseekjoinschemar   Zcopy_expert)r-  r<   r   r.  Z
dbapi_connrJ   Zs_bufrJ  r   r   	sql_queryrC  r(   r)   psql_insert_copy  s    




zBtest_copy_from_callable_insertion_method.<locals>.psql_insert_copyr   r
  r9  r   r   ncol1col2Zcol3r  F)r   r   r   r  )r   r   r   r   r!   r   r>  )r<   rC  r   rQ  expectedresult_countresultr(   rP  r)   (test_copy_from_callable_insertion_method  s   
rY  c              	     s  | | } ddlm  ddlm} ddlm}  fdd}|d}t| |rT|  !}|	  |
| W d    n1 s?w   Y  W d    n1 sNw   Y  n| 	  | 
| W d    n1 shw   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sJ tj| dd}|d W d    d S 1 sw   Y  d S )Nr   rN   rP   ri   c                   s<    fdd|D }| j |jdgd}||}|jS )Nc                   rR   r(   rS   rV   r,  r(   r)   r[   D  r\   z\test_insertion_method_on_conflict_do_nothing.<locals>.insert_on_conflict.<locals>.<listcomp>r   )Zindex_elements)r-  ra   Zon_conflict_do_nothingrD   rowcountr-  r<   r   r.  r   rK   rX  rN   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	@r<  r   r   r   r   r  Tr  )r   Zsqlalchemy.dialects.postgresqlrO   r`   rQ   sqlalchemy.sqlrj   rb   rc   rd   rD   r   listr   r   r!   r   r>  r   
drop_table)r<   r   rQ   rj   r\  
create_sqlr   rV  Z	df_insertinsertedrX  r  r(   rN   r)   ,test_insertion_method_on_conflict_do_nothing9  sJ   


	


"rh  c              	     s  | | } ddlm  ddlm} ddlm}  fdd}|d}t| |rT|  !}|	  |
| W d    n1 s?w   Y  W d    n1 sNw   Y  n| 	  | 
| W d    n1 shw   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sJ tj| dd}|d W d    d S 1 sw   Y  d S )Nr   rN   rP   ri   c                   sH    fdd|D }| j |}|j|jj|jjd}||}|jS )Nc                   rR   r(   rS   rV   r,  r(   r)   r[     r\   zXtest_insertion_method_on_conflict_update.<locals>.insert_on_conflict.<locals>.<listcomp>)r<  c)r-  ra   Zon_duplicate_key_updaterg  r<  ri  rD   rZ  r[  rN   r,  r)   r\  ~  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)
    );
    r]  r_  r   r`  r   Fr   ra  rb  r
  Tr  )r   Zsqlalchemy.dialects.mysqlrO   r`   rQ   rc  rj   rb   rc   rd   rD   r   rd  r   r   r!   r   r>  r   re  )r<   r   rQ   rj   r\  rf  r   r   rV  rg  rX  r  r(   rN   r)   (test_insertion_method_on_conflict_updatet  sF   

	


"rj  c           
   
   C  s&  | | } ddlm} ddlm} dt j }dt j }|d| d| d| d	| d
	}t| |re| 	 !}|
  || W d    n1 sPw   Y  W d    n1 s_w   Y  n| 
  | | W d    n1 syw   Y  t|| }tdgdd}	t||	 d S )Nr   rP   ri   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`   rQ   rc  rj   uuiduuid4hexrb   rc   rd   rD   r!   r   r   r>  )
r<   r   rQ   rj   r   	view_nameZsql_stmtr   rX  rV  r(   r(   r)   test_read_view_postgres  s<   
	




rp  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   rk  )rD   r   r  r   r   r>  )r   create_tableZinsert_intoZcreate_viewrX  rV  r(   r(   r)   test_read_view_sqlite  s   


rr  c              	   C  s~   t jtdd. tjtdd td|  W d    n1 s w   Y  W d    d S W d    d S 1 s8w   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   rD   )r   r(   r(   r)   test_execute_typeerror  s   "rw  c                 C  s@   t jtdd td|  W d    d S 1 sw   Y  d S )Nrs  r   rt  )r   r   rv  r   rD   )r   r(   r(   r)   test_execute_deprecated  s   "rx  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 d S w |% | |D ]}| 	|| q(| 
|D ]}| || q6W d    d S 1 sJw   Y  d S )Nr<   )hasattrr<   closerc   r8   excOperationalErrorrk   _get_all_views	drop_view_get_all_tablesre  )selfr<   viewtblr(   r(   r)   teardown_method  s   

"zMixInBase.teardown_methodN)__name__
__module____qualname__r  r(   r(   r(   r)   ry    s    ry  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   rk   rc   r  r(   r(   r)   rc        
zSQLiteMixIn.connectc                 C  "   | dt|  |  d S NzDROP TABLE IF EXISTS rD   r   _get_valid_sqlite_namecommitr  r   r<   r(   r(   r)   re       zSQLiteMixIn.drop_tablec                 C     | d}dd | D S )Nz1SELECT name FROM sqlite_master WHERE type='table'c                 S     g | ]}|d  qS r   r(   )rW   r-  r(   r(   r)   r[         z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>rD   fetchallr  r<   ri  r(   r(   r)   r       
zSQLiteMixIn._get_all_tablesc                 C  r  NzDROP VIEW IF EXISTS r  )r  ro  r<   r(   r(   r)   r    r  zSQLiteMixIn.drop_viewc                 C  r  )Nz0SELECT name FROM sqlite_master WHERE type='view'c                 S  r  r  r(   )rW   r  r(   r(   r)   r[      r  z.SQLiteMixIn._get_all_views.<locals>.<listcomp>r  r  r(   r(   r)   r~    r  zSQLiteMixIn._get_all_viewsN)r  r  r  rc   re  r  r  r~  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     | j   d S r   )r   r   clsr(   r(   r)   teardown_class$  s   zSQLAlchemyMixIn.teardown_classc                 C  s
   | j  S r   )r   rc   r  r(   r(   r)   rc   (  r  zSQLAlchemyMixIn.connectc                 C  sR   |  r
|   |  t|| W d    d S 1 s"w   Y  d S r   )in_transactionget_transactionrollbackrd   r   r   re  r  r(   r(   r)   re  +  s
   
"zSQLAlchemyMixIn.drop_tablec                 C     ddl m} || S Nr   r   )r8   r   Zget_table_namesr  r<   r   r(   r(   r)   r  1     zSQLAlchemyMixIn._get_all_tablesc                 C  sb   |j jj|}| r|   |  |d|  W d    d S 1 s*w   Y  d S r  )	r   r+   Zidentifier_preparerZquote_identifierr  r  r  rd   r   )r  ro  r<   Zquoted_viewr(   r(   r)   r  6  s   

"zSQLAlchemyMixIn.drop_viewc                 C  r  r  )r8   r   Zget_view_namesr  r(   r(   r)   r~  ?  r  zSQLAlchemyMixIn._get_all_viewsN)
r  r  r  classmethodr  rc   re  r  r  r~  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| d S t| j|| j d S r)  )re  r<   rb   rk   rl   rM   rh   flavor)r  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r%dd |D }t| j| d S t| j|| j  d S )Nr$   r|   c                 S  s   g | ]}t | qS r(   )tuplera   )rW   r   r(   r(   r)   r[   W  rF  z1PandasSQLTest.load_types_data.<locals>.<listcomp>)r  r   rb   r<   rk   rl   r   r   )r  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   r  r*   r'  rg   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  )r  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   )re  r<   r  r   r   r  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sJ | jds J t|}t| jd}||ks0J |  d| j dS )z `to_sql` with the `engine` paramr   r      N)re  r<   r  r   r   r  r   )r  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)
re  r<   r  r   r  	set_indexr   r   r   r>  r  r   rX  r(   r(   r)   
_roundtrip  s   zPandasSQLTest._roundtripc                 C  s(   | j d}| }t|g d d S Nr  r   )r  rD   r   r   r   )r  iris_resultsrX   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sJ | d}|dggks%J d S )Nr   r^  line1r
        ?line2r   r   r   r   r   r   Ztest_to_sql_saves_indexr
  )r   from_recordsr  r   _get_index_columns)r  r   ix_colsr(   r(   r)   _to_sql_save_index  s   
z PandasSQLTest._to_sql_save_indexc                 C  sN  | j  %}d}t| j tr|| nddlm} ||}|| W d    n1 s-w   Y  G dd dt}d}t| j trLddlm} ||}z| j  
}|| |d1 s_w   Y  W n	 |yn   Y nw | j 	d}t
|dks}J | j  }|| W d    n1 sw   Y  | j 	d}t
|d	ksJ d S )
Nz'CREATE TABLE test_trans (A INT, B TEXT)r   ri   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_transactionrb   r   rD   r8   rj   	Exceptionr   r  r  )r  ZtransrK   rj   r  Zins_sqlresrA  r(   r(   r)   _transaction_test  s:   


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   )rc   r<   r  r  load_test_data_and_sqlr  r   r   r(   r(   r)   setup_method  s   


z_TestSQLApi.setup_methodc                 C  s   t | j d S r   )rm   r<   r  r(   r(   r)   r       z"_TestSQLApi.load_test_data_and_sqlc                 C  s   t d| j}t| d S )NzSELECT * FROM iris_view)r   r    r<   r   r  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   r>  r   )r  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  sr   t j|d| jdd t d| jsJ d}tjt|d t j|d| jdd W d    d S 1 s2w   Y  d S )Ntest_frame2r  r  z"Table 'test_frame2' already existsr   )r   r   r<   r   r   r  r  r  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s-J d S )Nr   r  r  r   r   r   r<   r   r  r   r  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sJ t d| js%J dt| }t| jd}||ks7J 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   r>  )r  r   rX  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   r>  to_frame)r  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   r>  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   r>  r  r(   r(   r)   test_roundtrip_chunksize,  s   z$_TestSQLApi.test_roundtrip_chunksizec                 C  sR   t | j}|d}W d    n1 sw   Y  | }t|g d d S r  )r   r   r<   rD   r   r   r   )r  
pandas_sqlr  rX   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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sVJ |j	 t
ddddddt
ddddddgksoJ t jd| jd
g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
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did}t|jjjtjsJ |j	 t
dt
dgksJ d S )NSELECT * FROM typesrt   parse_datesi  r   r7  r   r  %Y-%m-%d %H:%M:%Sru   i        i  r  rv   z%Y%m%dz
2010-10-10z
2010-12-12)r   r    r<   r   rt   r9   r   r   
datetime64tolistr   ru   rv   r  r   r(   r(   r)   test_date_parsing>  s\   





z_TestSQLApi.test_date_parsingr  )ignoreraisecoercezread_sql, text, moder  )r8   fallbackrr   r8   c                 C  sD   | j |v r |ddi}||| jdd|iid}t|| d S d S )Nrt   datetime64[ns]errors)r   r  )r  r   r<   r   r>  )r  r  rj   r  r  r   rV  rX  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s"J d S )Nr  rt   ru   )	index_colr  )
r   r    r<   r   r   r9   r   r   r  ru   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 s$w   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_equalr  )r  r   rW  rX  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    d S 1 s)w   Y  d S )Nr   y      ?      ?y               @zComplex datatypes not supportedr   Ztest_complexr  r   r   r  r  r   r<   )r  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s(J d S )NrT  r  SELECT * FROM test_index_labeltest_index_label)index_labelr   )	r   ranger   r   r   r   r<   r    r   )r  r  r  rV  
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s8J tj|d| jdddgd}||ksKJ td| j}|jd d 	 ddgksaJ ddg|j
_tj|d| jdd}||kswJ td| j}|jd d 	 ddgksJ tj|d| jdddgd}||ksJ td| j}|jd d 	 ddgksJ d}tjt|d tj|d| jddd W d    d S 1 sw   Y  d S )Nr  rT  )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  )r  Zexpected_row_countr  rX  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   r>  r  r   rX  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sJ ||}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   r>  )r  r9   r   rV  rX  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
  r7  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<   r  r   rf  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   rN  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 s-J d|v s3J d S )Nr8   r   )rq   INTEGER皙?r  r^  皙@r;  r   r<  r"  r$  )r  r8   rq   r   r   r&  r<   )r  rq   r9   Zfloat_framerf  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 s1J d S )Nr,  r  r^  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<   )r  r   r   rf  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 ]}t
||gdd}t||| ksEJ |d7 }q1t|| | jdkrt  }d	}g d
}tjd| jddD ]}t
||gdd}t||| ks{J |d7 }qgt|| d S d S )Nr
  )   r   Zabcder   Ztest_chunksizeFr=  zselect * from test_chunksizer   )r   r   r   r   r
  r   r  TZignore_indexr   r8   )r   r   randomZdefault_rngZstandard_normalrd  r   r<   r   r    r   r  r   r>  r  r!   )r  r   r@  rA  isizeschunkres3r(   r(   r)   test_chunksize_readJ  s2   



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 )
Nr6  )John P. Doez	Jane Dover9  )Z	person_idperson_namer:  categorytest_categoricalFr=  zSELECT * FROM test_categorical)	r   copyr   r   r<   r   r    r   r>  )r  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
  r7  r     ér<  r   Ztest_unicodeFr=  )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-a7f6Fr=  z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r   nanr   r<   r   r    r   r>  r  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 )Nr6  r8  r   r   r<  ri  
test_tableFr=  z*SELECT a, b, a +1 as a, c FROM test_table;)r   r9  r
  r   )r
  r   r7  r   )r7  r:  r  r   )r   r<  r   ri  r   )r   r   r<   r   r  r   r>  )r  r   rX  rV  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.  r0  r8  r<  r@  rD  rG  r(   r(   r(   r)   r    sz   
 

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rId$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     t d| _d S Nzsqlite:///:memory:r8   r   r   r  r(   r(   r)   setup_class     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  )r  r   colsrX  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sJ 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sGJ |j ddgksRJ 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   r>  r!   )r  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 ]R}t| j|rM| j !}|  || W d    n1 s8w   Y  W d    n1 sGw   Y  q| j  | j| W d    n1 scw   Y  qt	
d  td| j td| j W d    d S 1 sw   Y  d S )Nr   ri   rP   z,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);Zother_tablezSELECT * FROM other_table)r8   rj   r`   rQ   rb   r<   rc   rd   rD   r   r   r   r!   r    )r  rj   rQ   Z
query_listr'  r<   r(   r(   r)   test_not_reflect_all_tables  s,   
"z&TestSQLApi.test_not_reflect_all_tablesc                 C  s   t jtdd t| jdd W d    n1 sw   Y  t d  |jd| jd W d    d S 1 s:w   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   "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  r  Zcolumn_namesr(   rW   r4  r(   r(   r)   r[     r  z1TestSQLApi._get_index_columns.<locals>.<listcomp>)r`   r]  Z	InspectorZfrom_enginer<   get_indexes)r  tbl_namer]  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|s,J d S )
Nr   )	TIMESTAMPr   2014-12-12 01:542014-12-11 02:54Tutc	test_typer   )r8   rc  r   r   r   r   r<   SQLTablerb   r-  ri  r   )r  rc  r   r&  r-  r(   r(   r)   test_sqlalchemy_type_mapping  s   z'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)Zint8SMALLINT)ZInt8rl  )Zuint8rl  )ZUInt8rl  )Zint16rl  )ZInt16rl  )Zuint16r+  )ZUInt16r+  )int32r+  )ZInt32r+  )Zuint32BIGINT)ZUInt32rn  )r   rn  )Int64rn  r   rn  r+  c                 C  sP   t ddgdg|d}t| j}tjd||d}t|jjjj	}||ks&J d S )Nr   r   r   r   r9   rh  ri  )
r   r   r   r<   rj  r,   r-  ri  r   r   )r  r4  rV  r   r&  r-  rX  r(   r(   r)   test_sqlalchemy_integer_mapping  s
   z*TestSQLApi.test_sqlalchemy_integer_mappingr4  Zuint64ZUInt64c                 C  sd   t ddgdg|d}t| j}tjtdd tjd||d W d    d S 1 s+w   Y  d S )	Nr   r   r   rp  z1Unsigned 64 bit integer datatype is not supportedr   rh  ri  )r   r   r   r<   r   r  r  rj  )r  r4  r   r&  r(   r(   r)   (test_sqlalchemy_integer_overload_mapping#  s   "z3TestSQLApi.test_sqlalchemy_integer_overload_mappingc           	      C  s   t  +}d| }d}|j||ddd t||}t||}d}t||}W d    n1 s2w   Y  t || t || t || d S )Nr   r3   r   Fr   r  )r   r   r   r   r  r!   r    r>  )	r  r   r   db_urir-  r  r   r'  r  r(   r(   r)   test_database_uri_string-  s   
z#TestSQLApi.test_database_uri_stringpg8000c                 C  sD   d}t jtdd td| W d    d S 1 sw   Y  d S )Nz)postgresql+pg8000://user:pass@host/dbnameru  r   zselect * from tabler   r  ImportErrorr   r  )r  rs  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s"J d S )Nr   ri   z#select * from iris where name=:namer   zIris-versicolorr!  r6   )r8   rj   r   r  r<   set)r  rj   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s2J d S )Nr   )	bindparamr  r   r   r!  r6   )r8   r}  r  r;   r  whereri  r6   r   r  r<   ry  )r  r}  r  r3   Zname_selectrz  r{  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 )NrA  r7  r  r   )r   z%_variationZtest_column_percentageFr=  r   r   r<   r   r!   r   r>  rC  r(   r(   r)   test_column_with_percentage\  s   z&TestSQLApi.test_column_with_percentageN)r  r  r  r  r  r  r  rR  rU  rV  rY  rZ  r\  r  rk  r   rJ  rK  r   r   r9   r   rq  rr  rt  tdZskip_if_installedrx  r|  r  r  r(   r(   r(   r)   rN    sR    
	
	
	
	rN  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  )r  Zdatabaser(   r(   r)   rc   o  r  zTestSQLiteFallbackApi.connectc              	   C  s   t  I}t| |}tj|d|dddksJ W d    n1 s$w   Y  t| |}td|}W d    n1 sAw   Y  W d    n1 sPw   Y  t || d S )NZtest_frame3_legacyFr  r  z!SELECT * FROM test_frame3_legacy;)r   r   r   rc   r   r   r    r>  )r  r   r   r<   rX  r(   r(   r)   test_sql_open_closer  s   


z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installedrL  c                 C  sH   d}d}t jt|d td| W d    d S 1 sw   Y  d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installedr   r  rv  )r  r<   r  r(   r(   r)   test_con_string_import_error  s
   "z2TestSQLiteFallbackApi.test_con_string_import_errorc              	   C  s   G dd d}t |d,}tt td| W d    n1 s%w   Y  W d    d S W d    d S 1 s=w   Y  d S )Nc                   @  s&   e Zd Z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returnNonec                 _  s   t j|i || _d S r   )rk   rl   r<   )r  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<   )r  r   r(   r(   r)   __getattr__     zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__getattr__c                 S  r  r   )r<   r{  r  r(   r(   r)   r{    r  zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.closeN)r  r  )r  r  r  r  r  r{  r(   r(   r(   r)   MockSqliteConnection  s    
r  r   zSELECT 1)r   r   r   r   r   r   r  )r  r  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  sp   t d| j}t d| j}t|| d}tjt j|d t d| j W d    d S 1 s1w   Y  d S )Nr  z9Execution failed on sql 'iris': near "iris": syntax errorr   r3   )	r   r    r<   r  r   r>  r   r  DatabaseError)r  rW  rX  r  r(   r(   r)   rY    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  sF   | dD ]}|  d d|kr|  d   S qtd| d)N
r   rD  r   zColumn 
 not found)splitstripr  )r  rN  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s(J d S )	Nr   rd  re  Trf  rh  ri  rc  )r   r   r   r   r<   ZSQLiteTableZ
sql_schemar  )r  r   r&  r-  rN  r(   r(   r)   test_sqlite_type_mapping  s   z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r   )r  r  r  r  r  r  rc   r  r   rJ  skipifSQLALCHEMY_INSTALLEDr  r  rY  r  r  r  r(   r(   r(   r)   r  f  s    


	r  c                   @  sf  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;d|dqdrZ<d|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)   rR    s   z_TestSQLAlchemy.setup_classTr  c                 C  sb   z| j  | _t| j| _W n tjjy$   t	
d| j d Y nw | | | | d S )NzCan't connect to z server)r   rc   r<   r   r   r  r8   r|  r}  r   skipr  r  r  r  r(   r(   r)   r    s   
z_TestSQLAlchemy.setup_methodc                 C     t  r   NotImplementedErrorr  r(   r(   r)   r       z_TestSQLAlchemy.setup_driverc                 C  r  r   r  r  r(   r(   r)   r    r  z_TestSQLAlchemy.setup_enginec                 C     |  | d S r   r  r  r*   r(   r(   r)   test_read_sql_parameter  r  z'_TestSQLAlchemy.test_read_sql_parameterc                 C  r  r   r  r  r(   r(   r)   test_read_sql_named_parameter  r  z-_TestSQLAlchemy.test_read_sql_named_parameterc                 C  r  r   r  r  r(   r(   r)   test_to_sql_empty  r  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s'J W d    n1 s1w   Y  ||}|dsAJ tj|dd}|d W d    d S 1 sYw   Y  d S )
Nr   r        ?       @      @      @r  r  r  r  onetwoTr  r  r  )	r8   r   rc   r   r   r   r   r   re  r  r   Z	temp_connr  r  r   r(   r(   r)   test_create_table  s   "z!_TestSQLAlchemy.test_create_tablec                 C  s   ddl m} |  }tg dg dd}t|}||ddks$J ||}|ds/J |d z|	  W n	 t
yC   Y nw |drKJ d S )Nr   r  r  r  r  r  r  )r8   r   rc   r   r   r   r   r   re  clear_cacheAttributeErrorr  r(   r(   r)   test_drop_table  s    

z_TestSQLAlchemy.test_drop_tablec                 C  r  r   r  r  r(   r(   r)   r    r  z_TestSQLAlchemy.test_roundtripc                 C     |    d S r   r  r  r(   r(   r)   r    r  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   ra   r  r(   r(   r)   rU    s   z'_TestSQLAlchemy.test_read_table_columnsc                 C  sH   d}t jt|d tjd| jd W d    d S 1 sw   Y  d S )Nz!Table this_doesnt_exist not foundr   Zthis_doesnt_existr  )r   r  r  r   r!   r<   )r  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s(J t|jjjtjs3J t|jjjts=J d S r3  )r   r!   r<   r   rw   r9   r   r   r   rx   r4  ry   Zbool_rz   r{   objectr  r(   r(   r)   r5  $  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_bigintFr=  r   r  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 r3  	r   r!   r<   r   rt   r9   r   r   r  r  r(   r(   r)   test_default_date_load8  s   z&_TestSQLAlchemy.test_default_date_loadc                 C  sF  dd }t d| j}t|ds|jtjjdd |j}t	|j
ts%J t d| jdgd}t|ds>|jtjjdd |j}t	|j
tsIJ t|jjdksSJ ||j ttt d| jd	d
dd}|j}t	|j
tsqJ t|jjdks{J td| j}|j}t	|j
tsJ t|j|j td| j}||j d S )Nc                 S  s   t | jdr| d tdksJ | d tdksJ d S t| jtrHt| jjdks-J tdddtdddg}t	|| j
d}t| | d S td	| j )
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   rb   r   r,   dtr  r   r   r   r
  AssertionError)r  Zexpected_datarV  r(   r(   r)   r0  D  s   


z:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkzselect * from typesr|   z&no column with datetime with time zonerL  r  r  r   r  Tr2  rr   )r    r<   rz  node
add_markerr   rJ  xfailr|   rb   r9   r   r,   r  r  r   rd  r   r!   r   r
  )r  r   r0  r   r  rV  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sJ | jd	kr(|d jd
|d< n
|d jd |d< t	d| j}t
|| td| j}| jdkr]t|jd tsUJ t|d |d< t
|| d S )Nr   2013-01-01 09:00:00r7  
US/Pacificperiodsr  Ztest_datetime_tzFr=  r$   r  zSELECT * FROM test_datetime_tzr"   r   r   )r   r   r   r<   r  r  Z
tz_convertZtz_localizer   r!   r   r>  r    rb   locr,   r   )r  rV  rX  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sJ td| j}t tjgdgd	}t	|| d S )
Nr   i'  r   r   r  Ztest_datetime_obbFr=  r   )
r   r   r   r<   r   r!   r   ZNaTr   r>  )r  r   rX  rV  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s!J tjd| jdd
}tj	||dd d S )Nz
2018-01-01r   Z6H)r  freqZnumsr  Z	foo_tableZ	info_date)r   r   r  r  F)Zcheck_names)
r   Z
_with_freqr   r  r   r<   r   r!   r   r>  )r  datesrV  rX  r(   r(   r)   "test_naive_datetimeindex_roundtrip  s   z2_TestSQLAlchemy.test_naive_datetimeindex_roundtripc                 C  sF  t d| j}| jdkrtntj}t|jj	j
|sJ t jd| jdgd}t|jj	j
tjs0J t jd| jddid}t|jj	j
tjsFJ t jd| jdddiid}t|jj	j
tjs^J t jd| jdgd}t|jj	j
tjssJ 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 d S )
Nrr   r"   rt   r  r  formatru   r  unit)r   r!   r<   r  r  r   r  r   rt   r9   r   ru   )r  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sJ td| j}|jdd	d
}t	
|| td| j}|jdd	d
}| jdkr[t|jd tsKJ t|d |d< t	
|| d S t	
|| d S )Nr  r7  r  r  r  test_datetimer	  r   r   )ZaxisSELECT * FROM test_datetimer"   r  r   )r   r   r   r  r   r<   r   r!   re   r   r>  r    r  rb   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s!J t	d| j}t
|| td
| j}| jdkrVt|jd tsDJ t|d dd|d< t
|| d S t
|| d S )Nr  r7  r  r  r  r   r   r  Fr=  r  r"   r  r   r   )r  )r   r   r   r  rB  r  r   r<   r   r!   r   r>  r    r  rb   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_dateFr=  )r   r   r   r<   r!   r   r   r
  )r  r   r  rX  rV  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s9J td|}|	dd }t|| tj|d| jddd
ksZJ | j
dkrstd| j}|	dd }t|| td| j}t|| d S )N	   r   r      r   r   	test_timeFr=  r
  Z
test_time2r  zSELECT * FROM test_time2c                 S  
   |  dS Nz%H:%M:%S.%fstrftime_r(   r(   r)   <lambda>	     
 z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>Z
test_time3r"   zSELECT * FROM test_time3c                 S  r  r  r  r  r(   r(   r)   r  #	  r  )r   r   r   r<   r!   r   r>  r   r    mapr  )r  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s"J td| j}t	j
||dd	d
 d S )Ni  r   g        )s1r  Ztest_read_writeFr=  r   T)Zcheck_dtypeZcheck_exact)r   r   rm  float32r   r   r<   r   r!   r   r>  )r  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sJ td| j}t|| t	d	| j}t|| d S )
NrA  r   r   r  test_nanFr=  r7  SELECT * FROM test_nan)
r   r   rB  r   r<   r   r!   r   r>  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sJ td| j}t|| |d 	d|d< d |d< t
d	| j}t|| d S )
NrA  r  r  Fr=  r7  r   r  r  )r   r   rB  r   r<   r   r!   r   r>  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sJ d |jd	< td| j}t	|| t
d
| j}t	|| d S )NrA  r   r<  r  r  Fr=  r7  r
  r   r  )r   r   rB  r   r<   r  r   r!   r   r>  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  r  r^  r(   r_  r(   r(   r)   r[   f	  r  z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)r8   r   r<   r`  )r  ra  r   r   rb  r(   r(   r)   r  `	  s
   

z"_TestSQLAlchemy._get_index_columnsc                 C  r  r   r  r  r(   r(   r)   test_to_sql_save_indexi	  r  z&_TestSQLAlchemy.test_to_sql_save_indexc                 C  r  r   r  r  r(   r(   r)   test_transactionsl	  r  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
 !}|  || W d    n1 sIw   Y  W d    n1 sXw   Y  n| j  | j| W d    n1 stw   Y  t|| j}tj||dd | || j d S )Nr   ri   rP   test_get_schema_create_tabler  Fr  )r8   rj   r`   rQ   r   r&  r<   r   re  rb   rc   rd   rD   r!   r   r>  )	r  r   rj   rQ   r  rf  Zblank_test_dfr<   Zreturned_dfr(   r(   r)   r   o	  s*   
z,_TestSQLAlchemy.test_get_schema_create_tablec                 C  s  ddl m}m} ddlm} ddg}ddg}t||d}|jd	| jd
dks(J |jd| jd|iddks7J | }|j| jd |j	d j
d j}t||sQJ d}	tjt|	d |jd| jdtid W d    n1 spw   Y  |jd| jd|diddksJ |j| jd |j	d j
d j}t||sJ |jdksJ |jd| j|ddksJ |j| jd |j	d j
d j}
|j	d j
d j}t|
|sJ t||sJ d S )Nr   )rn   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   rn   r1   sqlalchemy.schemar0   r   r   r<   reflecttablesr   r   rb   r   r  r  r,   r   )r  rn   r1   r0   rT  r   r   metaZsqltyper  ZsqltypeaZsqltypebr(   r(   r)   
test_dtype	  s4   "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sAJ t|| j}	| }
|
j| jd | jdkrY|n|}|
j| j}t|d j|skJ t|d j|suJ t|d j|sJ t|d j|sJ d S )Nr   )ro   rp   r/   rq   r  T  r   r   r  r   r,  BoolDateIntr/   notna_dtype_testr	  r
  r^   r#   r  r  r  r/   )r8   ro   rp   r/   rq   r	  r0   r   r   r   r   r<   r   r!   r
  r  r  r   rb   r   )r  ro   rp   r/   rq   r0   rT  r   r  r  r  Zmy_typecol_dictr(   r(   r)   test_notna_dtype	  s&   

z _TestSQLAlchemy.test_notna_dtypec           
   	   C  sd  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sIJ t
d| j	}t|d jd dt|d jd dkshJ | }|j| j	d |jd j}	t|	d jt|	d jksJ t|	d j|sJ t|	d j|sJ t|	d j|sJ t|	d j|sJ d S )Nr   )
BigIntegerr/   rq   r  gYB?r  r   float64r   rm  r   )f32f64
f64_as_f32i32r  Ztest_dtypesFr   r     )	precision)r   r   r   r   r9   r   r     r^   r  r  r  )r8   r  r/   rq   r	  r0   r   r   r   r<   r   r!   r   roundr   r
  r  r   r,   r   rb   )
r  r  r/   rq   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   rP   c                 S  s   d}t j|| dS )Nz'SELECT test_foo_data FROM test_foo_datar  )r   r    )rI  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   )rI  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              	     s   t |  r<|  )}|  | W d    n1 sw   Y  W d    d S W d    d S 1 s5w   Y  d S |  d S r   )rb   rc   rd   )Zconnectabler<   )rQ   r%  r(   r)   main
  s   



"z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr#  rA  r	  r7  )r`   rQ   r   r   r<   )r  r&  r(   )rQ   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rNtd}t|jtdk r)d|jv r)tjjdd}|j	
| d}tjt|d |jd	| jd
d W d    d S 1 sGw   Y  d S |jd	| jd
ddks[J td	| j}t|| d S )Nr#   r   z1.0.3r*  zGH 36465rL  zinf cannot be used with MySQLr   ZfoobarFr=  r   )r   r  r   r   r   __version__r   rJ  r  r  r  r  r  r   r<   r   r!   r   Zassert_equal)r  r(  r   r   r   rJ  r  r  r(   r(   r)   test_to_sql_with_negative_npinf
  s   


"z/_TestSQLAlchemy.test_to_sql_with_negative_npinfc              	     s   ddl m mmm} ddlm}m} d}td|gi}| }G  fddd|}|| j	<}|
 ( | }	|j|	 |||d |  tj||j|	d	}
W d    n1 saw   Y  W d    n1 spw   Y  t|
| d S )
Nr   )r.   rq   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__idr0  r(   r.   rq   r-  r(   r)   	TemporaryG
  s
    
r7  )r0  )r   r   )r8   r.   rq   r-  r  sqlalchemy.ormr.  r/  r   r<   rd   rI  Z	__table__rf   addflushr   r    r0  r   r>  )r  r  r.  r/  Z	test_datarV  ZBaser7  sessionr<   r   r(   r6  r)   test_temporary_table7
  s"   
z$_TestSQLAlchemy.test_temporary_tablec                 C  sD   d}t jt|d | |d W d    d S 1 sw   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  <   t dd | | W d    d S 1 sw   Y  d S )Nio.sql.enginer8   r   option_contextr  r  r(   r(   r)   test_options_sqlalchemy\
     "z'_TestSQLAlchemy.test_options_sqlalchemyc                 C  r>  )Nr?  r  r@  r  r(   r(   r)   test_options_autoa
  rC  z!_TestSQLAlchemy.test_options_autoc                 C  s   t tdts	J tdd t tdtsJ t tdts"J W d    n1 s,w   Y  tdd t tdtsAJ t tdtsJJ W d    d S 1 sUw   Y  d S )Nr8   r?  r  )rb   r   r   r   rA  r  r(   r(   r)   test_options_get_enginef
  s   "z'_TestSQLAlchemy.test_options_get_enginec                 C     d S r   r(   r  r(   r(   r)   "test_get_engine_auto_error_messageq
     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/w   Y  | ||}t|| td|) tt|d| | j|dd	}| ||}|D ]}t|| q^W d    d S 1 srw   Y  d S )
Nr   Fr   r   r   r   r   mode.string_storageSelect * from dtype_backendr7  )r   rN  r  	dtype_backend_datar   r<   r   rA  r  dtype_backend_expectedr   r>  	r  string_storagerI  rN  r-  r   rX  rV  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 s,w   Y  | ||}t|| td|& tt||| j|dd}| ||}|D ]}t|| qXW d    d S 1 slw   Y  d S )	Nr   Fr   rJ  rK  rM  r7  )rN  r  rO  rR  r(   r(   r)   !test_read_sql_dtype_backend_table
  s(   "z1_TestSQLAlchemy.test_read_sql_dtype_backend_table)r  r!   r    c                 C  sn   d}|   }|j|| jddd d}tjt|d tt||| jdd W d    d S 1 s0w   Y  d S )	Nr   Fr   rJ  zPdtype_backend numpy is invalid, only 'numpy_nullable' and 'pyarrow' are allowed.r   numpyrM  )rP  r   r<   r   r  r  r  r   )r  rI  r-  r   r  r(   r(   r)   )test_read_sql_invalid_dtype_backend_table
  s   "z9_TestSQLAlchemy.test_read_sql_invalid_dtype_backend_tabler  r   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   r7  ro  r   r6  r        @Float64r  r  rY  )TFNTFTrE  r   r<  Nr   r<  ri  defgh)r   r   r   rB  r  r(   r(   r)   rP  
  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rtdddlm  t	 fddjD S )NpythonrE  r   r   r<  r   r]  r   r7  ro  r6  r  rY  rZ  r[  TFbooleanr\  r^  r   )ArrowExtensionArrayc                   s$   i | ]}| j | d dqS )T)Zfrom_pandas)r   )rW   r  rf  r   par(   r)   
<dictcomp>
  s    z:_TestSQLAlchemy.dtype_backend_expected.<locals>.<dictcomp>)r   r   r   Zobject_r   ZNAr   r   r   r   r   rB  Zpandas.arraysrf  r   )r  storagerN  Zstring_arrayZstring_array_nar(   rg  r)   rQ  
  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|| q&d S )Nr   r  r;  r   r<  r   r   Fr   rJ  zSELECT * FROM testr   )r9   r  )r   r   r=  r   r<   r    r   r>  )r  r   r   rV  rX  r(   r(   r)   test_chunksize_empty_dtypes
  s   

z+_TestSQLAlchemy.test_chunksize_empty_dtypesrN  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s:dnddd}t	|| d S )Nr   r6  r   r;  Fr   rJ  rL  r   )r9   rN  r   )r   r   r   rl  r   ro  )
r   r   r<   r  r   r   r  r   r   r>  )r  rI  rN  r-  r   rX  rV  r(   r(   r)   test_read_sql_dtype
  s$   	z#_TestSQLAlchemy.test_read_sql_dtypeNr  r   )Br  r  r  r  rH  r  rR  r   rI  r  r  r  r  r  r  r  r  r  r  r  rU  r  r5  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r   r  r  r!  r'  rJ  rK  r   infr,  r<  r=  rB  rD  rE  rG  rU  rV  rX  rP  rQ  rk  r   Z
no_defaultrm  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  fddZejdddgdd Zdd Zdd Z  ZS )!TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r"   c                 C  rO  rP  rQ  r  r(   r(   r)   r    rS  zTestSQLiteAlchemy.setup_enginec                 C  s
   d | _ d S r   )driverr  r(   r(   r)   r    s   
zTestSQLiteAlchemy.setup_driverc                 C  sv   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    d S 1 s4w   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
  r7  r  Zexampler   r   r  )r   r   r<   r   r   rv  )r  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
s(J t|jjjtjs3J t|jjjtjs>J d S r3  )r   r!   r<   r   rw   r9   r   r   r   rx   r4  ry   rz   r{   r  r(   r(   r)   r5  -  s   z.TestSQLiteAlchemy.test_default_type_conversionc                 C  s(   t d| j}t|jjjtjrJ d S r3  r  r  r(   r(   r)   r  <  s   z(TestSQLiteAlchemy.test_default_date_loadc                 C  sl   t dddgidd}|jd| jdddksJ td  td| j W d    d S 1 s/w   Y  d S )	Nr   r   r
  r   r   Ztest_bigintwarningFr=  )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    d S 1 s(w   Y  d S )Nr   r
  r7  r  rT  rU  zEmpty table name specifiedr   r[  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 s8w   Y  || jd}| 1}tddgdd	gd
}|jd| jddddks`J |  ||j|j}t|}W d    n1 szw   Y  t|jddgksJ d S )Nr   r.   rq   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  Tr3  2   N)r  r  r  r4  r5  string_columnr(   rv  r(   r)   Test`  s    rz  r^   r   ZhelloZworld)r5  ry  r  Fr   rJ  r
  r5  ry  )r8   r.   rq   r1   r8  r/  rw  r<   rd   r:   Z
create_allr   r   r  r'  r5  ry  rd  r   )	r  r/  rw  Z	BaseModelrz  r.  r;  r   Z
test_queryr(   rv  r)   test_row_object_is_named_tupleO  s(   

z0TestSQLiteAlchemy.test_row_object_is_named_tupler  r   c                   >   t  ||}|dkr|ddd}|S |ddd}|S Nrl  ro  )r`  ra  zint64[pyarrow]superrQ  r   r  rj  rN  r   	__class__r(   r)   rQ  v     z(TestSQLiteAlchemy.dtype_backend_expectedrI  r  r!   c                 C  rF  r   r(   )r  rS  rI  r(   r(   r)   rV    rH  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 s0w   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   rA  r!   r   r   r>  )r  r-  r   rX  r9   rV  r(   r(   r)   test_read_sql_string_inference  s   
z0TestSQLiteAlchemy.test_read_sql_string_inferencec                 C  sT   t dtddddgidd}|jd| jdd	d
 td| jjd }|dks(J d S )Nti  r     r  r   r   r   F)r   r   select * from testr   r   z2020-12-31 12:00:00.000000)r   r   r   r<   r   r  r   r  r(   r(   r)   test_roundtripping_datetimes  s   z.TestSQLiteAlchemy.test_roundtripping_datetimesrn  )r  r  r  r  r  r  r  r  rr  r5  r  rs  ru  r{  rQ  r   rJ  rK  rV  r  r  __classcell__r(   r(   r  r)   rp    s$    

'	
rp  c                      sJ   e Zd ZdZdZdZedd Zedd Zdd	 Z	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   rq  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   rq  r   r   r   r   )r  r   r(   r(   r)   r    s   
zTestMySQLAlchemy.setup_driverc                 C  rF  r   r(   r  r(   r(   r)   r5    s   z-TestMySQLAlchemy.test_default_type_conversionr  r   c                   r|  r}  r~  r  r  r(   r)   rQ    r  z'TestMySQLAlchemy.dtype_backend_expectedrn  )r  r  r  r  r  r  r  r  r  r5  rQ  r  r(   r(   r  r)   r    s    

r  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:r  )r8   r   rq  r  r   r  r(   r(   r)   r    s   
z"TestPostgreSQLAlchemy.setup_enginec                 C  s   t d d| _d S )Nr   )r   r   rq  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 s0w   Y  |jd| jdddksBJ |jd| jddddksPJ |jd| jddddks^J 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 sw   Y  | j  | jd
 | jd W d    n1 sw   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|rh|  }	tj|	dd}
|
j|ddddks6J |
j|dddddksDJ |
j|dddddksRJ tjd| jdd}|
d}t	
|| d S d S ) Nr   rP   r   r
  r9  r   r   rR  rS  z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;Ztest_schema_publicFr=  Ztest_schema_public_explicitpublic)r   r   r   rN  Ztest_schema_otherother)rN  z!Table test_schema_other not foundr   )r   r   rN  r   r   )r   r   rN  r   r   r   Tr2  Ztest_schema_other2r  )r   r   )r`   rQ   r   r<   rd   r   r   r   r!   r   r>  r   r  r  r   rb   rc   r   Z
read_table)r  rQ   r   r@  rA  r7  Zres4r  r  Zengine2Zpdsqlr(   r(   r)   test_schema_support  s   	


	


z)TestPostgreSQLAlchemy.test_schema_supportc           	   	   C  sD  ddl m} ddlm} |d}t| j|rE| j !}|  || W d    n1 s0w   Y  W d    n1 s?w   Y  n| j  | j| W d    n1 s[w   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    d S 1 sw   Y  d S )Nr   rP   ri   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
  r5  Z
created_dtr   Tr  Zperson)r`   rQ   rc  rj   rb   r<   rc   rd   rD   r   r  r   r   r   r>  r   r   re  )	r  rQ   rj   rq  r   rO  rX  rV  r  r(   r(   r)   test_self_join_date_columns9  s4   
"z1TestPostgreSQLAlchemy.test_self_join_date_columnsN)r  r  r  r  r  r  r  r  r  r  r  r(   r(   r(   r)   r    s    

cr  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   )rc   r<   r  r  r   r   r  r  r(   r(   r)   r  l  s   


zTestSQLiteFallback.setup_methodc                 C  r  r   r  r  r(   r(   r)   r  s  r  z*TestSQLiteFallback.test_read_sql_parameterc                 C  r  r   r  r  r(   r(   r)   r  v  r  z0TestSQLiteFallback.test_read_sql_named_parameterc                 C  r  r   r  r  r(   r(   r)   r  y  r  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r,J d S )Nr  r  r  Zdrop_test_framer  )r   r  r   r   re  )r  r  r(   r(   r)   test_create_and_drop_table|  s   z-TestSQLiteFallback.test_create_and_drop_tablec                 C  r  r   r  r  r(   r(   r)   r    r  z!TestSQLiteFallback.test_roundtripc                 C  r  r   r  r  r(   r(   r)   r    r  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r4t||t	 d S | jdkrAt|| d S d S )Nr  r   r
  r   r   r  Fr=  zSELECT * FROM test_dater"   r#   )
r   r   r   r<   r    r  r   r>  r   r,   rC  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s5J td| j}| j	dkrO|dd
 }t
|| d S d S )Nr  r   r   r  r  r
  r  r  c                 S  s   |   S r   )timetz)r  r(   r(   r)   r    s    z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>r   r   r  Fr=  zSELECT * FROM test_timer"   c                 S  r  r  r  r  r(   r(   r)   r    r  )r   r   r   Zto_pydatetimer  r   r   r<   r    r  r   r>  )r  r  Ztz_timesZtz_dtr   r  rV  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  )r  ra  rb  r  Zix_nameZix_infor(   r(   r)   r    s   
z%TestSQLiteFallback._get_index_columnsc                 C  r  r   r  r  r(   r(   r)   r    r  z)TestSQLiteFallback.test_to_sql_save_indexc                 C  r  r   r  r  r(   r(   r)   r    r  z$TestSQLiteFallback.test_transactionsc           
      C  sP   | j d| d}|D ]\}}}}}}	||kr|  S qtd| d| d)NzPRAGMA table_info(r  zTable z	, column r  )r<   rD   r  )
r  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s$J |jd| jddidd
ks3J | dddks=J | dddksGJ d}tjt|d |jd| jdt	id W d    n1 sfw   Y  |jd| jddd
ksxJ | dddksJ | dddksJ 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)r  rT  r   r   r  r(   r(   r)   r    s    

zTestSQLiteFallback.test_dtypec                 C  s   | j dkr
td tdd gttdddd gtdd gddtd	d gd
}t|}d}|j|| jddks9J | |ddksCJ | |ddksMJ | |ddksWJ | |ddksaJ d S )Nr#   r  Tr  r   r   r  r   r,  r  r  r	  r
  r  r+  r  rc  r  r/   r-   )	r  r   r  r   r   r   r   r<   r  )r  rT  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 s*w   Y  tg dD ]>\}}|j|| jddksEJ t|| j t ddgddggd|gd}d|d}|j|| jddkslJ t|| j q5d S )Nr   r
  r7  r  r   r<  r   z$Empty table or column name specifiedr   r[  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)r  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   rI  r  r  r  r  r  r  r  r  rJ  rK  r  r  r  r  r  r  r  r  r(   r(   r(   r)   r  d  s*    


r  z'{}'z{:.8f}z{:d}c                 C  s   dS )NZNULLr(   )r  r(   r(   r)   r    s    r  z{:.10f}z'{!s}'c                 G  sJ   g }|D ]}t |trt|rd }tt| }||| q| t| S r   )rb   r   r   _formattersr   r   r  )r   r  Zprocessed_argsarg	formatterr(   r(   r)   format_query  s   r  c                 C  sL   t |}||  }W d   n1 sw   Y  |du r"dS t|S )z#Replace removed sql.tquery functionN)r   r   rD   r  rd  )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  )rC   rD   r   r  r  )r  r   r<   rJ   r(   r(   r)   re  3  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sQJ tjd|d
d}| }||_d
|j_t || d S )NrF  Fr=  r  zselect * from test_tabler   txtr   r  ZIdxZtest_table2zselect * from test_table2r  )r   makeTimeDataFramer   r   r  r   r>  r  r=  r   r   r  r   r   )r  r   r   rX  rV  Zframe2Znew_idxr(   r(   r)   
test_basic8  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 )Nr  r   z(INSERT INTO test VALUES (%s, %s, %s, %s)r  r  gMbP?)Zrtol)r   r  r   rB  r   r   r&  rC   rD   Ziterrowsr  r  r  r  r   r>  )
r  r   r   rf  rJ   insr  rX   Zfmt_sqlrX  r(   r(   r)   test_write_row_by_rowU  s   
z!TestXSQLite.test_write_row_by_rowc           	      C  s   t  }t|d}| }|| d}|jd }t|}||t| W d    n1 s2w   Y  |	  t
d|}|jd d |_t ||d d  d S )Nr   z$INSERT INTO test VALUES (?, ?, ?, ?)r   r  r   )r   r  r   r&  rC   rD   r   r   r  r  r  r   r>  )	r  r   r   rf  rJ   r  rX   r  rX  r(   r(   r)   test_executeg  s   

zTestXSQLite.test_executec                 C  s   t  }t|d}| }|D ]}|d}t|dkr+|d dkr+|d dks+J qtj|dddgd	}| }d
|v s@J | }|| d S )Nr    r
  r   r   r   ZDATETIMEr   r,  zPRIMARY KEY ("A", "B"))	r   r  r   r&  
splitlinesr  r  rC   rD   )r  r   r   rf  lineslinetokensrJ   r(   r(   r)   test_schemaw  s   
zTestXSQLite.test_schemac              	   C  s   d}|  }|| t|8}|d |d tjtjdd |d W d    n1 s3w   Y  W d    d S W d    d S 1 sKw   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))rC   rD   r   r   r   r  r  )r  r   rf  rJ   r  r(   r(   r)   test_execute_fail  s   	


"zTestXSQLite.test_execute_failc              	   C  s   d}t td+}| }|| t|}|d W d    n1 s)w   Y  W d    n1 s8w   Y  d}tj	tj
|d td|d W d    d S 1 sYw   Y  d S )Nr  r   r  z$Cannot operate on a closed database.r   r  r  )r   r   rk   rc   rC   rD   r   r   r   r  ZProgrammingErrorr  )r  rf  r<   rJ   r  r  r(   r(   r)   test_execute_closed_connection  s   	
"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   )r  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s*J td
|}t|| d S )Nr   r
  Zc0r   mono_dfFr  c                 s  s    | ]}|d  V  qdS )r   Nr(   )rW   Zmy_c0r(   r(   r)   	<genexpr>  s    z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr7  )r   r   r   sumrD   r  r   r>  )r  r   r  Zcon_xZthe_sumrX  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 s:w   Y  | || tj|||dd d}tjt|d tj|||dd W d    n1 skw   Y  tj|||ddd t||dddgksJ tj|||ddddksJ t||dg dksJ | || tj|||ddddksJ 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   rt  r  )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  r7  )r7  r   r  r   r   r  r   )r  r  r  r  r  )r   r   r  r  r   r   re  r  )r  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  re  r  r  r  r  r  r  r  r  r  r(   r(   r(   r)   r  2  s    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  
__future__r   r   r   rF   r   r   r   r   r   r   pathlibr
   rk   rl  rW  r   r   Zpandas._libsr   Zpandas.util._test_decoratorsutilZ_test_decoratorsr  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  rw  rI  r*   r;   rM   rh   rm   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_irisrJ  r&  rK  r   r   r   r	  r  r  r  r  r"  r(  r*  r+  r2  r5  rB  rY  rh  rj  rp  rr  rw  rx  ry  r  r  r  r  r  rN  r  r  rp  r  r  r  r  r,   Zstr_bytesr   r   r   r  r  r  r  r  r  r(   r(   r(   r)   <module>   s~   4$






!






#















		$%94#

"    U LX      W # # 0
