U
    sVc4                    @  s  d Z ddlmZ ddlZddlmZmZmZ ddlmZ ddl	m
Z
 ddlZddlZddlZddlm  mZ ddlmZmZ ddlZddlmZmZmZmZmZmZmZmZm Z m!Z! ddl"m#Z$ ddl%m  m&Z& dd	l%m'Z'm(Z(m)Z)m*Z*m+Z+m,Z,m-Z- zddl.Z.d
Z/W n e0k
r"   dZ/Y nX dddddddddddddZ1ddddZ2dddddZ3ddd d!d"Z4d#d$ Z5ddd%d&Z6dd'd(d)d*Z7d'dd+d,d-Z8d.d/d0d1Z9dd2d3d4Z:ej;d5d6 Z<ej;d7d8 Z=ej;d9d: Z>ej;d;d< Z?ej;d=d> Z@ej;d?d@ ZAej;dAdB ZBej;dCdD ZCej;dEdF ZDej;dGdH ZEej;dIdJ ZFej;dKdL ZGej;dMdN ZHej;dOdP ZIej;dQdR ZJd@dBgZKdDdFgZLdHdJgZMdLdNgZNeKeL eM ZOeKeL eN ZPeOdPg ZQePdRg ZRejSjTejSUdSeQejSUdTddUgdVdW ZVejSjTejSUdSeQejSUdXdYdZgd[d\ ZWejSjTejSUdSeQd]d^ ZXejSjTejSUdSeRd_d` ZYejSjTejSUdSeOdadb ZZejSjTejSUdSeKdcdd Z[ejSjTejSUdSeKdedf Z\ejSjTejSUdSeLejSUdgdhdigdjdk Z]G dldm dmZ^G dndo doe^Z_G dpdq dqe^Z`G drds dsZaG dtdu dueaZbejSjce/ dvdwG dxdy dye`ebZdG dzd{ d{ZeG d|d} d}eeedZfG d~d de_ebZgG dd de`eaZhG dd 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str)dialectc           
      C  sp   ddl m}m}m}m}m}m} | dkr,|n|}| }|d||d||d||d||d||d	|d
}	|	S )Nr   )REALColumnFloatMetaDataStringTabler   irisSepalLengthZ
SepalWidthZPetalLengthZ
PetalWidthName   )
sqlalchemyr$   r%   r&   r'   r(   r)   )
r#   r$   r%   r&   r'   r(   r)   dtypemetadatar*    r1   </tmp/pip-unpacked-wheel-xj8nt62q/pandas/tests/io/test_sql.pyiris_table_metadatae   s     		r3   zsqlite3.Connectionr   )conn	iris_filec              	   C  sT   |   }d}|| |jd d(}t|}t| d}||| W 5 Q R X d S )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)r4   r5   curstmtcsvfiler<   r1   r1   r2   create_and_load_iris_sqlite3}   s    

rB   )r5   r#   c           
        s   ddl m} ddlm} t|}|j| dd |j| d |jd d}t	|}t
|  fdd	|D }|||}	t| |r|  $} |   | |	 W 5 Q R X W 5 Q R X n
| |	 W 5 Q R X d S )
Nr   insertEngineTZ
checkfirstbindr6   c                   s    g | ]}d d t  |D qS )c                 S  s   i | ]\}}||qS r1   r1   ).0keyvaluer1   r1   r2   
<dictcomp>   s      z3create_and_load_iris.<locals>.<listcomp>.<dictcomp>)ziprJ   rowheaderr1   r2   
<listcomp>   s     z(create_and_load_iris.<locals>.<listcomp>)r.   rD   sqlalchemy.enginerF   r3   dropcreater:   r;   r<   r=   values
isinstanceconnectbeginr9   )
r4   r5   r#   rD   rF   r*   rA   r<   paramsr@   r1   rQ   r2   create_and_load_iris   s    



 r\   c              
   C  s   d}t | tjr$|  }|| nhddlm} ddlm} ||}t | |r| 	 $} | 
  | | W 5 Q R X W 5 Q R X n
| | d S )Nz+CREATE VIEW iris_view AS SELECT * FROM irisr   textrE   )rX   sqlite3
Connectionr8   r9   r.   r^   rT   rF   rY   rZ   )r4   r@   r?   r^   rF   r1   r1   r2   create_and_load_iris_view   s    


 ra   c                 C  s   ddl m}m}m}m}m}m}m}m} | dkr4|n|}	| dkrD|n|}
| }|d||d||d|	|d||d||d	||d
||d|
|d||d|
}| dkr|	|d|dd |S )Nr   )TEXTBooleanr%   DateTimer&   Integerr'   r)   r   typesTextColDateCol
IntDateColIntDateOnlyColFloatColIntColBoolColIntColWithNullBoolColWithNullr   DateColWithTzT)timezone)
r.   rb   rc   r%   rd   r&   re   r'   r)   Zappend_column)r#   rb   rc   r%   rd   r&   re   r'   r)   Z	date_typeZ	bool_typer0   rf   r1   r1   r2   types_table_metadata   s(    (rr   z
list[dict])r4   
types_datac                 C  s*   |   }d}|| d}||| d S )Na  CREATE TABLE types (
                    "TextCol" TEXT,
                    "DateCol" TEXT,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" REAL,
                    "IntCol" INTEGER,
                    "BoolCol" INTEGER,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" INTEGER
                )zY
            INSERT INTO types
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
            )r8   r9   r>   )r4   rs   r?   r@   r1   r1   r2   create_and_load_types_sqlite3   s
    
rt   )rs   r#   c              
   C  s   ddl m} ddlm} t|}|j| dd |j| d |||}t| |r| 	 $} | 
  | | W 5 Q R X W 5 Q R X n
| | d S )Nr   rC   rE   TrG   rH   )r.   rD   rT   rF   rr   rU   rV   rW   rX   rY   rZ   r9   )r4   rs   r#   rD   rF   rf   r@   r1   r1   r2   create_and_load_types   s    


 ru   r   framec                 C  sB   | j d j}| jd }t|tjs&tt|j	dddddg d S )Nr   ffffff@      @ffffff?皙?Iris-setosa)
dtypestypeiloc
issubclassnpfloatingAssertionErrortmequalContentsrW   )rw   ZpytyperP   r1   r1   r2   check_iris_frame   s    
r   )
table_namec              	   C  s   d| }t | tjr*|  }||}nTddlm} ddlm} ||}t | |rt| 	 } | |}W 5 Q R X n
| |}|
 d S )Nz SELECT count(*) AS count_1 FROM r   r]   rE   )rX   r_   r`   r8   r9   r.   r^   rT   rF   rY   fetchone)r4   r   r@   r?   resultr^   rF   r1   r1   r2   
count_rows  s    



r   c                 C  s   | dddd}t |S )Niodatar;   ziris.csvr   )datapath	iris_pathr1   r1   r2   r     s    r   c                   C  s4   ddddddddddd	
dd
dddddd d dd	
gS )Nfirst2000-01-03 00:00:00i wi2g333333$@   Fz2000-01-01 00:00:00-08:00)
rg   rh   ri   rj   rk   rl   rm   rn   ro   rp   2000-01-04 00:00:00i 'Pi\2z2000-06-01 00:00:00-07:00r1   r1   r1   r1   r2   rs     s.    rs   c              
   C  s2   dddddddddd	}t | }||  |S )Nr"   int64float)	rg   rh   ri   rj   rk   rl   rm   rn   ro   )r   keysastype)rs   r}   dfr1   r1   r2   types_data_frame:  s    r   c                  C  s&   dddddg} dddd	g}t || d
S )NindexABCD)r   gN\^?g9\x`|@g% TO׿g(nI)r   g}C?c^Vg~q"BĿg4?)2000-01-05 00:00:00gGͿ?2r¹e?g>ݦ4gёR?)2000-01-06 00:00:00g?{mX?$?gnk3Am?g
S?columnsr   r   r   r1   r1   r2   test_frame1K  s    r   c                  C  s"   dddg} ddddg}t || dS )	Nr   r   r   )r   igZ)r   ir   )r   i N  r   )r   i͏r   r   r   r   r1   r1   r2   test_frame3o  s    
r   c           	   
   c  s   t d}t d}|jdd|jjjid}||}|dsLt|| d |dsv|D ]}|	d	 qZt
||d |V  | .}|  |d
}|| W 5 Q R X W 5 Q R X |  d S )Nr.   pymysqlz*mysql+pymysql://root@localhost:3306/pandasclient_flagconnect_argsr*   r   rf   rp    DROP TABLE IF EXISTS test_frame;)pytestimportorskipcreate_engine	constantsCLIENTMULTI_STATEMENTSinspect	has_tabler\   popru   rY   rZ   r^   r9   dispose)	r   rs   r.   r   engineinspentryr4   r@   r1   r1   r2   mysql_pymysql_engine{  s&    







r   c                 c  s   |   V  d S NrY   )r   r1   r1   r2   mysql_pymysql_conn  s    r   c              
   c  s   t d}t d |d}||}|ds>t|| d |dsTt||d |V  | .}|  |	d}|
| W 5 Q R X W 5 Q R X |  d S )Nr.   psycopg2z=postgresql+psycopg2://postgres:postgres@localhost:5432/pandasr*   r   rf   r   )r   r   r   r   r   r\   ru   rY   rZ   r^   r9   r   )r   rs   r.   r   r   r4   r@   r1   r1   r2   postgresql_psycopg2_engine  s     







r   c                 c  s   |   V  d S r   r   )r   r1   r1   r2   postgresql_psycopg2_conn  s    r   c                  c  s&   t d} | d}|V  |  d S )Nr.   z	sqlite://)r   r   r   r   )r.   r   r1   r1   r2   sqlite_engine  s    

r   c                 c  s   |   V  d S r   r   )r   r1   r1   r2   sqlite_conn  s    r   c                 C  s   t | |d | S )Nr   )r\   )r   r   r1   r1   r2   sqlite_iris_engine  s    r   c                 c  s   |   V  d S r   r   )r   r1   r1   r2   sqlite_iris_conn  s    r   c                  c  s   t d} | V  |   d S N:memory:)r_   rY   close)r4   r1   r1   r2   sqlite_buildin  s    
r   c                 C  s   t | | | S r   )rB   )r   r   r1   r1   r2   sqlite_buildin_iris  s    
r   r4   methodZmultic                 C  sJ   | | } t| }|j|d|d |ds0tt| dt|ksFtd S )N
test_framer   getfixturevaluer   to_sqlr   r   r   len)r4   r   r   request	pandasSQLr1   r1   r2   test_to_sql  s
    
r   zmode, num_row_coef)replacer   )append   c                 C  s^   | | } t| }|j|ddd |j|d|d |ds@tt| d|t| ksZtd S )Nr   fail	if_existsr   )r4   modeZnum_row_coefr   r   r   r1   r1   r2   test_to_sql_exist   s    
r   c              	   C  sb   | | } t| }|j|ddd |ds0td}tjt|d |j|ddd W 5 Q R X d S )Nr   r   r   z!Table 'test_frame' already existsmatch)r   r   r   r   r   r   raises
ValueError)r4   r   r   r   msgr1   r1   r2   test_to_sql_exist_fail  s    
r   c                 C  s(   | | } t| }|d}t| d S )NSELECT * FROM iris)r   r   
read_queryr   )r4   r   r   
iris_framer1   r1   r2   test_read_iris  s    

r   c                   sh   | | } t| }g   fdd}|j|d|d |ds@t dgksNtt| dt|ksdtd S )Nc                   s2    d  fdd|D }|| j | d S )Nr   c                   s   g | ]}t t |qS r1   )dictrN   rO   r   r1   r2   rS   ,  s     z8test_to_sql_callable.<locals>.sample.<locals>.<listcomp>)r   r9   tablerD   )Zpd_tabler4   r   	data_iterr   checkr   r2   sample*  s    
z$test_to_sql_callable.<locals>.sampler   r   r   r   )r4   r   r   r   r   r1   r   r2   test_to_sql_callable"  s    
r   c                 C  s   | | } td| }t|jjjtjs,t	t|j
jjtjsBt	t|jjjtjsXt	t|jjjtjsnt	t|jjjtjst	d S Nrf   )r   sqlr   r   rk   r/   r~   r   r   r   rl   integerrm   rn   ro   )r4   r   r   r1   r1   r2   test_default_type_conversion5  s    
r   c           	   
   C  s   | | } ddlm} ddlm} tdddgddd	gd
}|jd| dd d}||}t| |r|  $}|	  |
| W 5 Q R X W 5 Q R X n
| 
| td| }t|| td| }t|| d S )Nr   r]   rE   r   r      皙?r{   g333333?abr   Fr   z}DROP PROCEDURE IF EXISTS get_testdb;

    CREATE PROCEDURE get_testdb ()

    BEGIN
        SELECT * FROM test_frame;
    ENDzCALL get_testdb();)r   r.   r^   rT   rF   r   r   rX   rY   rZ   r9   r   r   r   assert_frame_equalread_sql)	r4   r   r^   rF   r   procZengine_connres1res2r1   r1   r2   test_read_procedureH  s     



 
r   expected_countr   zSuccess!c                   s    fdd}| | } tddgddgddgd	}|jd
| d|d}t ts\|d kshtn| kshttd
| }t	|| d S )Nc              	     s   |j }| ~}t }t|}|| |d ddd |D }| jrb| j d| j	 }	n| j	}	d|	 d| d}
|j
|
|d	 W 5 Q R X  S )
Nr   z, c                 S  s   g | ]}d | d qS )"r1   )rJ   kr1   r1   r2   rS   }  s     zVtest_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<listcomp>.zCOPY z (z) FROM STDIN WITH CSV)r   file)
connectionr8   r   r;   writer	writerowsseekjoinschemanameZcopy_expert)r   r4   r   r   Z
dbapi_connr?   Zs_bufr  r   r   Z	sql_queryr   r1   r2   psql_insert_copyt  s    



zBtest_copy_from_callable_insertion_method.<locals>.psql_insert_copyr   r   r   r{   r   ncol1col2Zcol3r   F)r   r   )
r   r   r   rX   intr   r   r   r   r   )r4   r   r   r  expectedresult_countr   r1   r  r2   (test_copy_from_callable_insertion_methodm  s    
   
r  c                   @  s   e Zd Zdd ZdS )	MixInBasec                 C  s.   t | dr*|  D ]}| | q|   d S )Nr4   )hasattr_get_all_tables
drop_table_close_conn)selftblr1   r1   r2   teardown_method  s    
zMixInBase.teardown_methodN)__name__
__module____qualname__r  r1   r1   r1   r2   r    s   r  c                   @  s$   e Zd Zdd Zdd Zdd ZdS )SQLiteMixInc                 C  s&   | j dt|  | j   d S NzDROP TABLE IF EXISTS )r4   r9   r   _get_valid_sqlite_namecommitr  r   r1   r1   r2   r    s    zSQLiteMixIn.drop_tablec                 C  s   | j d}dd | D S )Nz1SELECT name FROM sqlite_master WHERE type='table'c                 S  s   g | ]}|d  qS )r   r1   )rJ   r   r1   r1   r2   rS     s     z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r4   r9   fetchall)r  cr1   r1   r2   r    s    zSQLiteMixIn._get_all_tablesc                 C  s   | j   d S r   r4   r   r  r1   r1   r2   r    s    zSQLiteMixIn._close_connNr  r  r  r  r  r  r1   r1   r1   r2   r     s   r   c                   @  s$   e Zd Zdd Zdd Zdd ZdS )SQLAlchemyMixInc                 C  s   t | j| d S r   )r   r   r4   r  r$  r1   r1   r2   r    s    zSQLAlchemyMixIn.drop_tablec                 C  s   ddl m} || j S )Nr   r   )r.   r   r4   Zget_table_names)r  r   r1   r1   r2   r    s    zSQLAlchemyMixIn._get_all_tablesc                 C  s   | j   d S r   )r4   r   r(  r1   r1   r2   r    s    zSQLAlchemyMixIn._close_connNr)  r1   r1   r1   r2   r*    s   r*  c                   @  sv   e Zd ZdZejdd Zejdd Zdd Zdd	 Z	d
d Z
dd ZdddZdd Zdd Zdd Zdd ZdS )PandasSQLTestzT
    Base class with common private methods for SQLAlchemy and fallback cases.

    c                 C  sL   t | ds|   | d t| jtjr8t| j| nt| j|| j	 d S )Nr4   r*   )
r  setup_connectr  rX   r4   r_   r`   rB   r\   flavor)r  r   r1   r1   r2   load_iris_data  s    

zPandasSQLTest.load_iris_datac                 C  sn   t | ds|   | jdkr0|D ]}|d q t| jtjrZdd |D }t| j| nt	| j|| j d S )Nr4   r   rp   c                 S  s   g | ]}t | qS r1   )tuplerW   )rJ   r   r1   r1   r2   rS     s     z1PandasSQLTest.load_types_data.<locals>.<listcomp>)
r  r-  r.  r   rX   r4   r_   r`   rt   ru   )r  rs   r   r1   r1   r2   load_types_data  s    

zPandasSQLTest.load_types_datac                 C  s2   t d | j }ddg}| jj||d}t| d S )Nr   r|   rx   r[   SQL_STRINGSr.  r   r   r   r  queryr[   r   r1   r1   r2   _read_sql_iris_parameter  s    z&PandasSQLTest._read_sql_iris_parameterc                 C  s4   t d | j }ddd}| jj||d}t| d S )Nr    r|   rx   )r
  lengthr2  r3  r5  r1   r1   r2   _read_sql_iris_named_parameter  s    
z,PandasSQLTest._read_sql_iris_named_parameterc                 C  s*   t d | j }| jj|d d}t| d S )Nr!   r2  r3  )r  r6  r   r1   r1   r2   (_read_sql_iris_no_parameter_with_percent  s    z6PandasSQLTest._read_sql_iris_no_parameter_with_percentc                 C  s.   |  d | j|jd d ddks*td S )Nr   r   )r  r   r   r   r   r  r   r1   r1   r2   _to_sql_empty  s    
zPandasSQLTest._to_sql_emptyautoc                 K  sj   |  d | jj|dfd|i|dks,t| jds<tt|}t| jd}||ks\t|  d dS )z `to_sql` with the `engine` paramr   r      N)r  r   r   r   r   r   r   r4   )r  r   r   Zengine_kwargsnum_entriesnum_rowsr1   r1   r2   _to_sql_with_sql_engine  s"    
 z%PandasSQLTest._to_sql_with_sql_enginec                 C  sR   |  d | j|ddks t| jd}|jddd d |j_t	|| d S )Ntest_frame_roundtripr>  "SELECT * FROM test_frame_roundtriplevel_0TZinplace)
r  r   r   r   r   	set_indexr   r
  r   r   r  r   r   r1   r1   r2   
_roundtrip  s    
zPandasSQLTest._roundtripc                 C  s.   | j d}| }t|dddddg d S )Nr   rx   ry   rz   r{   r|   )r   r9   r   r   r   r  Ziris_resultsrP   r1   r1   r2   _execute_sql  s    zPandasSQLTest._execute_sqlc                 C  sP   t jddgdddgdgd}| j|ddks2t| d}|dggksLtd S )	Nr    @Zline1r   g      ?Zline2r   r   r   r   r   Ztest_to_sql_saves_indexr   )r   from_recordsr   r   r   _get_index_columns)r  r   ix_colsr1   r1   r2   _to_sql_save_index  s      
z PandasSQLTest._to_sql_save_indexc              	   C  s$  | j  @}d}t| j tr(|| nddlm} ||}|| W 5 Q R X G dd dt}d}t| j trddlm} ||}z,| j  }|| |dW 5 Q R X W n |k
r   Y nX | j 	d}t
|dkst| j  }|| W 5 Q R X | j 	d}t
|d	ks td S )
Nz'CREATE TABLE test_trans (A INT, B TEXT)r   r]   c                   @  s   e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionNr  r  r  r1   r1   r1   r2   DummyException&  s   rT  z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')errorzSELECT * FROM test_transr   )r   Zrun_transactionrX   r   r9   r.   r^   	Exceptionr   r   r   r   )r  Ztransr@   r^   rT  Zins_sqlresr   r1   r1   r2   _transaction_test  s0    
zPandasSQLTest._transaction_testN)r=  )r  r  r  __doc__r   fixturer/  r1  r7  r9  r:  r<  rA  rH  rJ  rR  rX  r1   r1   r1   r2   r,    s   
	

r,  c                
   @  s  e Zd ZU dZdZded< dd Zejddd	d
 Z	dd Z
dd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd  Zd!d" Zd#d$ Zejd%d&d'd(gejd)ejd*d+fejd,d-fejd*d+fejd,d-fgd.d/ Zd0d1 Zd2d3 Zd4d5 Z ejd6d7d8d9d:d;d<gd=d> Z!d?d@ Z"dAdB Z#ejdCdDe$e%e$e%dEgdFdG Z&dHdI Z'dJdK Z(dLdM Z)dNdO Z*dPdQ Z+dRdS Z,dTdU Z-dVdW Z.dXdY Z/dDS )Z_TestSQLApia  
    Base class to test the public API.

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

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

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

    r   r"   r   c                 C  s   |   | _d S r   rY   r4   r(  r1   r1   r2   r-  X  s    z_TestSQLApi.setup_connectTautousec                 C  s   |    d S r   )load_test_data_and_sqlr  r/  r1  r1   r1   r2   setup_method[  s    z_TestSQLApi.setup_methodc                 C  s   t | j d S r   )ra   r4   r(  r1   r1   r2   r_  _  s    z"_TestSQLApi.load_test_data_and_sqlc                 C  s   t d| j}t| d S )NzSELECT * FROM iris_view)r   r   r4   r   r  r   r1   r1   r2   test_read_sql_viewb  s    z_TestSQLApi.test_read_sql_viewc                 C  s8   d}t j|| jdd}t || j}tt|| d S )Nz/SELECT * FROM iris_view WHERE SepalLength < 0.0   	chunksize)r   r   r4   r   r   r   )r  r6  Z
with_batchZwithout_batchr1   r1   r2   &test_read_sql_with_chunksize_no_resultf  s    z2_TestSQLApi.test_read_sql_with_chunksize_no_resultc                 C  s&   t |d| j t d| js"td S )Nr   )r   r   r4   r   r   r;  r1   r1   r2   r   l  s    z_TestSQLApi.test_to_sqlc              	   C  s\   t j|d| jdd t d| js&td}tjt|d t j|d| jdd W 5 Q R X d S )Ntest_frame2r   r   z"Table 'test_frame2' already existsr   )r   r   r4   r   r   r   r   r   r  r   r   r1   r1   r2   test_to_sql_failp  s
    z_TestSQLApi.test_to_sql_failc                 C  s^   t j|d| jdd t j|d| jdd t d| js:tt|}t| jd}||ksZtd S )Nr   r   r   r   )r   r   r4   r   r   r   r   r  r   r?  r@  r1   r1   r2   test_to_sql_replacex  s    z_TestSQLApi.test_to_sql_replacec                 C  sr   t j|d| jdddkstt j|d| jdddks8tt d| jsJtdt| }t| jd}||ksntd S )Ntest_frame4r   r   r>  r   r   )r   r   r4   r   r   r   r   rk  r1   r1   r2   test_to_sql_append  s    z_TestSQLApi.test_to_sql_appendc                 C  s2   t j|d| jdd t d| j}t|| d S )NZtest_frame5Fr   zSELECT * FROM test_frame5)r   r   r4   r   r   r   )r  r   r   r1   r1   r2   test_to_sql_type_mapping  s    z$_TestSQLApi.test_to_sql_type_mappingc                 C  sL   t tjddddd}tj|d| jdd td	| j}t|	 | d S )
Nrd  r   r/   Zseriesr
  Ztest_seriesFr   zSELECT * FROM test_series)
r   r   aranger   r   r4   r   r   r   to_frame)r  ss2r1   r1   r2   test_to_sql_series  s    z_TestSQLApi.test_to_sql_seriesc                 C  s\   t j|d| jd t jd| jd}|j|_|jddd |jt d |j_t	
|| d S )NrB  conrC  rD  TrE  )r   r   r4   r   r   rF  r   r  r
  r   r   rG  r1   r1   r2   test_roundtrip  s    z_TestSQLApi.test_roundtripc                 C  s6   t j|d| jddd t jd| jd}t|| d S )NrB  Fr   )rx  r   rf  rC  rw  )r   r   r4   r   r   r   rG  r1   r1   r2   test_roundtrip_chunksize  s    z$_TestSQLApi.test_roundtrip_chunksizec                 C  s2   t jd| jd}| }t|dddddg d S )Nr   rw  rx   ry   rz   r{   r|   )r   r9   r4   r   r   r   rI  r1   r1   r2   test_execute_sql  s    z_TestSQLApi.test_execute_sqlc              	   C  s  t d| j}t|jjjtjr$t	t jd| jdgd}t|jjjtjsNt	|j
 tddddddtddddddgkst	t jd| jdd	id}t|jjjtjst	|j
 tddddddtddddddgkst	t jd| jd
gd}t|jjjtjs
t	|j
 tddddddtddddddgks>t	t jd| jd
did}t|jjjtjslt	|j
 tddddddtddddddgkst	t jd| jddid}t|jjjtjst	|j
 tdtdgkst	d S )NSELECT * FROM typesrh   parse_datesi  r   r   r   r>  %Y-%m-%d %H:%M:%Sri   i        i  rt  rj   z%Y%m%dz
2010-10-10z
2010-12-12)r   r   r4   r   rh   r/   r~   r   
datetime64r   tolistr   ri   rj   r  r   r1   r1   r2   test_date_parsing  sh      

    z_TestSQLApi.test_date_parsingrU  ignoreraisecoercezread_sql, text, moder|  )r.   fallbackrf   r.   c                 C  s@   | j |kr<|ddi}||| jdd|iid}t|| d S )Nrh   zdatetime64[ns]errors)rx  r~  )r   r   r4   r   r   )r  r   r^   r   rU  r   r  r   r1   r1   r2   test_custom_dateparsing_error  s    
 z)_TestSQLApi.test_custom_dateparsing_errorc                 C  sH   t jd| jdddgd}t|jjjtjs.t	t|j
jjtjsDt	d S )Nr|  rh   ri   )	index_colr~  )r   r   r4   r   r   r/   r~   r   r  r   ri   r  r1   r1   r2   test_date_and_index  s    z_TestSQLApi.test_date_and_indexc              	   C  st   t tddgdd }tt |d| j}W 5 Q R X |dksHtt	
d| j}t|d |d d d S )	Nz00:00:01z00:00:03foorq  test_timedeltar   zSELECT * FROM test_timedeltar   )r   r   rs  r   assert_produces_warningUserWarningr   r4   r   r   r   assert_series_equalview)r  r   r  r   r1   r1   r2   r    s    z_TestSQLApi.test_timedeltac              	   C  sH   t dddgi}d}tjt|d |d| jd ks:tW 5 Q R X d S )Nr   y      ?      ?y               @zComplex datatypes not supportedr   Ztest_complex)r   r   r   r   r   r4   r   )r  r   r   r1   r1   r2   test_complex_raises%  s    z_TestSQLApi.test_complex_raiseszindex_name,index_label,expected)NNr   )Nother_labelr  )
index_nameNr  )r  r  r  )r   N0)Nr   r  c                 C  sT   t dtdi}||j_d}tj|d| j|d t|| j}|jd |ksPt	d S )Nr  r>  SELECT * FROM test_index_labeltest_index_labelindex_labelr   )
r   ranger   r
  r   r   r4   r   r   r   )r  r  r  r  
temp_framer6  rw   r1   r1   r2   test_to_sql_index_label+  s    z#_TestSQLApi.test_to_sql_index_labelc              	   C  s  d}t dtditddgd}t|d| j}||ks>ttd| j}|j	d d	ks^t|j	d
 dkspttj|d| jdddgd}||ksttd| j}|j	d d 
 ddgkstddg|j_tj|d| jdd}||ksttd| j}|j	d d 
 ddgksttj|d| jdddgd}||ksDttd| j}|j	d d 
 ddgksrtd}tjt|d tj|d| jddd W 5 Q R X d S )Nr>  r  )ZA0A1)B0ZB1r   r  r  r   rD  r   Zlevel_1r   r   r   )r   r  r   r   r   r   zALength of 'index_label' should match number of levels, which is 2r   )r   r  r   Zfrom_productr   r   r4   r   r   r   r  r   namesr   r   r   )r  Zexpected_row_countr  r   rw   r   r1   r1   r2   "test_to_sql_index_label_multiindexD  sb    
     z._TestSQLApi.test_to_sql_index_label_multiindexc                 C  sV   t jddgdddgddgd}|d| j tjd| jddgd	}tj||d
d d S )NrK  rM  r   r   r   rN  test_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtripr  TZcheck_index_type)r   rO  r   r4   r   r   r   r   r  r   r   r1   r1   r2   r  }  s      z%_TestSQLApi.test_multiindex_roundtripr/   Nr   r   c                 C  s^   t ddgddggddgd}|d| jd	ks2t||}tjd
| j|d}t|| d S )N333333?g333333@ffffff@g333333@r   r   r   test_dtype_argumentr   z$SELECT A, B FROM test_dtype_argumentrx  r/   )	r   r   r4   r   r   r   r   r   r   )r  r/   r   r  r   r1   r1   r2   r    s    
  z_TestSQLApi.test_dtype_argumentc                 C  s4   t ddgddggddgd}tj|d| jdd	 d S )
Nr   r   r   r>  r   r   Ztest_frame_integer_col_namesr   r   )r   r   r   r4   r  r1   r1   r2   test_integer_col_names  s    z"_TestSQLApi.test_integer_col_namesc                 C  s"   t j|d| jd}d|kstd S )Ntestrw  CREATEr   
get_schemar4   r   r  r   
create_sqlr1   r1   r2   test_get_schema  s    z_TestSQLApi.test_get_schemac                 C  s$   t j|d| jdd}d|ks td S )Nr  pypi)rx  r	  zCREATE TABLE pypi.r  r  r1   r1   r2   test_get_schema_with_schema  s    z'_TestSQLApi.test_get_schema_with_schemac                 C  sj   | j dkrddlm} |}nd}tddgddgd	}tj|d
| jd|id}d|ksZtd|ksftd S )Nr.   r   )re   INTEGER皙?r  rL  皙@r   r  r   r  r  )r   r.   re   r   r   r  r4   r   )r  re   r/   Zfloat_framer  r1   r1   r2   test_get_schema_dtypes  s    
   z"_TestSQLApi.test_get_schema_dtypesc                 C  sf   t ddgddgd}tj|d| jdd}d	}||ks:ttj|d| jd
dgd}d}||ksbtd S )Nr  r  rL  r  )Col1ZCol2r  r  )rx  r   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")r   r   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   r   r  r4   r   )r  r   rw   r  Zconstraint_sentencer1   r1   r2   test_get_schema_keys  s    z _TestSQLApi.test_get_schema_keysc                 C  s  t tjddtdd}|jd| jdd td| j}t  }d	}ddddd
g}tjd| jddD ]0}t	||gdd}t
||| kst|d7 }qdt|| | jdkrt  }d	}ddddd
g}tjd| jddD ]2}t	||gdd}t
||| kst|d7 }qt|| d S )N   rd  Zabcder   Ztest_chunksizeFr   zselect * from test_chunksizer   r   re  TZignore_indexr   r.   )r   r   randomZrandnlistr   r4   r   r   r   r   r   r   r   r   r   )r  r   r   r   iZsizeschunkres3r1   r1   r2   test_chunksize_read  s0      


z_TestSQLApi.test_chunksize_readc                 C  sd   t dddgdddgd}| }|d d|d< |jd	| jd
d td| j}t|| d S )Nr   r   r   zJohn P. Doez	Jane Dove)Z	person_idperson_namer  categorytest_categoricalFr   zSELECT * FROM test_categorical)	r   copyr   r   r4   r   r   r   r   )r  r   df2rW  r1   r1   r2   r    s    z_TestSQLApi.test_categoricalc                 C  s2   t ddgddggddgd}|jd| jd	d
 d S )Nr   r   r   r>     ér   r   Ztest_unicodeFr   )r   r   r4   r  r1   r1   r2   test_unicode_column_name  s    z$_TestSQLApi.test_unicode_column_namec                 C  sL   t dddgdtjdgd}|jd| jdd	 td
| j}t|| d S )Nr   r   r   r{   r  r  zd1187b08-4943-4c8d-a7f6Fr   z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r   nanr   r4   r   r   r   r   r  r   rW  r1   r1   r2   test_escaped_table_name  s    z#_TestSQLApi.test_escaped_table_name)0r  r  r  rY  r.  __annotations__r-  r   rZ  ra  r_  rc  rg  r   rj  rl  rn  ro  rv  ry  rz  r{  r  markparametrizer   r   r   r   r  r  r  r  r  r  r  r  r   r  r  r  r  r  r  r  r  r  r  r1   r1   r1   r2   r[  D  s   


7




9	
"r[  SQLAlchemy not installedreasonc                   @  s   e Zd ZdZdZdZdd Zdd Zdd	 Zd
d Z	dd Z
dd Zdd Zdd Zejddddddddddddd d!d"eeejd#krd$nd%fgd&d' Zejd(d)d*gd+d, Zd-d. Zed/d0d1 Zd2d3 Zd4d5 Zd6d7 Zd8S )9
TestSQLApiz
    Test the public API as it would be used directly

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

    r   r.   c                 C  s
   t dS Nzsqlite:///:memory:r.   r   r(  r1   r1   r2   rY     s    zTestSQLApi.connectc                 C  s@   t |d| j ddg}t jd| j|d}|j |ks<td S )Nr   r   r   r   )r   r   r4   r   r   r  r   )r  r   colsr   r1   r1   r2   test_read_table_columns  s    z"TestSQLApi.test_read_table_columnsc                 C  s   t |d| j t jd| jdd}|jjdgks4tt jd| jddgd}|jjddgks^tt jd| jddgddgd}|jjddgkst|j ddgkstd S )	Nr   r   r  r   r   r   r   )r  r   )	r   r   r4   r   r   r  r   r   r  rG  r1   r1   r2   test_read_table_index_col   s       z$TestSQLApi.test_read_table_index_colc                 C  sT   t d| j}t d| j}t|| t d| j}t d| j}t|| d S )Nr   r*   )r   r   r4   r   r   r   r   )r  iris_frame1iris_frame2r1   r1   r2   test_read_sql_delegate0  s    z!TestSQLApi.test_read_sql_delegatec                 C  s   ddl m} ddlm} |d|dg}|D ]R}t| j|rr| j $}|  || W 5 Q R X W 5 Q R X q,| j| q,t	
d " td| j td| j W 5 Q R X d S )Nr   r]   rE   z,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);Zother_tablezSELECT * FROM other_table)r.   r^   rT   rF   rX   r4   rY   rZ   r9   r   r  r   r   r   )r  r^   rF   Z
query_listr6  r4   r1   r1   r2   test_not_reflect_all_tables9  s    
 z&TestSQLApi.test_not_reflect_all_tablesc              	   C  sV   t jtdd t| jdd W 5 Q R X t d  |d| j W 5 Q R X d S )NzThe provided table name 'TABLE1' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.r   ZTABLE1 ZCaseSensitive)r   r  r  r   r   r4   Zcheck_case_sensitiver   r;  r1   r1   r2   (test_warning_case_insensitive_table_nameN  s    z3TestSQLApi.test_warning_case_insensitive_table_namec                 C  s6   ddl m} |j| j}|d}dd |D }|S )Nr   )
reflectionZtest_index_savedc                 S  s   g | ]}|d  qS Zcolumn_namesr1   rJ   r  r1   r1   r2   rS   c  s     z1TestSQLApi._get_index_columns.<locals>.<listcomp>)rT   r  Z	InspectorZfrom_enginer4   get_indexes)r  tbl_namer  r   ixsr1   r1   r2   rP  ^  s
    
zTestSQLApi._get_index_columnsc                 C  s\   ddl m} tdtddgddi}t| j}tjd||d	}t|j	j
d j|sXtd S )
Nr   )	TIMESTAMPr   201412120154201412110254Tutc	test_typerv   )r.   r  r   r   r   r   r4   SQLTablerX   r   r&  r~   r   )r  r  r   dbr   r1   r1   r2   test_sqlalchemy_type_mappingf  s    z'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)Zint8SMALLINT)ZInt8r  )Zuint8r  )ZUInt8r  )Zint16r  )ZInt16r  )Zuint16r  )ZUInt16r  )int32r  )ZInt32r  )Zuint32BIGINT)ZUInt32r  )r   r  )ZInt64r  r   r  r  c                 C  sP   t ddgdg|d}t| j}tjd||d}t|jjjj	}||ksLt
d S )Nr   r   r   r   r/   r  rv   )r   r   r   r4   r  r"   r   r&  r   r~   r   )r  r   r  r   r  r   r   r1   r1   r2   test_sqlalchemy_integer_mappingr  s
    z*TestSQLApi.test_sqlalchemy_integer_mappingr   Zuint64ZUInt64c              	   C  sN   t ddgdg|d}t| j}tjtdd tjd||d W 5 Q R X d S )	Nr   r   r   r  z1Unsigned 64 bit integer datatype is not supportedr   r  rv   )r   r   r   r4   r   r   r   r  )r  r   r   r  r1   r1   r2   (test_sqlalchemy_integer_overload_mapping  s     z3TestSQLApi.test_sqlalchemy_integer_overload_mappingc           	   	   C  s   t  L}d| }d}|j||ddd t||}t||}d}t||}W 5 Q R X t || t || t || d S )Nz
sqlite:///r*   r   F)r   r   r   )r   ensure_cleanr   r   r   r   r   r   )	r  r   r
  db_urir   rh  r   r6  rm  r1   r1   r2   test_database_uri_string  s    
z#TestSQLApi.test_database_uri_stringpg8000c              	   C  s.   d}t jtdd td| W 5 Q R X d S )Nz)postgresql+pg8000://user:pass@host/dbnamer  r   zselect * from tabler   r   ImportErrorr   r   )r  r  r1   r1   r2   (test_pg8000_sqlalchemy_passthrough_error  s    z3TestSQLApi.test_pg8000_sqlalchemy_passthrough_errorc                 C  sH   ddl m} |d}tj|| jddid}t|d }|dhksDtd S )Nr   r]   z#select * from iris where name=:namer
  zIris-versicolorr2  r,   )r.   r^   r   r   r4   setr   )r  r^   Z	name_textiris_df	all_namesr1   r1   r2   test_query_by_text_obj  s
    z!TestSQLApi.test_query_by_text_objc                 C  sh   ddl m}m} t| j}|||jj|dk}tj	|| j
ddid}t|d }|dhksdtd S )Nr   )	bindparamselectr
  r|   r2  r,   )r.   r   r  r3   r.  wherer&  r,   r   r   r4   r  r   )r  r   r  r*   Zname_selectr  r  r1   r1   r2   test_query_by_select_obj  s    
z#TestSQLApi.test_query_by_select_objc                 C  sJ   t dddgdddgd}|jd| jd	d
 td| j}t|| d S )Nr   r   r   r   r>  rd  )r   z%_variationZtest_column_percentageFr   )r   r   r4   r   r   r   r   r  r1   r1   r2   test_column_with_percentage  s    z&TestSQLApi.test_column_with_percentageN)r  r  r  rY  r.  r   rY   r  r  r  r  r  rP  r  r   r  r  r  r   r/   r
  r  r  r  tdZskip_if_installedr  r  r  r  r1   r1   r1   r2   r    sN   	
	
	
	r  c                      s,   e Zd ZdZejdd fddZ  ZS )_EngineToConnMixinzS
    A mixin that causes setup_connect to create a conn rather than an engine.
    Tr]  c                 #  sn   t    | j}| }| | _t|| _|| _	|| _d V  | j
  | j  | j	| _t| j	| _d S r   )superr_  r4   rY   rZ   Z_EngineToConnMixin__txr   r   r   Z_EngineToConnMixin__enginerollbackr   )r  r/  r1  r   r4   	__class__r1   r2   ra    s    



z_EngineToConnMixin.setup_method)r  r  r  rY  r   rZ  ra  __classcell__r1   r1   r	  r2   r    s   
r  c                   @  s   e Zd ZdS )TestSQLApiConnNrS  r1   r1   r1   r2   r    s   r  c                   @  sz   e Zd ZdZdZdZdddZdd Zej	j
ed	d
dd Zej	j
ed	d
dd Zdd Zdd Zdd Zdd ZdS )TestSQLiteFallbackApiz9
    Test the public sqlite connection fallback API

    r   r  r   c                 C  s
   t |S r   r_   rY   )r  Zdatabaser1   r1   r2   rY     s    zTestSQLiteFallbackApi.connectc              	   C  sn   t  P}| |}tj|d|dddks.t|  | |}td|}|  W 5 Q R X t || d S )NZtest_frame3_legacyFr   r>  z!SELECT * FROM test_frame3_legacy;)	r   r  rY   r   r   r   r   r   r   )r  r   r
  r4   r   r1   r1   r2   test_sql_open_close  s    


z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installedr  c              	   C  s2   d}d}t jt|d td| W 5 Q R X d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installedr   r   r  )r  r4   r   r1   r1   r2   test_con_string_import_error	  s    z2TestSQLiteFallbackApi.test_con_string_import_errorc              	   C  s<   G dd d}|d}t t td| W 5 Q R X d S )Nc                   @  s"   e Zd ZddddZdd ZdS )z~TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnectionNone)returnc                 _  s   t j||| _d S r   )r_   r`   r4   )r  argskwargsr1   r1   r2   __init__  s    zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__init__c                 S  s   t | j|S r   )getattrr4   )r  r
  r1   r1   r2   __getattr__  s    zTestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed.<locals>.MockSqliteConnection.__getattr__N)r  r  r  r  r  r1   r1   r1   r2   MockSqliteConnection  s   r  r   zSELECT 1)r   r  r  r   r   )r  r  r4   r1   r1   r2   Jtest_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installed  s    z`TestSQLiteFallbackApi.test_con_unknown_dbapi2_class_does_not_error_without_sql_alchemy_installedc              	   C  sZ   t d| j}t d| j}t|| d}tjt j|d t d| j W 5 Q R X d S )Nr   z9Execution failed on sql 'iris': near "iris": syntax errorr   r*   )	r   r   r4   r   r   r   r   r   DatabaseError)r  r  r  r   r1   r1   r2   r    s    z,TestSQLiteFallbackApi.test_read_sql_delegatec                 C  s   t |d}d|kstd S )Nr  r  )r   r  r   r  r1   r1   r2   test_get_schema2(  s    z&TestSQLiteFallbackApi.test_get_schema2c                 C  sJ   | dD ]*}|  d d|kr
|  d   S q
td| dd S )N
r   z""r   zColumn 
 not found)splitstripr   )r  r	  columncolr1   r1   r2   _get_sqlite_column_type-  s    z-TestSQLiteFallbackApi._get_sqlite_column_typec                 C  sT   t dtddgddi}t| j}tjd||d}| }| |ddksPtd S )	Nr   r  r  Tr  r  rv   r  )	r   r   r   r   r4   ZSQLiteTableZ
sql_schemar"  r   )r  r   r  r   r	  r1   r1   r2   test_sqlite_type_mapping4  s    z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r   )r  r  r  rY  r.  r   rY   r  r   r  skipifSQLALCHEMY_INSTALLEDr  r  r  r  r"  r#  r1   r1   r1   r2   r    s   


	r  c                   @  s  e Zd ZU dZded< ejddddd Zd	d
 Zejdddd Z	e
dd Ze
dd Ze
dd Zdd Zdd Zdd Zdd Zdd Zdd Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- Zd.d/ Zd0d1 Zd2d3 Zd4d5 Zd6d7 Zd8d9 Z d:d; Z!d<d= Z"d>d? Z#d@dA Z$dBdC Z%dDdE Z&dFdG Z'dHdI Z(dJdK Z)dLdM Z*dNdO Z+dPdQ Z,dRdS Z-dTdU Z.dVdW Z/dXdY Z0e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"   r.  Tclass)r^  Zscopec                 C  s*   |    |   |   }| _|  d S r   )setup_importsetup_driverrY   r4   )clsr4   r1   r1   r2   setup_classO  s    z_TestSQLAlchemy.setup_classc                 C  s   d S r   r1   r(  r1   r1   r2   r_  V  s    z&_TestSQLAlchemy.load_test_data_and_sqlr]  c                 C  s   d S r   r1   r`  r1   r1   r2   ra  Y  s    z_TestSQLAlchemy.setup_methodc                 C  s   t std d S )Nr  )r%  r   skipr*  r1   r1   r2   r(  ]  s    z_TestSQLAlchemy.setup_importc                 C  s
   t  d S r   NotImplementedErrorr-  r1   r1   r2   r)  c  s    z_TestSQLAlchemy.setup_driverc                 C  s
   t  d S r   r.  r-  r1   r1   r2   rY   g  s    z_TestSQLAlchemy.connectc                 C  sX   z&|   | _t| j| _| j   W n, tjjk
rR   t	d| j
 d Y nX d S )NzCan't connect to z server)rY   r4   r   r   r   r.   excZOperationalErrorr   r,  r.  r(  r1   r1   r2   r-  k  s    
z_TestSQLAlchemy.setup_connectc                 C  s   |    d S r   r7  r(  r1   r1   r2   test_read_sql_parametert  s    z'_TestSQLAlchemy.test_read_sql_parameterc                 C  s   |    d S r   r9  r(  r1   r1   r2   test_read_sql_named_parameterw  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc                 C  s   |  | d S r   r<  r;  r1   r1   r2   test_to_sql_emptyz  s    z!_TestSQLAlchemy.test_to_sql_emptyc                 C  sj   ddl m} |  }tddddgddddgd}t|}||dd	ksPt||}|dsftd S 
Nr   r+        ?       @      @      @ZoneZtwor  r>  )	r.   r   rY   r   r   r   r   r   r   r  r   Z	temp_connr  r   r   r1   r1   r2   test_create_table}  s    
z!_TestSQLAlchemy.test_create_tablec                 C  s   ddl m} |  }tddddgddddgd}t|}||dd	ksPt||}|dsft|	d |dr~td S r7  )
r.   r   rY   r   r   r   r   r   r   r  r=  r1   r1   r2   test_drop_table  s    

z_TestSQLAlchemy.test_drop_tablec                 C  s   |  | d S r   rH  r;  r1   r1   r2   ry    s    z_TestSQLAlchemy.test_roundtripc                 C  s   |    d S r   rJ  r(  r1   r1   r2   r{    s    z _TestSQLAlchemy.test_execute_sqlc                 C  s   t jd| jd}t| d S )Nr*   rw  )r   r   r4   r   rb  r1   r1   r2   test_read_table  s    z_TestSQLAlchemy.test_read_tablec                 C  s.   t jd| jddgd}t|jjddg d S )Nr*   r+   )rx  r   )r   r   r4   r   r   r   rW   rb  r1   r1   r2   r    s      z'_TestSQLAlchemy.test_read_table_columnsc              	   C  s2   d}t jt|d tjd| jd W 5 Q R X d S )Nz!Table this_doesnt_exist not foundr   Zthis_doesnt_existrw  )r   r   r   r   r   r4   )r  r   r1   r1   r2   test_read_table_absent_raises  s    z-_TestSQLAlchemy.test_read_table_absent_raisesc                 C  s~   t d| j}t|jjjtjs$t	t|j
jjtjs:t	t|jjjtjsPt	t|jjjtjsft	t|jjjtszt	d S r   )r   r   r4   r   rk   r/   r~   r   r   r   rl   r   rm   Zbool_rn   ro   objectr  r1   r1   r2   r     s    z,_TestSQLAlchemy.test_default_type_conversionc                 C  sH   t ddgid}|jd| jdddks*ttd| j}t|| d S )Ni64l            )r   test_bigintFr   r   )r   r   r4   r   r   r   r   r   r  r1   r1   r2   rF    s    z_TestSQLAlchemy.test_bigintc                 C  s(   t d| j}t|jjjtjs$t	d S r   
r   r   r4   r   rh   r/   r~   r   r  r   r  r1   r1   r2   test_default_date_load  s    z&_TestSQLAlchemy.test_default_date_loadc                 C  s@  dd }t d| j}t|ds4|jtjjdd |j}t	|j
sHtt d| jdgd}t|dsz|jtjjdd |j}t	|j
stt|jjdkst||j ttt d| jd	d
dd}|j}t	|j
stt|jjdksttd| j}|j}t	|j
stt|j|j td| j}||j d S )Nc                 S  s   t | jr4| d tdkst| d tdkstnbt| jrt| jjdksRttdddtdddg}t|| j	d}t
| | ntd| j d S )	Nr   z2000-01-01 08:00:00r   z2000-06-01 07:00:00UTC)tzrq  z&DateCol loaded with incorrect type -> )r	   r/   r   r   r
   r"   dtrJ  r   r
  r   r  )r!  Zexpected_datar  r1   r1   r2   r     s    




z:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkzselect * from typesrp   z&no column with datetime with time zoner  r}  rI  r   re  Tr  rf   )r   r4   r  node
add_markerr   r  xfailrp   r
   r/   r   r"   rK  rJ  r   r  r   r   r   r  )r  r   r   r   r!  r  r1   r1   r2   test_datetime_with_timezone  sD    "
  

z+_TestSQLAlchemy.test_datetime_with_timezonec                 C  s   t dtddddi}|jd| jdddks0t| jd	krP|d jd
|d< n|d jd |d< t	
d| j}t|| t	d| j}| jdkrt|jd tstt|d |d< t|| d S )Nr   2013-01-01 09:00:00r   
US/PacificperiodsrJ  Ztest_datetime_tzFr   r   rI  zSELECT * FROM test_datetime_tzr   r   r   )r   r   r   r4   r   r.  rK  Z
tz_convertZtz_localizer   r   r   r   r   rX   locr"   r   )r  r  r   r1   r1   r2   %test_datetime_with_timezone_roundtrip  s    

z5_TestSQLAlchemy.test_datetime_with_timezone_roundtripc                 C  sd   t dtdddidgd}|jd| jdddks4ttd| j}t tjgdgd}t	
|| d S )	Nr   i'  r   r   r   Ztest_datetime_obbFr   )r   r   r   r4   r   r   r   pdZNaTr   r   )r  r   r   r  r1   r1   r2   test_out_of_bounds_datetime5  s
    z+_TestSQLAlchemy.test_out_of_bounds_datetimec                 C  sh   t ddddd }tdtdi|d}|jd| jdd	dksBttjd| jdd
}t	j
||dd d S )Nz
2018-01-01rd  Z6H)rS  freqnumsr   Z	foo_tableZ	info_dater  r  F)Zcheck_names)r   Z
_with_freqr   r  r   r4   r   r   r   r   r   )r  datesr  r   r1   r1   r2   "test_naive_datetimeindex_roundtrip=  s
    z2_TestSQLAlchemy.test_naive_datetimeindex_roundtripc                 C  sJ  t d| j}| jdkrtntj}t|jj	j
|s6tt jd| jdgd}t|jj	j
tjs`tt jd| jddid}t|jj	j
tjstt jd| jdddiid}t|jj	j
tjstt jd| jdgd}t|jj	j
tjstt jd| jddid}t|jj	j
tjstt jd| jdd	diid}t|jj	j
tjsFtd S )
Nrf   r   rh   r}  r  formatri   rt  unit)r   r   r4   r.  rD  r   r  r   rh   r/   r~   r   ri   )r  r   Zexpected_typer1   r1   r2   r  G  s6      
  
z!_TestSQLAlchemy.test_date_parsingc                 C  s   t tdddtdd}|d| jdks2ttd| j}|j	ddd	}t
|| td
| j}|j	ddd	}| jdkrt|jd tstt|d |d< t
|| nt
|| d S )NrP  r   rS  r:  r  test_datetimer   r   )ZaxisSELECT * FROM test_datetimer   rT  r   )r   r   r   rr  r   r4   r   r   r   rU   r   r   r   r.  rX   rU  r"   r   r  r1   r1   r2   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sBtt	
d| j}t|| t	d
| j}| jdkrt|jd tstt|d dd|d< t|| nt|| d S )NrP  r   r_  r:  r  r   r   r`  Fr   ra  r   rT  r   r  )r  )r   r   r   rr  r  rU  r   r4   r   r   r   r   r   r   r.  rX   r"   r   r  r1   r1   r2   test_datetime_NaT|  s    
z!_TestSQLAlchemy.test_datetime_NaTc                 C  sl   t tdddtdddgdgd}|jd| jdddks<ttd| j}|d }t|d }t|| d S )	N  r   r   r   r   	test_dateFr   )	r   r   r   r4   r   r   r   r   r  )r  r   rW  r   r  r1   r1   r2   test_datetime_date  s    "z"_TestSQLAlchemy.test_datetime_datec                 C  s  t tdddtdddgdgd}|jd| jdd	d
ks<ttd| j}t|| t	 }t
j|d|dd	d
ksvtt
d|}|dd }t|| t
j|d| jdd	d
kst| jdkrt
d| j}|dd }t|| t
d| j}t|| d S )N	   r   r      r   r   	test_timeFr   r   Z
test_time2zSELECT * FROM test_time2c                 S  s
   |  dS Nz%H:%M:%S.%fstrftime_r1   r1   r2   <lambda>      z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>Z
test_time3r   zSELECT * FROM test_time3c                 S  s
   |  dS rj  rk  rm  r1   r1   r2   ro    rp  )r   r   r   r4   r   r   r   r   TestSQLiteFallbackrY   r   r   applymapr.  )r  r   rW  r   refr1   r1   r2   test_datetime_time  s     "
z"_TestSQLAlchemy.test_datetime_timec                 C  sh   t dtjd}t dtjd}t||d}|jd| jdddksDtt	d| j}t
j||dd	d
 d S )Ni  rp  g        )s1ru  Ztest_read_writeFr   r   T)Zcheck_dtypeZcheck_exact)r   r   r  float32r   r   r4   r   r   r   r   r   )r  ru  ru  r   r  r1   r1   r2   test_mixed_dtype_insert  s    z'_TestSQLAlchemy.test_mixed_dtype_insertc                 C  sn   t dddgdtjdgd}|jd| jdd	d
ks6ttd| j}t	|| t
d| j}t	|| d S )Nr   r   r   r{   r  r  test_nanFr   r   SELECT * FROM test_nan)r   r   r  r   r4   r   r   r   r   r   r   r  r1   r1   r2   test_nan_numeric  s    z _TestSQLAlchemy.test_nan_numericc                 C  s   t dddgtjtjtjgd}|jd| jdddks:ttd| j}t	|| |d	 
d
|d	< d |d	< td| j}t	|| d S )Nr   r   r   r  rx  Fr   r   r   rD  ry  )r   r   r  r   r4   r   r   r   r   r   r   r   r  r1   r1   r2   test_nan_fullcolumn  s     z#_TestSQLAlchemy.test_nan_fullcolumnc                 C  sx   t dddgddtjgd}|jd| jdd	d
ks6td |jd< td| j}t	
|| td| j}t	
|| d S )Nr   r   r   r   r   r  rx  Fr   r   r   r   ry  )r   r   r  r   r4   r   rU  r   r   r   r   r   r  r1   r1   r2   test_nan_string  s    
z_TestSQLAlchemy.test_nan_stringc                 C  s2   ddl m} || j}||}dd |D }|S )Nr   r+  c                 S  s   g | ]}|d  qS r  r1   r  r1   r1   r2   rS     s     z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)r.   r   r4   r  )r  r  r   r   r  r1   r1   r2   rP    s
    

z"_TestSQLAlchemy._get_index_columnsc                 C  s   |    d S r   rR  r(  r1   r1   r2   test_to_sql_save_index  s    z&_TestSQLAlchemy.test_to_sql_save_indexc                 C  s   |    d S r   rX  r(  r1   r1   r2   test_transactions  s    z!_TestSQLAlchemy.test_transactionsc           	   
   C  s   ddl m} ddlm} d}tj||| jd}|jd d }| | ||}t	| j|r| j
 $}|  || W 5 Q R X W 5 Q R X n| j| t|| j}tj||dd | | d S )Nr   r]   rE   test_get_schema_create_tablerw  Fr  )r.   r^   rT   rF   r   r  r4   r   r  rX   rY   rZ   r9   r   r   r   )	r  r   r^   rF   r  r  Zblank_test_dfr4   Zreturned_dfr1   r1   r2   r    s    

 z,_TestSQLAlchemy.test_get_schema_create_tablec              	   C  s  ddl m}m} ddlm} ddg}ddg}t||d}|d	| jd
ksNt|jd| jd|idd
kslt| }|j	| jd |j
d jd j}t||std}	tjt|	d |jd| jdtid W 5 Q R X |jd| jd|didd
kst|j	| jd |j
d jd j}t||s&t|jdks6t|jd| j|dd
ksRt|j	| jd |j
d jd j}
|j
d jd j}t|
|stt||std S )Nr   )rb   r(   r'   r   r   g?Tg?Nr   
dtype_testr   dtype_test2rp  rH   z&The type of B is not a SQLAlchemy typer   rU  Zdtype_test3
   single_dtype_test)r.   rb   r(   sqlalchemy.schemar'   r   r   r4   r   reflecttablesr   r~   rX   r   r   r   r"   r8  )r  rb   r(   r'   r  r   r   metaZsqltyper   ZsqltypeaZsqltypebr1   r1   r2   
test_dtype  s2     "z_TestSQLAlchemy.test_dtypec                 C  s  ddl m}m}m}m} ddlm} tdd gttdddd gtdd gdd	td
d gd}t	|}d}|
|| jdkstt|| j}	| }
|
j| jd | jdkr|n|}|
j| j}t|d j|stt|d j|stt|d j|stt|d j|std S )Nr   )rc   rd   r&   re   r  T  rd  r   rD  rp  r  BoolDateIntr&   notna_dtype_testr   rH   r   r  r  r  r&   )r.   rc   rd   r&   re   r  r'   r   r   r   r   r4   r   r   r   r  r.  r  r   rX   r~   )r  rc   rd   r&   re   r'   r  r   r  rn  r  Zmy_typecol_dictr1   r1   r2   test_notna_dtype2  s&    

z _TestSQLAlchemy.test_notna_dtypec           
   	   C  sn  ddl m}m}m} ddlm} d}tt|gddt|gddt|gddtdgd	dtdgd
dd}|jd| j	ddd|ddiddkst
td| j	}t|d jd dt|d jd dkst
| }|j| j	d |jd j}	t|	d jt|	d jkst
t|	d j|s(t
t|	d j|s>t
t|	d j|sTt
t|	d j|sjt
d S )Nr   )
BigIntegerr&   re   r  gYB?rv  rp  float64rd  r  r   )f32f64
f64_as_f32i32rE  Ztest_dtypesFr   r     )Z	precision)r   r   r/   r   r     rH   r  r  rE  )r.   r  r&   re   r  r'   r   r   r   r4   r   r   r   r   roundr   r  r  r   r"   r~   rX   )
r  r  r&   re   r'   Vr   rW  r  r  r1   r1   r2   test_double_precisionO  s>    
0"z%_TestSQLAlchemy.test_double_precisionc                   sj   ddl m  dd dd fdd fd	d
}tddddgid| jdks\t|| j d S )Nr   rE   c                 S  s   d}t j|| dS )Nz'SELECT test_foo_data FROM test_foo_datarw  )r   r   )r  r6  r1   r1   r2   r    s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.fooc                 S  s   |j d| dd d S )Ntest_foo_datar   )r
  rx  r   )r   )r  r   r1   r1   r2   bar  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.barc                   s   | } | | d S r   r1   )r4   Zfoo_data)r  r  r1   r2   baz  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.bazc              
     sH   t |  r<|  "}|  | W 5 Q R X W 5 Q R X n|  d S r   )rX   rY   rZ   )Zconnectabler4   )rF   r  r1   r2   main  s
    


z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr  r   r   r   )rT   rF   r   r   r4   r   )r  r  r1   )rF   r  r  r  r2   test_connectable_issue_example|  s    	z._TestSQLAlchemy.test_connectable_issue_exampleinputr  r  )r  infe0c              	   C  s   t |}| jdkrdd l}|jdd dkrPd|jkrPtjjdd}|j	| d}tj
t|d	 |jd
| jdd W 5 Q R X n4|jd
| jdddksttd
| j}t|| d S )Nr   r   r   )r   r  r   r  zGH 36465r  zinf cannot be used with MySQLr   ZfoobarFr   r   )r   r.  r   VERSIONr   r   r  rN  rL  rM  r   r   r   r4   r   r   r   r   Zassert_equal)r  r  r   r   r   r  r   rW  r1   r1   r2   test_to_sql_with_negative_npinf  s    
z/_TestSQLAlchemy.test_to_sql_with_negative_npinfc              
     s   ddl m mmm} ddlm}m} d}td|gi}| }G  fddd|}|| j	Z}|
 F | }	|j|	 |||d |  tj||j|	d	}
W 5 Q R X W 5 Q R X t|
| d S )
Nr   )r%   re   Unicoder  )Sessiondeclarative_basezHello, World!spamc                      s6   e Zd ZdZddgiZ ddZ dddZd	S )
z7_TestSQLAlchemy.test_temporary_table.<locals>.TemporaryZ	temp_testprefixes	TEMPORARYTZprimary_keyrh  F)ZnullableN)r  r  r  __tablename__Z__table_args__idr  r1   r%   re   r  r1   r2   	Temporary  s   
r  )r  )r   rx  )r.   r%   re   r  r  sqlalchemy.ormr  r  r   r4   rZ   r  Z	__table__rV   addflushr   r   r  r   r   )r  r  r  r  Z	test_datar  ZBaser  sessionr4   r   r1   r  r2   test_temporary_table  s    
(z$_TestSQLAlchemy.test_temporary_tablec              	   C  s.   d}t jt|d | |d W 5 Q R X d S )Nz*engine must be one of 'auto', 'sqlalchemy'r   Z
bad_engine)r   r   r   rA  ri  r1   r1   r2   test_invalid_engine  s    z#_TestSQLAlchemy.test_invalid_enginec              	   C  s&   t dd | | W 5 Q R X d S )Nio.sql.enginer.   rW  option_contextrA  r;  r1   r1   r2   test_options_sqlalchemy  s    z'_TestSQLAlchemy.test_options_sqlalchemyc              	   C  s&   t dd | | W 5 Q R X d S )Nr  r=  r  r;  r1   r1   r2   test_options_auto  s    z!_TestSQLAlchemy.test_options_autoc              	   C  s   t tdtsttdd* t tdts2tt tdtsDtW 5 Q R X tdd* t tdtsntt tdtstW 5 Q R X d S )Nr.   r  r=  )rX   r   r   r   rW  r  r(  r1   r1   r2   test_options_get_engine  s    z'_TestSQLAlchemy.test_options_get_enginec                 C  s   d S r   r1   r(  r1   r1   r2   "test_get_engine_auto_error_message  s    z2_TestSQLAlchemy.test_get_engine_auto_error_messageN)<r  r  r  rY  r  r   rZ  r+  r_  ra  classmethodr(  r)  rY   r-  r2  r4  r6  r>  r?  ry  r{  rB  r  rC  r   rF  rH  rO  rV  rX  r\  r  r`  rc  rf  rt  rw  rz  r{  r}  rP  r  r  r  r  r  r  r  r  r  r   infr  r  r  r  r  r  r  r1   r1   r1   r2   r&  D  sv   






	P
 
	#- (
 r&  c                      s*   e Zd Zejjdd fddZ  ZS )_TestSQLAlchemyConnz4Nested transactions rollbacks don't work with Pandasr  c                   s   t    d S r   )r  r  r(  r	  r1   r2   r    s    z%_TestSQLAlchemyConn.test_transactions)r  r  r  r   r  rN  r  r  r1   r1   r	  r2   r    s   r  c                   @  sL   e Zd ZdZdZedd Zedd Zdd Zd	d
 Z	dd Z
dd ZdS )_TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r   c                 C  s
   t dS r  r  r-  r1   r1   r2   rY   	  s    z_TestSQLiteAlchemy.connectc                 C  s
   d | _ d S r   )driverr-  r1   r1   r2   r)  	  s    z_TestSQLiteAlchemy.setup_driverc                 C  s   t d| j}t|jjjtjs$t	t|j
jjtjs:t	t|jjjtjsPt	t|jjjtjsft	t|jjjtjs|t	d S r   )r   r   r4   r   rk   r/   r~   r   r   r   rl   r   rm   rn   ro   r  r1   r1   r2   r   	  s    z/_TestSQLiteAlchemy.test_default_type_conversionc                 C  s(   t d| j}t|jjjtjr$t	d S r   rG  r  r1   r1   r2   rH  	  s    z)_TestSQLiteAlchemy.test_default_date_loadc              	   C  sV   t dddgidd}|jd| jdddks.ttd  td| j W 5 Q R X d S )	Nr   r   r   r   rp  Ztest_bigintwarningFr   )r   r   r4   r   r   r  r   r   r  r1   r1   r2   test_bigint_warning%	  s    z&_TestSQLiteAlchemy.test_bigint_warningc           	        s   ddl m mm ddlm}m} | }G  fddd|}|j| j	 || j	d}| }t
ddgdd	gd
}|jd| j	ddddkst|  ||j|j}t
|}|  t|jddgkstd S )Nr   r%   re   r(   )r  sessionmakerc                      s(   e Zd ZdZ ddZ dZdS )z?_TestSQLiteAlchemy.test_row_object_is_named_tuple.<locals>.Testr   Tr  2   N)r  r  r  r  r  r  r1   r  r1   r2   Test>	  s   r  rH   r   ZhelloZworld)r  r  r   Fr   )rx  r   r   r   r  r  )r.   r%   re   r(   r  r  r  r0   Z
create_allr4   r   r   r   r#  r6  r  r  r   r  r   )	r  r  r  Z	BaseModelr  r  r  r   r  r1   r  r2   test_row_object_is_named_tuple-	  s"    z1_TestSQLiteAlchemy.test_row_object_is_named_tupleN)r  r  r  rY  r.  r  rY   r)  r   rH  r  r  r1   r1   r1   r2   r    s   

r  c                   @  s8   e Zd ZdZdZdZedd Zedd Zdd	 Z	d
S )_TestMySQLAlchemyzA
    Test the sqlalchemy backend against an MySQL database.

    r   i  c                 C  s"   t jd| j d| j d| jdS )Nzmysql+z://root@localhost:/pandasr   )r.   r   r  portr   r-  r1   r1   r2   rY   ^	  s    z_TestMySQLAlchemy.connectc                 C  s$   t d}d| _d|jjji| _d S )Nr   r   )r   r   r  r   r   r   r   )r*  r   r1   r1   r2   r)  e	  s    
z_TestMySQLAlchemy.setup_driverc                 C  s   d S r   r1   r(  r1   r1   r2   r   k	  s    z._TestMySQLAlchemy.test_default_type_conversionN)
r  r  r  rY  r.  r  r  rY   r)  r   r1   r1   r1   r2   r  U	  s   

r  c                   @  s8   e Zd ZdZdZdZedd Zedd Zdd	 Z	d
S )_TestPostgreSQLAlchemyzF
    Test the sqlalchemy backend against an PostgreSQL database.

    r   i8  c                 C  s   t d| j d| j dS )Nzpostgresql+z://postgres:postgres@localhost:r  )r.   r   r  r  r-  r1   r1   r2   rY   x	  s    z_TestPostgreSQLAlchemy.connectc                 C  s   t d d| _d S )Nr   )r   r   r  r-  r1   r1   r2   r)  ~	  s    
z#_TestPostgreSQLAlchemy.setup_driverc              	   C  sj  ddl m} tddgddgddgd	}| jd
 | jd |jd| jdddksZt|jd| jddddksvt|jd| jddddksttd| j}t	
|| td| j}t	
|| tjd| jdd}t	
|| tjd| jdd}t	
|| d}tjt|d tjd| jdd W 5 Q R X | jd
 | jd |jd| jddddksht|jd| jdddd |jd| jdddddksttjd| jdd}t	
t||gdd| t| j|rf|  }	tj|	dd}
|
j|ddddkst|
j|dddddks"t|
j|dddddks>ttjd| jdd}|
d}t	
|| d S )Nr   rE   r   r   r   r{   r   r  r  z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;Ztest_schema_publicFr   Ztest_schema_public_explicitpublic)r   r	  Ztest_schema_otherother)r	  z!Table test_schema_other not foundr   )r	  r   r   )r	  r   r   r   Tr  Ztest_schema_other2)r   r   )rT   rF   r   r4   r9   r   r   r   r   r   r   r   r   r   r   rX   rY   r   Z
read_table)r  rF   r   r   r   r  Zres4r   rW  Zengine2Zpdsqlr1   r1   r2   test_schema_support	  s         

z*_TestPostgreSQLAlchemy.test_schema_supportN)
r  r  r  rY  r.  r  r  rY   r)  r  r1   r1   r1   r2   r  o	  s   

r  c                   @  s   e Zd ZdS )TestMySQLAlchemyNrS  r1   r1   r1   r2   r  	  s   r  c                   @  s   e Zd ZdS )TestMySQLAlchemyConnNrS  r1   r1   r1   r2   r  	  s   r  c                   @  s   e Zd ZdS )TestPostgreSQLAlchemyNrS  r1   r1   r1   r2   r  	  s   r  c                   @  s   e Zd ZdS )TestPostgreSQLAlchemyConnNrS  r1   r1   r1   r2   r  	  s   r  c                   @  s   e Zd ZdS )TestSQLiteAlchemyNrS  r1   r1   r1   r2   r  	  s   r  c                   @  s   e Zd ZdS )TestSQLiteAlchemyConnNrS  r1   r1   r1   r2   r  	  s   r  c                   @  s   e Zd ZdZdZedd Zdd Zej	ddd	d
 Z
dd Zdd Zdd Zdd Zdd Zdd Zdd Zejdddgdd Zdd Zdd  Zd!d" Zd#d$ Zd%d& Zd'd( Zd)d* Zd+S ),rq  zG
    Test the fallback mode against an in-memory sqlite database.

    r   c                 C  s
   t dS r   r  r-  r1   r1   r2   rY   
  s    zTestSQLiteFallback.connectc                 C  s   |   | _d S r   r\  r(  r1   r1   r2   r-  
  s    z TestSQLiteFallback.setup_connectTr]  c                 C  s   t | j| _d S r   )r   r   r4   r   r`  r1   r1   r2   ra  
  s    zTestSQLiteFallback.setup_methodc                 C  s   |    d S r   r1  r(  r1   r1   r2   r2  
  s    z*TestSQLiteFallback.test_read_sql_parameterc                 C  s   |    d S r   r3  r(  r1   r1   r2   r4  
  s    z0TestSQLiteFallback.test_read_sql_named_parameterc                 C  s   |  | d S r   r5  r;  r1   r1   r2   r6  
  s    z$TestSQLiteFallback.test_to_sql_emptyc                 C  sd   t ddddgddddgd}| j|ddks4t| jdsDt| jd | jdr`td S )Nr8  r9  r:  r;  r<  Zdrop_test_framer>  )r   r   r   r   r   r  )r  r  r1   r1   r2   test_create_and_drop_table
  s    z-TestSQLiteFallback.test_create_and_drop_tablec                 C  s   |  | d S r   r@  r;  r1   r1   r2   ry  %
  s    z!TestSQLiteFallback.test_roundtripc                 C  s   |    d S r   rA  r(  r1   r1   r2   r{  (
  s    z#TestSQLiteFallback.test_execute_sqlc                 C  s   t tdddtdddgdgd}|jd| jdddks<ttd	| j}| jd
krft||	t
 n| jdkr|t|| d S )Nrd  r   r   r   r   re  Fr   zSELECT * FROM test_dater   r   )r   r   r   r4   r   r   r.  r   r   r   r"   r  r1   r1   r2   rf  +
  s    "

z%TestSQLiteFallback.test_datetime_datetz_awareFc                 C  s   |st dddt dddg}n$tdddd}t| d	d
 }t|dgd}|jd| jdddksjtt	d| j}| j
dkr|dd
 }t|| d S )Nrg  r   r   rh  rP  r   rQ  rR  c                 S  s   |   S r   )timetz)rK  r1   r1   r2   ro  =
  rp  z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>r   r   ri  Fr   zSELECT * FROM test_timer   c                 S  s
   |  dS rj  rk  rm  r1   r1   r2   ro  E
  rp  )r   r   r   Zto_pydatetimemapr   r   r4   r   r   r.  rr  r   r   )r  r  Ztz_timesZtz_dtr   rW  r  r1   r1   r2   rt  6
  s    
z%TestSQLiteFallback.test_datetime_timec                 C  sT   t dd| d | j}g }|jD ]*}t d| d| j}||j  q$|S )Nz1SELECT * FROM sqlite_master WHERE type = 'index' zAND tbl_name = ''zPRAGMA index_info())r   r   r4   r
  r   r  )r  r  r  rQ  Zix_nameZix_infor1   r1   r2   rP  H
  s    

z%TestSQLiteFallback._get_index_columnsc                 C  s   |    d S r   r~  r(  r1   r1   r2   r  T
  s    z)TestSQLiteFallback.test_to_sql_save_indexc                 C  s   |    d S r   r  r(  r1   r1   r2   r  W
  s    z$TestSQLiteFallback.test_transactionsc           
      C  sT   | j d| d}|D ] \}}}}}}	||kr|  S qtd| d| dd S )NzPRAGMA table_info(r  zTable z	, column r  )r4   r9   r   )
r  r   r   ZrecsZcidr
  ctypeZnot_nulldefaultpkr1   r1   r2   r"  Z
  s
    
z*TestSQLiteFallback._get_sqlite_column_typec              	   C  s  | j dkrtd ddg}ddg}t||d}|d| jd	ksFt|jd
| jddidd	ksdt| dddksxt| d
ddkstd}tjt	|d |jd| jdt
id W 5 Q R X |jd| jddd	kst| dddkst| dddkstd S )Nr   Not applicable to MySQL legacyr   r   r  r  r   r  r   r  STRINGrp  r  z!B \(<class 'bool'>\) not a stringr   rU  r  )r.  r   r,  r   r   r4   r   r"  r   r   bool)r  r  r   r   r   r1   r1   r2   r  a
  s    

 zTestSQLiteFallback.test_dtypec                 C  s   | j dkrtd tdd gttdddd gtdd gddtd	d gd
}t|}d}||| jdkspt| 	|ddkst| 	|ddkst| 	|ddkst| 	|ddkstd S )Nr   r  Tr  rd  r   rD  rp  r  r  r  r   r  r  r  r  r  r&   r$   )
r.  r   r,  r   r   r   r   r4   r   r"  )r  r  r   r  r1   r1   r2   r  w
  s    



z#TestSQLiteFallback.test_notna_dtypec                 C  s   t ddgddggddgd}d}tjt|d	 |d
| j W 5 Q R X tddddddddddg
D ]x\}}||| jdkstt	|| j t ddgddggd|gd}d|d}||| jdkstt	|| j qdd S )Nr   r   r   r>  r   r   r   z$Empty table or column name specifiedr   r  ztest_weird_name]ztest_weird_name[ztest_weird_name`ztest_weird_name"ztest_weird_name'z_b.test_weird_name_01-30z"_b.test_weird_name_01-30"Z99beginswithnumberZ12345r  Ztest_weird_col_named)
r   r   r   r   r   r4   	enumerater   r   Ztable_exists)r  r   r   ZndxZ
weird_namer  Zc_tblr1   r1   r2   test_illegal_names
  s.    z%TestSQLiteFallback.test_illegal_namesN)r  r  r  rY  r.  r  rY   r-  r   rZ  ra  r2  r4  r6  r  ry  r{  rf  r  r  rt  rP  r  r  r"  r  r  r  r1   r1   r1   r2   rq  	  s.   



rq  z'{}'z{:.8f}z{:d}c                 C  s   dS )NZNULLr1   )xr1   r1   r2   ro  
  rp  ro  z{:.10f}z'{!s}'c                 G  sJ   g }|D ]4}t |tr"t|r"d }tt| }||| q| t| S r   )rX   r   r   _formattersr~   r   r0  )r   r  Zprocessed_argsarg	formatterr1   r1   r2   format_query
  s    r  c                 C  s&   t j| |d }|dkrdS t|S )z#Replace removed sql.tquery functionrw  N)r   r9   r%  r  )r6  rx  rW  r1   r1   r2   tquery
  s    r  c                   @  sl   e Zd Zdd Zdd Zdd Zdd Zd	d
 Zdd Zdd Z	dd Z
dd Zdd Zdd Zdd ZdS )TestXSQLitec                 C  s   t d| _d S r   )r_   rY   r4   r(  r1   r1   r2   ra  
  s    zTestXSQLite.setup_methodc                 C  s   | j   d S r   r'  r(  r1   r1   r2   r  
  s    zTestXSQLite.teardown_methodc                 C  s.   | j  }|dt|  | j   d S r!  )r4   r8   r9   r   r"  r#  )r  r   r?   r1   r1   r2   r  
  s    
zTestXSQLite.drop_tablec                 C  s   t  }tj|d| jdddks$ttd| j}|j|_|}t || dgt	| |d< |
 }ttt	|d }|
 |d	< tj|d
| jdddksttjd| jd	d}|
 }||_d	|j_t || d S )NZ
test_tableF)r
  rx  r   rh  zselect * from test_tabler   txtr  ZIdxZtest_table2zselect * from test_table2r  )r   makeTimeDataFramer   r   r4   r   r   r   r   r   r  r   r   rr  r
  )r  rw   r   r  Zframe2Znew_idxr1   r1   r2   
test_basic
  s     zTestXSQLite.test_basicc           	      C  s   t  }tj|jd< t|d}| j }|	| d}|
 D ]$\}}t|f| }t|| jd q@| j  tjd| jd}|j|_t j||dd d S )N)r   r   r  z(INSERT INTO test VALUES (%s, %s, %s, %s)rw  select * from testgMbP?)Zrtol)r   r  r   r  r   r   r  r4   r8   r9   Ziterrowsr  r  r#  r   r   r   )	r  rw   r  r?   insrn  rP   Zfmt_sqlr   r1   r1   r2   test_write_row_by_row
  s    


z!TestXSQLite.test_write_row_by_rowc                 C  s   t  }t|d}| j }|| d}|jd }tj|| jt|d | j	  t
d| j}|jd d |_t ||d d  d S )Nr  z$INSERT INTO test VALUES (?, ?, ?, ?)r   r2  r  r   )r   r  r   r  r4   r8   r9   r   r0  r#  r   r   r   )r  rw   r  r?   r  rP   r   r1   r1   r2   test_execute  s    



zTestXSQLite.test_executec                 C  s   t  }t|d}| }|D ]6}|d}t|dkr |d dkr |d dks tq tj|dddgd	}| }d
|kst| j	 }|
| d S )Nr   r   r   r   r   ZDATETIMEr   r   zPRIMARY KEY ("A", "B"))r   r  r   r  
splitlinesr  r   r   r4   r8   r9   )r  rw   r  lineslinetokensr?   r1   r1   r2   test_schema  s    

zTestXSQLite.test_schemac              	   C  sb   d}| j  }|| td| j  td| j  tjtjdd td| j  W 5 Q R X d S )N
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a, b)
        );
        ,INSERT INTO test VALUES("foo", "bar", 1.234)z,INSERT INTO test VALUES("foo", "baz", 2.567)zExecution failed on sqlr   z(INSERT INTO test VALUES("foo", "bar", 7))r4   r8   r9   r   r   r   r  )r  r  r?   r1   r1   r2   test_execute_fail  s    	

zTestXSQLite.test_execute_failc              	   C  sb   d}| j  }|| td| j  | j   d}tjtj|d t	d| j d W 5 Q R X d S )Nr  r  z$Cannot operate on a closed database.r   r  rw  )
r4   r8   r9   r   r   r   r   r_   ZProgrammingErrorr  )r  r  r?   r   r1   r1   r2   test_execute_closed_connection2  s    	


z*TestXSQLite.test_execute_closed_connectionc                 C  s2   t dtdi}tj|| jddddks.td S )NFromrd  ZtestkeywordsFrx  r
  r   )r   r   Zonesr   r   r4   r   r  r1   r1   r2   test_keyword_as_column_namesF  s    z(TestXSQLite.test_keyword_as_column_namesc                 C  st   t ddgdgd}tj|| jddddks.t| j}tdd	 |d
D }|dksXttd
|}t	|| d S )Nr   r   Zc0r   mono_dfFr  c                 s  s   | ]}|d  V  qdS )r   Nr1   )rJ   Zmy_c0r1   r1   r2   	<genexpr>R  s     z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr   )
r   r   r   r4   r   sumr9   r   r   r   )r  r  Zcon_xZthe_sumr   r1   r1   r2   test_onecolumn_of_integerJ  s    z%TestXSQLite.test_onecolumn_of_integerc              	   C  s  t ddgddgd}t dddgd	d
dgd}d}d| }d}tjt|d tj|| j|dd W 5 Q R X | | tj|| j|dd d}tjt|d tj|| j|dd W 5 Q R X tj|| j|ddd t|| jdddgkst	tj|| j|ddddkst	t|| jddddgks.t	| | tj|| j|ddddksXt	t|| jdddgkstt	tj|| j|ddddkst	t|| jddddddgkst	| | d S )Nr   r   r   r   )r  r  r   r>  rd  r   r   EZtable_if_existszSELECT * FROM z*'notvalidvalue' is not valid for if_existsr   Znotvalidvalue)rw   rx  r
  r   r   z&Table 'table_if_exists' already existsr   F)rw   rx  r
  r   r   rw  rb  r|  )r   r   )r>  r   )rd  r	  r   )
r   r   r   r   r   r   r4   r  r  r   )r  Zdf_if_exists_1Zdf_if_exists_2r   Z
sql_selectr   r1   r1   r2   test_if_existsY  s    

      



zTestXSQLite.test_if_existsN)r  r  r  ra  r  r  r  r  r  r  r   r  r  r  r
  r1   r1   r1   r2   r  
  s   r  )N)rY  
__future__r   r;   r   r   r   r   r   pathlibr   r_   Znumpyr   r   Zpandas.util._test_decoratorsutilZ_test_decoratorsr  Zpandas.core.dtypes.commonr	   r
   ZpandasrW  r   r   r   r   r   r   r   r   r   r   Zpandas._testingZ_testingr   Zpandas.io.sqlr   r   r   r   r   r   r   r   r.   r%  r  r4  r3   rB   r\   ra   rr   rt   ru   r   r   rZ  r   rs   r   r   r   r   r   r   r   r   r   r   r   r   r   Zmysql_connectableZpostgresql_connectableZsqlite_connectableZsqlite_iris_connectableZsqlalchemy_connectableZsqlalchemy_connectable_irisZall_connectableZall_connectable_irisr  r  r  r   r   r   r   r   r   r   r  r  r   r*  r,  r[  r$  r  r  r  r  r&  r  r  r  r  r  r  r  r  r  r  rq  r]  r"   Zstr_bytesr   r  r~   r  r  r  r  r  r  r1   r1   r1   r2   <module>   s^  0$

!



#













	#%	 
   G JW     :Vm 4         
