o
    i4                    @  s  d Z ddlmZ ddlZddlmZmZmZ ddlmZ ddl	m
Z
 ddlZddlZddlZddlm  mZ ddlmZmZ ddlZddlmZmZmZmZmZmZmZmZm Z m!Z! ddl"m#Z$ ddl%m  m&Z& dd	l%m'Z'm(Z(m)Z)m*Z*m+Z+m,Z,m-Z- zddl.Z.d
Z/W n e0y   dZ/Y nw dddddddddddddZ1dddZ2ddd Z3dd!d"Z4d#d$ Z5dd%d&Z6dd)d*Z7dd+d,Z8dd/d0Z9dd2d3Z:ej;d4d5 Z<ej;d6d' Z=ej;d7d8 Z>ej;d9d: Z?ej;d;d< Z@ej;d=d> ZAej;d?d@ ZBej;dAdB ZCej;dCdD ZDej;dEdF ZEej;dGdH ZFej;dIdJ ZGej;dKdL ZHej;dMdN ZIej;dOdP ZJd>d@gZKdBdDgZLdFdHgZMdJdLgZNeKeL eM ZOeKeL eN ZPeOdNg ZQePdPg ZRejSjTejSUdeQejSUdQddRgdSdT ZVejSjTejSUdeQejSUdUdVdWgdXdY ZWejSjTejSUdeQdZd[ ZXejSjTejSUdeRd\d] ZYejSjTejSUdeOd^d_ ZZejSjTejSUdeKd`da Z[ejSjTejSUdeKdbdc Z\ejSjTejSUdeLejSUdddedfgdgdh Z]G didj djZ^G dkdl dle^Z_G dmdn dne^Z`G dodp dpZaG dqdr dreaZbejSjce/ dsdtG dudv dve`ebZdG dwdx dxZeG dydz dzeeedZfG d{d| d|e_ebZgG d}d~ d~e`eaZhG dd deeehZiG dd dZjG dd dZkG dd dZlejSjTG dd dekehZmejSjTG dd dekeiZnejSjTG dd delehZoejSjTG dd deleiZpG dd dejehZqG dd dejeiZrG dd de_eaZsedjteudjtejvdjtewdjtexdjteydjtezddd ej{djte|djti	Z}dd Z~dddZG dd dZdS )a  SQL io tests

The SQL tests are broken down in different classes:

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

    )annotationsN)datedatetimetime)StringIOPath)is_datetime64_dtypeis_datetime64tz_dtype)
	DataFrameIndex
MultiIndexSeries	Timestampconcat
date_rangeisnato_datetimeto_timedelta)SQLAlchemyEngineSQLDatabaseSQLiteDatabase
get_enginepandasSQL_builderread_sql_queryread_sql_tableTFz1SELECT * FROM iris WHERE Name=? AND SepalLength=?z7SELECT * FROM iris WHERE `Name`=%s AND `SepalLength`=%sz7SELECT * FROM iris WHERE "Name"=%s AND "SepalLength"=%s)sqlitemysql
postgresqlz]
                SELECT * FROM iris WHERE Name=:name AND SepalLength=:length
                zw
                SELECT * FROM iris WHERE
                `Name`=%(name)s AND `SepalLength`=%(length)s
                zw
                SELECT * FROM iris WHERE
                "Name"=%(name)s AND "SepalLength"=%(length)s
                z&SELECT * FROM iris WHERE Name LIKE '%'z(SELECT * FROM iris WHERE `Name` LIKE '%'z(SELECT * FROM iris WHERE "Name" LIKE '%')read_parametersread_named_parametersread_no_parameters_with_percentdialectstrc           
      C  sp   ddl m}m}m}m}m}m} | dkr|n|}| }|d||d||d||d||d||d	|d
}	|	S )Nr   )REALColumnFloatMetaDataStringTabler   irisSepalLength
SepalWidthPetalLength
PetalWidthName   )
sqlalchemyr$   r%   r&   r'   r(   r)   )
r"   r$   r%   r&   r'   r(   r)   dtypemetadatar*    r4   S/var/www/edux/Edux_v2/venv/lib/python3.10/site-packages/pandas/tests/io/test_sql.pyiris_table_metadatae   s    		r6   connsqlite3.Connection	iris_filer   c                 C  sj   |   }d}|| |jd d}t|}t| d}||| W d    d S 1 s.w   Y  d S )NzCREATE TABLE iris (
            "SepalLength" REAL,
            "SepalWidth" REAL,
            "PetalLength" REAL,
            "PetalWidth" REAL,
            "Name" TEXT
        )newlinez&INSERT INTO iris VALUES(?, ?, ?, ?, ?))cursorexecuteopencsvreadernextexecutemany)r7   r9   curstmtcsvfiler@   r4   r4   r5   create_and_load_iris_sqlite3}   s   

"rF   c           
   
     s  ddl m} ddlm} t|}|j| dd |j| d |jd db}t	|}t
|  fdd	|D }|||}	t| |rp|  !} |   | |	 W d    n1 s[w   Y  W d    n1 sjw   Y  n| |	 W d    d S W d    d S 1 sw   Y  d S )
Nr   insertEngineT
checkfirstbindr:   c                   s    g | ]}d d t  |D qS )c                 S  s   i | ]\}}||qS r4   r4   ).0keyvaluer4   r4   r5   
<dictcomp>   s    z3create_and_load_iris.<locals>.<listcomp>.<dictcomp>)ziprO   rowheaderr4   r5   
<listcomp>   s     z(create_and_load_iris.<locals>.<listcomp>)r1   rH   sqlalchemy.enginerJ   r6   dropcreater>   r?   r@   rA   values
isinstanceconnectbeginr=   )
r7   r9   r"   rH   rJ   r*   rE   r@   paramsrD   r4   rV   r5   create_and_load_iris   s,   



" ra   c              	   C  s   d}t | tjr|  }|| d S ddlm} ddlm} ||}t | |r`| 	 *} | 
  | | W d    n1 sAw   Y  W d    d S W d    d S 1 sYw   Y  d S | | d S )Nz+CREATE VIEW iris_view AS SELECT * FROM irisr   textrI   )r]   sqlite3
Connectionr<   r=   r1   rc   rY   rJ   r^   r_   )r7   rD   rC   rc   rJ   r4   r4   r5   create_and_load_iris_view   s    


"rf   c                 C  s   ddl m}m}m}m}m}m}m}m} | dkr|n|}	| dkr"|n|}
| }|d||d||d|	|d||d||d	||d
||d|
|d||d|
}| dkr_|	|d|dd |S )Nr   )TEXTBooleanr%   DateTimer&   Integerr'   r)   r   typesTextColDateCol
IntDateColIntDateOnlyColFloatColIntColBoolColIntColWithNullBoolColWithNullr   DateColWithTzT)timezone)
r1   rg   rh   r%   ri   r&   rj   r'   r)   append_column)r"   rg   rh   r%   ri   r&   rj   r'   r)   	date_type	bool_typer3   rk   r4   r4   r5   types_table_metadata   s(   (rz   
types_data
list[dict]c                 C  s*   |   }d}|| d}||| d S )Na  CREATE TABLE types (
                    "TextCol" TEXT,
                    "DateCol" TEXT,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" REAL,
                    "IntCol" INTEGER,
                    "BoolCol" INTEGER,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" INTEGER
                )zY
            INSERT INTO types
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
            )r<   r=   rB   )r7   r{   rC   rD   r4   r4   r5   create_and_load_types_sqlite3   s
   
r}   c              	   C  s   ddl m} ddlm} t|}|j| dd |j| d |||}t| |ra| 	 *} | 
  | | W d    n1 sBw   Y  W d    d S W d    d S 1 sZw   Y  d S | | d S )Nr   rG   rI   TrK   rM   )r1   rH   rY   rJ   rz   rZ   r[   r\   r]   r^   r_   r=   )r7   r{   r"   rH   rJ   rk   rD   r4   r4   r5   create_and_load_types   s   


"r~   framer   c                 C  s<   | j d j}| jd }t|tjsJ t|jg d d S )Nr   ffffff@g      @gffffff?皙?Iris-setosa)	dtypestypeiloc
issubclassnpfloatingtmequalContentsr\   )r   pytyperU   r4   r4   r5   check_iris_frame   s   
r   
table_namec                 C  s   d| }t | tjr|  }||}n4ddlm} ddlm} ||}t | |rD| 	 } | |}W d    n1 s>w   Y  n| |}|
 d S )Nz SELECT count(*) AS count_1 FROM r   rb   rI   )r]   rd   re   r<   r=   r1   rc   rY   rJ   r^   fetchone)r7   r   rD   rC   resultrc   rJ   r4   r4   r5   
count_rows  s   



r   c                 C  s   | dddd}t |S )Niodatar?   ziris.csvr   )datapath	iris_pathr4   r4   r5   r     s   r   c                   C  s4   ddddddddddd	
dd
dddddd d dd	
gS )Nfirst2000-01-03 00:00:00i wi2g333333$@   Fz2000-01-01 00:00:00-08:00)
rl   rm   rn   ro   rp   rq   rr   rs   rt   ru   2000-01-04 00:00:00i 'Pi\2z2000-06-01 00:00:00-07:00r4   r4   r4   r4   r5   r{     s.   c              
   C  s2   dddddddddd	}t | }||  |S )Nr#   int64float)	rl   rm   rn   ro   rp   rq   rr   rs   rt   )r   keysastype)r{   r   dfr4   r4   r5   types_data_frame:  s   r   c                  C     g d} g d}t || dS )N)indexABCD))r   gN\^?g9\x`|@g% TO׿g(nI)r   g}C?c^Vg~q"BĿg4?)2000-01-05 00:00:00gGͿ?2r¹e?g>ݦ4gёR?)2000-01-06 00:00:00g?{mX?$?gnk3Am?g
S?columnsr   r   r   r4   r4   r5   test_frame1K  s   r   c                  C  r   )N)r   r   r   ))r   igZ)r   ir   )r   i N  r   )r   i͏r   r   r   r   r4   r4   r5   test_frame3o  s   r   c           	   	   c  s    t d}t d}|jdd|jjjid}||}|ds't|| d |ds<|D ]}|	d	 q.t
||d |V  | &}|  |d
}|| W d    n1 s]w   Y  W d    n1 slw   Y  |  d S )Nr1   pymysqlz*mysql+pymysql://root@localhost:3306/pandasclient_flagconnect_argsr*   r   rk   ru    DROP TABLE IF EXISTS test_frame;)pytestimportorskipcreate_engine	constantsCLIENTMULTI_STATEMENTSinspect	has_tablera   popr~   r^   r_   rc   r=   dispose)	r   r{   r1   r   engineinspentryr7   rD   r4   r4   r5   mysql_pymysql_engine{  s.   







r   c                 c      |   V  d S Nr^   )r   r4   r4   r5   mysql_pymysql_conn     r   c              	   c  s    t d}t d |d}||}|ds t|| d |ds+t||d |V  | &}|  |	d}|
| W d    n1 sLw   Y  W d    n1 s[w   Y  |  d S )Nr1   psycopg2z=postgresql+psycopg2://postgres:postgres@localhost:5432/pandasr*   r   rk   r   )r   r   r   r   r   ra   r~   r^   r_   rc   r=   r   )r   r{   r1   r   r   r7   rD   r4   r4   r5   postgresql_psycopg2_engine  s(   







r   c                 c  r   r   r   )r   r4   r4   r5   postgresql_psycopg2_conn  r   r   c                  c  s(    t d} | d}|V  |  d S )Nr1   z	sqlite://)r   r   r   r   )r1   r   r4   r4   r5   sqlite_engine  s
   

r   c                 c  r   r   r   )r   r4   r4   r5   sqlite_conn  r   r   c                 C  s   t | |d | S )Nr   )ra   )r   r   r4   r4   r5   sqlite_iris_engine  s   r   c                 c  r   r   r   )r   r4   r4   r5   sqlite_iris_conn  r   r   c                  c  s    t d} | V  |   d S N:memory:)rd   r^   close)r7   r4   r4   r5   sqlite_buildin  s   
r   c                 C  s   t | | | S r   )rF   )r   r   r4   r4   r5   sqlite_buildin_iris  s   
r   methodmultic                 C  sJ   | | } t| }|j|d|d |dsJ t| dt|ks#J d S )N
test_framer   getfixturevaluer   to_sqlr   r   len)r7   r   r   request	pandasSQLr4   r4   r5   test_to_sql  s
   
r   zmode, num_row_coef)replacer   )append   c                 C  s^   | | } t| }|j|ddd |j|d|d |ds J t| d|t| ks-J d S )Nr   fail	if_existsr   )r7   modenum_row_coefr   r   r   r4   r4   r5   test_to_sql_exist   s   
r   c                 C  sx   | | } t| }|j|ddd |dsJ d}tjt|d |j|ddd W d    d S 1 s5w   Y  d S )Nr   r   r   z!Table 'test_frame' already existsmatch)r   r   r   r   r   raises
ValueError)r7   r   r   r   msgr4   r4   r5   test_to_sql_exist_fail  s   
"r   c                 C  s(   | | } t| }|d}t| d S )NSELECT * FROM iris)r   r   
read_queryr   )r7   r   r   
iris_framer4   r4   r5   test_read_iris  s   

r   c                   sh   | | } t| }g   fdd}|j|d|d |ds J  dgks'J t| dt|ks2J d S )Nc                   s2    d  fdd|D }|| j | d S )Nr   c                   s   g | ]	}t t |qS r4   )dictrS   rT   r   r4   r5   rX   ,  s    z8test_to_sql_callable.<locals>.sample.<locals>.<listcomp>)r   r=   tablerH   )pd_tabler7   r   	data_iterr   checkr   r5   sample*  s   
z$test_to_sql_callable.<locals>.sampler   r   r   r   )r7   r   r   r   r   r4   r   r5   test_to_sql_callable"  s   
r   c                 C  s   | | } td| }t|jjjtjsJ t|j	jjtj
s!J t|jjjtj
s,J t|jjjtjs7J t|jjjtjsBJ d S Nrk   )r   sqlr   r   rp   r2   r   r   r   rq   integerrr   rs   rt   )r7   r   r   r4   r4   r5   test_default_type_conversion5  s   
r   c           	   	   C  s   | | } ddlm} ddlm} tg dg dd}|jd| dd	 d
}||}t| |r]|  !}|	  |
| W d    n1 sHw   Y  W d    n1 sWw   Y  n| 
| td| }t|| td| }t|| d S )Nr   rb   rI   r   r      )皙?r   g333333?abr   Fr   z}DROP PROCEDURE IF EXISTS get_testdb;

    CREATE PROCEDURE get_testdb ()

    BEGIN
        SELECT * FROM test_frame;
    ENDzCALL get_testdb();)r   r1   rc   rY   rJ   r   r   r]   r^   r_   r=   r   r   r   assert_frame_equalread_sql)	r7   r   rc   rJ   r   procengine_connres1res2r4   r4   r5   test_read_procedureH  s(   




r  expected_countr   zSuccess!c                   s    fdd}| | } tddgddgddgd	}|jd
| d|d}t ts.|d u s-J n| ks4J td
| }t|| d S )Nc                   s   |j }| E}t }t|}|| |d ddd |D }| jr1| j d| j	 }	n| j	}	d|	 d| d}
|j
|
|d	 W d     S 1 sOw   Y   S )
Nr   z, c                 S  s   g | ]}d | d qS )"r4   )rO   kr4   r4   r5   rX   }      zVtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<listcomp>.zCOPY z (z) FROM STDIN WITH CSV)r   file)
connectionr<   r   r?   writer	writerowsseekjoinschemanamecopy_expert)r   r7   r   r   
dbapi_connrC   s_bufr  r   r   	sql_queryr  r4   r5   psql_insert_copyt  s    




zBtest_copy_from_callable_insertion_method.<locals>.psql_insert_copyr   r   r  r   r  ncol1col2col3r   F)r   r   )	r   r   r   r]   intr   r   r   r  )r7   r  r   r   expectedresult_countr   r4   r  r5   (test_copy_from_callable_insertion_methodm  s   

r)  c                   @  s   e Zd Zdd ZdS )	MixInBasec                 C  s2   t | dr|  D ]}| | q	|   d S d S )Nr7   )hasattr_get_all_tables
drop_table_close_conn)selftblr4   r4   r5   teardown_method  s
   
zMixInBase.teardown_methodN)__name__
__module____qualname__r1  r4   r4   r4   r5   r*    s    r*  c                   @  $   e Zd Zdd Zdd Zdd ZdS )SQLiteMixInc                 C  s&   | j dt|  | j   d S NzDROP TABLE IF EXISTS )r7   r=   r   _get_valid_sqlite_namecommitr/  r   r4   r4   r5   r-    s   zSQLiteMixIn.drop_tablec                 C  s   | j d}dd | D S )Nz1SELECT name FROM sqlite_master WHERE type='table'c                 S     g | ]}|d  qS )r   r4   )rO   r   r4   r4   r5   rX         z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r7   r=   fetchall)r/  cr4   r4   r5   r,    s   zSQLiteMixIn._get_all_tablesc                 C     | j   d S r   r7   r   r/  r4   r4   r5   r.       zSQLiteMixIn._close_connNr2  r3  r4  r-  r,  r.  r4   r4   r4   r5   r6    s    r6  c                   @  r5  )SQLAlchemyMixInc                 C  s   t | j| d S r   )r   r   r7   r-  r:  r4   r4   r5   r-       zSQLAlchemyMixIn.drop_tablec                 C  s   ddl m} || j S )Nr   r   )r1   r   r7   get_table_names)r/  r   r4   r4   r5   r,    s   zSQLAlchemyMixIn._get_all_tablesc                 C  r?  r   )r7   r   rA  r4   r4   r5   r.       zSQLAlchemyMixIn._close_connNrC  r4   r4   r4   r5   rD    s    rD  c                   @  sv   e Zd ZdZejdd Zejdd Zdd Zdd	 Z	d
d Z
dd ZdddZdd Zdd Zdd Zdd ZdS )PandasSQLTestzT
    Base class with common private methods for SQLAlchemy and fallback cases.

    c                 C  sN   t | ds	|   | d t| jtjrt| j| d S t| j|| j	 d S )Nr7   r*   )
r+  setup_connectr-  r]   r7   rd   re   rF   ra   flavor)r/  r   r4   r4   r5   load_iris_data  s   

zPandasSQLTest.load_iris_datac                 C  sp   t | ds	|   | jdkr|D ]}|d qt| jtjr.dd |D }t| j| d S t	| j|| j d S )Nr7   r   ru   c                 S  s   g | ]}t | qS r4   )tupler\   )rO   r   r4   r4   r5   rX     r  z1PandasSQLTest.load_types_data.<locals>.<listcomp>)
r+  rJ  rK  r   r]   r7   rd   re   r}   r~   )r/  r{   r   r4   r4   r5   load_types_data  s   

zPandasSQLTest.load_types_datac                 C  s2   t d | j }ddg}| jj||d}t| d S )Nr   r   r   r`   SQL_STRINGSrK  r   r   r   r/  queryr`   r   r4   r4   r5   _read_sql_iris_parameter  s   z&PandasSQLTest._read_sql_iris_parameterc                 C  s4   t d | j }ddd}| jj||d}t| d S )Nr    r   r   )r  lengthrO  rP  rR  r4   r4   r5   _read_sql_iris_named_parameter  s   
z,PandasSQLTest._read_sql_iris_named_parameterc                 C  s*   t d | j }| jj|d d}t| d S )Nr!   rO  rP  )r/  rS  r   r4   r4   r5   (_read_sql_iris_no_parameter_with_percent  s   z6PandasSQLTest._read_sql_iris_no_parameter_with_percentc                 C  s.   |  d | j|jd d ddksJ d S )Nr   r   )r-  r   r   r   r/  r   r4   r4   r5   _to_sql_empty  s   
$zPandasSQLTest._to_sql_emptyautoc                 K  sj   |  d | jj|dfd|i|dksJ | jdsJ t|}t| jd}||ks.J |  d dS )z `to_sql` with the `engine` paramr   r      N)r-  r   r   r   r   r   r7   )r/  r   r   engine_kwargsnum_entriesnum_rowsr4   r4   r5   _to_sql_with_sql_engine  s   
z%PandasSQLTest._to_sql_with_sql_enginec                 C  sR   |  d | j|ddksJ | jd}|jddd d |j_t|| d S )Ntest_frame_roundtripr[  "SELECT * FROM test_frame_roundtriplevel_0Tinplace)	r-  r   r   r   	set_indexr   r  r   r  r/  r   r   r4   r4   r5   
_roundtrip  s   
zPandasSQLTest._roundtripc                 C  s(   | j d}| }t|g d d S )Nr   r   )r   r=   r   r   r   r/  iris_resultsrU   r4   r4   r5   _execute_sql  s   zPandasSQLTest._execute_sqlc                 C  sN   t jddgg ddgd}| j|ddksJ | d}|dggks%J d S )Nr    @line1r   g      ?line2r   r   r   r   r   r   test_to_sql_saves_indexr   )r   from_recordsr   r   _get_index_columns)r/  r   ix_colsr4   r4   r5   _to_sql_save_index  s   
z PandasSQLTest._to_sql_save_indexc                 C  sN  | j  %}d}t| j tr|| nddlm} ||}|| W d    n1 s-w   Y  G dd dt}d}t| j trLddlm} ||}z| j  
}|| |d1 s_w   Y  W n	 |yn   Y nw | j 	d}t
|dks}J | j  }|| W d    n1 sw   Y  | j 	d}t
|d	ksJ d S )
Nz'CREATE TABLE test_trans (A INT, B TEXT)r   rb   c                   @     e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionNr2  r3  r4  r4   r4   r4   r5   DummyException&      ry  z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')errorzSELECT * FROM test_transr   )r   run_transactionr]   r   r=   r1   rc   	Exceptionr   r   r   )r/  transrD   rc   ry  ins_sqlresr  r4   r4   r5   _transaction_test  s:   


zPandasSQLTest._transaction_testN)rZ  )r2  r3  r4  __doc__r   fixturerL  rN  rT  rV  rW  rY  r_  rg  rj  rv  r  r4   r4   r4   r5   rI    s    
	

rI  c                
   @  s  e Zd ZU dZdZded< dd Zejddd	d
 Z	dd Z
dd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd  Zd!d" Zd#d$ Zejd%g d&ejd'ejd(d)fejd*d+fejd(d)fejd*d+fgd,d- Zd.d/ Zd0d1 Zd2d3 Z ejd4g d5d6d7 Z!d8d9 Z"d:d; Z#ejd<d=e$e%e$e%d>gd?d@ Z&dAdB Z'dCdD Z(dEdF Z)dGdH Z*dIdJ Z+dKdL Z,dMdN Z-dOdP Z.dQdR Z/d=S )S_TestSQLApia  
    Base class to test the public API.

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

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

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

    r   r#   r   c                 C     |   | _d S r   r^   r7   rA  r4   r4   r5   rJ  X  rB  z_TestSQLApi.setup_connectTautousec                 C     |    d S r   )load_test_data_and_sqlr/  rL  rN  r4   r4   r5   setup_method[  s   z_TestSQLApi.setup_methodc                 C  s   t | j d S r   )rf   r7   rA  r4   r4   r5   r  _  rB  z"_TestSQLApi.load_test_data_and_sqlc                 C  s   t d| j}t| d S )NzSELECT * FROM iris_view)r   r   r7   r   r/  r   r4   r4   r5   test_read_sql_viewb  s   z_TestSQLApi.test_read_sql_viewc                 C  s8   d}t j|| jdd}t || j}tt|| d S )Nz/SELECT * FROM iris_view WHERE SepalLength < 0.0   	chunksize)r   r   r7   r   r  r   )r/  rS  
with_batchwithout_batchr4   r4   r5   &test_read_sql_with_chunksize_no_resultf  s   z2_TestSQLApi.test_read_sql_with_chunksize_no_resultc                 C  s&   t |d| j t d| jsJ d S )Nr   )r   r   r7   r   rX  r4   r4   r5   r   l  s   z_TestSQLApi.test_to_sqlc                 C  sr   t j|d| jdd t d| jsJ d}tjt|d t j|d| jdd W d    d S 1 s2w   Y  d S )Ntest_frame2r   r   z"Table 'test_frame2' already existsr   )r   r   r7   r   r   r   r   r/  r   r   r4   r4   r5   test_to_sql_failp  s   "z_TestSQLApi.test_to_sql_failc                 C  s^   t j|d| jdd t j|d| jdd t d| jsJ t|}t| jd}||ks-J d S )Nr   r   r   r   r   r   r7   r   r   r   r/  r   r]  r^  r4   r4   r5   test_to_sql_replacex  s   z_TestSQLApi.test_to_sql_replacec                 C  sr   t j|d| jdddksJ t j|d| jdddksJ t d| js%J dt| }t| jd}||ks7J d S )Ntest_frame4r   r   r[  r   r   r  r  r4   r4   r5   test_to_sql_append  s   z_TestSQLApi.test_to_sql_appendc                 C  s2   t j|d| jdd t d| j}t|| d S )Ntest_frame5Fr  zSELECT * FROM test_frame5)r   r   r7   r  r   r  )r/  r   r   r4   r4   r5   test_to_sql_type_mapping  s   z$_TestSQLApi.test_to_sql_type_mappingc                 C  sL   t tjddddd}tj|d| jdd td	| j}t|	 | d S )
Nr  r   r2   seriesr  test_seriesFr  zSELECT * FROM test_series)
r   r   aranger   r   r7   r   r   r  to_frame)r/  ss2r4   r4   r5   test_to_sql_series  s   z_TestSQLApi.test_to_sql_seriesc                 C  s\   t j|d| jd t jd| jd}|j|_|jddd |jt d |j_t	
|| d S )Nr`  conra  rb  Trc  )r   r   r7   r   r   re  r   r&  r  r   r  rf  r4   r4   r5   test_roundtrip  s   z_TestSQLApi.test_roundtripc                 C  s6   t j|d| jddd t jd| jd}t|| d S )Nr`  Fr   )r  r   r  ra  r  )r   r   r7   r   r   r  rf  r4   r4   r5   test_roundtrip_chunksize  s   z$_TestSQLApi.test_roundtrip_chunksizec                 C  s,   t jd| jd}| }t|g d d S )Nr   r  r   )r   r=   r7   r   r   r   rh  r4   r4   r5   test_execute_sql  s   z_TestSQLApi.test_execute_sqlc              	   C  s  t d| j}t|jjjtjrJ t jd| jdgd}t|jjjtjs'J |j	 t
ddddddt
ddddddgks@J t jd| jdd	id}t|jjjtjsVJ |j	 t
ddddddt
ddddddgksoJ t jd| jd
gd}t|jjjtjsJ |j	 t
ddddddt
ddddddgksJ t jd| jd
did}t|jjjtjsJ |j	 t
ddddddt
ddddddgksJ t jd| jddid}t|jjjtjsJ |j	 t
dt
dgksJ d S )NSELECT * FROM typesrm   parse_datesi  r   r  r   r[  %Y-%m-%d %H:%M:%Srn   i        i  r  ro   z%Y%m%dz
2010-10-10z
2010-12-12)r   r   r7   r   rm   r2   r   r   
datetime64tolistr   rn   ro   r/  r   r4   r4   r5   test_date_parsing  s\   





z_TestSQLApi.test_date_parsingr{  )ignoreraisecoercezread_sql, text, moder  )r1   fallbackrk   r1   c                 C  sD   | j |v r |ddi}||| jdd|iid}t|| d S d S )Nrm   zdatetime64[ns]errors)r  r  )r   r   r7   r   r  )r/  r  rc   r   r{  r   r'  r   r4   r4   r5   test_custom_dateparsing_error  s   
z)_TestSQLApi.test_custom_dateparsing_errorc                 C  sH   t jd| jdddgd}t|jjjtjsJ t|j	jjtjs"J d S )Nr  rm   rn   )	index_colr  )
r   r   r7   r   r   r2   r   r   r  rn   r  r4   r4   r5   test_date_and_index  s   z_TestSQLApi.test_date_and_indexc                 C  s   t tddgdd }tt |d| j}W d    n1 s#w   Y  |dks.J t	d| j}t
|d |d d d S )	Nz00:00:01z00:00:03foor  test_timedeltar   zSELECT * FROM test_timedeltar   )r   r   r  r   assert_produces_warningUserWarningr   r7   r   r   assert_series_equalview)r/  r   r(  r   r4   r4   r5   r    s   z_TestSQLApi.test_timedeltac                 C  s^   t dddgi}d}tjt|d |d| jd u sJ W d    d S 1 s(w   Y  d S )Nr  y      ?      ?y               @zComplex datatypes not supportedr   test_complex)r   r   r   r   r   r7   )r/  r   r   r4   r4   r5   test_complex_raises%  s
   "z_TestSQLApi.test_complex_raiseszindex_name,index_label,expected))NNr   )Nother_labelr  )
index_nameNr  )r  r  r  )r   N0)Nr   r  c                 C  sT   t dtdi}||j_d}tj|d| j|d t|| j}|jd |ks(J d S )Nr#  r[  SELECT * FROM test_index_labeltest_index_labelindex_labelr   )	r   ranger   r  r   r   r7   r   r   )r/  r  r  r'  
temp_framerS  r   r4   r4   r5   test_to_sql_index_label+  s   z#_TestSQLApi.test_to_sql_index_labelc                 C  s  d}t dtditddgd}t|d| j}||ksJ td| j}|jd d	ks/J |jd
 dks8J tj|d| jdddgd}||ksKJ td| j}|jd d 	 ddgksaJ ddg|j
_tj|d| jdd}||kswJ td| j}|jd d 	 ddgksJ tj|d| jdddgd}||ksJ td| j}|jd d 	 ddgksJ d}tjt|d tj|d| jddd W d    d S 1 sw   Y  d S )Nr[  r#  )A0A1)B0B1r  r  r  r   rb  r   level_1r   r   r   )r   r  r   r   r   r   zALength of 'index_label' should match number of levels, which is 2r   )r   r  r   from_productr   r   r7   r   r   r  r   namesr   r   r   )r/  expected_row_countr  r   r   r   r4   r4   r5   "test_to_sql_index_label_multiindexD  s^   

"z._TestSQLApi.test_to_sql_index_label_multiindexc                 C  sT   t jddgg dddgd}|d| j tjd| jddgd	}tj||d
d d S )Nrk  rn  rp  r   r   rq  test_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtripr  Tcheck_index_type)r   rs  r   r7   r   r   r   r  r/  r   r   r4   r4   r5   r  }  s   z%_TestSQLApi.test_multiindex_roundtripr2   Nr   r   c                 C  s^   t ddgddggddgd}|d| jd	ksJ ||}tjd
| j|d}t|| d S )N333333?g333333@ffffff@g333333@r   r   r   test_dtype_argumentr   z$SELECT A, B FROM test_dtype_argumentr  r2   )r   r   r7   r   r   r   r   r  )r/  r2   r   r'  r   r4   r4   r5   r    s   
z_TestSQLApi.test_dtype_argumentc                 C  s4   t ddgddggddgd}tj|d| jdd	 d S )
Nr   r   r  r[  r   r   test_frame_integer_col_namesr   r   )r   r   r   r7   r  r4   r4   r5   test_integer_col_names  s   z"_TestSQLApi.test_integer_col_namesc                 C  s"   t j|d| jd}d|v sJ d S )Ntestr  CREATEr   
get_schemar7   r/  r   
create_sqlr4   r4   r5   test_get_schema  s   z_TestSQLApi.test_get_schemac                 C  s$   t j|d| jdd}d|v sJ d S )Nr  pypi)r  r  zCREATE TABLE pypi.r  r  r4   r4   r5   test_get_schema_with_schema  s   z'_TestSQLApi.test_get_schema_with_schemac                 C  sj   | j dkrddlm} |}nd}tddgddgd	}tj|d
| jd|id}d|v s-J d|v s3J d S )Nr1   r   )rj   INTEGER皙?r  rl  皙@r  r  r  r  r  )r   r1   rj   r   r   r  r7   )r/  rj   r2   float_framer  r4   r4   r5   test_get_schema_dtypes  s   
z"_TestSQLApi.test_get_schema_dtypesc                 C  sf   t ddgddgd}tj|d| jdd}d	}||v sJ tj|d| jd
dgd}d}||v s1J d S )Nr  r  rl  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  r7   )r/  r   r   r  constraint_sentencer4   r4   r5   test_get_schema_keys  s   z _TestSQLApi.test_get_schema_keysc                 C  s  t tjddtdd}|jd| jdd td| j}t  }d	}g d
}tjd| jddD ]}t	||gdd}t
||| ksCJ |d7 }q/t|| | jdkrt  }d	}g d
}tjd| jddD ]}t	||gdd}t
||| ksyJ |d7 }qet|| d S d S )N   r  abcder   test_chunksizeFr  zselect * from test_chunksizer   )r  r  r  r  r   r  Tignore_indexr   r1   )r   r   randomrandnlistr   r7   r   r   r   r   r   r  r   r   )r/  r   r  r  isizeschunkres3r4   r4   r5   test_chunksize_read  s.   



z_TestSQLApi.test_chunksize_readc                 C  s`   t g dg dd}| }|d d|d< |jd| jdd td	| j}t|| d S )
Nr   )John P. Doez	Jane Dover  )	person_idperson_namer  categorytest_categoricalFr  zSELECT * FROM test_categorical)	r   copyr   r   r7   r   r   r   r  )r/  r   df2r  r4   r4   r5   r	    s   z_TestSQLApi.test_categoricalc                 C  s2   t ddgddggddgd}|jd| jd	d
 d S )Nr   r   r  r[     ér  r   test_unicodeFr  )r   r   r7   r  r4   r4   r5   test_unicode_column_name  s   z$_TestSQLApi.test_unicode_column_namec                 C  sJ   t g ddtjdgd}|jd| jdd td| j}t|| d S )	Nr   r   r   r   r  r  zd1187b08-4943-4c8d-a7f6Fr  z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r   nanr   r7   r   r   r   r  r/  r   r  r4   r4   r5   test_escaped_table_name  s   z#_TestSQLApi.test_escaped_table_name)0r2  r3  r4  r  rK  __annotations__rJ  r   r  r  r  r  r  r   r  r  r  r  r  r  r  r  r  markparametrizer   r  r   r   r  r  r  r  r  r  r  r&  r   r  r  r  r  r  r  r  r	  r  r  r4   r4   r4   r5   r  D  sz   
 

7




9
	"r  SQLAlchemy not installedreasonc                   @  s   e Zd ZdZdZdZdd Zdd Zdd	 Zd
d Z	dd Z
dd Zdd Zdd Zejddddddddddddd d!d"eeejd#krGd$nd%fgd&d' Zejd(d)d*gd+d, Zd-d. Zed/d0d1 Zd2d3 Zd4d5 Zd6d7 Zd8S )9
TestSQLApiz
    Test the public API as it would be used directly

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

    r   r1   c                 C  
   t dS Nzsqlite:///:memory:r1   r   rA  r4   r4   r5   r^        
zTestSQLApi.connectc                 C  s@   t |d| j ddg}t jd| j|d}|j |ksJ d S )Nr   r   r   r   )r   r   r7   r   r   r  )r/  r   colsr   r4   r4   r5   test_read_table_columns  s   z"TestSQLApi.test_read_table_columnsc                 C  s   t |d| j t jd| jdd}|jjdgksJ t jd| jddgd}|jjddgks/J t jd| jddgddgd}|jjddgksGJ |j ddgksRJ d S )	Nr   r   r  r   r   r   r   )r  r   )r   r   r7   r   r   r  r   r  rf  r4   r4   r5   test_read_table_index_col   s   z$TestSQLApi.test_read_table_index_colc                 C  sT   t d| j}t d| j}t|| t d| j}t d| j}t|| d S )Nr   r*   )r   r   r7   r  r   r  r   )r/  iris_frame1iris_frame2r4   r4   r5   test_read_sql_delegate0  s   z!TestSQLApi.test_read_sql_delegatec              
   C  s   ddl m} ddlm} |d|dg}|D ]=}t| j|rM| j !}|  || W d    n1 s8w   Y  W d    n1 sGw   Y  q| j| qt	
d  td| j td| j W d    d S 1 ssw   Y  d S )Nr   rb   rI   z,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);other_tablezSELECT * FROM other_table)r1   rc   rY   rJ   r]   r7   r^   r_   r=   r   r  r   r   r   )r/  rc   rJ   
query_listrS  r7   r4   r4   r5   test_not_reflect_all_tables9  s&   
"z&TestSQLApi.test_not_reflect_all_tablesc                 C  s   t jtdd t| jdd W d    n1 sw   Y  t d  |d| j W d    d S 1 s9w   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 CaseSensitive)r   r  r  r   r   r7   check_case_sensitiver   rX  r4   r4   r5   (test_warning_case_insensitive_table_nameN  s   "z3TestSQLApi.test_warning_case_insensitive_table_namec                 C  s6   ddl m} |j| j}|d}dd |D }|S )Nr   )
reflectiontest_index_savedc                 S  r;  column_namesr4   rO   r   r4   r4   r5   rX   c  r<  z1TestSQLApi._get_index_columns.<locals>.<listcomp>)rY   r,  	Inspectorfrom_enginer7   get_indexes)r/  tbl_namer,  r   ixsr4   r4   r5   rt  ^  s
   
zTestSQLApi._get_index_columnsc                 C  s\   ddl m} tdtddgddi}t| j}tjd||d	}t|j	j
d j|s,J d S )
Nr   )	TIMESTAMPr   201412120154201412110254Tutc	test_typer   )r1   r6  r   r   r   r   r7   SQLTabler]   r   r>  r   )r/  r6  r   dbr   r4   r4   r5   test_sqlalchemy_type_mappingf  s   z'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)int8SMALLINT)Int8rA  )uint8rA  )UInt8rA  )int16rA  )Int16rA  )uint16r  )UInt16r  )int32r  )Int32r  )uint32BIGINT)UInt32rL  )r   rL  )Int64rL  r   rL  r  c                 C  sP   t ddgdg|d}t| j}tjd||d}t|jjjj	}||ks&J d S )Nr   r   r  r   r2   r;  r<  )
r   r   r   r7   r=  r#   r   r>  r  r   )r/  r   r'  r   r>  r   r   r4   r4   r5   test_sqlalchemy_integer_mappingr  s
   z*TestSQLApi.test_sqlalchemy_integer_mappingr   uint64UInt64c                 C  sd   t ddgdg|d}t| j}tjtdd tjd||d W d    d S 1 s+w   Y  d S )	Nr   r   r  rO  z1Unsigned 64 bit integer datatype is not supportedr   r;  r<  )r   r   r   r7   r   r   r   r=  )r/  r   r   r>  r4   r4   r5   (test_sqlalchemy_integer_overload_mapping  s   "z3TestSQLApi.test_sqlalchemy_integer_overload_mappingc           	      C  s   t  +}d| }d}|j||ddd t||}t||}d}t||}W d    n1 s2w   Y  t || t || t || d S )Nz
sqlite:///r*   r   F)r   r   r   )r   ensure_cleanr   r   r  r   r   r  )	r/  r   r  db_urir   r  r   rS  r  r4   r4   r5   test_database_uri_string  s   
z#TestSQLApi.test_database_uri_stringpg8000c                 C  sD   d}t jtdd td| W d    d S 1 sw   Y  d S )Nz)postgresql+pg8000://user:pass@host/dbnamerW  r   zselect * from tabler   r   ImportErrorr   r  )r/  rU  r4   r4   r5   (test_pg8000_sqlalchemy_passthrough_error  s   "z3TestSQLApi.test_pg8000_sqlalchemy_passthrough_errorc                 C  sH   ddl m} |d}tj|| jddid}t|d }|dhks"J d S )Nr   rb   z#select * from iris where name=:namer  zIris-versicolorrO  r/   )r1   rc   r   r  r7   set)r/  rc   	name_textiris_df	all_namesr4   r4   r5   test_query_by_text_obj  s
   z!TestSQLApi.test_query_by_text_objc                 C  sh   ddl m}m} t| j}|||jj|dk}tj	|| j
ddid}t|d }|dhks2J d S )Nr   )	bindparamselectr  r   rO  r/   )r1   r`  ra  r6   rK  wherer>  r/   r   r  r7   r[  )r/  r`  ra  r*   name_selectr]  r^  r4   r4   r5   test_query_by_select_obj  s   
z#TestSQLApi.test_query_by_select_objc                 C  sF   t g dg dd}|jd| jdd td| j}t|| d S )Nr  r  r[  r  )r   z%_variationtest_column_percentageFr  r   r   r7   r   r   r   r  r  r4   r4   r5   test_column_with_percentage  s   z&TestSQLApi.test_column_with_percentageN)r2  r3  r4  r  rK  r   r^   r  r   r#  r&  r+  rt  r?  r   r  r  r&  r   r2   r  rP  rS  rV  tdskip_if_installedrZ  r_  rd  rh  r4   r4   r4   r5   r    sP    	
	
	
	r  c                      s,   e Zd ZdZejdd fddZ  ZS )_EngineToConnMixinzS
    A mixin that causes setup_connect to create a conn rather than an engine.
    Tr  c                 #  sp    t    | j}| }| | _t|| _|| _	|| _d V  | j
  | j  | j	| _t| j	| _d S r   )superr  r7   r^   r_   _EngineToConnMixin__txr   r   r   _EngineToConnMixin__enginerollbackr   )r/  rL  rN  r   r7   	__class__r4   r5   r    s   



z_EngineToConnMixin.setup_method)r2  r3  r4  r  r   r  r  __classcell__r4   r4   rp  r5   rk    s    
rk  c                   @  rw  )TestSQLApiConnNrx  r4   r4   r4   r5   rs    rz  rs  c                   @  sz   e Zd ZdZdZdZdddZdd Zej	j
ed	d
dd Zej	j
ed	d
dd Zdd Zdd Zdd Zdd ZdS )TestSQLiteFallbackApiz9
    Test the public sqlite connection fallback API

    r   r  r   c                 C  s
   t |S r   rd   r^   )r/  databaser4   r4   r5   r^     r  zTestSQLiteFallbackApi.connectc                 C  s   t  -}| |}tj|d|dddksJ |  | |}td|}|  W d    n1 s4w   Y  t || d S )Ntest_frame3_legacyFr  r[  z!SELECT * FROM test_frame3_legacy;)r   rT  r^   r   r   r   r   r  )r/  r   r  r7   r   r4   r4   r5   test_sql_open_close  s   




z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installedr  c                 C  sH   d}d}t jt|d td| W d    d S 1 sw   Y  d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installedr   r   rX  )r/  r7   r   r4   r4   r5   test_con_string_import_error	  s
   "z2TestSQLiteFallbackApi.test_con_string_import_errorc                 C  sR   G dd d}|d}t t td| W d    d S 1 s"w   Y  d S )Nc                   @  s   e Zd ZdddZdd ZdS )	z~TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnectionreturnNonec                 _  s   t j|i || _d S r   )rd   re   r7   )r/  argskwargsr4   r4   r5   __init__  rE  zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__init__c                 S  s   t | j|S r   )getattrr7   )r/  r  r4   r4   r5   __getattr__     zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__getattr__N)rz  r{  )r2  r3  r4  r~  r  r4   r4   r4   r5   MockSqliteConnection  s    
r  r   zSELECT 1)r   r  r  r   r  )r/  r  r7   r4   r4   r5   Jtest_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed  s
   "z`TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installedc                 C  sp   t d| j}t d| j}t|| d}tjt j|d t d| j W d    d S 1 s1w   Y  d S )Nr   z9Execution failed on sql 'iris': near "iris": syntax errorr   r*   )	r   r   r7   r  r   r  r   r   DatabaseError)r/  r!  r"  r   r4   r4   r5   r#    s   "z,TestSQLiteFallbackApi.test_read_sql_delegatec                 C  s   t |d}d|v sJ d S )Nr  r  )r   r  r  r4   r4   r5   test_get_schema2(  s   z&TestSQLiteFallbackApi.test_get_schema2c                 C  sF   | dD ]}|  d d|kr|  d   S qtd| d)N
r   z""r   zColumn 
 not found)splitstripr   )r/  r  columncolr4   r4   r5   _get_sqlite_column_type-  s
   z-TestSQLiteFallbackApi._get_sqlite_column_typec                 C  sT   t dtddgddi}t| j}tjd||d}| }| |ddks(J d S )	Nr   r7  r8  Tr9  r;  r<  r6  )r   r   r   r   r7   SQLiteTable
sql_schemar  )r/  r   r>  r   r  r4   r4   r5   test_sqlite_type_mapping4  s   z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r   )r2  r3  r4  r  rK  r   r^   rx  r   r  skipifSQLALCHEMY_INSTALLEDry  r  r#  r  r  r  r4   r4   r4   r5   rt    s    


	rt  c                   @  s  e Zd ZU dZded< ejddddd Zd	d
 Zejdddd Z	e
dd Ze
dd Ze
dd Zdd Zdd Zdd Zdd Zdd Zdd Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- Zd.d/ Zd0d1 Zd2d3 Zd4d5 Zd6d7 Zd8d9 Z d:d; Z!d<d= Z"d>d? Z#d@dA Z$dBdC Z%dDdE Z&dFdG Z'dHdI Z(dJdK Z)dLdM Z*dNdO Z+dPdQ Z,dRdS Z-dTdU Z.dVdW Z/dXdY Z0ej12dZd[e3j4gid[e3j4 gie3j4 gd\gd]gd^d_ Z5d`da Z6dbdc Z7ddde Z8dfdg Z9dhdi Z:djdk Z;dlS )m_TestSQLAlchemyz
    Base class for testing the sqlalchemy backend.

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

    r#   rK  Tclass)r  scopec                 C  s*   |    |   |   }| _|  d S r   )setup_importsetup_driverr^   r7   )clsr7   r4   r4   r5   setup_classO  s   z_TestSQLAlchemy.setup_classc                 C     d S r   r4   rA  r4   r4   r5   r  V     z&_TestSQLAlchemy.load_test_data_and_sqlr  c                 C  r  r   r4   r  r4   r4   r5   r  Y  s   z_TestSQLAlchemy.setup_methodc                 C  s   t s	td d S d S )Nr  )r  r   skipr  r4   r4   r5   r  ]  s   z_TestSQLAlchemy.setup_importc                 C     t  r   NotImplementedErrorr  r4   r4   r5   r  c     z_TestSQLAlchemy.setup_driverc                 C  r  r   r  r  r4   r4   r5   r^   g  r  z_TestSQLAlchemy.connectc                 C  sV   z|   | _t| j| _| j   W d S  tjjy*   t	d| j
 d Y d S w )NzCan't connect to z server)r^   r7   r   r   r   r1   excOperationalErrorr   r  rK  rA  r4   r4   r5   rJ  k  s   
z_TestSQLAlchemy.setup_connectc                 C  r  r   rT  rA  r4   r4   r5   test_read_sql_parametert  r  z'_TestSQLAlchemy.test_read_sql_parameterc                 C  r  r   rV  rA  r4   r4   r5   test_read_sql_named_parameterw  r  z-_TestSQLAlchemy.test_read_sql_named_parameterc                 C     |  | d S r   rY  rX  r4   r4   r5   test_to_sql_emptyz  rB  z!_TestSQLAlchemy.test_to_sql_emptyc                 C  sb   ddl m} |  }tg dg dd}t|}||ddks$J ||}|ds/J d S Nr   rF        ?       @      @      @r  r  r  r  onetwor  r[  )r1   r   r^   r   r   r   r   r   r/  r   	temp_connr  r   r   r4   r4   r5   test_create_table}  s   
z!_TestSQLAlchemy.test_create_tablec                 C  sz   ddl m} |  }tg dg dd}t|}||ddks$J ||}|ds/J |d |dr;J d S r  )	r1   r   r^   r   r   r   r   r   r-  r  r4   r4   r5   test_drop_table  s   

z_TestSQLAlchemy.test_drop_tablec                 C  r  r   rg  rX  r4   r4   r5   r    rB  z_TestSQLAlchemy.test_roundtripc                 C  r  r   rj  rA  r4   r4   r5   r    r  z _TestSQLAlchemy.test_execute_sqlc                 C  s   t jd| jd}t| d S )Nr*   r  )r   r   r7   r   r  r4   r4   r5   test_read_table  s   z_TestSQLAlchemy.test_read_tablec                 C  s.   t jd| jddgd}t|jjddg d S )Nr*   r+   )r  r   )r   r   r7   r   r   r   r\   r  r4   r4   r5   r    s   z'_TestSQLAlchemy.test_read_table_columnsc                 C  sH   d}t jt|d tjd| jd W d    d S 1 sw   Y  d S )Nz!Table this_doesnt_exist not foundr   this_doesnt_existr  )r   r   r   r   r   r7   )r/  r   r4   r4   r5   test_read_table_absent_raises  s   "z-_TestSQLAlchemy.test_read_table_absent_raisesc                 C  s~   t d| j}t|jjjtjsJ t|j	jjtj
sJ t|jjjtjs(J t|jjjtjs3J t|jjjts=J d S r   )r   r   r7   r   rp   r2   r   r   r   rq   r   rr   bool_rs   rt   objectr  r4   r4   r5   r     s   z,_TestSQLAlchemy.test_default_type_conversionc                 C  sH   t ddgid}|jd| jdddksJ td| j}t|| d S )Ni64l            )r   test_bigintFr  r   rg  r  r4   r4   r5   r    s   z_TestSQLAlchemy.test_bigintc                 C  s(   t d| j}t|jjjtjsJ d S r   	r   r   r7   r   rm   r2   r   r   r  r  r4   r4   r5   test_default_date_load  s   z&_TestSQLAlchemy.test_default_date_loadc                 C  s>  dd }t d| j}t|ds|jtjjdd |j}t	|j
s$J t d| jdgd}t|ds=|jtjjdd |j}t	|j
sGJ t|jjdksQJ ||j ttt d| jd	d
dd}|j}t	|j
snJ t|jjdksxJ td| j}|j}t	|j
sJ t|j|j td| j}||j d S )Nc                 S  s   t | jr| d tdksJ | d tdksJ d S t| jrEt| jjdks*J tdddtdddg}t|| jd}t	
| | d S td| j )	Nr   z2000-01-01 08:00:00r   z2000-06-01 07:00:00UTC)tzr  z&DateCol loaded with incorrect type -> )r	   r2   r   r
   r#   dtr  r   r  r   r  AssertionError)r  expected_datar'  r4   r4   r5   r     s   




z:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkzselect * from typesru   z&no column with datetime with time zoner  r  r  r   r  Tr  rk   )r   r7   r+  node
add_markerr   r  xfailru   r
   r2   r#   r  r  r   r  r   r   r   r  )r/  r   r   r   r  r'  r4   r4   r5   test_datetime_with_timezone  s@   "



z+_TestSQLAlchemy.test_datetime_with_timezonec                 C  s   t dtddddi}|jd| jdddksJ | jd	kr(|d jd
|d< n
|d jd |d< t	d| j}t
|| td| j}| jdkr]t|jd tsUJ t|d |d< t
|| d S )Nr   2013-01-01 09:00:00r  
US/Pacificperiodsr  test_datetime_tzFr  r   r  zSELECT * FROM test_datetime_tzr   r   r   )r   r   r   r7   rK  r  
tz_converttz_localizer   r   r   r  r   r]   locr#   r   )r/  r'  r   r4   r4   r5   %test_datetime_with_timezone_roundtrip  s   

z5_TestSQLAlchemy.test_datetime_with_timezone_roundtripc                 C  sd   t dtdddidgd}|jd| jdddksJ td| j}t tjgdgd}t	|| d S )	Nr   i'  r   r   r  test_datetime_obbFr   )
r   r   r   r7   r   r   pdNaTr   r  )r/  r   r   r'  r4   r4   r5   test_out_of_bounds_datetime5  s
   z+_TestSQLAlchemy.test_out_of_bounds_datetimec                 C  sh   t ddddd }tdtdi|d}|jd| jdd	dks!J tjd| jdd
}tj	||dd d S )Nz
2018-01-01r  6H)r  freqnumsr  	foo_table	info_dater  r  F)check_names)
r   
_with_freqr   r  r   r7   r   r   r   r  )r/  datesr'  r   r4   r4   r5   "test_naive_datetimeindex_roundtrip=  s
   z2_TestSQLAlchemy.test_naive_datetimeindex_roundtripc                 C  sF  t d| j}| jdkrtntj}t|jj	j
|sJ t jd| jdgd}t|jj	j
tjs0J t jd| jddid}t|jj	j
tjsFJ t jd| jdddiid}t|jj	j
tjs^J t jd| jdgd}t|jj	j
tjssJ t jd| jddid}t|jj	j
tjsJ t jd| jdd	diid}t|jj	j
tjsJ d S )
Nrk   r   rm   r  r  formatrn   r  unit)r   r   r7   rK  r  r   r  r   rm   r2   r   rn   )r/  r   expected_typer4   r4   r5   r  G  s.   
z!_TestSQLAlchemy.test_date_parsingc                 C  s   t tdddtdd}|d| jdksJ td| j}|jddd	}t	
|| td
| j}|jddd	}| jdkrZt|jd tsJJ t|d |d< t	
|| d S t	
|| d S )Nr  r  r  r  r  test_datetimer   r   )axisSELECT * FROM test_datetimer   r  r   )r   r   r   r  r   r7   r   r   rZ   r   r  r   rK  r]   r  r#   r   r  r4   r4   r5   r  g  s   
z_TestSQLAlchemy.test_datetimec                 C  s   t tdddtdd}tj|jd< |jd| jdd	dks!J t	d| j}t
|| td
| j}| jdkrVt|jd tsDJ t|d dd|d< t
|| d S t
|| d S )Nr  r  r  r  r  r   r   r  Fr  r  r   r  r   r  )r  )r   r   r   r  r  r  r   r7   r   r   r   r  r   rK  r]   r#   r   r  r4   r4   r5   test_datetime_NaT|  s   
z!_TestSQLAlchemy.test_datetime_NaTc                 C  sl   t tdddtdddgdgd}|jd| jdddksJ td| j}|d }t|d }t|| d S )	N  r   r   r  r   	test_dateFr  )r   r   r   r7   r   r   r   r  )r/  r   r  r   r'  r4   r4   r5   test_datetime_date  s   "z"_TestSQLAlchemy.test_datetime_datec                 C  s  t tdddtdddgdgd}|jd| jdd	d
ksJ td| j}t|| t }t	j|d|dd	d
ks;J t	
d|}|dd }t|| t	j|d| jdd	d
ks\J | jdkrut	
d| j}|dd }t|| t	d| j}t|| d S )N	   r   r      r  r   	test_timeFr  r   
test_time2zSELECT * FROM test_time2c                 S  
   |  dS Nz%H:%M:%S.%fstrftime_r4   r4   r5   <lambda>     
 z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>
test_time3r   zSELECT * FROM test_time3c                 S  r  r  r  r  r4   r4   r5   r    r  )r   r   r   r7   r   r   r  TestSQLiteFallbackr^   r   r   applymaprK  )r/  r   r  r   refr4   r4   r5   test_datetime_time  s    "
z"_TestSQLAlchemy.test_datetime_timec                 C  sh   t dtjd}t dtjd}t||d}|jd| jdddks"J td| j}t	j
||dd	d
 d S )Ni  r  g        )s1r  test_read_writeFr  r   T)check_dtypecheck_exact)r   r   rI  float32r   r   r7   r   r   r   r  )r/  r   r  r   r  r4   r4   r5   test_mixed_dtype_insert  s   z'_TestSQLAlchemy.test_mixed_dtype_insertc                 C  sl   t g ddtjdgd}|jd| jdddksJ td| j}t|| t	d	| j}t|| d S )
Nr  r   r  r  test_nanFr  r  SELECT * FROM test_nan)
r   r   r  r   r7   r   r   r   r  r   r  r4   r4   r5   test_nan_numeric  s   z _TestSQLAlchemy.test_nan_numericc                 C  s   t g dtjtjtjgd}|jd| jdddksJ td| j}t|| |d 	d|d< d |d< t
d	| j}t|| d S )
Nr  r  r  Fr  r  r   r  r  )r   r   r  r   r7   r   r   r   r  r   r   r  r4   r4   r5   test_nan_fullcolumn  s   z#_TestSQLAlchemy.test_nan_fullcolumnc                 C  sv   t g dddtjgd}|jd| jdddksJ d |jd	< td| j}t	|| t
d
| j}t	|| d S )Nr  r  r  r  r  Fr  r  r   r   r  )r   r   r  r   r7   r  r   r   r   r  r   r  r4   r4   r5   test_nan_string  s   
z_TestSQLAlchemy.test_nan_stringc                 C  s2   ddl m} || j}||}dd |D }|S )Nr   rF  c                 S  r;  r.  r4   r0  r4   r4   r5   rX     r<  z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)r1   r   r7   r3  )r/  r4  r   r   r5  r4   r4   r5   rt    s
   

z"_TestSQLAlchemy._get_index_columnsc                 C  r  r   rv  rA  r4   r4   r5   test_to_sql_save_index  r  z&_TestSQLAlchemy.test_to_sql_save_indexc                 C  r  r   r  rA  r4   r4   r5   test_transactions  r  z!_TestSQLAlchemy.test_transactionsc           	   	   C  s   ddl m} ddlm} d}tj||| jd}|jd d }| | ||}t	| j|r\| j
 !}|  || W d    n1 sGw   Y  W d    n1 sVw   Y  n| j| t|| j}tj||dd | | d S )Nr   rb   rI   test_get_schema_create_tabler  Fr  )r1   rc   rY   rJ   r   r  r7   r   r-  r]   r^   r_   r=   r   r   r  )	r/  r   rc   rJ   r0  r  blank_test_dfr7   returned_dfr4   r4   r5   r    s&   

z,_TestSQLAlchemy.test_get_schema_create_tablec                 C  s  ddl m}m} ddlm} ddg}ddg}t||d}|d	| jd
ks'J |jd| jd|idd
ks6J | }|j| jd |j	d j
d j}t||sPJ d}	tjt|	d |jd| jdtid W d    n1 sow   Y  |jd| jd|didd
ksJ |j| jd |j	d j
d j}t||sJ |jdksJ |jd| j|dd
ksJ |j| jd |j	d j
d j}
|j	d j
d j}t|
|sJ t||sJ d S )Nr   )rg   r(   r'   r   r   g?Tg?Nr   
dtype_testr   dtype_test2r  rM   z&The type of B is not a SQLAlchemy typer   r{  dtype_test3
   single_dtype_test)r1   rg   r(   sqlalchemy.schemar'   r   r   r7   reflecttablesr   r   r]   r   r   r   r#   rU  )r/  rg   r(   r'   r  r   r   metasqltyper   sqltypeasqltypebr4   r4   r5   
test_dtype  s4   "z_TestSQLAlchemy.test_dtypec                 C  s  ddl m}m}m}m} ddlm} tdd gttdddd gtdd gdd	td
d gd}t	|}d}|
|| jdks@J t|| j}	| }
|
j| jd | jdkrX|n|}|
j| j}t|d j|sjJ t|d j|stJ t|d j|s~J t|d j|sJ d S )Nr   )rh   ri   r&   rj   r  T  r  r   r  r  r  BoolDateIntr&   notna_dtype_testr   rM   r   r%  r&  r'  r&   )r1   rh   ri   r&   rj   r  r'   r   r   r   r   r7   r   r   r  rK  r  r   r]   r   )r/  rh   ri   r&   rj   r'   r  r   r0  r  r  my_typecol_dictr4   r4   r5   test_notna_dtype2  s&   

z _TestSQLAlchemy.test_notna_dtypec           
   	   C  sd  ddl m}m}m} ddlm} d}tt|gddt|gddt|gddtdgd	dtdgd
dd}|jd| j	ddd|ddiddksIJ t
d| j	}t|d jd dt|d jd dkshJ | }|j| j	d |jd j}	t|	d jt|	d jksJ t|	d j|sJ t|	d j|sJ t|	d j|sJ t|	d j|sJ d S )Nr   )
BigIntegerr&   rj   r  gYB?r  r  float64r  rI  r   )f32f64
f64_as_f32i32r  test_dtypesFr   r0     )	precision)r   r   r2   r   r/     rM   r.  r1  r  )r1   r,  r&   rj   r  r'   r   r   r   r7   r   r   r   roundr   r  r  r   r#   r   r]   )
r/  r,  r&   rj   r'   Vr   r  r  r*  r4   r4   r5   test_double_precisionO  s<   	0 z%_TestSQLAlchemy.test_double_precisionc                   sh   ddl m  dd dd fdd fd	d
}tdg did| jdks-J || j d S )Nr   rI   c                 S  s   d}t j|| dS )Nz'SELECT test_foo_data FROM test_foo_datar  )r   r   )r  rS  r4   r4   r5   r    s   z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.fooc                 S  s   |j d| dd d S )Ntest_foo_datar   )r  r  r   )r   )r  r   r4   r4   r5   bar  s   z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.barc                   s   | } | | d S r   r4   )r7   foo_data)r:  r  r4   r5   baz  s   z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.bazc              	     s   t |  r<|  )}|  | W d    n1 sw   Y  W d    d S W d    d S 1 s5w   Y  d S |  d S r   )r]   r^   r_   )connectabler7   )rJ   r<  r4   r5   main  s   



"z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr9  r  r  )rY   rJ   r   r   r7   )r/  r>  r4   )rJ   r:  r<  r  r5   test_connectable_issue_example|  s   	z._TestSQLAlchemy.test_connectable_issue_exampleinputr  r:  )r  infe0c                 C  s   t |}| jdkrMdd l}|jdd dkr(d|jv r(tjjdd}|j	| d}tj
t|d	 |jd
| jdd W d    d S 1 sFw   Y  d S |jd
| jdddksZJ td
| j}t|| d S )Nr   r   r  )r   r  r   rA  zGH 36465r  zinf cannot be used with MySQLr   foobarFr  r   )r   rK  r   VERSIONr   r   r  r  r  r  r   r   r   r7   r   r   r   assert_equal)r/  r@  r   r   r   r  r   r  r4   r4   r5   test_to_sql_with_negative_npinf  s   
"z/_TestSQLAlchemy.test_to_sql_with_negative_npinfc              	     s   ddl m mmm} ddlm}m} d}td|gi}| }G  fddd|}|| j	<}|
 ( | }	|j|	 |||d |  tj||j|	d	}
W d    n1 saw   Y  W d    n1 spw   Y  t|
| d S )
Nr   )r%   rj   Unicodera  )Sessiondeclarative_basezHello, World!spamc                      s6   e Zd ZdZddgiZ ddZ dddZd	S )
z7_TestSQLAlchemy.test_temporary_table.<locals>.Temporary	temp_testprefixes	TEMPORARYTprimary_keyr  F)nullableN)r2  r3  r4  __tablename____table_args__idrI  r4   r%   rj   rF  r4   r5   	Temporary  s
    
rT  )rI  )r   r  )r1   r%   rj   rF  ra  sqlalchemy.ormrG  rH  r   r7   r_   r  	__table__r[   addflushr   r   rI  r   r  )r/  ra  rG  rH  	test_datar'  BaserT  sessionr7   r   r4   rS  r5   test_temporary_table  s"   
z$_TestSQLAlchemy.test_temporary_tablec                 C  sD   d}t jt|d | |d W d    d S 1 sw   Y  d S )Nz*engine must be one of 'auto', 'sqlalchemy'r   
bad_engine)r   r   r   r_  r  r4   r4   r5   test_invalid_engine  s   "z#_TestSQLAlchemy.test_invalid_enginec                 C  <   t dd | | W d    d S 1 sw   Y  d S )Nio.sql.enginer1   r  option_contextr_  rX  r4   r4   r5   test_options_sqlalchemy     "z'_TestSQLAlchemy.test_options_sqlalchemyc                 C  r_  )Nr`  rZ  ra  rX  r4   r4   r5   test_options_auto  rd  z!_TestSQLAlchemy.test_options_autoc                 C  s   t tdts	J tdd t tdtsJ t tdts"J W d    n1 s,w   Y  tdd t tdtsAJ t tdtsJJ W d    d S 1 sUw   Y  d S )Nr1   r`  rZ  )r]   r   r   r  rb  rA  r4   r4   r5   test_options_get_engine  s   "z'_TestSQLAlchemy.test_options_get_enginec                 C  r  r   r4   rA  r4   r4   r5   "test_get_engine_auto_error_message  s   z2_TestSQLAlchemy.test_get_engine_auto_error_messageN)<r2  r3  r4  r  r  r   r  r  r  r  classmethodr  r  r^   rJ  r  r  r  r  r  r  r  r  r  r  r   r  r  r  r  r  r  r  r  r  r  r  r  r  r	  r  rt  r  r  r  r"  r+  r8  r?  r  r  r   infrE  r\  r^  rc  re  rf  rg  r4   r4   r4   r5   r  D  sx   
 





	P
 
	#- (
 r  c                      s*   e Zd Zejjdd fddZ  ZS )_TestSQLAlchemyConnz4Nested transactions rollbacks don't work with Pandasr  c                   s   t    d S r   )rl  r  rA  rp  r4   r5   r    rH  z%_TestSQLAlchemyConn.test_transactions)r2  r3  r4  r   r  r  r  rr  r4   r4   rp  r5   rj    s    rj  c                   @  sL   e Zd ZdZdZedd Zedd Zdd Zd	d
 Z	dd Z
dd ZdS )_TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r   c                 C  r  r  r  r  r4   r4   r5   r^   	     
z_TestSQLiteAlchemy.connectc                 C  s
   d | _ d S r   )driverr  r4   r4   r5   r  	  s   
z_TestSQLiteAlchemy.setup_driverc                 C  s   t d| j}t|jjjtjsJ t|j	jjtj
sJ t|jjjtj
s(J t|jjjtjs3J t|jjjtjs>J d S r   )r   r   r7   r   rp   r2   r   r   r   rq   r   rr   rs   rt   r  r4   r4   r5   r   	  s   z/_TestSQLiteAlchemy.test_default_type_conversionc                 C  s(   t d| j}t|jjjtjrJ d S r   r  r  r4   r4   r5   r  	  s   z)_TestSQLiteAlchemy.test_default_date_loadc                 C  sl   t dddgidd}|jd| jdddksJ td  td| j W d    d S 1 s/w   Y  d S )	Nr  r   r   r   r  test_bigintwarningFr  )r   r   r7   r   r  r   r   r  r4   r4   r5   test_bigint_warning%	  s
   "z&_TestSQLiteAlchemy.test_bigint_warningc           	        s   ddl m mm ddlm}m} | }G  fddd|}|j| j	 || j	d}| }t
ddgdd	gd
}|jd| j	ddddksJJ |  ||j|j}t
|}|  t|jddgksiJ d S )Nr   r%   rj   r(   )rH  sessionmakerc                      s(   e Zd ZdZ ddZ dZdS )z?_TestSQLiteAlchemy.test_row_object_is_named_tuple.<locals>.Testr   TrM  2   N)r2  r3  r4  rP  rR  r  r4   rp  r4   r5   Test>	  s    rs  rM   r   helloworld)rR  r  r   Fr   )r  r   r   r   rR  r  )r1   r%   rj   r(   rU  rH  rq  r3   
create_allr7   r   r   r9  rS  rR  r  r   r  r   )	r/  rH  rq  	BaseModelrs  rG  r[  r   r  r4   rp  r5   test_row_object_is_named_tuple-	  s    z1_TestSQLiteAlchemy.test_row_object_is_named_tupleN)r2  r3  r4  r  rK  rh  r^   r  r   r  ro  rx  r4   r4   r4   r5   rk    s    

rk  c                   @  8   e Zd ZdZdZdZedd Zedd Zdd	 Z	d
S )_TestMySQLAlchemyzA
    Test the sqlalchemy backend against an MySQL database.

    r   i  c                 C  s"   t jd| j d| j d| jdS )Nzmysql+z://root@localhost:/pandasr   )r1   r   rm  portr   r  r4   r4   r5   r^   ^	  s   z_TestMySQLAlchemy.connectc                 C  s$   t d}d| _d|jjji| _d S )Nr   r   )r   r   rm  r   r   r   r   )r  r   r4   r4   r5   r  e	  s   
z_TestMySQLAlchemy.setup_driverc                 C  r  r   r4   rA  r4   r4   r5   r   k	  r  z._TestMySQLAlchemy.test_default_type_conversionN)
r2  r3  r4  r  rK  r|  rh  r^   r  r   r4   r4   r4   r5   rz  U	  s    

rz  c                   @  ry  )_TestPostgreSQLAlchemyzF
    Test the sqlalchemy backend against an PostgreSQL database.

    r   i8  c                 C  s   t d| j d| j dS )Nzpostgresql+z://postgres:postgres@localhost:r{  )r1   r   rm  r|  r  r4   r4   r5   r^   x	  s   z_TestPostgreSQLAlchemy.connectc                 C  s   t d d| _d S )Nr   )r   r   rm  r  r4   r4   r5   r  ~	  s   

z#_TestPostgreSQLAlchemy.setup_driverc                 C  s~  ddl m} tddgddgddgd	}| jd
 | jd |jd| jdddks-J |jd| jddddks;J |jd| jddddksIJ td| j}t	|| td| j}t	|| tjd| jdd}t	|| tjd| jdd}t	|| d}t
jt|d tjd| jdd W d    n1 sw   Y  | jd
 | jd |jd| jddddksJ |jd| jdddd |jd| jdddddksJ tjd| jdd}t	t||gdd| t| j|r=|  }	tj|	dd}
|
j|ddddksJ |
j|dddddksJ |
j|dddddks'J tjd| jdd}|
d}t	|| d S d S )Nr   rI   r   r   r  r   r  r!  r"  z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;test_schema_publicFr  test_schema_public_explicitpublic)r   r  test_schema_otherother)r  z!Table test_schema_other not foundr   )r  r   r   )r  r   r   r   Tr  test_schema_other2)r   r   )rY   rJ   r   r7   r=   r   r   r   r   r  r   r   r   r   r]   r^   r   
read_table)r/  rJ   r   r  r  r  res4r   r  engine2pdsqlr4   r4   r5   test_schema_support	  sx   
	


z*_TestPostgreSQLAlchemy.test_schema_supportN)
r2  r3  r4  r  rK  r|  rh  r^   r  r  r4   r4   r4   r5   r}  o	  s    

r}  c                   @  rw  )TestMySQLAlchemyNrx  r4   r4   r4   r5   r  	      r  c                   @  rw  )TestMySQLAlchemyConnNrx  r4   r4   r4   r5   r  	  r  r  c                   @  rw  )TestPostgreSQLAlchemyNrx  r4   r4   r4   r5   r  	  r  r  c                   @  rw  )TestPostgreSQLAlchemyConnNrx  r4   r4   r4   r5   r  	  r  r  c                   @  rw  )TestSQLiteAlchemyNrx  r4   r4   r4   r5   r  	  rz  r  c                   @  rw  )TestSQLiteAlchemyConnNrx  r4   r4   r4   r5   r  	  rz  r  c                   @  s   e Zd ZdZdZedd Zdd Zej	ddd	d
 Z
dd Zdd Zdd Zdd Zdd Zdd Zdd Zejdddgdd Zdd Zdd  Zd!d" Zd#d$ Zd%d& Zd'd( Zd)d* Zd+S ),r  zG
    Test the fallback mode against an in-memory sqlite database.

    r   c                 C  r  r   ru  r  r4   r4   r5   r^   
  rl  zTestSQLiteFallback.connectc                 C  r  r   r  rA  r4   r4   r5   rJ  
  rB  z TestSQLiteFallback.setup_connectTr  c                 C  s   t | j| _d S r   )r   r   r7   r   r  r4   r4   r5   r  
  s   zTestSQLiteFallback.setup_methodc                 C  r  r   r  rA  r4   r4   r5   r  
  r  z*TestSQLiteFallback.test_read_sql_parameterc                 C  r  r   r  rA  r4   r4   r5   r  
  r  z0TestSQLiteFallback.test_read_sql_named_parameterc                 C  r  r   r  rX  r4   r4   r5   r  
  rB  z$TestSQLiteFallback.test_to_sql_emptyc                 C  s\   t g dg dd}| j|ddksJ | jdsJ | jd | jdr,J d S )Nr  r  r  drop_test_framer[  )r   r   r   r   r-  )r/  r  r4   r4   r5   test_create_and_drop_table
  s   z-TestSQLiteFallback.test_create_and_drop_tablec                 C  r  r   r  rX  r4   r4   r5   r  %
  rB  z!TestSQLiteFallback.test_roundtripc                 C  r  r   r  rA  r4   r4   r5   r  (
  r  z#TestSQLiteFallback.test_execute_sqlc                 C  s   t tdddtdddgdgd}|jd| jdddksJ td	| j}| jd
kr4t||t	 d S | jdkrAt|| d S d S )Nr  r   r   r  r   r  Fr  zSELECT * FROM test_dater   r   )
r   r   r   r7   r   rK  r   r  r   r#   r  r4   r4   r5   r  +
  s   "

z%TestSQLiteFallback.test_datetime_datetz_awareFc                 C  s   |st dddt dddg}ntdddd}t| d	d
 }t|dgd}|jd| jdddks5J td| j}| j	dkrO|
dd
 }t|| d S d S )Nr  r   r   r  r  r   r  r  c                 S  s   |   S r   )timetz)r  r4   r4   r5   r  =
  s    z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>r  r   r  Fr  zSELECT * FROM test_timer   c                 S  r  r  r  r  r4   r4   r5   r  E
  r  )r   r   r   to_pydatetimemapr   r   r7   r   rK  r  r   r  )r/  r  tz_timestz_dtr   r  r'  r4   r4   r5   r  6
  s   
z%TestSQLiteFallback.test_datetime_timec                 C  sT   t dd| d | j}g }|jD ]}t d| d| j}||j  q|S )Nz1SELECT * FROM sqlite_master WHERE type = 'index' zAND tbl_name = ''zPRAGMA index_info())r   r   r7   r  r   r  )r/  r4  r5  ru  ix_nameix_infor4   r4   r5   rt  H
  s   

z%TestSQLiteFallback._get_index_columnsc                 C  r  r   r  rA  r4   r4   r5   r  T
  r  z)TestSQLiteFallback.test_to_sql_save_indexc                 C  r  r   r  rA  r4   r4   r5   r  W
  r  z$TestSQLiteFallback.test_transactionsc           
      C  sP   | j d| d}|D ]\}}}}}}	||kr|  S qtd| d| d)NzPRAGMA table_info(r  zTable z	, column r  )r7   r=   r   )
r/  r   r  recscidr  ctypenot_nulldefaultpkr4   r4   r5   r  Z
  s   z*TestSQLiteFallback._get_sqlite_column_typec                 C  s  | j dkr
td ddg}ddg}t||d}|d| jd	ks#J |jd
| jddidd	ks2J | dddks<J | d
ddksFJ d}tjt|d |jd| jdt	id W d    n1 sew   Y  |jd| jddd	kswJ | dddksJ | dddksJ d S )Nr   Not applicable to MySQL legacyr   r   r  r  r   r  r   r  STRINGr  r  z!B \(<class 'bool'>\) not a stringr   r{  r  )
rK  r   r  r   r   r7   r  r   r   bool)r/  r  r   r   r   r4   r4   r5   r"  a
  s    

zTestSQLiteFallback.test_dtypec                 C  s   | j dkr
td tdd gttdddd gtdd gddtd	d gd
}t|}d}||| jdks8J | |ddksBJ | |ddksLJ | |ddksVJ | |ddks`J d S )Nr   r  Tr#  r  r   r  r  r  r$  r(  r   r%  r  r&  r6  r'  r&   r$   )	rK  r   r  r   r   r   r   r7   r  )r/  r  r   r0  r4   r4   r5   r+  w
  s   



z#TestSQLiteFallback.test_notna_dtypec                 C  s   t ddgddggddgd}d}tjt|d	 |d
| j W d    n1 s)w   Y  tg dD ]<\}}||| jdksCJ t|| j t ddgddggd|gd}d|d}||| jdksiJ t|| j q4d S )Nr   r   r  r[  r  r  r   z$Empty table or column name specifiedr   r(  )
ztest_weird_name]ztest_weird_name[ztest_weird_name`ztest_weird_name"ztest_weird_name'z_b.test_weird_name_01-30z"_b.test_weird_name_01-30"99beginswithnumber12345r  test_weird_col_named)	r   r   r   r   r   r7   	enumerater   table_exists)r/  r   r   ndx
weird_namer  c_tblr4   r4   r5   test_illegal_names
  s   z%TestSQLiteFallback.test_illegal_namesN)r2  r3  r4  r  rK  rh  r^   rJ  r   r  r  r  r  r  r  r  r  r  r  r  r  rt  r  r  r  r"  r+  r  r4   r4   r4   r5   r  	  s0    



r  z'{}'z{:.8f}z{:d}c                 C  s   dS )NNULLr4   )xr4   r4   r5   r  
  s    r  z{:.10f}z'{!s}'c                 G  sJ   g }|D ]}t |trt|rd }tt| }||| q| t| S r   )r]   r   r   _formattersr   r   rM  )r   r|  processed_argsarg	formatterr4   r4   r5   format_query
  s   r  c                 C  s&   t j| |d }|du rdS t|S )z#Replace removed sql.tquery functionr  N)r   r=   r=  r  )rS  r  r  r4   r4   r5   tquery
  s   r  c                   @  sl   e Zd Zdd Zdd Zdd Zdd Zd	d
 Zdd Zdd Z	dd Z
dd Zdd Zdd Zdd ZdS )TestXSQLitec                 C  s   t d| _d S r   )rd   r^   r7   rA  r4   r4   r5   r  
  s   zTestXSQLite.setup_methodc                 C  r?  r   r@  rA  r4   r4   r5   r1  
  rB  zTestXSQLite.teardown_methodc                 C  s.   | j  }|dt|  | j   d S r7  )r7   r<   r=   r   r8  r9  )r/  r   rC   r4   r4   r5   r-  
  s   
zTestXSQLite.drop_tablec                 C  s   t  }tj|d| jdddksJ td| j}|j|_|}t || dgt| |d< |	 }t
tt|d }|	 |d	< tj|d
| jdddksQJ tjd| jd	d}|	 }||_d	|j_t || d S )N
test_tableF)r  r  r   r  zselect * from test_tabler  txtr  Idxtest_table2zselect * from test_table2r  )r   makeTimeDataFramer   r   r7   r  r   r  r   r
  r   r   r  r  )r/  r   r   r'  frame2new_idxr4   r4   r5   
test_basic
  s    zTestXSQLite.test_basicc           	      C  s   t  }tj|jd< t|d}| j }|	| d}|
 D ]\}}t|g|R  }t|| jd q | j  tjd| jd}|j|_t j||dd d S )N)r   r   r  z(INSERT INTO test VALUES (%s, %s, %s, %s)r  select * from testgMbP?)rtol)r   r  r   r  r   r   r  r7   r<   r=   iterrowsr  r  r9  r  r   r  )	r/  r   r  rC   insr  rU   fmt_sqlr   r4   r4   r5   test_write_row_by_row
  s   


z!TestXSQLite.test_write_row_by_rowc                 C  s   t  }t|d}| j }|| d}|jd }tj|| jt|d | j	  t
d| j}|jd d |_t ||d d  d S )Nr  z$INSERT INTO test VALUES (?, ?, ?, ?)r   rO  r  r   )r   r  r   r  r7   r<   r=   r   rM  r9  r  r   r  )r/  r   r  rC   r  rU   r   r4   r4   r5   test_execute  s   



zTestXSQLite.test_executec                 C  s   t  }t|d}| }|D ]}|d}t|dkr+|d dkr+|d dks+J qtj|dddgd	}| }d
|v s@J | j }|	| d S )Nr   r   r   r   r   DATETIMEr   r   zPRIMARY KEY ("A", "B"))
r   r  r   r  
splitlinesr  r   r7   r<   r=   )r/  r   r  lineslinetokensrC   r4   r4   r5   test_schema  s   

zTestXSQLite.test_schemac                 C  sx   d}| j  }|| td| j  td| j  tjtjdd td| j  W d    d S 1 s5w   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))r7   r<   r=   r   r   r   r  )r/  r  rC   r4   r4   r5   test_execute_fail  s   
	
"zTestXSQLite.test_execute_failc                 C  sx   d}| j  }|| td| j  | j   d}tjtj|d t	d| j d W d    d S 1 s5w   Y  d S )Nr  r  z$Cannot operate on a closed database.r   r  r  )
r7   r<   r=   r   r   r   r   rd   ProgrammingErrorr  )r/  r  rC   r   r4   r4   r5   test_execute_closed_connection2  s   
	

"z*TestXSQLite.test_execute_closed_connectionc                 C  s2   t dtdi}tj|| jddddksJ d S )NFromr  testkeywordsFr  r  r   )r   r   onesr   r   r7   r  r4   r4   r5   test_keyword_as_column_namesF  s    z(TestXSQLite.test_keyword_as_column_namesc                 C  st   t ddgdgd}tj|| jddddksJ | j}tdd	 |d
D }|dks,J td
|}t|| d S )Nr   r   c0r   mono_dfFr  c                 s  s    | ]}|d  V  qdS )r   Nr4   )rO   my_c0r4   r4   r5   	<genexpr>R  s    z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr  )	r   r   r   r7   sumr=   r  r   r  )r/  r  con_xthe_sumr   r4   r4   r5   test_onecolumn_of_integerJ  s   z%TestXSQLite.test_onecolumn_of_integerc                 C  s  t ddgddgd}t g dg dd}d}d	| }d
}tjt|d tj|| j|dd W d    n1 s;w   Y  | | tj|| j|dd d}tjt|d tj|| j|dd W d    n1 smw   Y  tj|| j|ddd t|| jdddgksJ tj|| j|ddddksJ t|| jdg dksJ | | tj|| j|ddddksJ t|| jdddgksJ tj|| j|ddddksJ t|| jdg dksJ | | d S )Nr   r   r   r   )r#  r$  re  )r   r   Etable_if_existszSELECT * FROM z*'notvalidvalue' is not valid for if_existsr   notvalidvalue)r   r  r  r   r   z&Table 'table_if_exists' already existsr   F)r   r  r  r   r   r  r  r
  r  )r  r   r[  r   r  r  r   )r  r
  r  r  r  )	r   r   r   r   r   r   r7   r-  r  )r/  df_if_exists_1df_if_exists_2r   
sql_selectr   r4   r4   r5   test_if_existsY  s~   



	
		zTestXSQLite.test_if_existsN)r2  r3  r4  r  r1  r-  r  r  r  r  r  r  r  r  r  r4   r4   r4   r5   r  
  s    r  )r"   r#   )r7   r8   r9   r   )r9   r   r"   r#   )r7   r8   r{   r|   )r{   r|   r"   r#   )r   r   )r   r#   r   )r  
__future__r   r?   r   r   r   r   r   pathlibr   rd   numpyr   r   pandas.util._test_decoratorsutil_test_decoratorsri  pandas.core.dtypes.commonr	   r
   pandasr  r   r   r   r   r   r   r   r   r   r   pandas._testing_testingr   pandas.io.sqlr   r   r   r   r   r   r   r   r1   r  rY  rQ  r6   rF   ra   rf   rz   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  r>  r  r   r   r   r   r   r   r  r)  r*  r6  rD  rI  r  r  r  rk  rs  rt  r  rj  rk  rz  r}  r  r  r  r  r  r  r  r  r#   str_bytesr   r&  r   r-  r  r  r  r  r  r4   r4   r4   r5   <module>   sP   0$





!






#













	#%	 
   G JW     :Vm 4
