
    o[we                   :   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c 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 	 ddl8Z8dZ9n# e:$ r dZ9Y nw xY wej;        d             Z<ddZ=ddZ>ddZ?d Z@ddZAddZBddZCdd"ZDdd$ZEej;        d%             ZFej;        d&             ZGej;        d'             ZHej;        d(             ZIej;        d)             ZJej;        d*             ZKej;        d+             ZLej;        d,             ZMej;        d-             ZNej;        d.             ZOej;        d/             ZPej;        d0             ZQej;        d1             ZRej;        d2             ZSej;        d3             ZTej;        d4             ZUej;        d5             ZVd6d7gZWd8d9gZXg d:ZYg d;ZZeWeXz   eYz   Z[eWeXz   eZz   Z\e[d<gz   Z]e\d=gz   Z^ej_        j`        ej_        a                    de]          d>                         Zbej_        j`        ej_        a                    de]          d?                         Zcej_        j`        ej_        a                    de]          d@                         Zdej_        j`        ej_        a                    de]          ej_        a                    dAddBg          dC                                     Zeej_        j`        ej_        a                    de]          ej_        a                    dDdEdFg          dG                                     Zfej_        j`        ej_        a                    de]          dH                         Zgej_        j`        ej_        a                    de^          dI                         Zhej_        j`        ej_        a                    de^          dJ                         Ziej_        j`        ej_        a                    de\          dK                         Zjej_        j`        ej_        a                    de^          dL                         Zkej_        j`        ej_        a                    de\          dM                         Zlej_        j`        ej_        a                    de\          dN                         Zmej_        j`        ej_        a                    de[          dO                         Znej_        j`        ej_        a                    deW          dP                         Zoej_        j`        ej_        a                    deW          dQ                         Zpej_        j`        ej_        a                    deX          ej_        a                    dRdSdTg          dU                                     Zqej_        j`        ej_        a                    deX          dV                         Zrej_        j`        ej_        a                    deW          dW                         Zsej_        j`        ej_        a                    deX          dX                         ZtdY ZudZ Zvd[ Zw G d\ d]          Zx G d^ d_ex          Zy G d` daex          Zz G db dc          Z{ G dd dee{          Z|ej_        }                    e9 dfg           G dh dieze|                      Z~ G dj dkeye|          Zej_        }                    e9 dfg           G dl dmeze{                      Z G dn doe          Zej;        dp             Zdq Zej_        j`         G dr dse                      Zej_        j`         G dt due                      Z G dv dweye{          Zedxj        edxj        ej        dxj        edxj        edyj        edzj         ed          d{ ej        d|j        ed}j        i	Zd~ ZddZ G d 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                 &    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*       8lib/python3.11/site-packages/pandas/tests/io/test_sql.pysql_stringsr-   P   sU     JNS
 
"
 "
 ??F,
 ,
'  r+   dialectstrc                    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
SepalWidthPetalLength
PetalWidthName   )
sqlalchemyr1   r2   r3   r4   r5   r6   )
r.   r1   r2   r3   r4   r5   r6   dtypemetadatar7   s
             r,   iris_table_metadatarA   m   s                    ,,EE$ExzzH5}e$$|U##}e$$|U##vvvc{{## D Kr+   connsqlite3.Connection	iris_filer   c                2   |                                  }d}|                    |           |                    d d          5 }t          j        |          }t          |           d}|                    ||           d d d            d S # 1 swxY 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)rB   rD   curstmtcsvfilerN   s         r,   create_and_load_iris_sqlite3rT      s    
++--CD KK	w	7	7 &7G$$V7f%%%	& & & & & & & & & & & & & & & & & &s   <BBBc                n  
 ddl m} ddlm} t	          |          }|                    d d          5 }t          j        |          }t          |          

fd|D             } ||          	                    |          }	t          | |          r|                                 5 } |                                 5  |                    | d           |                    | 	           |                     |	           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nn|                                 5  |                    | d           |                    | 	           |                     |	           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S )
Nr   insertEnginerF   rG   c                J    g | ]}t          t          |                     S r*   dictzip).0rowheaders     r,   
<listcomp>z(create_and_load_iris.<locals>.<listcomp>   s+    ;;;S$s63''((;;;r+   T
checkfirstbind)r>   rW   sqlalchemy.enginerY   rA   rL   rM   rN   rO   values
isinstanceconnectbegindropcreaterK   )rB   rD   r.   rW   rY   r7   rS   rN   paramsrR   r`   s             @r,   create_and_load_irisrn      s   !!!!!!((((((w''D	w	7	7 #7G$$f;;;;F;;;vd||""6**dF## 
	# '4ZZ\\ ' 'IIdtI444KKTK***LL&&&' ' ' ' ' ' ' ' ' ' ' ' ' ' '' ' ' ' ' ' ' ' ' ' ' ' ' ' '  # #		$4	000&&&T"""# # # # # # # # # # # # # # ## # # # # # # # # # # # # # # # # #s   A4F*(D#=AD D#DD#DD#F*#D'	'F**D'	+F*AFF*F	F*F	F**F.1F.c                F   d}t          | t          j                  r+|                                 }|                    |           d S ddlm} ddlm}  ||          }t          | |          rp| 	                                5 } | 
                                5  |                     |           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S | 
                                5  |                     |           d d d            d S # 1 swxY w Y   d S )Nz+CREATE VIEW iris_view AS SELECT * FROM irisr   textrX   )rh   sqlite3
ConnectionrJ   rK   r>   rq   rf   rY   ri   rj   )rB   rR   rQ   rq   rY   s        r,   create_and_load_iris_viewrt      s   8D$*++ #kkmmD######,,,,,,tDzzdF## 	# '4ZZ\\ ' 'LL&&&' ' ' ' ' ' ' ' ' ' ' ' ' ' '' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '  # #T"""# # # # # # # # # # # # # # # # # #sH   CB:.C:B>	>CB>	CCC3DDDc                   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                               |S )Nr   )TEXTBooleanr2   DateTimer3   Integerr4   r6   r$   typesTextColDateCol
IntDateColIntDateOnlyColFloatColIntColBoolColIntColWithNullBoolColWithNullr&   DateColWithTzT)timezone)
r>   rv   rw   r2   rx   r3   ry   r4   r6   append_column)r.   rv   rw   r2   rx   r3   ry   r4   r6   	date_type	bool_typer@   rz   s                r,   types_table_metadatar      sy   	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	  8++I"h..GIxzzHEy$y)$$|W%%))z5!!x!!y)$$)) ),, E ,FF?HHd4K4K4KLLMMMLr+   
types_data
list[dict]c                    |                                  }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(?, ?, ?, ?, ?, ?, ?, ?, ?)
            )rJ   rK   rP   )rB   r   rQ   rR   s       r,   create_and_load_types_sqlite3r      sM    
++--C
D KKD OOD*%%%%%r+   c                   ddl m} ddlm} t	          |          } ||                              |          }t          | |          r|                                 5 } |                                 5  |	                    | d           |
                    |            |                     |           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S |                                 5  |	                    | d           |
                    |            |                     |           d d d            d S # 1 swxY w Y   d S )Nr   rV   rX   Trb   rd   )r>   rW   rf   rY   r   rg   rh   ri   rj   rk   rl   rK   )rB   r   r.   rW   rY   rz   rR   s          r,   create_and_load_typesr      s   !!!!!!(((((( ))E6%==
++D$ 
\\^^ 	#t # #

4D
111$'''T"""# # # # # # # # # # # # # # #	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# ZZ\\ 	 	JJtJ---LLdL###LL	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	sJ   C3AC6CC	C	C	
CC!C;AEEEframer   c                    | j         j        d         j        }| j        d         }t          |t          j                  sJ t          j        |j        g d           | j	        dv sJ d S )Nr   ffffff@g      @gffffff?皙?Iris-setosa))      )   r   )
dtypesiloctype
issubclassnpfloatingtmequalContentsrg   shape)r   pytyper_   s      r,   check_iris_framer     sm    \q!&F
*Q-Cfbk*****SZ!D!D!DEEE;,,,,,,,r+   
table_namec                b   d| }t          | t          j                  rA|                                 }|                    |                                          d         S ddlm} ddlm	} t          | t                    r	  ||           }|                                5 } |                     |                                          cd d d            |                                 S # 1 swxY w Y   	 |                                 d S # |                                 w xY wt          | |          rU|                                 5 } |                     |                                          cd d d            S # 1 swxY w Y   d S |                     |                                          S )Nz SELECT count(*) AS count_1 FROM r   )create_enginerX   )rh   rr   rs   rJ   rK   fetchoner>   r   rf   rY   r/   ri   exec_driver_sql
scalar_onedispose)rB   r   rR   rQ   r   rY   engines          r,   
count_rowsr     s6   :j::D$*++ ;kkmm{{4  ))++A..,,,,,,,,,,,,dC   	;!&t,,^^%% C//55@@BBC C C C C C C     C C C C C C C C C          f%% 	; ?4++D11<<>>? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ''--88:::sB   D "'C*	D *C..D 1C.2D D"	'E==FFc                <     | dddd          }t          |          S )NiodatarM   ziris.csvr
   )datapath	iris_paths     r,   r   r   +  s#    vuj99I	??r+   c                 6    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)
r{   r|   r}   r~   r   r   r   r   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`     ,#&$8	
 	
 ,$&"#8	
 	
 r+   c           
         dddddddddd	}t          |           }||                                                             |          S )Nr/   int64float)	r{   r|   r}   r~   r   r   r   r   r   )r   keysastype)r   r   dfs      r,   types_data_framer   O  s[     !!"
 
F 
:		Bfkkmm##F+++r+   c                 4    g d} g d}t          ||           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   s     r,   test_frame1r   `  s3    +++G  D< T7++++r+   c                 4    g d} g d}t          ||           S )N)r   r   r   ))r   igZ)r   ir   )r   i N  r   )r   i͏r   r   r   r   s     r,   test_frame3r     s3    !!!G  D T7++++r+   c              #    K   t          j        d          }t          j        d          } |j        dd|j        j        j        i|j        j                  } |j        |          }|	                    d          st          || d           |	                    d          s+|D ]}|                    d	           t          ||d           |V  |                                5 }|                                5   |j        d
          }|                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   |                                 d S )Nr>   pymysqlz*mysql+pymysql://root@localhost:3306/pandasclient_flag)connect_args	poolclassr7   r%   rz   r    DROP TABLE IF EXISTS test_frame;)pytestimportorskipr   	constantsCLIENTMULTI_STATEMENTSpoolNullPoolinspect	has_tablern   popr   ri   rj   rq   rK   r   )	r   r   r>   r   r   inspentryrB   rR   s	            r,   mysql_pymysql_enginer     s     $\22J!),,G%Z%4#W%6%=%NO/*  F
 :f%%D>>&!! 9VY888>>'"" ; 	' 	'EIIo&&&&fj':::
LLL			 TZZ\\ 	 	":?#EFFDLL	 	 	 	 	 	 	 	 	 	 	 	 	 	 	               NNs6   'E<&D."E.D2	2E5D2	6EE	E	c              #  l   K   |                                  5 }|V  d d d            d S # 1 swxY w Y   d S Nri   )r   rB   s     r,   mysql_pymysql_connr     s      		%	%	'	' 4


                    )--c              #  t  K   t          j        d          }t          j        d            |j        d|j        j                  } |j        |          }|                    d          st          || d           |                    d          st          ||d           |V  |	                                5 }|
                                5   |j        d          }|                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   |                                 d S )	Nr>   psycopg2z=postgresql+psycopg2://postgres:postgres@localhost:5432/pandasr   r7   r&   rz   r   )r   r   r   r   r   r   r   rn   r   ri   rj   rq   rK   r   )r   r   r>   r   r   rB   rR   s          r,   postgresql_psycopg2_enginer     s     $\22J

###%Z%G/*  F :f%%D>>&!! >VY===>>'"" @fj,???
LLL			 TZZ\\ 	 	":?#EFFDLL	 	 	 	 	 	 	 	 	 	 	 	 	 	 	               NNs6   ;D&D6DD	D	D	
DD Dc              #  l   K   |                                  5 }|V  d d d            d S # 1 swxY w Y   d S r   r   )r   rB   s     r,   postgresql_psycopg2_connr     s      	#	+	+	-	- 


                 r   c               #     K   t          j        d           t          j                    5 } d| z   V  d d d            d S # 1 swxY w Y   d S )Nr>   
sqlite:///)r   r   r   ensure_cleannames    r,   
sqlite_strr     s      
%%%			 "dT!!!!" " " " " " " " " " " " " " " " " "s   ?AAc              #     K   t          j        d          } |j        | |j        j                  }|V  |                                 d S )Nr>   r   )r   r   r   r   r   r   )r   r>   r   s      r,   sqlite_enginer     sQ      $\22J%Z%jJO<TUUUF
LLL
NNr+   c              #  l   K   |                                  5 }|V  d d d            d S # 1 swxY w Y   d S r   r   )r   rB   s     r,   sqlite_connr     s      				 	  D


                 r   c                    t          j        d          } |j        |           }t          ||d           |                                 | S )Nr>   r$   )r   r   r   rn   r   )r   r   r>   r   s       r,   sqlite_iris_strr     sL    $\22J%Z%j11FH555
NNr+   c                (    t          | |d           | S )Nr$   )rn   )r   r   s     r,   sqlite_iris_enginer     s    	8<<<r+   c              #  l   K   |                                  5 }|V  d d d            d S # 1 swxY w Y   d S r   r   )r   rB   s     r,   sqlite_iris_connr     s      		#	#	%	% 


                 r   c               #     K   t          j        t          j        d                    5 } | 5 }|V  d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S N:memory:)
contextlibr   rr   ri   closing_connrB   s     r,   sqlite_buildinr    s      		GOJ77	8	8 L 	TJJJ	 	 	 	 	 	 	 	 	 	 	 	 	 	 	                 s1   A=AA	AA	AAAc                &    t          | |           | S r   )rT   )r  r   s     r,   sqlite_buildin_irisr    s     ;;;r+   r   r   r   r   )r   r   r   )r   r   r   r  r  c                b    |                     |           } |                    d| dd           d S )NtestappendFr   con	if_existsr   )getfixturevalueto_sql)rB   r   requests      r,   test_dataframe_to_sqlr  "  s:     ""4((DFNNNNNr+   c                D   t          j        d           t          t          j        dgd          t          j        t          ddd          gd          t          j        t          ddd          gd          t          j        t          d          gd          t          j        d	gd
          d          }|                    |           } t          j
        t          d          5  |                    d| dd           d d d            d S # 1 swxY w Y   d S )Npyarrowr   zint8[pyarrow]r?     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  rB   r  r   s      r,   "test_dataframe_to_sql_arrow_dtypesr"  *  s}    	"""	8QC777$1%%&.F   Hd4A../7MNNN9Q<<.8OPPPhu,=>>>	
 	


 

B ""4((D		#K7H	I	I	I Q Q
		|%	PPPQ Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Qs   .DDDc           
         t          j        d           t          dt          j        t          ddd          |gd          i          }|                    |           } |                    d| dd	
           d S )Nr  r   r  r   r  r  r  r  Fr
  )r   r   r   r  r  r   r  r  )rB   r  nulls_fixturer   s       r,   *test_dataframe_to_sql_arrow_dtypes_missingr%  ?  s     	"""	$1%%}5=U  	

 
B ""4((DII<TYeILLLLLr+   methodmultic                (   |                     |           } t          | d          5 }|                    |d|           |                    d          sJ 	 d d d            n# 1 swxY w Y   t	          | d          t          |          k    sJ d S )NTneed_transaction
test_framer&  r  r    r  r   r   len)rB   r&  r   r  	pandasSQLs        r,   test_to_sqlr0  O  s     ""4((D	4$	7	7	7 19l6BBB""<0000001 1 1 1 1 1 1 1 1 1 1 1 1 1 1 dL))S-=-=======s   0A$$A(+A(zmode, num_row_coef)r  r   )r	     c                ^   |                     |           } t          | d          5 }|                    |dd           |                    |d|           |                    d          sJ 	 d d d            n# 1 swxY w Y   t	          | d          |t          |          z  k    sJ d S )NTr)  r+  failr  r-  )rB   modenum_row_coefr   r  r/  s         r,   test_to_sql_existr7  Z  s     ""4((D	4$	7	7	7 19lfEEEldCCC""<0000001 1 1 1 1 1 1 1 1 1 1 1 1 1 1 dL))\C<L<L-LLLLLLLs   AA<<B B c                |   |                     |           } t          | d          5 }|                    |dd           |                    d          sJ d}t	          j        t          |          5  |                    |dd           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S )NTr)  r+  r3  r4  z!Table 'test_frame' already existsr  )r  r    r  r   r   raises
ValueError)rB   r   r  r/  msgs        r,   test_to_sql_exist_failr<  f  sn    ""4((D	4$	7	7	7 J9lfEEE""<000001]:S111 	J 	J[,&III	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	JJ J J J J J J J J J J J J J J J J Js6   AB14BB1B	B1 B	!B11B58B5c                   |                     |           } t          d|           }t          |           t          j        d|           }t          |           t          j        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   r9   )r  r!   r   r  read_sqlr   r   rB   r  
iris_frames      r,   test_read_iris_queryrD  s  s     ""4((D 4d;;JZ   1488JZ   ;TBBJv%%%%:-------r+   c                j   |                     |           } t          t          d| d                    }t          |           t          t	          j        d| d                    }t          |           t          t	          j        d| d                    }|j        dk    sJ d|j        v sJ d S )Nr>     	chunksizer?  r@  r9   )r  r   r!   r   r  rA  r   r   rB  s      r,   test_read_iris_query_chunksizerI    s     ""4((D';TQOOOPPJZ   $8$!LLLMMJZ   $BDTUVVVWWJv%%%%:-------r+   c                l   |                     |           } ddlm}m}m}m}  |            }t          | t                    r ||           n| } |d||          }t           ||          | ddd          }	t          |	           t          | t                    r|
                                 d S d S )	Nr   )r4   r6   r   selectr7   )autoload_withr   r   r   lengthrm   )r  r>   r4   r6   r   rK  rh   r/   r!   r   r   )
rB   r  r4   r6   r   rK  r@   autoload_conr7   rC  s
             r,   .test_read_iris_query_expression_with_parameterrQ    s    ""4((D            xzzH*4T3*?*?I==&&&TL5>>>DtdMS#I#I  J Z   $  r+   c                    |d                                          D ]\  }}|| v r nt          d|  d          |                    |           } t          || d          }t	          |           d S )Nr'   zNo part of z( found in sql_strings['read_parameters']r   r   rO  )itemsKeyErrorr  r!   r   )rB   r  r-   dbqueryrC  s         r,   *test_read_iris_query_string_with_parameterrX    s     !!2399;; U U	E::E  STSSSTTT""4((Dt4HIIIJZ     r+   c                    |                     |           } t          d|           }t          |           t          j        d|           }t          |           d S Nr7   )r  r"   r   r  rA  rB  s      r,   test_read_iris_tabler[    sY     ""4((D--JZ   VT**JZ     r+   c                    |                     |           } t          t          d| d                    }t          |           t          t	          j        d| d                    }t          |           d S )Nr7   rF  rG  )r  r   r"   r   r  rA  rB  s      r,   test_read_iris_table_chunksizer]    ss     ""4((DvtqAAABBJZ   FDA>>>??JZ     r+   c                J   |                     |           } g fd}t          | d          5 }|                    |d|           |                    d          sJ 	 d d d            n# 1 swxY w Y   dgk    sJ t	          | d          t          |          k    sJ d S )Nc                                         d           fd|D             }|                    | j                                        |           d S )Nr   c                J    g | ]}t          t          |                     S r*   r[   r^   r_   r   s     r,   ra   z8test_to_sql_callable.<locals>.sample.<locals>.<listcomp>  )    :::Ss^^$$:::r+   )r	  rK   tablerW   )pd_tablerB   r   	data_iterr   checks     `  r,   samplez$test_to_sql_callable.<locals>.sample  sT    Q::::	:::X^**,,d33333r+   Tr)  r+  r,  r   r-  )rB   r   r  rg  r/  rf  s        @r,   test_to_sql_callablerh    s    ""4((DE4 4 4 4 4
 
4$	7	7	7 19l6BBB""<0000001 1 1 1 1 1 1 1 1 1 1 1 1 1 1 QC<<<<dL))S-=-=======s   0A,,A03A0c                   |                     |           } t          j        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                  sJ t          |j        j        j        t          j                  sJ d S Nrz   )r  r   r"   r   r   r?   r   r   r   r   integerr   r   r   r!  s      r,   test_default_type_conversionrl    s     ""4((D		GT	*	*Bbk',bk:::::bio*BJ77777 bj&+RZ88888 b'-2BK@@@@@ b(.3R[AAAAAAAr+   c                   |                     |           } ddlm} ddlm} t          g dg dd          }|                    d| d	           d
} ||          }t          | |          rn|                                 5 }|	                                5  |
                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nA| 	                                5  | 
                    |           d d d            n# 1 swxY w Y   t          j        d|           }t          j        ||           t          j        d|           }t          j        ||           d S )Nr   rp   rX   r   r1     皙?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  r>   rq   rf   rY   r   r  rh   ri   rj   rK   r   r!   r   assert_frame_equalrA  )	rB   r  rq   rY   r   procengine_connres1res2s	            r,   test_read_procedurer{    sB    ""4((D
  ((((((	99	:	:BII<TI777D 4::D$ \\^^ 	*{""$$ * *##D)))* * * * * * * * * * * * * * *	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* ZZ\\ 	 	LL	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 2D99D"d### <,d33D"d#####sH   CB8,C8B<	<C?B<	 CCC/DDDexpected_countr1  zSuccess!c                   fd}|                     |           } t          ddgddgddgd          }|                    d	| d
|          }|J n|k    sJ t          j        d	|           }t          j        ||           d S )Nc                   |j         }|                                5 }t                      }t          j        |          }|                    |           |                    d           d                    d |D                       }| j        r| j         d| j	         }	n| j	        }	d|	 d| d}
|
                    |
|           d d d            n# 1 swxY w Y   S )	Nr   z, c                    g | ]}d | d 	S )"r*   )r^   ks     r,   ra   zVtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<listcomp>!  s      8 8 8aQ 8 8 8r+   .zCOPY z (z) FROM STDIN WITH CSV)r   file)
connectionrJ   r	   rM   writer	writerowsseekjoinschemar   copy_expert)rc  rB   r   re  
dbapi_connrQ   s_bufr  r   r   	sql_queryr|  s              r,   psql_insert_copyzBtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy  s)   _
   	7CJJEZ&&FY'''JJqMMMii 8 84 8 8 899G| ( %;;uz;;

"Z
L
LLgLLLIOO	O666	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 s   B,CCCr   r1  rq  r   r  ncol1col2col3r+  F)r   r  r   r&  )r  r   r  r   r"   r   rv  )rB   r|  r  r  expectedresult_countresults    `     r,   (test_copy_from_callable_insertion_methodr    s        & ""4((D1a&3*sCjQQRRH??t59I #  L ####~----d33F&(+++++r+   c                   |                     |           } ddlm ddlm} ddlm} fd} |d          }t          | |          rn|                                 5 }|	                                5  |
                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nA| 	                                5  | 
                    |           d d d            n# 1 swxY w Y   t          g dgt          d          	          }|                    d
| dd           t          g dgt          d          	          }|                    d
| dd|          }	t          j        d
|           }
t!          j        |
|           |	dk    sJ t          j        | d          5 }|                    d
           d d d            d S # 1 swxY w Y   d S )Nr   rV   rX   rp   c                    fd|D             } | j                                       |                              dg          }|                    |          }|j        S )Nc                J    g | ]}t          t          |                     S r*   r[   ra  s     r,   ra   z\test_insertion_method_on_conflict_do_nothing.<locals>.insert_on_conflict.<locals>.<listcomp>D  rb  r+   r  )index_elements)rc  rg   on_conflict_do_nothingrK   rowcountrc  rB   r   re  r   rR   r  rW   s     `    r,   insert_on_conflictzHtest_insertion_method_on_conflict_do_nothing.<locals>.insert_on_conflictC  sj    ::::	:::F5;VD\\##C5#99 	
 d##r+   zx
    CREATE TABLE test_insert_conflict (
        a  integer PRIMARY KEY,
        b  numeric,
        c  text
    );
    r    @r  abcr   test_insert_conflictr	  Fr
  r   g	@rt  r   r  r   r  r&  Tr)  )r  sqlalchemy.dialects.postgresqlrW   rf   rY   sqlalchemy.sqlrq   rh   ri   rj   rK   r   listr  r   r"   r   rv  r   
drop_table)rB   r  rY   rq   r  
create_sqlr  r  	df_insertinsertedr  r/  rW   s               @r,   ,test_insertion_method_on_conflict_do_nothingr  9  s/    ""4((D555555((((((######     	 J $ %\\^^ 	(s ( (J'''( ( ( ( ( ( ( ( ( ( ( ( ( ( (	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( ZZ\\ 	% 	%LL$$$	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% ---$u++>>>HOO#     ===/4;;???I#!    H  6==F&(+++q==== 
	5	5	5 534445 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5Z   B+2BB+B	B+B	B++B/2B/C--C14C15GGGc                   |                     |           } ddlm ddlm} ddlm} fd} |d          }t          | |          rn|                                 5 }|	                                5  |
                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nA| 	                                5  | 
                    |           d d d            n# 1 swxY w Y   t          g dgt          d          	          }|                    d
| dd           t          g dgt          d          	          }|                    d
| dd|          }	t          j        d
|           }
t!          j        |
|           |	dk    sJ t          j        | d          5 }|                    d
           d d d            d S # 1 swxY w Y   d S )Nr   rV   rX   rp   c                    fd|D             } | j                                       |          }|                    |j        j        |j        j                  }|                    |          }|j        S )Nc                J    g | ]}t          t          |                     S r*   r[   ra  s     r,   ra   zXtest_insertion_method_on_conflict_update.<locals>.insert_on_conflict.<locals>.<listcomp>  rb  r+   )rt  c)rc  rg   on_duplicate_key_updater  rt  r  rK   r  r  s     `    r,   r  zDtest_insertion_method_on_conflict_update.<locals>.insert_on_conflict~  su    ::::	:::vek""))$//++dmo+QQd##r+   zv
    CREATE TABLE test_insert_conflict (
        a INT PRIMARY KEY,
        b FLOAT,
        c VARCHAR(10)
    );
    r  r  r   r  r	  Fr
  r  r  r1  Tr)  )r  sqlalchemy.dialects.mysqlrW   rf   rY   r  rq   rh   ri   rj   rK   r   r  r  r   r"   r   rv  r   r  )rB   r  rY   rq   r  r  r  r   r  r  r  r/  rW   s               @r,   (test_insertion_method_on_conflict_updater  t  s"    ""4((D000000((((((######     	 J $ %\\^^ 	(s ( (J'''( ( ( ( ( ( ( ( ( ( ( ( ( ( (	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( 	( ZZ\\ 	% 	%LL$$$	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 	% 
MMM?DKK	8	8	8BII)txuIUUU---$u++>>>H#!   H  6==F&(+++q==== 
	5	5	5 534445 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5r  c                   |                     |           } ddlm} ddlm} dt          j                    j         }dt          j                    j         } |d| d| d| d	| d
	          }t          | |          rn| 	                                5 }|
                                5  |                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nA| 
                                5  |                     |           d d d            n# 1 swxY w Y   t          ||           }t          dgdd          }	t          j        ||	           d S )Nr   rX   rp   group_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   group_idr   )r  rf   rY   r  rq   uuiduuid4hexrh   ri   rj   rK   r"   r   r   rv  )
rB   r  rY   rq   r   	view_namesql_stmtr  r  r  s
             r,   test_read_view_postgresr    s\    ""4((D((((((######,$*,,*,,J0djll.00It
	
	 
	
 
	 
	 
	 
	 
	 
	 
	 H $ #\\^^ 	&s & &H%%%& & & & & & & & & & & & & & &	& 	& 	& 	& 	& 	& 	& 	& 	& 	& 	& 	& 	& 	& 	& ZZ\\ 	# 	#LL"""	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	#It,,Fqc6::;;H&(+++++sH   C")C?C"C	C"C	C""C&)C&D$$D(+D(c                
   d}d}d}|                      |           |                      |           |                      |           t          j        d|           }t          dgdd          }t	          j        ||           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   r  )rK   r  rA  r   r   rv  )r  create_tableinsert_intocreate_viewr  r  s         r,   test_read_view_sqliter    s    LKK
 <(((;''';'''[3^DDFqc6::;;H&(+++++r+   c                    t          j        t          d          5  t          j        t
          d          5  t          j        d|            d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w 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   r9  	TypeErrorr   r  FutureWarningr   rK   )r   s    r,   test_execute_typeerrorr    s,   	y(U	V	V	V B B'5
 
 
 	B 	B
 K,.@AAA	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	BB B B B B B B B B B B B B B B B B Bs4   A2AA2A	A2!A	"A22A69A6c                    t          j        t          d          5  t          j        d|            d d d            d S # 1 swxY w Y   d S )Nr  r  r  )r   r  r  r   rK   )r  s    r,   test_execute_deprecatedr    s    		#1
 
 
 ? ?
 	(*=>>>? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?s   ?AAc                      e Zd Zd ZdS )	MixInBasec                   t          | d          r| j                                         	 |                                 }|5  |                     |          D ]}|                     ||           |                     |          D ]}|                     ||           	 d d d            d S # 1 swxY w Y   d S # t          j	        j
        t          j
        f$ r Y d S w xY w)NrB   )hasattrrB   closeri   _get_all_views	drop_view_get_all_tablesr  r>   excOperationalErrorrr   )selfrB   viewtbls       r,   teardown_methodzMixInBase.teardown_method  sB   4   	IOO		/<<>>D  / / //55 / /DNN4....//55 / /COOC....// / / / / / / / / / / / / / / / / / /1IJ 	 	 	DD	s$   B9 AB,,B03B09 CCN)__name__
__module____qualname__r  r*   r+   r,   r  r    s#        / / / / /r+   r  c                  ,    e Zd Zd Zd Zd Zd Zd ZdS )SQLiteMixInc                *    t          j        d          S r   rr   ri   r  s    r,   ri   zSQLiteMixIn.connect  s    z***r+   c                    |                     dt          j        |                      |                                 d S NzDROP TABLE IF EXISTS rK   r   _get_valid_sqlite_namecommitr  r   rB   s      r,   r  zSQLiteMixIn.drop_table  s9    US-G
-S-SUUVVVr+   c                h    |                     d          }d |                                D             S )Nz1SELECT name FROM sqlite_master WHERE type='table'c                    g | ]
}|d          S r   r*   )r^   rc  s     r,   ra   z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>  s    333Ua333r+   rK   fetchallr  rB   r  s      r,   r  zSQLiteMixIn._get_all_tables  s0    LLLMM33ajjll3333r+   c                    |                     dt          j        |                      |                                 d S NzDROP VIEW IF EXISTS r  )r  r  rB   s      r,   r  zSQLiteMixIn.drop_view  s9    SC,Fy,Q,QSSTTTr+   c                h    |                     d          }d |                                D             S )Nz0SELECT name FROM sqlite_master WHERE type='view'c                    g | ]
}|d          S r  r*   )r^   r  s     r,   ra   z.SQLiteMixIn._get_all_views.<locals>.<listcomp>   s    111DQ111r+   r  r  s      r,   r  zSQLiteMixIn._get_all_views  s0    LLKLL11AJJLL1111r+   N)r  r  r  ri   r  r  r  r  r*   r+   r,   r  r    s_        + + +  4 4 4  2 2 2 2 2r+   r  c                  B    e Zd Zed             Zd Zd Zd Zd Zd Z	dS )SQLAlchemyMixInc                8    | j                                          d S r   )r   r   clss    r,   teardown_classzSQLAlchemyMixIn.teardown_class$  s    
r+   c                4    | j                                         S r   )r   ri   r  s    r,   ri   zSQLAlchemyMixIn.connect(  s    {""$$$r+   c                "   |                                 r&|                                                                 |                                5  t	          j        |                              |           d d d            d S # 1 swxY w Y   d S r   )in_transactionget_transactionrollbackrj   r   r   r  r  s      r,   r  zSQLAlchemyMixIn.drop_table+  s       	.  ""++---ZZ\\ 	9 	9OD!!,,Z888	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9 	9s   (BBBc                H    ddl m}  ||                                          S Nr   r   )r>   r   get_table_namesr  rB   r   s      r,   r  zSQLAlchemyMixIn._get_all_tables1  s.    &&&&&&wt}},,...r+   c                L   |j         j        j                            |          }|                                r&|                                                                 |                                5  |                    d|            d d d            d S # 1 swxY w Y   d S r  )	r   r.   identifier_preparerquote_identifierr  r  r  rj   r   )r  r  rB   quoted_views       r,   r  zSQLAlchemyMixIn.drop_view6  s    k)=NN
 
    	.  ""++---ZZ\\ 	G 	G  !E!E!EFFF	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	Gs   3BB Bc                H    ddl m}  ||                                          S r   )r>   r   get_view_namesr  s      r,   r  zSQLAlchemyMixIn._get_all_views?  s.    &&&&&&wt}}++---r+   N)
r  r  r  classmethodr  ri   r  r  r  r  r*   r+   r,   r  r  #  s{          [% % %9 9 9/ / /
G G G. . . . .r+   r  c                  V    e Zd ZdZd Zd Zd Zd Zd Zd Z	dd	Z
d
 Zd Zd Zd ZdS )PandasSQLTestzT
    Base class with common private methods for SQLAlchemy and fallback cases.

    c                    |                      d| j                   t          | j        t          j                  rt          | j        |           d S t          | j        || j                   d S rZ  )r  rB   rh   rr   rs   rT   rn   flavor)r  r   s     r,   load_iris_datazPandasSQLTest.load_iris_dataK  sd    	***di!344 	D(I>>>>> It{CCCCCr+   c                
   | j         dk    r|D ]}|                    d           t          | j        t          j                  r#d |D             }t          | j        |           d S t          | j        || j                    d S )Nr&   r   c                P    g | ]#}t          |                                          $S r*   )tuplerg   )r^   r   s     r,   ra   z1PandasSQLTest.load_types_data.<locals>.<listcomp>W  s(    HHHE%//HHHr+   )r  r   rh   rB   rr   rs   r   r   )r  r   r   s      r,   load_types_datazPandasSQLTest.load_types_dataR  s    ;,&&# + +		/****di!344 	FHHZHHHJ)$)Z@@@@@!$)ZEEEEEr+   c                    |d         | j                  }d}| j                            ||          }t          |           d S )Nr'   rS  rO  r  r/  
read_queryr   r  r-   rW  rm   rC  s        r,   _read_sql_iris_parameterz&PandasSQLTest._read_sql_iris_parameter\  sG    -.t{;%^..uV.DD
$$$$$r+   c                    |d         | j                  }ddd}| j                            ||          }t          |           d S )Nr(   r   r   rM  rO  r  r  s        r,   _read_sql_iris_named_parameterz,PandasSQLTest._read_sql_iris_named_parameterb  sO    34T[A'377^..uV.DD
$$$$$r+   c                    |d         | j                  }| j                            |d           }t          |           d S )Nr)   rO  r  )r  r-   rW  rC  s       r,   (_read_sql_iris_no_parameter_with_percentz6PandasSQLTest._read_sql_iris_no_parameter_with_percenth  sB    =>t{K^..uT.BB
$$$$$r+   c                    |                      d| j                   | j                            |j        d d         d          dk    sJ d S )Nr   r   )r  rB   r/  r  r   r  r   s     r,   _to_sql_emptyzPandasSQLTest._to_sql_emptym  sM    ty111~$$[%5bqb%9=IIQNNNNNNr+   autoc                :   |                      d| j                    | j        j        |dfd|i|dk    sJ | j                            d          sJ t          |          }t          | j        d          }||k    sJ |                      d| j                   dS )z `to_sql` with the `engine` paramr   r      N)r  rB   r/  r  r   r.  r   )r  r   r   engine_kwargsnum_entriesnum_rowss         r,   _to_sql_with_sql_enginez%PandasSQLTest._to_sql_with_sql_engineq  s     	ty111 "DN!] 39=J     
 ~''66666+&&di77;&&&& 	ty11111r+   c                "   |                      d| j                   | j                            |d          dk    sJ | j                            d          }|                    dd           d |j        _        t          j	        ||           d S )Ntest_frame_roundtripr"  "SELECT * FROM test_frame_roundtriplevel_0Tinplace)
r  rB   r/  r  r  	set_indexr   r   r   rv  r  r   r  s      r,   
_roundtripzPandasSQLTest._roundtrip  s    .	:::~$$[2HIIQNNNN**+OPPD111 !
fk22222r+   c                    | j                             d          }|                                }t          j        |g d           d S Nr>  r   )r/  rK   r   r   r   )r  iris_resultsr_   s      r,   _execute_sqlzPandasSQLTest._execute_sql  sI    ~--.BCC##%%
AAABBBBBr+   c                    t          j        ddgg ddg          }| j                            |d          dk    sJ |                     d          }|dggk    sJ d S )Nr   r  line1r1        ?line2r   r   r   r   r   r   test_to_sql_saves_indexr1  )r   from_recordsr/  r  _get_index_columns)r  r   ix_colss      r,   _to_sql_save_indexz PandasSQLTest._to_sql_save_index  s    # 12OOOTWSX
 
 
 ~$$R)BCCqHHHH))*CDDC5'!!!!!!r+   c                   | j                                         5 }d}t          | j         t                    r|                    |           n&ddlm}  ||          }|                    |           d d d            n# 1 swxY w Y    G d dt                    }d}t          | j         t                    rddlm}  ||          }	 | j                                         5 }|                    |            |d          # 1 swxY w Y   n# |$ r Y nw xY w| j         	                    d          }t          |          dk    sJ | j                                         5 }|                    |           d d d            n# 1 swxY w Y   | j         	                    d          }t          |          d	k    sJ d S )
Nz'CREATE TABLE test_trans (A INT, B TEXT)r   rp   c                      e Zd ZdS )7PandasSQLTest._transaction_test.<locals>.DummyExceptionN)r  r  r  r*   r+   r,   DummyExceptionrC    s        Dr+   rD  z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')errorzSELECT * FROM test_transr   )r/  run_transactionrh   r   rK   r>   rq   	Exceptionr   r  r.  )r  transrR   rq   rD  ins_sqlresrz  s           r,   _transaction_testzPandasSQLTest._transaction_test  s   ^++-- 	$<D$..99 $d####++++++tDzzd###	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$ 	$	 	 	 	 	Y 	 	 	 Ddnk22 	$''''''d7mmG	//11 .Ug&&&$nW---. . . . . . . . .  	 	 	D	 n''(BCC3xx1}}}} ^++-- 	#MM'"""	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	# 	#~(()CDD4yyA~~~~~~sT   AA??BBD $!DD		D D	D DD$FF
F
N)r   )r  r  r  __doc__r  r  r  r  r  r  r&  r/  r3  r@  rK  r*   r+   r,   r  r  E  s         
D D DF F F% % %% % %% % %
O O O2 2 2 2(
3 
3 
3C C C" " "" " " " "r+   r  c            
      v   e Zd ZU dZdZded<    ej        d          d             Zd Z	d	 Z
d
 Zd Zd Zd Zd Zd Zd Zd Zd Zd Z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                         Zd Zd Zd Zej                            d g d!          d"             Z d# Z!d$ Z"ej                            d%d&e#e$e#e$d'g          d(             Z%d) Z&d* Z'd+ Z(d, Z)d- Z*d. Z+d/ Z,d0 Z-d1 Z.d2 Z/d&S )3_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/   r5  Tautousec                    |                                  | _        |                     |           |                     |           |                                  d S r   )ri   rB   r  r  load_test_data_and_sqlr  r   r   s      r,   setup_methodz_TestSQLApi.setup_method  sQ    LLNN	I&&&Z(((##%%%%%r+   c                .    t          | j                   d S r   )rt   rB   r  s    r,   rR  z"_TestSQLApi.load_test_data_and_sql  s    !$),,,,,r+   c                X    t          j        d| j                  }t          |           d S )NzSELECT * FROM iris_view)r   r!   rB   r   r  rC  s     r,   test_read_sql_viewz_TestSQLApi.test_read_sql_view  s+    '(A49MM
$$$$$r+   c                    d}t          j        || j        d          }t          j        || j                  }t          j        t          |          |           d S )Nz/SELECT * FROM iris_view WHERE SepalLength < 0.0r   rG  )r   r!   rB   r   rv  r   )r  rW  
with_batchwithout_batchs       r,   &test_read_sql_with_chunksize_no_resultz2_TestSQLApi.test_read_sql_with_chunksize_no_result  sU    A'tyAFFF
*5$)<<
fZ00-@@@@@r+   c                t    t          j        |d| j                   t          j        d| j                  sJ d S )Nr   )r   r  rB   r   r  s     r,   r0  z_TestSQLApi.test_to_sql  s8    
;ty999}]DI6666666r+   c                   t          j        |d| j        d           t          j        d| j                  sJ d}t	          j        t          |          5  t          j        |d| j        d           d d d            d S # 1 swxY w Y   d S )Ntest_frame2r3  r4  z"Table 'test_frame2' already existsr  )r   r  rB   r   r   r9  r:  r  r   r;  s      r,   test_to_sql_failz_TestSQLApi.test_to_sql_fail  s    
;tyFKKKK}]DI666662]:S111 	P 	PJ{M49OOOO	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	Ps   BB	Bc                
   t          j        |d| j        d           t          j        |d| j        d           t          j        d| j                  sJ t	          |          }t          | j        d          }||k    sJ d S )Nr   r3  r4  r  r   r  rB   r   r.  r   r  r   r$  r%  s       r,   test_to_sql_replacez_TestSQLApi.test_to_sql_replace  s    
;tyFKKKK
;tyINNNN}]DI66666+&&di77;&&&&&&r+   c                (   t          j        |d| j        d          dk    sJ t          j        |d| j        d          dk    sJ t          j        d| j                  sJ dt	          |          z  }t          | j        d          }||k    sJ d S )Ntest_frame4r3  r4  r"  r	  r1  rc  rd  s       r,   test_to_sql_appendz_TestSQLApi.test_to_sql_append  s    z+}di6RRRVWWWWW J{M49QQQUVVVVV}]DI66666#k***di77;&&&&&&r+   c                    t          j        |d| j        d           t          j        d| j                  }t	          j        ||           d S )Ntest_frame5Fr   zSELECT * FROM test_frame5)r   r  rB   rA  r   rv  )r  r   r  s      r,   test_to_sql_type_mappingz$_TestSQLApi.test_to_sql_type_mapping  sJ    
;tyFFFF949EE
k622222r+   c                   t          t          j        dd          d          }t          j        |d| j        d           t          j        d	| j                  }t          j        |	                                |           d S )
Nr   r   r  seriesr   test_seriesFrk  zSELECT * FROM test_series)
r   r   aranger   r  rB   r!   r   rv  to_frame)r  ss2s      r,   test_to_sql_seriesz_TestSQLApi.test_to_sql_series  st    29Qg...X>>>
1mTYe<<<< ;TYGG
ajjllB/////r+   c                :   t          j        |d| j                   t          j        d| j                  }|j        |_        |                    dd           |j                            t                     d |j        _        t          j
        ||           d S )Nr(  r  r)  r*  Tr+  )r   r  rB   r!   r   r-  r   r  r   r   rv  r.  s      r,   test_roundtripz_TestSQLApi.test_roundtrip!  s    
; 6DIFFFF#$HdiXXX #(D111C    
fk22222r+   c                    t          j        |d| j        dd           t          j        d| j                  }t	          j        ||           d S )Nr(  Fr1  )r  r   rH  r)  rv  )r   r  rB   r!   r   rv  r.  s      r,   test_roundtrip_chunksizez$_TestSQLApi.test_roundtrip_chunksize,  s_    
"		
 	
 	
 	
 #$HdiXXX
fk22222r+   c                    t          j        | j                  5 }|                    d          }d d d            n# 1 swxY w Y   |                                }t          j        |g d           d S r1  )r   r    rB   rK   r   r   r   )r  
pandas_sqlr2  r_   s       r,   test_execute_sqlz_TestSQLApi.test_execute_sql7  s    "49-- 	D%--.BCCL	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D##%%
AAABBBBBs   <A A c           
        t          j        d| j                  }t          |j        j        j        t          j                  rJ t          j        d| j        dg          }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          }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
g          }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          }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          }t          |j        j        j        t          j                  sJ |j        	                                t          d          t          d          gk    sJ d S )NSELECT * FROM typesr|   parse_datesi  r   ro  r   r"  %Y-%m-%d %H:%M:%Sr}   i        i  rr  r~   z%Y%m%dz
2010-10-10z
2010-12-12)r   r!   rB   r   r|   r?   r   r   
datetime64tolistr   r}   r~   r  r   s     r,   test_date_parsingz_TestSQLApi.test_date_parsing>  s?     5tyAAbj.3R]CCCCC!499+
 
 
 "**/?????z  ""dAq!Q**dAq!Q**'
 
 
 
 

 !I"$78
 
 

 "**/?????z  ""dAq!Q**dAq!Q**'
 
 
 
 

 !49<.
 
 
 "--2BMBBBBB}##%%dBAq!,,dAq!Q***
 
 
 
 

 !49<:M
 
 
 "--2BMBBBBB}##%%dBAq!,,dAq!Q***
 
 
 
 

 !I)84
 
 

 "+16FFFFF ''))l##l##.
 
 
 
 
 
 
r+   rE  )ignoreraisecoercezread_sql, text, moder~  )r>   fallbackrz   r>   c                    | j         |v rE|                    ddi          } ||| j        dd|ii          }t          j        ||           d S d S )Nr|   datetime64[ns]errors)r  r  )r5  r   rB   r   rv  )r  rA  rq   r5  rE  r   r  r  s           r,   test_custom_dateparsing_errorz)_TestSQLApi.test_custom_dateparsing_erroru  sz    " 9'..	;K/LMMHXI%0  F !&(33333 r+   c                    t          j        d| j        dddg          }t          |j        j        j        t          j                  sJ t          |j	        j        j        t          j                  sJ d S )Nr~  r|   r}   )	index_colr  )
r   r!   rB   r   r   r?   r   r   r  r}   r  s     r,   test_date_and_indexz_TestSQLApi.test_date_and_index  su     !I"L1	
 
 
 "(.-r}====="--2BMBBBBBBBr+   c                   t          t          ddgd                                                    }t          j        t
                    5  |                    d| j                  }d d d            n# 1 swxY w Y   |dk    sJ t          j	        d| j                  }t          j
        |d         |d                             d	                     d S )
Nz00:00:01z00:00:03foor   test_timedeltar   r  r1  zSELECT * FROM test_timedeltar   )r   r   rq  r   r  r   r  rB   r   r!   assert_series_equalr  )r  r   r  r  s       r,   r  z_TestSQLApi.test_timedelta  s   &*j!9FFFGGPPRR'44 	K 	K99*:	9JJL	K 	K 	K 	K 	K 	K 	K 	K 	K 	K 	K 	K 	K 	K 	Kq    #$BDINN
ve}binnW.E.EFFFFFs   A55A9<A9c                    t          dddgi          }d}t          j        t          |          5  |                    d| j                  J 	 d d d            d S # 1 swxY w Y   d S )Nr  y      ?      ?y               @zComplex datatypes not supportedr  test_complexrv  r   r   r9  r:  r  rB   )r  r   r;  s      r,   test_complex_raisesz_TestSQLApi.test_complex_raises  s    fb\*++/]:S111 	D 	D99^9;;CCCC	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	Ds   AA"%A"zindex_name,index_label,expected))NNr   )Nother_labelr  )
index_nameNr  )r  r  r  )r   N0)Nr   r  c                    t          dt          d          i          }||j        _        d}t	          j        |d| j        |           t	          j        || j                  }|j        d         |k    sJ d S )Nr  r"  SELECT * FROM test_index_labeltest_index_label)index_labelr   )	r   ranger   r   r   r  rB   r!   r   )r  r  r  r  
temp_framerW  r   s          r,   test_to_sql_index_labelz#_TestSQLApi.test_to_sql_index_label  sz    $ a122
 *
0
:149+VVVV"5$)44}Q8++++++r+   c                x   d}t          dt          d          it          j        ddg                    }t	          j        |d| j                  }||k    sJ t	          j        d| j                  }|j        d         d	k    sJ |j        d
         dk    sJ t	          j        |d| j        dddg          }||k    sJ t	          j        d| j                  }|j        d d         	                                ddgk    sJ ddg|j
        _        t	          j        |d| j        d          }||k    sJ t	          j        d| j                  }|j        d d         	                                ddgk    sJ t	          j        |d| j        dddg          }||k    sJ t	          j        d| j                  }|j        d d         	                                ddgk    sJ d}t          j        t          |          5  t	          j        |d| j        dd           d d d            d S # 1 swxY w Y   d S )Nr"  r  )A0A1)B0B1rk  r  r  r   r*  r   level_1r  r   r   )r  r  r1  r4  r   r   zALength of 'index_label' should match number of levels, which is 2r  )r   r  r   from_productr   r  rB   r!   r   r  r   namesr   r9  r:  )r  expected_row_countr  r  r   r;  s         r,   "test_to_sql_index_label_multiindexz._TestSQLApi.test_to_sql_index_label_multiindex  s   U1XX)<*FGG
 
 

 J(:DIFF+++++"#CTYOO}Q9,,,,}Q9,,,, Ic

 
 
 +++++"#CTYOO}RaR ''))c3Z7777 #&s
*DI
 
 
 +++++"#CTYOO}RaR ''))c3Z7777 Ic

 
 
 +++++"#CTYOO}RaR ''))c3Z7777Q]:S111 	 	J"	#   	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	s   H//H36H3c                    t          j        ddgg dddg          }|                    d| j                   t	          j        d	| j        ddg
          }t          j        ||d           d S )Nr5  r7  r:  r   r   r;  test_multiindex_roundtripr  z'SELECT * FROM test_multiindex_roundtripr  Tcheck_index_type)r   r=  r  rB   r   r!   r   rv  r  r   r  s      r,   r  z%_TestSQLApi.test_multiindex_roundtrip  s    # 12#OO*
 
 
 			2		BBB#5tySRUJ
 
 
 	b&4@@@@@@r+   r?   Nr   r   c                   t          ddgddggddg          }|                    d| j        	          d
k    sJ |                    |          }t	          j        d| j        |          }t          j        ||           d S )N333333?g333333@ffffff@g333333@r   r   r   test_dtype_argumentr  r1  z$SELECT A, B FROM test_dtype_argumentr  r?   )r   r  rB   r   r   r!   r   rv  )r  r?   r   r  r  s        r,   r  z_TestSQLApi.test_dtype_argument  s     c
S#J/#sDDDyy3yCCqHHHH99U###2	
 
 
 	fh/////r+   c                r    t          ddgddggddg          }t          j        |d| j        d	           d S )
Nr   r1  ro  r"  r   r   test_frame_integer_col_namesr  r4  )r   r   r  rB   r  s     r,   test_integer_col_namesz"_TestSQLApi.test_integer_col_names#  sG    AA'!Q888
25tyIVVVVVVr+   c                J    t          j        |d| j                  }d|v sJ d S )Nr  rv  CREATEr   
get_schemarB   r  r   r  s      r,   test_get_schemaz_TestSQLApi.test_get_schema'  s1    ^KTYGGG
:%%%%%%r+   c                L    t          j        |d| j        d          }d|v sJ d S )Nr  pypi)r  r  zCREATE TABLE pypi.r  r  s      r,   test_get_schema_with_schemaz'_TestSQLApi.test_get_schema_with_schema+  s3    ^KTYvVVV
#z111111r+   c                    | 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|v sJ d|v sJ d S )Nr>   r   )ry   INTEGER皙?r  r  皙@rs  r  rt  r  r  )r5  r>   ry   r   r   r  rB   )r  ry   r?   float_framer  s        r,   test_get_schema_dtypesz"_TestSQLApi.test_get_schema_dtypes0  s    9$$******EEEsCjSz B BCC^TYsEl
 
 

 :%%%%J&&&&&&r+   c                    t          ddgddgd          }t          j        |d| j        d          }d	}||v sJ t          j        |d| j        d
dg          }d}||v sJ d S )Nr  r  r  r  )Col1Col2r  r  )r  r   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")r   r   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   r   r  rB   )r  r   r   r  constraint_sentences        r,   test_get_schema_keysz _TestSQLApi.test_get_schema_keys?  s    C:SzBBCC^E6tyvNNN
G"j0000 ^KTYcSVZXXX
I"j000000r+   c                   t          t          j                            d                              d          t          d                    }|                    d| j        d           t          j	        d| j                  }t                      }d	}g d
}t          j	        d| j        d          D ]5}t          ||gd          }t          |          ||         k    sJ |dz  }6t          j        ||           | j        dk    r}t                      }d	}g d
}t          j        d| j        d          D ]5}t          ||gd          }t          |          ||         k    sJ |dz  }6t          j        ||           d S d S )Nr1  )   r   abcder   test_chunksizeFru  zselect * from test_chunksizer   )r   r   r   r   r1  r   rG  Tignore_indexr   r>   )r   r   randomdefault_rngstandard_normalr  r  rB   r   r!   r   r.  r   rv  r5  r"   )r  r   ry  rz  isizeschunkres3s           r,   test_chunksize_readz_TestSQLApi.test_chunksize_readJ  s   I!!!$$44W==tG}}
 
 
 			'TYe	DDD !"@$)LL {{'*DI
 
 
 	 	E 4-d;;;Du::q))))FAA
dD))) 9$$;;DA#OOE+,<diSTUUU  tUm$???5zzU1X----Q!$----- %$r+   c                .   t          g dg dd          }|                                }|d                             d          |d<   |                    d| j        d           t          j        d	| j                  }t          j        ||           d S )
Nrn  )John P. Doez	Jane Dover  )	person_idperson_namer  categorytest_categoricalFru  zSELECT * FROM test_categorical)	r   copyr   r  rB   r   r!   r   rv  )r  r   df2rJ  s       r,   r  z_TestSQLApi.test_categoricaln  s     &YYJJJ 
 
 ggii /66zBBM

*	
GGG !A49MM
c2&&&&&r+   c                r    t          ddgddggddg          }|                    d| j        d	
           d S )Nr   r1  ro  r"     ért  r   test_unicodeFru  )r   r  rB   r  s     r,   test_unicode_column_namez$_TestSQLApi.test_unicode_column_name  sE    AA'&#???
		~49E	BBBBBr+   c                    t          g ddt          j        dgd          }|                    d| j        d           t          j        d| j                  }t          j        ||           d S )	Nr   r   r1  r   r  r  zd1187b08-4943-4c8d-a7f6Fru  z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r   nanr  rB   r   r!   r   rv  r  r   rJ  s      r,   test_escaped_table_namez#_TestSQLApi.test_escaped_table_name  sm    YYYc263-?@@AA
		0diu	MMM !JDIVV
c2&&&&&r+   c                   t          g dg ddd          }|                    d| j        d           t          j        d| j                  }t          g d	g d
g dgg d          }t          j        ||           d S )Nrn  rp  r   r  rt  r  
test_tableFru  z*SELECT a, b, a +1 as a, c FROM test_table;)r   rq  r1  r   )r1  r   ro  r   )ro  rr  r"  r   )r  rt  r  r  r   )r   r  rB   r  rA  r   rv  )r  r   r  r  s       r,   test_read_sql_duplicate_columnsz+_TestSQLApi.test_read_sql_duplicate_columns  s    YYY___1EEFF
		|%	@@@I49UU^^^^^^^^<(((
 
 
 	fh/////r+   )0r  r  r  rL  r  __annotations__r   fixturerT  rR  rX  r\  r0  ra  re  rh  rl  rt  rw  ry  r|  r  markparametrizer   rA  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,   rN  rN    s@           FIIIV^D!!!& & "!&- - -% % %A A A7 7 7P P P	' 	' 	'' ' '3 3 30 0 0	3 	3 	3	3 	3 	3C C C5
 5
 5
n [W&C&C&CDD[\02LM\7\3"%*
 <9		
 4 4  ED4 C C CG G GD D D [)	
 	
 	
 ", ,# ",7 7 7rA A A [E""		
 
0 
0 
0W W W& & &2 2 2
' ' '	1 	1 	1". ". ".H' ' '"C C C
' ' '
0 
0 
0 
0 
0r+   rN  zSQLAlchemy not installedreasonc                     e Zd ZdZdZdZed             Zd Zd Z	d Z
d Zd	 Zd
 Zd Zej                            ddddddddddddddde ej        e          j        dk    rdndfg          d             Zej                            dd d!g          d"             Zd# Z ej        d$          d%             Zd& Zd' Zd( Zd)S )*
TestSQLApiz
    Test the public API as it would be used directly

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

    r$   r>   c                8    t          j        d          | _        d S Nzsqlite:///:memory:r>   r   r   r  s    r,   setup_classzTestSQLApi.setup_class      -.BCC


r+   c                    t          j        |d| j                   ddg}t          j        d| j        |          }|j                                        |k    sJ d S )Nr+  r   r   r   )r   r  rB   r"   r   r  )r  r   colsr  s       r,   test_read_table_columnsz"TestSQLApi.test_read_table_columns  s_    
;di888Sz#L$)TJJJ~$$&&$......r+   c                   t          j        |d| j                   t          j        d| j        d          }|j        j        dgk    sJ t          j        d| j        ddg          }|j        j        ddgk    sJ t          j        d| j        ddgddg          }|j        j        ddgk    sJ |j                                        ddgk    sJ d S )	Nr+  r   r  r   r   r   r   )r  r   )r   r  rB   r"   r   r  r   r  r.  s      r,   test_read_table_index_colz$TestSQLApi.test_read_table_index_col  s    
;di888#L$)wOOO|!gY....#L$)SzRRR|!c3Z////#$)SzC:
 
 
 |!c3Z////~$$&&3*444444r+   c                *   t          j        d| j                  }t          j        d| j                  }t	          j        ||           t          j        d| j                  }t          j        d| j                  }t	          j        ||           d S )Nr>  r7   )r   r!   rB   rA  r   rv  r"   )r  iris_frame1iris_frame2s      r,   test_read_sql_delegatez!TestSQLApi.test_read_sql_delegate  s~    ()=tyIIl#7CC
k;777(;;l64955
k;77777r+   c                   ddl m} ddlm}  |d           |d          g}|D ]}t	          | j        |          rs| j                                        5 }|                                5  |                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   | j                                        5  | j                            |           d d d            n# 1 swxY w Y   t          j
        d           5  t          j        d| j                   t          j        d| j                   d d d            d S # 1 swxY w Y   d S )Nr   rp   rX   z,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);other_tablezSELECT * FROM other_table)r>   rq   rf   rY   rh   rB   ri   rj   rK   r   r  r   r"   r!   )r  rq   rY   
query_listrW  rB   s         r,   test_not_reflect_all_tablesz&TestSQLApi.test_not_reflect_all_tables  s   ######,,,,,, D?@@DCDD

   	- 	-E$)V,, -Y&&(( ,D , ,U+++, , , , , , , , , , , , , , ,, , , , , , , , , , , , , , , Y__&& - -I%%e,,,- - - - - - - - - - - - - - - '-- 	G 	G}di888:DIFFF	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	GsZ   B#*B B#BB#BB##B'	*B'	C//C3	6C3	5EEEc                X   t          j        t          d          5  t          j        | j                                      dd           d d d            n# 1 swxY w Y   t          j        d           5  |                    d| j                   d d d            d S # 1 swxY 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  TABLE1 CaseSensitiver  )r   r  r   r   r   rB   check_case_sensitiver  r  s     r,   (test_warning_case_insensitive_table_namez3TestSQLApi.test_warning_case_insensitive_table_name  sS   'N
 
 
 	J 	J ODI&&;;HbIII	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J '-- 	D 	DOCCC	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	Ds#   .AAA5BB#&B#c                    ddl m} |j                            | j                  }|                    d          }d |D             }|S )Nr   )
reflectiontest_index_savedc                    g | ]
}|d          S column_namesr*   r^   r  s     r,   ra   z1TestSQLApi._get_index_columns.<locals>.<listcomp>      ...Qq ...r+   )rf   r  	Inspectorfrom_enginerB   get_indexes)r  tbl_namer  r   ixss        r,   r>  zTestSQLApi._get_index_columns  sX    000000#//	::122..#...
r+   c                   ddl m} t          dt          ddgd          i          }t	          j        | j                  }t	          j        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   )r>   r$  r   r   r   r   rB   SQLTablerh   rc  r  r   )r  r$  r   rV  rc  s        r,   test_sqlalchemy_type_mappingz'TestSQLApi.test_sqlalchemy_type_mapping  s    (((((( ["46H!ItTTTU
 
 _TY''["B777%+-/4i@@@@@@@r+   zinteger, expected)int8SMALLINT)Int8r.  )uint8r.  )UInt8r.  )int16r.  )Int16r.  )uint16r  )UInt16r  )int32r  )Int32r  )uint32BIGINT)UInt32r9  )r   r9  )Int64r9  r   r9  r  c                    t          ddgdg|          }t          j        | j                  }t          j        d||          }t          |j        j        j        j	                  }||k    sJ d S )Nr   r   r  r   r?   r)  r*  )
r   r   r   rB   r+  r/   rc  r  r  r   )r  rk  r  r   rV  rc  r  s          r,   test_sqlalchemy_integer_mappingz*TestSQLApi.test_sqlalchemy_integer_mapping  sr    , 1vuG<<<_TY''["B777U[]_)**!!!!!!r+   rk  uint64UInt64c                    t          ddgdg|          }t          j        | j                  }t	          j        t          d          5  t          j        d||           d d d            d S # 1 swxY w Y   d S )	Nr   r   r  r=  z1Unsigned 64 bit integer datatype is not supportedr  r)  r*  )r   r   r   rB   r   r9  r:  r+  )r  rk  r   rV  s       r,   (test_sqlalchemy_integer_overload_mappingz3TestSQLApi.test_sqlalchemy_integer_overload_mapping#  s     1vuG<<<_TY'']Q
 
 
 	4 	4 Lb3333	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4 	4s   
A//A36A3c                   t          j                    5 }d|z   }d}|                    ||dd           t          j        ||          }t          j        ||          }d}t          j        ||          }d d d            n# 1 swxY w Y   t          j        ||           t          j        ||           t          j        ||           d S )Nr   r7   r  Fr
  r>  )r   r   r  r   rA  r"   r!   rv  )	r  r   r   db_urirc  r_  r   rW  rg  s	            r,   test_database_uri_stringz#TestSQLApi.test_database_uri_string-  s   
 _ 	<$!D(FEEvRWXXX,uf55K,UF;;K(E,UF;;K	< 	< 	< 	< 	< 	< 	< 	< 	< 	< 	< 	< 	< 	< 	< 	k;777
k;777
k;77777s   A"BB	Bpg8000c                    d}t          j        t          d          5  t          j        d|           d d d            d S # 1 swxY w Y   d S )Nz)postgresql+pg8000://user:pass@host/dbnamerF  r  zselect * from tabler   r9  ImportErrorr   rA  )r  rD  s     r,   (test_pg8000_sqlalchemy_passthrough_errorz3TestSQLApi.test_pg8000_sqlalchemy_passthrough_error>  s     =];h777 	8 	8L.777	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8 	8s   AAAc                    ddl m}  |d          }t          j        || j        ddi          }t          |d                   }|dhk    sJ d S )Nr   rp   z#select * from iris where name=:namer   zIris-versicolorrO  r<   )r>   rq   r   rA  rB   set)r  rq   	name_textiris_df	all_namess        r,   test_query_by_text_objz!TestSQLApi.test_query_by_text_objF  sn    ######D>??	,y$)VEV<WXXX((	.///////r+   c                    ddl m}m} t          | j                  } ||                              |j        j         |d          k              }t          j	        || j
        ddi          }t          |d                   }|dhk    sJ d S )Nr   )	bindparamrK  r   r   rO  r<   )r>   rR  rK  rA   r  wherer  r<   r   rA  rB   rL  )r  rR  rK  r7   name_selectrN  rO  s          r,   test_query_by_select_objz#TestSQLApi.test_query_by_select_objO  s    	
 	
 	
 	
 	
 	
 	
 	

 #4;//fTll((		&8I8I)IJJ,{DIv}>UVVV((	]O++++++r+   c                    t          g dg dd          }|                    d| j        d           t          j        d| j                  }t          j        ||           d S )Nr  ro  r"  r   )r   z%_variationtest_column_percentageFru  r   r  rB   r   r"   r   rv  r  s      r,   test_column_with_percentagez&TestSQLApi.test_column_with_percentage\  sh    YYYyyyAABB
		/TYe	LLL !949EE
c2&&&&&r+   N)r  r  r  rL  r  r5  r
  r  r  r  r  r  r  r>  r,  r   r  r  r  r   r?   r   r>  rB  rE  tdskip_if_installedrJ  rP  rU  rZ  r*   r+   r,   r  r    s         FDD D [D/ / /5 5 5 8 8 8G G G,D D D   
A 
A 
A [  !!!!!!    hbhsmm0G;;((K	
 (" ") (" [Y8(<==4 4 >=48 8 8" R(##8 8 $#80 0 0, , ,' ' ' ' 'r+   r  c                      e Zd ZdZdZdZddZd Zej	        
                    ed          d	             Zej	        
                    ed          d
             Zd Zd Zd Zd ZdS )TestSQLiteFallbackApiz9
    Test the public sqlite connection fallback API

    r$   r  r   c                *    t          j        |          S r   r  )r  databases     r,   ri   zTestSQLiteFallbackApi.connecto  s    x(((r+   c                   t          j                    5 }t          |                     |                    5 }t	          j        |d|d          dk    sJ 	 d d d            n# 1 swxY w Y   t          |                     |                    5 }t	          j        d|          }d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   t          j        ||           d S )Ntest_frame3_legacyFrk  r"  z!SELECT * FROM test_frame3_legacy;)r   r   r   ri   r   r  r!   rv  )r  r   r   rB   r  s        r,   test_sql_open_closez)TestSQLiteFallbackApi.test_sql_open_closer  s    _ 	W$d++,, J{,@$eTTT                   d++,, W+,OQUVVW W W W W W W W W W W W W W W	W 	W 	W 	W 	W 	W 	W 	W 	W 	W 	W 	W 	W 	W 	W 	k622222sX   #C	A#C	#A'	'C	*A'	+%C	B2&C	2B6	6C	9B6	:C		CCzSQLAlchemy is installedr  c                    d}d}t          j        t          |          5  t          j        d|           d d d            d S # 1 swxY w Y   d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installedr  r>  rH  )r  rB   r;  s      r,   test_con_string_import_errorz2TestSQLiteFallbackApi.test_con_string_import_error  s    .=];c222 	5 	5L-t444	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5s   AA
Ac                    G d d          }t          j         |d                    5 }t          j        t                    5  t          j        d|           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S )Nc                  "    e Zd ZddZd Zd ZdS )~TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnectionreturnNonec                2    t          j        |i || _        d S r   )rr   rs   rB   )r  argskwargss      r,   __init__zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__init__  s    #.???			r+   c                ,    t          | j        |          S r   )getattrrB   )r  r   s     r,   __getattr__zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__getattr__  s    ty$///r+   c                8    | j                                          d S r   )rB   r  r  s    r,   r  zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.close  s    	!!!!!r+   N)ri  rj  )r  r  r  rn  rq  r  r*   r+   r,   MockSqliteConnectionrh    sJ        @ @ @ @0 0 0" " " " "r+   rs  r   zSELECT 1)r  r   r   r  r   r   rA  )r  rs  rB   s      r,   Jtest_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installedz`TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed  s"   	" 	" 	" 	" 	" 	" 	" 	"  4 4Z @ @AA 	/T+K88 / /Z.../ / / / / / / / / / / / / / /	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/s5   A?A'A?'A+	+A?.A+	/A??BBc                B   t          j        d| j                  }t          j        d| j                  }t	          j        ||           d}t          j        t           j        |          5  t          j        d| j                   d d d            d S # 1 swxY w Y   d S )Nr>  z9Execution failed on sql 'iris': near "iris": syntax errorr  r7   )	r   r!   rB   rA  r   rv  r   r9  DatabaseError)r  r	  r
  r;  s       r,   r  z,TestSQLiteFallbackApi.test_read_sql_delegate  s    ()=tyIIl#7CC
k;777K]3,C888 	, 	,L+++	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	, 	,s   ,BBBc                <    t          j        |d          }d|v sJ d S )Nr  r  )r   r  r  s      r,   test_get_schema2z&TestSQLiteFallbackApi.test_get_schema2  s*    ^K88
:%%%%%%r+   c                    |                     d          D ]O}|                                 d                             d          |k    r|                                 d         c S Pt          d| d          )N
r   r  r   zColumn 
 not found)splitstripr:  )r  r  columncols       r,   _get_sqlite_column_typez-TestSQLiteFallbackApi._get_sqlite_column_type  sy    <<%% 	& 	&Cyy{{1~##C((F22yy{{1~%%% 356555666r+   c                
   t          dt          ddgd          i          }t          j        | j                  }t          j        d||          }|                                }|                     |d          dk    sJ d S )	Nr   r%  r&  Tr'  r)  r*  r$  )r   r   r   r   rB   SQLiteTable
sql_schemar  )r  r   rV  rc  r  s        r,   test_sqlite_type_mappingz.TestSQLiteFallbackApi.test_sqlite_type_mapping  s    ["46H!ItTTTU
 
 	**Rr:::!!##++FF;;{JJJJJJr+   N)r   )r  r  r  rL  r  r5  ri   rc  r   r  skipifSQLALCHEMY_INSTALLEDre  rt  r  rx  r  r  r*   r+   r,   r^  r^  f  s         
 FD) ) ) )3 3 3  [,5NOO5 5 PO5 [,5NOO/ / PO/", , ,& & &
7 7 7K K K K Kr+   r^  c                     e Zd ZU dZded<   ed             Z ej        d          d             Z	ed             Z
ed	             Zd
 Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Z d Z!d  Z"d! Z#d" Z$d# Z%d$ Z&d% Z'd& Z(d' Z)d( Z*d) Z+d* Z,d+ Z-ej.        /                    d,d-e0j1        gid-e0j1         gie0j1         gd.gd/g          d0             Z2d1 Z3d2 Z4d3 Z5d4 Z6d5 Z7d6 Z8ej.        /                    d7d8d9g          d:             Z9ej.        /                    d7d8d;g          d<             Z:ej.        /                    d7g d=          d>             Z;dHdAZ<dHdBZ=dC Z>ej.        /                    dDe?j@        dEg          ej.        /                    d7d8d9g          dF                         ZAdGS )I_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                V    |                                   |                                  d S r   )setup_driversetup_enginer  s    r,   r  z_TestSQLAlchemy.setup_class  s,    r+   TrO  c                D   	 | j                                         | _        t          j        | j                  | _        n7# t          j        j        $ r  t          j
        d| j         d           Y nw xY w|                     |           |                     |           d S )NzCan't connect to z server)r   ri   rB   r   r   r/  r>   r  r  r   skipr  r  r  rS  s      r,   rT  z_TestSQLAlchemy.setup_method  s    	B++--DI _TY77DNN~. 	B 	B 	BK@DK@@@AAAAA	BI&&&Z(((((s   <? 1A32A3c                    t                      r   NotImplementedErrorr  s    r,   r  z_TestSQLAlchemy.setup_driver      !###r+   c                    t                      r   r  r  s    r,   r  z_TestSQLAlchemy.setup_engine  r  r+   c                0    |                      |           d S r   r  r  r-   s     r,   test_read_sql_parameterz'_TestSQLAlchemy.test_read_sql_parameter      %%k22222r+   c                0    |                      |           d S r   r  r  s     r,   test_read_sql_named_parameterz-_TestSQLAlchemy.test_read_sql_named_parameter      ++K88888r+   c                0    |                      |           d S r   r  r  s     r,   test_to_sql_emptyz!_TestSQLAlchemy.test_to_sql_empty      ;'''''r+   c                   ddl m} |                                 }t          g dg dd          }t	          j        |d          5 }|                    |d          d	k    sJ 	 d d d            n# 1 swxY w Y    ||          }|                    d          sJ t	          j        |d          5 }|                    d           d d d            d S # 1 swxY w Y   d S )
Nr   r        ?       @      @      @r  r  r  r  onetwoTr)  r  r"  )	r>   r   ri   r   r   r   r  r   r  r  r   	temp_connr  r/  r   s         r,   test_create_tablez!_TestSQLAlchemy.test_create_table  s   &&&&&&LLNN	(((1E1E1EFF
 

 _Y>>> 	C)##J==BBBBB	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C wy!!~~l+++++ _Y>>> 	/)  ...	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/s$   A11A58A54CCCc                   ddl m} |                                 }t          g dg dd          }t	          j        |          }|                    |d          dk    sJ  ||          }|                    d          sJ |                    d           	 |	                                 n# t          $ r Y nw xY w|                    d          rJ d S )Nr   r  r  r  r  r  r"  )r>   r   ri   r   r   r   r  r   r  clear_cacheAttributeErrorr  s         r,   test_drop_tablez_TestSQLAlchemy.test_drop_table  s   &&&&&&LLNN	(((1E1E1EFF
 

 OI..	
L99Q>>>>wy!!~~l+++++\***	 	 	 	D	>>,///////s   B. .
B;:B;c                0    |                      |           d S r   r/  r  s     r,   rw  z_TestSQLAlchemy.test_roundtrip      $$$$$r+   c                .    |                                   d S r   r3  r  s    r,   r|  z _TestSQLAlchemy.test_execute_sql      r+   c                Z    t          j        d| j                  }t          |           d S )Nr7   rv  )r   r"   rB   r   rW  s     r,   test_read_tablez_TestSQLAlchemy.test_read_table  s-    'DI>>>
$$$$$r+   c                    t          j        d| j        ddg          }t          j        |j        j        ddg           d S )Nr7   r8   )r  r   )r   r"   rB   r   r   r   rg   rW  s     r,   r  z'_TestSQLAlchemy.test_read_table_columns  sM    '	M=+I
 
 

 	+2]M4RSSSSSr+   c                    d}t          j        t          |          5  t          j        d| j                   d d d            d S # 1 swxY w Y   d S )Nz!Table this_doesnt_exist not foundr  this_doesnt_existrv  )r   r9  r:  r   r"   rB   )r  r;  s     r,   test_read_table_absent_raisesz-_TestSQLAlchemy.test_read_table_absent_raises  s    1]:S111 	C 	C2	BBBB	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	C 	Cs   AAAc                   t          j        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                  sJ t          |j        j        j        t                    sJ d S rj  )r   r"   rB   r   r   r?   r   r   r   r   rk  r   bool_r   r   objectr  s     r,   rl  z,_TestSQLAlchemy.test_default_type_conversion$  s    33"++0"+>>>>>")/.
;;;;;"**/::::: "+16DDDDD",27@@@@@@@r+   c                    t          ddgi          }|                    d| j        d          dk    sJ t          j        d| j                  }t          j        ||           d S )Ni64l            )r   test_bigintFru  r   rY  r  s      r,   r  z_TestSQLAlchemy.test_bigint0  sk    UUG,---yym%yHHAMMMM#M49==
b&)))))r+   c                    t          j        d| j                  }t          |j        j        j        t          j                  sJ d S rj  	r   r"   rB   r   r|   r?   r   r   r  r  s     r,   test_default_date_loadz&_TestSQLAlchemy.test_default_date_load8  s>    33 "**/???????r+   c           	     f   d }t          d| j                  }t          |d          s8|j                            t
          j                            d                     |j        }t          |j
        t                    sJ t          d| j        dg          }t          |d          s8|j                            t
          j                            d                     |j        }t          |j
        t                    sJ t          |j        j                  dk    sJ  ||j                   t          t!          t          d| j        d	                    d
          }|j        }t          |j
        t                    sJ t          |j        j                  dk    sJ t#          j        d| j                  }|j        }t          |j
        t                    sJ t'          j        |j        |j                   t#          j        d| j                  } ||j                   d S )Nc                   t          j        | j        d          r8| d         t          d          k    sJ | d         t          d          k    sJ d S t	          | j        t
                    rnt          | j        j                  dk    sJ t          dd          t          dd          g}t          || j
                  }t          j        | |           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   is_np_dtyper?   r   rh   r   r/   dtr  r   r   r   r  AssertionError)r  expected_datar  s      r,   rf  z:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkD  s
    sy#..  1v+@!A!AAAAA 1v+@!A!AAAAAAACI77 369~~.... 3>>>3>>>! "-ch???&sH55555 %HSYHH  r+   zselect * from typesr   z&no column with datetime with time zoner  r  r  r   rG  Tr  rz   )r!   rB   r  node
add_markerr   r  xfailr   rh   r?   r   r/   r  r  r   r  r   r"   r   r  )r  r  rf  r   r  r  s         r,   test_datetime_with_timezonez+_TestSQLAlchemy.test_datetime_with_timezone?  s-   
	 	 	B 149==r?++ 	L##!!)Q!RR   #)_55555!49?:K
 
 
 r?++ 	L##!!)Q!RR   #)_55555369~~&&&&b 5tyANNNOO
 
 
 #)_55555369~~&&&&%gty99$#)_55555
r/1GHHH 33br+   c                x   t          dt          ddd          i          }|                    d| j        d          dk    sJ | j        d	k    r$|d         j                            d
          |d<   n#|d         j                            d           |d<   t          j	        d| j                  }t          j        ||           t          j        d| j                  }| j        dk    r:t          |j        d         t                    sJ t!          |d                   |d<   t          j        ||           d S )Nr   2013-01-01 09:00:00ro  
US/Pacificperiodsr  test_datetime_tzFru  r&   r  zSELECT * FROM test_datetime_tzr$   r   r   )r   r   r  rB   r  r  
tz_converttz_localizer   r"   r   rv  r!   rh   locr/   r   )r  r  r  s      r,   %test_datetime_with_timezone_roundtripz5_TestSQLAlchemy.test_datetime_with_timezone_roundtrip  s6   
 *2A,OOOP
 
 $6DIUSSWXXXXX;,&&$SM,77>>HSMM %SM,88>>HSM#$6	BB
fh///#$DdiPP;(""fj0#66666%fSk22F3K
fh/////r+   c                *   t          dt          ddd          idg          }|                    d| j        d          dk    sJ t	          j        d| j                  }t          t          j        gdg	          }t          j	        ||           d S )
Nr   i'  r   r   rk  test_datetime_obbFru  r   )
r   r   r  rB   r   r"   r  NaTr   rv  )r  r   r  r  s       r,   test_out_of_bounds_datetimez+_TestSQLAlchemy.test_out_of_bounds_datetime  s    &(4A"6"67sCCC{{ 3%{PPTUUUUU#$7CCbfXx888
fh/////r+   c                <   t          ddd                              d           }t          dt          d          i|          }|                    d| j        d	          dk    sJ t          j        d| j        d
          }t          j	        ||d           d S )Nz
2018-01-01r   6H)r  freqnumsrk  	foo_table	info_date)r   r  r  r  F)check_names)
r   
_with_freqr   r  r  rB   r   r"   r   rv  )r  datesr  r  s       r,   "test_naive_datetimeindex_roundtripz2_TestSQLAlchemy.test_naive_datetimeindex_roundtrip  s     <>>>II$OOfeAhh/u===OO$)OUU    #KkRRR
fhEBBBBBBr+   c                (   t          j        d| j                  }| j        dk    rt          nt
          j        }t          |j        j	        j
        |          sJ t          j        d| j        dg          }t          |j        j	        j
        t
          j                  sJ t          j        d| j        ddi          }t          |j        j	        j
        t
          j                  sJ t          j        d| j        dddii          }t          |j        j	        j
        t
          j                  sJ t          j        d| j        dg          }t          |j        j	        j
        t
          j                  sJ t          j        d| j        ddi          }t          |j        j	        j
        t
          j                  sJ t          j        d| j        dd	dii          }t          |j        j	        j
        t
          j                  sJ d S )
Nrz   r$   r|   r  r  formatr}   rr  unit)r   r"   rB   r  r  r   r  r   r|   r?   r   r}   )r  r   expected_types      r,   r  z!_TestSQLAlchemy.test_date_parsing  s   33"&+"9"9r}"**/?????LLL"**/?????TYY8K,L
 
 
 "**/?????I"X/B$CD
 
 

 "**/?????OOO"--2BMBBBBBs@STTT"--2BMBBBBBTY\FC=,I
 
 
 "--2BMBBBBBBBr+   c                   t          t          dd          t          j        d          d          }|                    d| j                  dk    sJ t          j        d| j                  }|                    dd	
          }t          j
        ||           t          j        d| j                  }|                    dd	
          }| j        dk    rQt          |j        d         t                    sJ t!          |d                   |d<   t          j
        ||           d S t          j
        ||           d S )Nr  ro  r  r  r  test_datetimer  r   r   )axisSELECT * FROM test_datetimer$   r  r   )r   r   r   rp  r  rB   r   r"   rk   r   rv  r!   r  rh   r  r/   r   r  s      r,   r  z_TestSQLAlchemy.test_datetime  s2   2A>>>RYs^^TT
 
 yyo49y==BBBB #OTY??W1--
fb))) #$A49MMW1--;(""fj0#66666%fSk22F3K!&"-----!&"-----r+   c                R   t          t          dd          t          j        d          d          }t          j        |j        d<   |                    d| j        d	          dk    sJ t          j	        d| j                  }t          j        ||           t          j        d
| j                  }| j        dk    rSt          |j        d         t                    sJ t!          |d         d          |d<   t          j        ||           d S t          j        ||           d S )Nr  ro  r  r  r  r   r   r  Fru  r  r$   r  r   r  )r  )r   r   r   rp  r  r  r  rB   r   r"   r   rv  r!   r  rh   r/   r   r  s      r,   test_datetime_NaTz!_TestSQLAlchemy.test_datetime_NaT  s   2A>>>RYs^^TT
 
 vyyo49EyJJaOOOO #OTY??
fb))) #$A49MM;(""fj0#66666%fSk(CCCF3K!&"-----!&"-----r+   c                >   t          t          ddd          t          ddd          gdg          }|                    d| j        d          dk    sJ t	          d| j                  }|d         }t          |d                   }t          j        ||           d S )	N  r   r1  r  r   	test_dateFru  )r   r   r  rB   r"   r   r   r  )r  r   rJ  r  r  s        r,   test_datetime_datez"_TestSQLAlchemy.test_datetime_date	  s    T1a(($tQ*:*:;cUKKKyyktyyFF!KKKK[$)44Sr#w''
vx00000r+   c                   t          t          ddd          t          ddd          gdg          }|                    d| j        d	          d
k    sJ t	          d| j                  }t          j        ||           |}t          j        |d|d          d
k    sJ t          j        d|          }|	                    d           }t          j        ||           t          j        |d| j        d          d
k    sJ | j
        dk    rEt          j        d| j                  }|	                    d           }t          j        ||           t          j        d| j                  }t          j        ||           d S )N	   r   r      r  r   	test_timeFru  r1  
test_time2rk  zSELECT * FROM test_time2c                ,    |                      d          S Nz%H:%M:%S.%fstrftime_s    r,   <lambda>z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>	  s    qzz-88 r+   
test_time3r$   zSELECT * FROM test_time3c                ,    |                      d          S r  r  r  s    r,   r  z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>#	  s    1::m#<#< r+   )r   r   r  rB   r"   r   rv  r   r!   mapr  )r  r  r   rJ  r   refs         r,   test_datetime_timez"_TestSQLAlchemy.test_datetime_time	  s{   Q1tAq"~~6FFFyyktyyFF!KKKK[$)44
c2&&& %z"lKuEEEJJJJ !;[IIff8899
c3'''z"lDIUCCCqHHHH;(""$%?KKC&&<<==C!#s+++ ty99
b#&&&&&r+   c                @   t          dt          j                  }t          dt          j                  }t	          ||d          }|                    d| j        d          dk    sJ t          j        d| j                  }t          j
        ||dd	
           d S )Ni  r  g        )s1rs  test_read_writeFru  r   T)check_dtypecheck_exact)r   r   r6  float32r   r  rB   r   r"   r   rv  )r  r  rs  r   r  s        r,   test_mixed_dtype_insertz'_TestSQLAlchemy.test_mixed_dtype_insert(	  s    IRX...Crz***b++,, yy/TYeyLLPQQQQQ !2DI>>
b#5dKKKKKKr+   c                J   t          g ddt          j        dgd          }|                    d| j        d          dk    sJ t          j        d| j                  }t          j        ||           t          j	        d	| j                  }t          j        ||           d S )
Nr  r   r  r  test_nanFru  ro  SELECT * FROM test_nan)
r   r   r  r  rB   r   r"   r   rv  r!   r  s      r,   test_nan_numericz _TestSQLAlchemy.test_nan_numeric4	  s    YYYc263-?@@AAyyjdiuyEEJJJJ #J	::
fb))) #$<diHH
fb)))))r+   c                   t          g dt          j        t          j        t          j        gd          }|                    d| j        d          dk    sJ t          j        d| j                  }t          j        ||           |d         	                    d          |d<   d |d<   t          j
        d	| j                  }t          j        ||           d S )
Nr  r  r  Fru  ro  r   r  r  )r   r   r  r  rB   r   r"   r   rv  r   r!   r  s      r,   test_nan_fullcolumnz#_TestSQLAlchemy.test_nan_fullcolumnA	  s    YYYbfbfbf-EFFGGyyjdiuyEEJJJJ #J	::
fb))) S'..**33#$<diHH
fb)))))r+   c                ^   t          g dddt          j        gd          }|                    d| j        d          dk    sJ d |j        d	<   t          j        d| j                  }t          j	        ||           t          j
        d
| j                  }t          j	        ||           d S )Nr  r  rt  r  r  Fru  ro  r1  r   r  )r   r   r  r  rB   r  r   r"   r   rv  r!   r  s      r,   test_nan_stringz_TestSQLAlchemy.test_nan_stringP	  s    YYYc3-?@@AAyyjdiuyEEJJJJ v #J	::
fb))) #$<diHH
fb)))))r+   c                t    ddl m}  || j                  }|                    |          }d |D             }|S )Nr   r  c                    g | ]
}|d          S r  r*   r  s     r,   ra   z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>f	  r  r+   )r>   r   rB   r   )r  r!  r   r   r"  s        r,   r>  z"_TestSQLAlchemy._get_index_columns`	  sP    &&&&&&wty!!x((..#...
r+   c                .    |                                   d S r   r@  r  s    r,   test_to_sql_save_indexz&_TestSQLAlchemy.test_to_sql_save_indexi	      !!!!!r+   c                .    |                                   d S r   rK  r  s    r,   test_transactionsz!_TestSQLAlchemy.test_transactionsl	           r+   c                   ddl m} ddlm} d}t	          j        ||| j                  }|j        d d         }|                     || j                    ||          }t          | j        |          rs| j        
                                5 }|                                5  |                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nK| j                                        5  | j                            |           d d d            n# 1 swxY w Y   t	          j        || j                  }t          j        ||d           |                     || j                   d S )Nr   rp   rX   test_get_schema_create_tablerv  Fr  )r>   rq   rf   rY   r   r  rB   r   r  rh   ri   rj   rK   r"   r   rv  )	r  r   rq   rY   r  r  blank_test_dfrB   returned_dfs	            r,   r)  z,_TestSQLAlchemy.test_get_schema_create_tableo	  s2    	$#####,,,,,,,^K$)DDD
#(!,TY'''T*%%
di(( 	.""$$ -ZZ\\ - -LL,,,- - - - - - - - - - - - - - -- - - - - - - - - - - - - - - "" . .	!!*---. . . . . . . . . . . . . . .(di88
k=5QQQQTY'''''sH   C#C9CC			CC		CC #C D((D,/D,c                ~   ddl m}m} ddlm} ddg}ddg}t          ||          }|                    d	| j        
          dk    sJ |                    d| j        d|i          dk    sJ  |            }|                    | j                   |j	        d         j
        d         j        }t          ||          sJ d}	t          j        t          |	          5  |                    d| j        dt           i           d d d            n# 1 swxY w Y   |                    d| j        d |d          i          dk    sJ |                    | j                   |j	        d         j
        d         j        }t          ||          sJ |j        dk    sJ |                    d| j        |          dk    sJ |                    | j                   |j	        d         j
        d         j        }
|j	        d         j
        d         j        }t          |
|          sJ t          ||          sJ d S )Nr   )rv   r5   r4   r   r   g?Tg?Nr   
dtype_testr  r1  dtype_test2r   r  r?   rd   z&The type of B is not a SQLAlchemy typer  rE  dtype_test3
   single_dtype_test)r>   rv   r5   sqlalchemy.schemar4   r   r  rB   reflecttablesr   r   rh   r   r9  r:  r/   rN  )r  rv   r5   r4   r  r   r   metasqltyper;  sqltypeasqltypebs               r,   
test_dtypez_TestSQLAlchemy.test_dtype	  s   	
 	
 	
 	
 	
 	
 	
 	
 	/.....Sz[)tT***yyl	y::a????yym3+yNNRSSSSSxzz$)$$$+m,4S9>'4(((((6]:S111 	E 	EII7	#sIDDD	E 	E 	E 	E 	E 	E 	E 	E 	E 	E 	E 	E 	E 	E 	E
 II=diVVBZZ?PIQQUVVVVV$)$$$+m,4S9>'6*****~#### yy1tyyMMQRRRRR$)$$$;23;C@E;23;C@E(D)))))(D)))))))s    %DDDc                   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	          t          d
d g          d}t          |          }d}|
                    || j                  dk    sJ t          j        || j                  }	 |            }
|
                    | j                   | j        dk    r|n|}|
j        |         j        }t%          |d         j        |          sJ t%          |d         j        |          sJ t%          |d         j        |          sJ t%          |d         j        |          sJ d S )Nr   )rw   rx   r3   ry   r-  T  r   r   r  r  r  BoolDateIntr3   notna_dtype_testr  r1  rd   r%   rA  rB  rC  r3   )r>   rw   rx   r3   ry   r6  r4   r   r   r   r  rB   r   r"   r7  r  r8  r   rh   r   )r  rw   rx   r3   ry   r4   r  r   r  r  r9  my_typecol_dicts                r,   test_notna_dtypez _TestSQLAlchemy.test_notna_dtype	  s   	
 	
 	
 	
 	
 	
 	
 	
 	
 	
 	
 	
 	/..... D$<((HT1a00$7881d)8444S$K((	
 
 t__ yyctyy11Q6666sDI..xzz$)$$$![G33'';s#+(6*/99999(6*/:::::(5/.88888(7+0%8888888r+   c           
        ddl m}m}m} ddlm} d}t          t          |gd          t          |gd          t          |gd          t          dgd	          t          dgd
          d          }|                    d| j	        ddd |d          i          dk    sJ t          j        d| j	                  }t          j        |d         j        d         d          t          j        |d         j        d         d          k    sJ  |            }|                    | j	                   |j        d         j        }	t%          |	d         j                  t%          |	d         j                  k    sJ t)          |	d         j        |          sJ t)          |	d         j        |          sJ t)          |	d         j        |          sJ t)          |	d         j        |          sJ d S )Nr   )
BigIntegerr3   ry   r-  gYB?r  r  float64r   r6  r   )f32f64
f64_as_f32i32r  test_dtypesFr  rM     )	precision)r   r  r   r  r?   r   rL     rd   rK  rN  r  )r>   rI  r3   ry   r6  r4   r   r   r  rB   r   r"   r   roundr   r7  r8  r   r/   r   rh   )
r  rI  r3   ry   r4   Vr   rJ  r9  rF  s
             r,   test_double_precisionz%_TestSQLAlchemy.test_double_precision	  sG   	
 	
 	
 	
 	
 	
 	
 	
 	
 	

 	/....."qc333qc333$aS	:::qc111qc111 
 
 II"I##UUR%8%8%89         	:: x5	q)2.."(3u:?1;Mr2R2RRRRR xzz$)$$$;}-58E?'((C0F0K,L,LLLLL(5/.66666(5/.66666(5/.88888(5/.
;;;;;;;r+   c                    ddl m d d fdfd}t          dg di                              d| j        	          d
k    sJ  || j                   d S )Nr   rX   c                2    d}t          j        ||           S )Nz'SELECT test_foo_data FROM test_foo_datarv  )r   r!   )r  rW  s     r,   test_selectzC_TestSQLAlchemy.test_connectable_issue_example.<locals>.test_select	  s    =E%e<<<<r+   c                6    |                     d| d           d S )Ntest_foo_datar	  )r   r  r  )r  )r  r   s     r,   test_appendzC_TestSQLAlchemy.test_connectable_issue_example.<locals>.test_append	  s     KK_*KQQQQQr+   c                6     |           } | |           d S r   r*   )rB   foo_datar[  rX  s     r,   test_connectablezH_TestSQLAlchemy.test_connectable_issue_example.<locals>.test_connectable
  s,     #{4((HKh'''''r+   c                
   t          |           rf|                                 5 }|                                5   |           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S  |            d S r   )rh   ri   rj   )connectablerB   rY   r^  s     r,   mainz<_TestSQLAlchemy.test_connectable_issue_example.<locals>.main
  s   +v.. . ((** /d / /((.../ / / / / / / / / / / / / / // / / / / / / / / / / / / / / / / / ! -----s4   A+AA+A	A+A	A++A/2A/rZ  r  r  ro  )rf   rY   r   r  rB   )r  ra  rY   r[  r^  rX  s     @@@@r,   test_connectable_issue_examplez._TestSQLAlchemy.test_connectable_issue_example	  s     	-,,,,,	= 	= 	=	R 	R 	R	( 	( 	( 	( 	( 	(	. 	. 	. 	. 	. 	. 			233::$$) ;      
 	TYr+   inputr  bar)r  infe0c                   t          |          }| j        dk    rt          j        d          }t	          |j                  t	          d          k     rCd|j        v r:t          j                            d          }|j	        
                    |           d}t          j        t          |          5  |                    d	| j        d
           d d d            d S # 1 swxY w Y   d S |                    d	| j        d
          dk    sJ t          j        d	| j                  }t#          j        ||           d S )Nr%   r   z1.0.3re  zGH 36465r  zinf cannot be used with MySQLr  foobarFru  r   )r   r  r   r   r   __version__r   r  r  r  r  r9  r:  r  rB   r   r"   r   assert_equal)r  rc  r  r   r   r  r;  rJ  s           r,   test_to_sql_with_negative_npinfz/_TestSQLAlchemy.test_to_sql_with_negative_npinf
  s    u;'!!
 ))44G +,,ww/?/???rz)){((
(;;''---1Cz555 E E		xTYe	DDDE E E E E E E E E E E E E E E E E E 99(	9GG1LLLL$Xty99COB$$$$$s   4CC#&C#c                p   ddl mmmm} ddlm}m} d}t          d|gi          } |            } G fdd|          } || j	                  5 }|
                                5  |                                }	|j                            |	           |                     ||                     |                                 t!          j         ||j                  |		          }
d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   t'          j        |
|           d S )
Nr   )r2   ry   UnicoderK  )Sessiondeclarative_basezHello, World!spamc                  d    e Zd ZdZddgiZ  d          Z   d          d          Zd	S )
7_TestSQLAlchemy.test_temporary_table.<locals>.Temporary	temp_testprefixes	TEMPORARYTprimary_keyr  F)nullableN)r  r  r  __tablename____table_args__idro  )r2   ry   rl  s   r,   	Temporaryrq  G
  sP        'M(;-8NT222B6''"++666DDDr+   r{  )ro  )r   r  )r>   r2   ry   rl  rK  sqlalchemy.ormrm  rn  r   rB   rj   r  	__table__rl   addflushr   r!   ro  r   rv  )r  rK  rm  rn  	test_datar  Baser{  sessionrB   r   r2   ry   rl  s              @@@r,   test_temporary_tablez$_TestSQLAlchemy.test_temporary_table7
  sJ   	
 	
 	
 	
 	
 	
 	
 	
 	
 	
 	
 	
	
 	
 	
 	
 	
 	
 	
 	

 $	fyk233!!	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 	7 WTY 	N7 N N))++#**4000II9555666'FF9>,B,BMMMN N N N N N N N N N N N N N N	N 	N 	N 	N 	N 	N 	N 	N 	N 	N 	N 	N 	N 	N 	N 	b(+++++s7   D-BC?3D?D	DD	DDDc                    d}t          j        t          |          5  |                     |d           d d d            d S # 1 swxY w Y   d S )Nz*engine must be one of 'auto', 'sqlalchemy'r  
bad_engine)r   r9  r:  r&  r`  s      r,   test_invalid_enginez#_TestSQLAlchemy.test_invalid_engineW
  s    :]:S111 	D 	D((lCCC	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	D 	Ds   AA	Ac                    t          j        dd          5  |                     |           d d d            d S # 1 swxY w Y   d S )Nio.sql.enginer>   r  option_contextr&  r  s     r,   test_options_sqlalchemyz'_TestSQLAlchemy.test_options_sqlalchemy\
  s    == 	6 	6((555	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6   9= =c                    t          j        dd          5  |                     |           d d d            d S # 1 swxY w Y   d S )Nr  r   r  r  s     r,   test_options_autoz!_TestSQLAlchemy.test_options_autoa
  s    77 	6 	6((555	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6r  c                (   t          t          d          t                    sJ t          j        dd          5  t          t          d          t                    sJ t          t          d          t                    sJ 	 d d d            n# 1 swxY w Y   t          j        dd          5  t          t          d          t                    sJ t          t          d          t                    sJ 	 d d d            d S # 1 swxY w Y   d S )Nr>   r  r   )rh   r   r   r  r  r  s    r,   test_options_get_enginez'_TestSQLAlchemy.test_options_get_enginef
  s   *\224DEEEEE== 	J 	Jj002BCCCCCj668HIIIIII	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 77 	J 	Jj002BCCCCCj668HIIIIII	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	J 	Js%   A	BBB0A	DDDc                    d S r   r*   r  s    r,   "test_get_engine_auto_error_messagez2_TestSQLAlchemy.test_get_engine_auto_error_messageq
  	     	r+   funcrA  r!   c                   d}|                                  }|                    || j        dd           t          j        d|          5   t          t          |          d| | j        |          }d d d            n# 1 swxY w Y   |                     ||          }t          j        ||           t          j        d|          5   t          t          |          d| | j        |d	          }|                     ||          }|D ]}t          j        ||           	 d d d            d S # 1 swxY w Y   d S )
Nr  Fr  r   r  r   r  mode.string_storageSelect * from dtype_backendro  )r  r  rH  	dtype_backend_datar  rB   r  r  rp  dtype_backend_expectedr   rv  	r  string_storager  r  rc  r   r  r  iterators	            r,   test_read_sql_dtype_backendz+_TestSQLAlchemy.test_read_sql_dtype_backendw
  s    $$&&
		u$)5I	NNN4nEE 	 	&WR&&((($)=  F	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 ..~}MM
fh///4nEE 		8 		8(wr4(((((I+	  H 22>=QQH" 8 8%fh77778		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8s%   
*B  BBAD44D8;D8r"   c                v   d}|                                  }|                    || j        dd           t          j        d|          5   t          t          |          || j        |          }d d d            n# 1 swxY w Y   |                     ||          }t          j        ||           t          j        d|          5   t          t          |          || j        |d          }|                     ||          }|D ]}t          j        ||           	 d d d            d S # 1 swxY w Y   d S )	Nr  Fr  r  r  r  ro  )r  rH  r  r  s	            r,   !test_read_sql_dtype_backend_tablez1_TestSQLAlchemy.test_read_sql_dtype_backend_table
  s    $$&&
		u$)5I	NNN4nEE 	V 	V&WR&&udi}UUUF	V 	V 	V 	V 	V 	V 	V 	V 	V 	V 	V 	V 	V 	V 	V..~}MM
fh///4nEE 		8 		8(wr4((	+	  H 22>=QQH" 8 8%fh77778		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8 		8s%   
'A==BBAD..D25D2)rA  r"   r!   c                &   d}|                                  }|                    || j        dd           d}t          j        t
          |          5   t          t          |          || j        d           d d d            d S # 1 swxY w Y   d S )	Nr  Fr  r  zPdtype_backend numpy is invalid, only 'numpy_nullable' and 'pyarrow' are allowed.r  numpyr  )r  r  rB   r   r9  r:  rp  r  )r  r  rc  r   r;  s        r,   )test_read_sql_invalid_dtype_backend_tablez9_TestSQLAlchemy.test_read_sql_invalid_dtype_backend_table
  s    $$&&
		u$)5I	NNN% 	 ]:S111 	G 	GGBeTYgFFFF	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	Gs   'BB
B
ri  r   c                    t          t          dt          j        dgd          t          g dd          t          dt          j        dgd          t          g d	d          g d
g dg dg dd          S )Nr   ro  r;  r  rn  r8        @Float64r8  r  r  )TFNTFTr  r  rt  Nr  rt  r  defgh)r   r   r   r  r  s    r,   r  z"_TestSQLAlchemy.dtype_backend_data
  s    QN':::IIIW555S"&#.i@@@OOO9===(((((($__%%%	 	
 
 	
r+   c                f   |dk    rjt          t          j        g dt          j                            }t          t          j        ddt          j        gt          j                            }n\t          j        d          t                              g d                    }t                              g d                    }t          t          dt          j        d	gd
          t          g dd
          t          dt          j        dgd          t          g dd          t          ddt          j        gd          t          g dd          ||d          |dk    r<t          j        d          ddlm t          fdj        D                       S )Npythonr  r  r  rt  r  r  r   ro  r;  rn  r8  r  r  r  TFbooleanr  r  r   )ArrowExtensionArrayc           	     ^    i | ])}|                      |         d                     *S )T)from_pandas)r  )r^   r  r  r   pas     r,   
<dictcomp>z:_TestSQLAlchemy.dtype_backend_expected.<locals>.<dictcomp>
  sK        ,,RXXbg4X-P-PQQ  r+   )r   r   r  object_r  NAr   r   r   r   r   r  pandas.arraysr  r   )r  storager  string_arraystring_array_nar  r   r  s        @@@r,   r  z&_TestSQLAlchemy.dtype_backend_expected
  s    h&rxrz'R'R'RSSL)"(Cbe3DBJ*W*W*WXXOO $Y//B+BHH___,E,EFFL.rxx8H8H8H/I/IJJOQN':::IIIW555S"&#.i@@@OOO9===T5"%0	BBB///yAAA!$	 	
 
 I%%$Y//B999999     !z   B 	r+   c                   ddd}t          ddg                              |          }|                                }|                    d| j        dd	
           t          d| j        |d          D ]}t          j        ||           d S )Nr   r  rs  r  rt  r   r  Fr  r  zSELECT * FROM testr   )r?   rH  )r   r   r  r  rB   r!   r   rv  )r  r   r   r  r  s        r,   test_chunksize_empty_dtypesz+_TestSQLAlchemy.test_chunksize_empty_dtypes
  s    X..Sz***11&997799
		v49EY	OOO$ I	
 
 
 	4 	4F !&(3333	4 	4r+   r  numpy_nullablec                   d}t          g ddd          }|                    || j        dd            t          t          |          d| | j        d	t
          j        i|
          }t          t          g dt
          j                  t          g d|dk    sdnd          d          }t          j	        ||           d S )Nr  rn  r   rs  Fr  r  r  r  )r?   r  r  )r   r   r   r  r   r;  )
r   r  rB   rp  r  r   rJ  r   r   rv  )r  r  r  rc  r   r  r  s          r,   test_read_sql_dtypez#_TestSQLAlchemy.test_read_sql_dtype
  s     YYYQ//00
		u$)5I	NNN"T""$U$$I
#'	
 
 
 IIIRZ888II)6:J)J)J''PW   
 
 	fh/////r+   Nri  r   )Br  r  r  rL  r  r
  r  r   r  rT  r  r  r  r  r  r  r  rw  r|  r  r  r  rl  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r>  r"  r&  r)  r=  rG  rU  rb  r  r  r   infrj  r  r  r  r  r  r  r  r  r  r  r  r  r   
no_defaultr  r*   r+   r,   r  r    s          KKK  [ V^D!!!) ) "!) $ $ [$ $ $ [$3 3 39 9 9( ( (/ / /"0 0 0(% % %  % % %T T TC C C

A 
A 
A* * *@ @ @M  M  M ^0 0 060 0 0C C CC C C@. . .*. . .(1 1 1' ' '.
L 
L 
L* * ** * ** * *   " " "! ! !( ( (0#* #* #*J9 9 9:+< +< +<Z     D [
"&	UbfWI."&	UG0T0TU % %	 %6, , ,@D D D
6 6 6
6 6 6
	J 	J 	J   [Vj2B%CDD8 8 ED80 [Vj2B%CDD8 8 ED8, [V%U%U%UVV
G 
G WV
G
 
 
 
# # # #J4 4 4 [_s~?O.PQQ[Vj2B%CDD0 0 ED RQ0 0 0r+   r  c                       e Zd ZdZdZed             Zed             Zd Zd Z	d Z
d Zd	 Zd
 Zd fdZej                            dddg          d             Zd Zd Z xZS )TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r$   c                8    t          j        d          | _        d S r  r   r  s    r,   r  zTestSQLiteAlchemy.setup_engine  r  r+   c                    d | _         d S r   )driverr  s    r,   r  zTestSQLiteAlchemy.setup_driver  s     


r+   c                   d}t          ddddddddg          }|                    d| j                   t          j        t
          |          5  |                    d| j        d d	           d d d            d S # 1 swxY w 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   r1  ro  r:  exampler  r  r4  )r   r  rB   r   r  r  )r  r;  r   s      r,   test_keyword_deprecationz*TestSQLiteAlchemy.test_keyword_deprecation!  s    ? 	 aaa002J2JKLL
		)TY''''SAAA 	G 	GIIiDIIFFF	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	G 	Gs   A>>BBc                   t          j        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                  sJ t          |j        j        j        t          j                  sJ d S rj  )r   r"   rB   r   r   r?   r   r   r   r   rk  r   r   r   r  s     r,   rl  z.TestSQLiteAlchemy.test_default_type_conversion-  s    33"++0"+>>>>>")/.
;;;;; "**/<<<<< "+16DDDDD ",27EEEEEEEr+   c                    t          j        d| j                  }t          |j        j        j        t          j                  rJ d S rj  r  r  s     r,   r  z(TestSQLiteAlchemy.test_default_date_load<  s>    33 bj.3R]CCCCCCCr+   c                   t          dddgid          }|                    d| j        d          dk    sJ t          j        d           5  t          j        d| j                   d d d            d S # 1 swxY w Y   d S )	Nr  r   r1  r   r  test_bigintwarningFru  )r   r  rB   r   r  r   r"   r  s     r,   test_bigint_warningz%TestSQLiteAlchemy.test_bigint_warningB  s    aV}G444yy2	yOOSTTTTT'-- 	@ 	@3TY???	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@ 	@s   A55A9<A9c                    t          ddgddgd          }t          j        t          d          5  |                    d| j        d	d
           d d d            d S # 1 swxY w Y   d S )Nr   r1  ro  r"  r  r  zEmpty table name specifiedr  r  r  Fr
  r  r  s     r,   test_valueerror_exceptionz+TestSQLiteAlchemy.test_valueerror_exceptionJ  s    AA7788]:-IJJJ 	P 	PII249	IOOO	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	Ps   AA"%A"c                  	
 ddl m	m
m ddlm}m}  |            } G 	
fdd|          }| j                                        5  |j	        
                    | j                   d d d            n# 1 swxY w Y    || j                  } |            5 }t          ddgdd	gd
          }|                    d| j        dd          dk    sJ |                                 |                    |j        |j                  }t          |          }d d d            n# 1 swxY w Y   t#          |j                  ddgk    sJ d S )Nr   r2   ry   r5   )rn  sessionmakerc                  V    e Zd ZdZ  d          Z   d                    ZdS )>TestSQLiteAlchemy.test_row_object_is_named_tuple.<locals>.Testr+  Tru  2   N)r  r  r  rx  rz  string_columnr  s   r,   Testr  `  s?        (MT222B"F66"::..MMMr+   r  rd   r   helloworld)rz  r  r+  Fr  r  r1  rz  r  )r>   r2   ry   r5   r|  rn  r  rB   rj   r@   
create_allr   r  r  rW  rz  r  r  r   )r  rn  r  	BaseModelr  rm  r  r   
test_queryr2   ry   r5   s            @@@r,   test_row_object_is_named_tuplez0TestSQLiteAlchemy.test_row_object_is_named_tupleO  sX   
	
 	
 	
 	
 	
 	
 	
 	
 	
 	

	
 	
 	
 	
 	
 	
 	
 	

 %$&&		/ 	/ 	/ 	/ 	/ 	/ 	/ 	/ 	/9 	/ 	/ 	/
 Y__ 	5 	5))$)444	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5 	5,DI...WYY 
	''1a&GW;MNNOOB		%49EY       
 NN tw0BCCJ:&&B
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' 
	' BJD/#:::::::s%    A77A;>A;A>D''D+.D+ri  r   c                    t                                          ||          }|dk    r|                    ddd          }n|                    ddd          }|S Nr  r;  )r  r  zint64[pyarrow]superr  r   r  r  r  r   	__class__s       r,   r  z(TestSQLiteAlchemy.dtype_backend_expectedv  c    WW++G]CC,,,w7788BB!18HIIJJB	r+   r  rA  r"   c                    d S r   r*   )r  r  r  s      r,   r  z3TestSQLiteAlchemy.test_read_sql_dtype_backend_table  r  r+   c                   t          j        d           d}t          dddgi          }|                    || j        dd           t          j        d	d
          5  t          || j                  }d d d            n# 1 swxY w Y   d}t          dddgi|t          dg|                    }t          j
        ||           d S )Nr  r  r  xyFr  )r  r   r  zfuture.infer_stringTzstring[pyarrow_numpy]r  )r?   r   )r   r   r   r  rB   r  r  r"   r   r   rv  )r  rc  r   r  r?   r  s         r,   test_read_sql_string_inferencez0TestSQLiteAlchemy.test_read_sql_string_inference  s   I&&&c3Z())
		%TYey	III4d;; 	6 	6#E4955F	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 	6 (3*UE3%u4M4M4M
 
 
 	fh/////s   A??BBc           	         t          dt          dddd          gid          }|                    d| j        dd	
           t	          j        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  rB   r  rA  r   r  s      r,   test_roundtripping_datetimesz.TestSQLiteAlchemy.test_roundtripping_datetimes  s{    htRR889:BRSSS
		&$)y	FFF149==B4H5555555r+   r  )r  r  r  rL  r  r
  r  r  r  rl  r  r  r  r  r  r   r  r  r  r  r  __classcell__r  s   @r,   r  r    sB        
 FD D [D   [
G 
G 
GF F FD D D@ @ @P P P
%; %; %;N      [Vj2B%CDD  ED0 0 0"6 6 6 6 6 6 6r+   r  c               #     K   t          j        t          j        dt          j                            5 } | 5 }|V  d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w Y   d S )Nr   )detect_types)r  r   rr   ri   PARSE_DECLTYPESr  s     r,   sqlite_builtin_detect_typesr    s      		
1HIII
 
 	 	TJJJ	 	 	 	 	 	 	 	 	 	 	 	 	 	 	                 s3   A!A	A!	A	A!A	A!!A%(A%c           	         | }t          dt          dddd          gid          }|                    d|dd	
           t          j        d|          j        d         }|t          d          k    sJ d S r  )r   r   r  r  rA  r   r   )r  rB   r   r  s       r,   )test_roundtripping_datetimes_detect_typesr    s    &D	C(4R4456>N	O	O	OBIIfdiuI===[-t449$?FY;<<<<<<<<r+   c                  ^     e Zd ZdZdZdZed             Zed             Zd Z	d
 fd	Z
 xZS )TestMySQLAlchemyzA
    Test the sqlalchemy backend against an MySQL database.

    r%   i  c                h    t          j        d| j         d| j         d| j                  | _        d S )Nzmysql+z://root@localhost:/pandas)r   )r>   r   r  portr   r   r  s    r,   r  zTestMySQLAlchemy.setup_engine  s?    -DSZDD38DDD)
 
 



r+   c                l    t          j        d          }d| _        d|j        j        j        i| _        d S )Nr   r   )r   r   r  r   r   r   r   )r  r   s     r,   r  zTestMySQLAlchemy.setup_driver  s5    %i00
)7+<+C+TUr+   c                    d S r   r*   r  s    r,   rl  z-TestMySQLAlchemy.test_default_type_conversion  s    r+   ri  r   c                    t                                          ||          }|dk    r|                    ddd          }n|                    ddd          }|S r  r  r  s       r,   r  z'TestMySQLAlchemy.dtype_backend_expected  r  r+   r  )r  r  r  rL  r  r  r
  r  r  rl  r  r  r  s   @r,   r  r    s         
 FD
 
 [
 V V [V
           r+   r  c                  R    e Zd ZdZdZdZed             Zed             Zd Z	d Z
dS )	TestPostgreSQLAlchemyzF
    Test the sqlalchemy backend against an PostgreSQL database.

    r&   i8  c                Z    t          j        d| j         d| j         d          | _        d S )Nzpostgresql+z://postgres:postgres@localhost:r  )r>   r   r  r  r   r  s    r,   r  z"TestPostgreSQLAlchemy.setup_engine  s4    -V#*VVSXVVV
 



r+   c                <    t          j        d           d| _        d S )Nr   )r   r   r  r  s    r,   r  z"TestPostgreSQLAlchemy.setup_driver  s    J'''


r+   c                   ddl m} t          ddgddgddgd	          }| j                                        5  | j                            d
           | j                            d           d d d            n# 1 swxY w Y   |                    d| j        d          dk    sJ |                    d| j        dd          dk    sJ |                    d| j        dd          dk    sJ t          j        d| j                  }t          j
        ||           t          j        d| j                  }t          j
        ||           t          j        d| j        d          }t          j
        ||           t          j        d| j        d          }t          j
        ||           d}t          j        t          |          5  t          j        d| j        d           d d d            n# 1 swxY w Y   | j                                        5  | j                            d
           | j                            d           d d d            n# 1 swxY w Y   |                    d| j        dd          dk    sJ |                    d| j        ddd           |                    d| j        ddd          dk    sJ t          j        d| j        d          }t          j
        t          ||gd          |           t          | j        |          r|                                 }	t          j        |	d          }
|
                    |dd          dk    sJ |
                    |ddd          dk    sJ |
                    |ddd          dk    sJ t          j        d| j        d          }|
                    d          }t          j
        ||           d S d S ) Nr   rX   r   r1  rq  r   r  r  r  z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;test_schema_publicFru  test_schema_public_explicitpublic)r   r  r   r  test_schema_otherother)r  z!Table test_schema_other not foundr  )r   r  r  r   r  )r   r  r  r   r  r	  Tr  test_schema_other2rk  )r   r  )rf   rY   r   rB   rj   r   r  r   r"   r   rv  r   r9  r:  r   rh   ri   r   
read_table)r  rY   r   ry  rz  r  res4r;  rJ  engine2pdsqls              r,   test_schema_supportz)TestPostgreSQLAlchemy.test_schema_support  sR   ,,,,,, Ac
S#JOOPP Y__ 	> 	>I%%&LMMMI%%&<===	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	>
 yy2	yOOSTTTTTII2I	        II(diuW        !"6	BB
b$'''!"?KK
b$'''!)49X
 
 
 	b$'''!"5tyQQQ
b$'''1]:S111 	P 	P2DIhOOOO	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P 	P Y__ 	> 	>I%%&LMMMI%%&<===	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> 	> II(diu       
 			$	 	 	
 	
 	
 II(I"         !4diPPP
fb"XDAAA3GGG di(( 	.llnnGOGG<<<E<<$8<FF!KKKKR!5UiXX    R!5UhWW    %&:DIgVVVD##$899D!$-----	. 	.s5   5A::A>A>G66G:=G:5II"Ic                $   ddl m} ddlm}  |d          }t	          | j        |          rs| j                                        5 }|                                5  |                    |           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   nK| j                                        5  | j                            |           d d d            n# 1 swxY w Y   d}t          j
        || j                  }t          dt          dd	          gd
z  gddgd
z            }t          j        ||           t          j        | j        d          5 }|                    d           d d d            d S # 1 swxY w Y   d S )Nr   rX   rp   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   2021r  r  r1  rz  
created_dtr   Tr)  person)rf   rY   r  rq   rh   rB   ri   rj   rK   r  rA  r   r   r   rv  r   r   r  )	r  rY   rq   r  r  r  r  r  r/  s	            r,   test_self_join_date_columnsz1TestPostgreSQLAlchemy.test_self_join_date_columnsK  s   ,,,,,,''''''t	
 
 di(( 	0""$$ .YY[[ . .KK---. . . . . . . . . . . . . . .. . . . . . . . . . . . . . . "" 0 0	!!,///0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 W 	 Y	226e,,,-12T<<PST<T
 
 
 	fh/// _TY>>> 	+)  ***	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+ 	+sZ   BA=1B=B	BB	BBB9C  C$'C$"FF	F	N)r  r  r  rL  r  r  r
  r  r  r  r  r*   r+   r,   r  r    s         
 FD
 
 [

     [ a. a. a.F$+ $+ $+ $+ $+r+   r  c                      e Zd ZdZdZ ej        d          d             Zd Zd Z	d Z
d	 Zd
 Zd Zd Zej                            dddg          d             Zd Zd Zd Zd Zd Zd Zd ZdS )TestSQLiteFallbackzG
    Test the fallback mode against an in-memory sqlite database.

    r$   TrO  c                    |                                  | _        |                     |           |                     |           t	          j        | j                  | _        d S r   )ri   rB   r  r  r   r   r/  rS  s      r,   rT  zTestSQLiteFallback.setup_method~  sQ    LLNN	I&&&Z(((+DI66r+   c                0    |                      |           d S r   r  r  s     r,   r  z*TestSQLiteFallback.test_read_sql_parameter  r  r+   c                0    |                      |           d S r   r  r  s     r,   r  z0TestSQLiteFallback.test_read_sql_named_parameter  r  r+   c                0    |                      |           d S r   r  r  s     r,   r  z$TestSQLiteFallback.test_to_sql_empty  r  r+   c                   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  drop_test_framer"  )r   r/  r  r   r  )r  r  s     r,   test_create_and_drop_tablez-TestSQLiteFallback.test_create_and_drop_table  s    (((1E1E1EFF
 

 ~$$Z1BCCqHHHH~''(9:::::!!"3444>++,=>>>>>>>r+   c                0    |                      |           d S r   r  r  s     r,   rw  z!TestSQLiteFallback.test_roundtrip  r  r+   c                .    |                                   d S r   r  r  s    r,   r|  z#TestSQLiteFallback.test_execute_sql  r  r+   c                   t          t          ddd          t          ddd          gdg          }|                    d| j        d          dk    sJ t	          d	| j                  }| j        d
k    r/t          j        ||                    t                               d S | j        dk    rt          j        ||           d S d S )Nr  r   r1  r  r   r  Fru  zSELECT * FROM test_dater$   r%   )
r   r   r  rB   r!   r  r   rv  r   r/   r  s      r,   r  z%TestSQLiteFallback.test_datetime_date  s    T1a(($tQ*:*:;cUKKKyyktyyFF!KKKK6	BB;(""!#ryy~~66666[G##!#r***** $#r+   tz_awareFc                   |s#t          ddd          t          ddd          g}nGt          ddd          }t          |                                                              d	           }t          |d
g          }|                    d| j        d          dk    sJ t          d| j                  }| j	        dk    r-|                    d           }t          j        ||           d S d S )Nr  r   r   r  r  r1  r  r  c                *    |                                  S r   )timetz)r  s    r,   r  z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>  s    BIIKK r+   r  r   r   Fru  zSELECT * FROM test_timer$   c                ,    |                      d          S r  r  r  s    r,   r  z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>  s    

=(A(A r+   )r   r   r   to_pydatetimer  r   r  rB   r!   r  r   rv  )r  r+  tz_timestz_dtr   rJ  r  s          r,   r  z%TestSQLiteFallback.test_datetime_time  s      	QQ1tAq"~~6HH4aLQQQEe113344889O9OPPHx#///yyktyyFF!KKKK6	BB;(""vvAABBH!#x00000 #"r+   c                    t          j        d| d| j                  }g }|j        D ]L}t          j        d| d| j                  }|                    |j                                                   M|S )NzASELECT * FROM sqlite_master WHERE type = 'index' AND tbl_name = ''zPRAGMA index_info())r   r!   rB   r   r	  r  )r  r!  r"  r?  ix_nameix_infos         r,   r>  z%TestSQLiteFallback._get_index_columns  s     +'+ + +I
 

 x 	2 	2G()Hg)H)H)H$)TTGNN7<..001111r+   c                .    |                                   d S r   r!  r  s    r,   r"  z)TestSQLiteFallback.test_to_sql_save_index  r#  r+   c                .    |                                   d S r   r%  r  s    r,   r&  z$TestSQLiteFallback.test_transactions  r'  r+   c                    | j                             d| d          }|D ]\  }}}}}}	||k    r|c S t          d| d| d          )NzPRAGMA table_info(r5  zTable z	, column r{  )rB   rK   r:  )
r  rc  r~  recscidr   ctypenot_nulldefaultpks
             r,   r  z*TestSQLiteFallback._get_sqlite_column_type  sx    y  !>e!>!>!>??7; 	 	3Cuhv~~ D%DD&DDDEEEr+   c                   | j         dk    rt          j        d           ddg}ddg}t          ||          }|                    d| j        	          d
k    sJ |                    d| j        ddi          d
k    sJ |                     dd          dk    sJ |                     dd          dk    sJ d}t          j        t          |          5  |                    d| j        dt          i           d d d            n# 1 swxY w Y   |                    d| j        d          d
k    sJ |                     dd          dk    sJ |                     dd          dk    sJ d S )Nr%   Not applicable to MySQL legacyr   r   r.  r/  r   r0  r  r1  r1  STRINGr2  r  z!B \(<class 'bool'>\) not a stringr  rE  r5  )
r  r   r  r   r  rB   r  r9  r:  bool)r  r  r   r   r;  s        r,   r=  zTestSQLiteFallback.test_dtype  s   ;'!!K8999Sz[)tT***yyl	y::a????yym3/yRRVWWWWW ++L#>>)KKKK++M3??8KKKK2]:S111 	F 	FII7	#tIEEE	F 	F 	F 	F 	F 	F 	F 	F 	F 	F 	F 	F 	F 	F 	F yy1tyyQQUVVVVV++,?EEQQQQ++,?EEQQQQQQs   %DD
D
c                2   | j         dk    rt          j        d           t          dd g          t          t	          ddd          d g          t          dd gd          t          d	d g          d
}t          |          }d}|                    || j                  dk    sJ |                     |d          dk    sJ |                     |d          dk    sJ |                     |d          dk    sJ |                     |d          dk    sJ d S )Nr%   rB  Tr?  r   r   r  r  r  r@  rD  r  r1  rA  r  rB  r$  rC  r3   r1   )	r  r   r  r   r   r   r  rB   r  )r  r  r   r  s       r,   rG  z#TestSQLiteFallback.test_notna_dtype  s;   ;'!!K8999 D$<((HT1a00$7881d)8444S$K((	
 
 t__ yyctyy11Q6666++C88IEEEE++C88KGGGG++C779DDDD++C99VCCCCCCr+   c                6   t          ddgddggddg          }d}t          j        t          |	          5  |                    d
| j                   d d d            n# 1 swxY w Y   t          g d          D ]\  }}|                    || j                  dk    sJ t          j        || j                   t          ddgddggd|g          }d|d}|                    || j                  dk    sJ t          j        || j                   d S )Nr   r1  ro  r"  r  rt  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"99beginswithnumber12345r  test_weird_col_namer  )	r   r   r9  r:  r  rB   	enumerater   table_exists)r  r   r;  ndx
weird_namer  c_tbls          r,   test_illegal_namesz%TestSQLiteFallback.test_illegal_names  s   AA'#s<<<4]:S111 	. 	.II249I---	. 	. 	. 	. 	. 	. 	. 	. 	. 	. 	. 	. 	. 	. 	.  )   
  
 	/ 	/OC 99*$)9<<AAAAZ333aVaV,sJ6GHHHC1#111E::5di:88A====UDI....)	/ 	/s   A  A$'A$N)r  r  r  rL  r  r   r  rT  r  r  r  r'  rw  r|  r  r  r  r  r>  r"  r&  r  r=  rG  rO  r*   r+   r,   r   r   v  s[        
 FV^D!!!7 7 "!73 3 39 9 9( ( (? ? ?% % %  	+ 	+ 	+ [Z%771 1 871"
 
 
" " "! ! !F F FR R R,D D D(/ / / / /r+   r   z'{}'z{:.8f}z{:d}c                    dS )NNULLr*   )r  s    r,   r  r  +  s    & r+   z{:.10f}z'{!s}'c                    g }|D ]`}t          |t                    rt          |          rd }t          t	          |                   }|                     ||                     a| t          |          z  S r   )rh   r   r   _formattersr   r	  r  )r   rl  processed_argsarg	formatters        r,   format_queryrW  1  s|    N . .c5!! 	d3ii 	CS		*	iinn----~&&&&r+   c                    t          j        |          5 }|                    |                                           }ddd           n# 1 swxY w Y   |dnt	          |          S )z#Replace removed sql.tquery functionN)r   r    rK   r  r  )rW  r  r{  rJ  s       r,   tqueryrY  =  s    		s	#	# 3z  ''00223 3 3 3 3 3 3 3 3 3 3 3 3 3 3;44DII-s   (A		AAc                  J    e Zd Zd Zd Zd Zd Zd Zd Zd Z	d Z
d	 Zd
 ZdS )TestXSQLitec                    |                                 }|                    dt          j        |                      |                                 d S r  )rJ   rK   r   r  r  )r  r   rB   rQ   s       r,   r  zTestXSQLite.drop_tableE  sF    kkmmTC,Fz,R,RTTUUUr+   c                   t          j                    }t          j        |d|d          dk    sJ t          j        d|          }|j        |_        |}t          j        ||           dgt          |          z  |d<   |                                }t          t          j        t          |                    t          j                  d	z   }|                                |d
<   t          j        |d|d          dk    sJ t          j        d|d
          }|                                }||_        d
|j        _        t          j        ||           d S )Nr  Fru  r  zselect * from test_tabler  txtr  r4  Idxtest_table2zselect * from test_table2r  )r   makeTimeDataFramer   r  rA  r   rv  r.  r  r   r   rp  r   r   )r  r  r   r  r  frame2new_idxs          r,   
test_basiczTestXSQLite.test_basicJ  sL   $&&Ju<^5QQQUWWWWW8.II {
fe,,,us5zz)e	#f++..bh???"DuJvM~USSS    '5
 
 
 ::<< #
h/////r+   c                   t          j                    }t          j        |j        d<   t          j        |d          }|                                }|                    |           d}|	                                D ]#\  }}t          |g|R  }t          ||           $|                                 t          j        d|          }	|j        |	_        t          j        |	|d           d S )Nr  r  z(INSERT INTO test VALUES (%s, %s, %s, %s)rv  r  gMbP?)rtol)r   ra  r   r  r   r   r  rJ   rK   iterrowsrW  rY  r  rA  r   rv  )
r  r  r   r  rQ   insr  r_   fmt_sqlr  s
             r,   test_write_row_by_rowz!TestXSQLite.test_write_row_by_rowg  s    $&&6
4^E622
##%%J8nn&& 	0 	0FAs"3----G7/////2GGG{
fe$777777r+   c                   t          j                    }t          j        |d          }|                                }|                    |           d}|j        d         }t          j        |          5 }|                    |t          |                     d d d            n# 1 swxY w Y   |	                                 t          j
        d|          }|j        d d         |_        t          j        ||d d                    d S )Nr  z$INSERT INTO test VALUES (?, ?, ?, ?)r   r  r   )r   ra  r   r  rJ   rK   r   r    r  r  rA  r   rv  )	r  r  r   r  rQ   rh  r_   r{  r  s	            r,   test_executezTestXSQLite.test_executey  s)   $&&^E622
##%%J4jm">22 	0jsE#JJ///	0 	0 	0 	0 	0 	0 	0 	0 	0 	0 	0 	0 	0 	0 	02NCC{2A2
feBQBi00000s   5$B%%B),B)c                   t          j                    }t          j        |d          }|                                }|D ]D}|                    d          }t          |          dk    r|d         dk    r|d         dk    sJ Et          j        |dddg	          }|                                }d
|v sJ |                                }|                    |           d S )Nr   r1  r   r   r   DATETIMEr   )r   zPRIMARY KEY ("A", "B"))	r   ra  r   r  
splitlinesr|  r.  rJ   rK   )r  r  r   r  lineslinetokensrQ   s           r,   test_schemazTestXSQLite.test_schema  s    $&&^E622
%%'' 	/ 	/DZZ__F6{{aF1I$4$4ayJ....^E6c
CCC
%%''':5555##%%Jr+   c                   d}|                                 }|                    |           t          j        |          5 }|                    d           |                    d           t	          j        t          j        d          5  |                    d           d d d            n# 1 swxY w Y   d d d            d S # 1 swxY w 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))rJ   rK   r   r    r   r9  rv  )r  r  r  rQ   r{  s        r,   test_execute_failzTestXSQLite.test_execute_fail  sq   
 ##%%J">22 	OjMNNNMNNNs08QRRR O O""#MNNNO O O O O O O O O O O O O O O		O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	O 	Os7    ACB-!C-B1	1C4B1	5CC	C	c                   d}t          j        t          j        d                    5 }|                                }|                    |           t          j        |          5 }|                    d           d d d            n# 1 swxY w Y   d d d            n# 1 swxY w Y   d}t          j	        t          j
        |          5  t          d|           d d d            d S # 1 swxY w Y   d S )Nrv  r   rw  z$Cannot operate on a closed database.r  r  rv  )r  r   rr   ri   rJ   rK   r   r    r   r9  ProgrammingErrorrY  )r  r  rB   rQ   r{  r;  s         r,   test_execute_closed_connectionz*TestXSQLite.test_execute_closed_connection  s   
 
 ; ;<< 	S++--CKK
###&t,, S
""#QRRRS S S S S S S S S S S S S S S		S 	S 	S 	S 	S 	S 	S 	S 	S 	S 	S 	S 	S 	S 	S 5]733??? 	3 	3'T2222	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3 	3sG   >B 'B	=B 	B	B B	B  B$'B$C,,C03C0c                    t          dt          j        d          i          }t          j        ||dd          dk    sJ d S )NFromr   testkeywordsFr  r   r   )r   r   onesr   r  )r  r  r   s      r,   test_keyword_as_column_namesz(TestXSQLite.test_keyword_as_column_names  sG    

+,,z".~USSSWXXXXXXXr+   c                *   t          ddgdg          }t          j        ||dd          dk    sJ |}t          d |                    d	          D                       }|d
k    sJ t          j        d	|          }t          j        ||           d S )Nr   r1  c0r   mono_dfFr  c              3  &   K   | ]}|d          V  dS )r   Nr*   )r^   my_c0s     r,   	<genexpr>z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>  s&      SS5eAhSSSSSSr+   zselect * from mono_dfro  )r   r   r  sumrK   rA  r   rv  )r  r  r  con_xthe_sumr  s         r,   test_onecolumn_of_integerz%TestXSQLite.test_onecolumn_of_integer  s     QFTF333z'~IUSSSWXXXXXSSEMM:Q,R,RSSSSS!||||5u==
fg.....r+   c                   t          ddgddgd          }t          g dg dd          }d}d	| }d
}t          j        t          |          5  t	          j        |||d           d d d            n# 1 swxY w Y   |                     ||           t	          j        |||d           d}t          j        t          |          5  t	          j        |||d           d d d            n# 1 swxY w Y   t	          j        |||dd           t          ||          ddgk    sJ t	          j        |||dd          dk    sJ t          ||          g dk    sJ |                     ||           t	          j        |||dd          dk    sJ t          ||          ddgk    sJ t	          j        |||dd          dk    sJ t          ||          g dk    sJ |                     ||           d S )Nr   r1  r   r   r  rW  )r   r   Etable_if_existszSELECT * FROM z*'notvalidvalue' is not valid for if_existsr  notvalidvalue)r   r  r   r  r3  z&Table 'table_if_exists' already existsr  F)r   r  r   r  r   rv  r  r  ro  )ro  r   r"  r   r   r  r	  )r  r  r  r  r  )r   r   r9  r:  r   r  r  rY  )r  r  df_if_exists_1df_if_exists_2r   
sql_selectr;  s          r,   test_if_existszTestXSQLite.test_if_exists  si   "QFS#J#G#GHH"III#O#OPP&
2j22
:]:S111 	 	J$")	   	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	
N333 	
 n:QW	
 	
 	
 	
 7]:S111 	 	J$" 	   	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	
 	
 	
 	
 	
 jn555(H9MMMMMJ$"#       jn5559W9W9WWWWW
N333 J$"        jn555(H9MMMMMJ$""       jn555 :
 :
 :
 
 
 
 
 	
N33333s$   A66A:=A:C11C58C5N)r  r  r  r  rd  rj  rl  rt  rx  r{  r  r  r  r*   r+   r,   r[  r[  D  s          
0 0 0:8 8 8$1 1 1      O O O(3 3 3*Y Y Y/ / /O4 O4 O4 O4 O4r+   r[  )r.   r/   )rB   rC   rD   r   )rD   r   r.   r/   )rB   rC   r   r   )r   r   r.   r/   )r   r   )r   r/   r   )rL  
__future__r   r  r   rM   r   r   r   r   r   r	   pathlibr   rr   r  r  r   r   pandas._libsr   pandas.util._test_decoratorsutil_test_decoratorsr[  pandasr  r   r   r   r   r   r   r   r   r   r   r   pandas._testing_testingr   pandas.core.arraysr   r   pandas.util.versionr   	pandas.ior   pandas.io.sqlr   r   r   r   r    r!   r"   r>   r  rI  r  r-   rA   rT   rn   rt   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  mysql_connectablepostgresql_connectablesqlite_connectablesqlite_iris_connectablesqlalchemy_connectablesqlalchemy_connectable_irisall_connectableall_connectable_irisr  rV  r  r  r"  r%  r0  r7  r<  rD  rI  rQ  rX  r[  r]  rh  rl  r{  r  r  r  r  r  r  r  r  r  r  r  rN  r  r  r^  r  r  r  r  r  r  r   r  r/   str_bytesr   r  r   rJ  rD  rS  rW  rY  r[  r*   r+   r,   <module>r     s   " # " " " " "           



                                     ) ) ) ) ) ) ) ) )                                           ( ' ' ' ' '                       ! ! ! ! !   8   0& & & &"# # # #0# # #&   B& & & &,   (- - - -; ; ; ;.   
   : , , ,   ,  ,  ,F , , ,   .   
   (   
 " " "      
      
   
         ! 
       +-CCFXX  ..1HH  ),<+==26K5LL  11O O 21 O 11Q Q 21 Q& 11M M 21 M 11D'?33> > 43 21 > 11-/NOOM M PO 21 M 11J J 21 J !566. . 76 . !566. . 76 . !<==  >= ( !566! ! 76 ! !<==! ! >= ! !<==! ! >= ! !788> > 98 >" !233B B 43 B" !233!$ !$ 43 !$H !788)Az?;;", ", <; 98 ",J !78865 65 98 65r !23315 15 43 15h !788 ,  , 98  ,F, , ,2B B B? ? ?/ / / / / / / /$2 2 2 2 2) 2 2 2*. . . . .i . . .D| | | | | | | |FO0 O0 O0 O0 O0- O0 O0 O0d ,,5OPPH' H' H' H' H'+ H' H' QPH'VQK QK QK QK QKK QK QK QKp ,,5OPPN0 N0 N0 N0 N0o} N0 N0 QPN0bJ6 J6 J6 J6 J6 J6 J6 J6Z   = = =                F [+ [+ [+ [+ [+O [+ [+ [+Dg/ g/ g/ g/ g/m g/ g/ g/^ fmGV]	6=	8?DJJ  J	 (/
	' 	' 	'. . . ._4 _4 _4 _4 _4 _4 _4 _4 _4 _4s   B   B*)B*