U
    f/eb                    @   s2  d Z ddlZddlmZmZmZ ddlmZ ddlZddlZddl	Z
ddlZddlmZ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$ zNddl%Z%ddl%m&Z& dd	l'm(Z( dd
l)m*Z+ ddl,Z%ddl-m  m.Z. dZ/W n e0k
r6   dZ/Y nX ddddddddddddddddddddddddddd d!d"dd#d$d%dd&d'id(Z1G d)d* d*Z2G d+d, d,e2Z3G d-d. d.e2Z4G d/d0 d0e2Z5G d1d2 d2Z6G d3d4 d4e6Z7ej8j9ej8j:e/ d5d6G d7d8 d8e5e7Z;G d9d: d:Z<ej8j9G d;d< d<e<e;Z=ej8j9G d=d> d>e4e7Z>G d?d@ d@e5e6Z?G dAdB dBe<e?Z@G dCdD dDZAG dEdF dFZBG dGdH dHZCej8j9ej8jDG dIdJ dJeBe?ZEej8j9ej8jDG dKdL dLeBe@ZFej8j9ej8jDG dMdN dNeCe?ZGej8j9ej8jDG dOdP dPeCe@ZHej8j9G dQdR dReAe?ZIej8j9G dSdT dTeAe@ZJej8j9G dUdV dVe4e6ZKdWdX ZLedYjMeNdYjMe
jOdYjMePdYjMeQdZjMeRd[jMeSdd\d] e
jTd^jMeUd_jMi	ZVd`da ZWdidbdcZXej8j9G ddde dee4ZYej8j9ej8jDej8jZdfd6G dgdh dhe3Z[dS )ja  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`)

    N)datedatetimetime)StringIO)is_datetime64_dtypeis_datetime64tz_dtype)
	DataFrameIndex
MultiIndexSeries	Timestampconcat
date_rangeisnato_datetimeto_timedelta)SQLAlchemyEngine_gt14
get_engineread_sql_queryread_sql_tableinspect)declarative)sessionTFzCREATE TABLE iris (
                "SepalLength" REAL,
                "SepalWidth" REAL,
                "PetalLength" REAL,
                "PetalWidth" REAL,
                "Name" TEXT
            )zCREATE TABLE iris (
                `SepalLength` DOUBLE,
                `SepalWidth` DOUBLE,
                `PetalLength` DOUBLE,
                `PetalWidth` DOUBLE,
                `Name` VARCHAR(200)
            )a  CREATE TABLE iris (
                "SepalLength" DOUBLE PRECISION,
                "SepalWidth" DOUBLE PRECISION,
                "PetalLength" DOUBLE PRECISION,
                "PetalWidth" DOUBLE PRECISION,
                "Name" VARCHAR(200)
            )sqlitemysql
postgresqlz&INSERT INTO iris VALUES(?, ?, ?, ?, ?)z.INSERT INTO iris VALUES(%s, %s, %s, %s, "%s");z,INSERT INTO iris VALUES(%s, %s, %s, %s, %s);a  CREATE TABLE types_test_data (
                    "TextCol" TEXT,
                    "DateCol" TEXT,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" REAL,
                    "IntCol" INTEGER,
                    "BoolCol" INTEGER,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" INTEGER
                )a  CREATE TABLE types_test_data (
                    `TextCol` TEXT,
                    `DateCol` DATETIME,
                    `IntDateCol` INTEGER,
                    `IntDateOnlyCol` INTEGER,
                    `FloatCol` DOUBLE,
                    `IntCol` INTEGER,
                    `BoolCol` BOOLEAN,
                    `IntColWithNull` INTEGER,
                    `BoolColWithNull` BOOLEAN
                )a  CREATE TABLE types_test_data (
                    "TextCol" TEXT,
                    "DateCol" TIMESTAMP,
                    "DateColWithTz" TIMESTAMP WITH TIME ZONE,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" DOUBLE PRECISION,
                    "IntCol" INTEGER,
                    "BoolCol" BOOLEAN,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" BOOLEAN
                )zo
                INSERT INTO types_test_data
                VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
                	TextColDateCol
IntDateColIntDateOnlyColFloatColIntColBoolColIntColWithNullBoolColWithNull)queryfieldszz
                INSERT INTO types_test_data
                VALUES("%s", %s, %s, %s, %s, %s, %s, %s, %s)
                z|
                INSERT INTO types_test_data
                VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                
r    r!   DateColWithTzr"   r#   r$   r%   r&   r'   r(   z1SELECT * FROM iris WHERE Name=? AND SepalLength=?z9SELECT * FROM iris WHERE `Name`="%s" AND `SepalLength`=%sz7SELECT * FROM iris WHERE "Name"=%s AND "SepalLength"=%sz]
                SELECT * FROM iris WHERE Name=:name AND SepalLength=:length
                zy
                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 '%'r   z]
                CREATE VIEW iris_view AS
                SELECT * FROM iris
                )create_irisinsert_iriscreate_test_typesinsert_test_typesread_parametersread_named_parametersread_no_parameters_with_percentcreate_viewc                   @   s   e Zd Zdd ZdS )	MixInBasec                 C   s.   t | dr*|  D ]}| | q|   d S )Nconn)hasattr_get_all_tables
drop_table_close_conn)selfmethodtbl r>   </tmp/pip-unpacked-wheel-tiezk1ph/pandas/tests/io/test_sql.pyteardown_method   s    
zMixInBase.teardown_methodN)__name__
__module____qualname__r@   r>   r>   r>   r?   r5      s   r5   c                   @   s$   e Zd Zdd Zdd Zdd ZdS )
MySQLMixInc                 C   s.   | j  }|dt|  | j   d S NzDROP TABLE IF EXISTS )r6   cursorexecutesqlZ_get_valid_mysql_namecommit)r;   
table_namecurr>   r>   r?   r9      s    
zMySQLMixIn.drop_tablec                 C   s&   | j  }|d dd | D S )NzSHOW TABLESc                 S   s   g | ]}|d  qS r   r>   .0tabler>   r>   r?   
<listcomp>   s     z.MySQLMixIn._get_all_tables.<locals>.<listcomp>)r6   rF   rG   fetchall)r;   rK   r>   r>   r?   r8      s    

zMySQLMixIn._get_all_tablesc                 C   s4   ddl m} z| j  W n |k
r.   Y nX d S )Nr   )Error)Zpymysql.errrR   r6   close)r;   rR   r>   r>   r?   r:      s
    zMySQLMixIn._close_connNrA   rB   rC   r9   r8   r:   r>   r>   r>   r?   rD      s   rD   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 rE   )r6   rG   rH   Z_get_valid_sqlite_namerI   r;   rJ   r>   r>   r?   r9      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 rL   r>   rM   r>   r>   r?   rP     s     z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r6   rG   rQ   )r;   cr>   r>   r?   r8     s    zSQLiteMixIn._get_all_tablesc                 C   s   | j   d S N)r6   rS   r;   r>   r>   r?   r:   	  s    zSQLiteMixIn._close_connNrT   r>   r>   r>   r?   rU      s   rU   c                   @   s$   e Zd Zdd Zdd Zdd ZdS )SQLAlchemyMixInc                 C   s   t | j| d S rX   )rH   SQLDatabaser6   r9   rV   r>   r>   r?   r9     s    zSQLAlchemyMixIn.drop_tablec                 C   s&   t jj| jd}|  |j }|S )Nbind)
sqlalchemyschemaMetaDatar6   reflecttableskeys)r;   metaZ
table_listr>   r>   r?   r8     s    
zSQLAlchemyMixIn._get_all_tablesc                 C   s   | j   d S rX   )r6   ZdisposerY   r>   r>   r?   r:     s    zSQLAlchemyMixIn._close_connNrT   r>   r>   r>   r?   rZ     s   rZ   c                   @   s   e Zd ZdZdd Zejdg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d8d!d"Zd#d$ Zd%d& Zd'd( Zd)d* Zd+d, Zd9d.d/Zd0d1 Zd2d3 Zd4d5 Zd6d7 Zd S ):PandasSQLTestzT
    Base class with common private methods for SQLAlchemy and fallback cases.

    c                 C   s    t | jdr| jS | j S d S )NrG   )r7   r6   rF   rY   r>   r>   r?   	_get_exec"  s    zPandasSQLTest._get_exec)iodatacsvziris.csvparamsc              	   C   s   ||j  }t| ds|   | d |  td | j  t|d d@}t	
|}t| td | j }|D ]}|  || qpW 5 Q R X d S )Nr6   irisr-   )newliner.   )paramr7   setup_connectr9   rf   rG   SQL_STRINGSflavoropenri   readernext)r;   datapathrequestZiris_csv_fileZiris_csvrinsrowr>   r>   r?   load_iris_data(  s    



zPandasSQLTest.load_iris_datac                 C   s&   |  d |  td | j  d S )NZ	iris_viewr4   )r9   rf   rG   rp   rq   rY   r>   r>   r?   _load_iris_view;  s    
zPandasSQLTest._load_iris_viewc                 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equalContentsvalues)r;   
iris_frameZpytypery   r>   r>   r?   _check_iris_loaded_frame?  s    
z&PandasSQLTest._check_iris_loaded_framec                 C   s,   dddddg}dddd	g}t ||d
| _d S )NindexABCD)2000-01-03 00:00:00gN\^?g9\x`|@g% TO׿g(nI)2000-01-04 00:00:00g}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   test_frame1r;   r   rh   r>   r>   r?   _load_test1_dataF  s    zPandasSQLTest._load_test1_datac              	   C   sV   t ddddgddddgd	d
ddgddddgddddgd}t|d |d< || _d S )N            asdZgsqZyltZjkl皙?g@g@g333333@FTz
1990-11-22z
1991-10-26z
1993-11-26z
1995-12-12)r   r   r   r   Er   )r   r   test_frame2r;   dfr>   r>   r?   _load_test2_datai  s    




	zPandasSQLTest._load_test2_datac                 C   s(   dddg}ddddg}t ||d| _d S )	Nr   r   r   )r   igZ)r   ir   )r   i N  r   )r   i͏r   r   )r   test_frame3r   r>   r>   r?   _load_test3_dataw  s    
zPandasSQLTest._load_test3_datac                    s*   dd  t | fdddD | _d S )Nc                 S   sd   dddddddddd	dddddddddd	ddddddddddd
d}||  }||   |S )Nstrint64floatr   boolr+   r   )rc   astype)rq   r   Zflavor_dtypesr   r>   r>   r?   _filter_to_flavor  sD    %z>PandasSQLTest._load_types_test_data.<locals>._filter_to_flavorc                    s   i | ]}| |qS r>   r>   )rN   rq   r   r   r>   r?   
<dictcomp>  s    z7PandasSQLTest._load_types_test_data.<locals>.<dictcomp>r   )r   
types_test)r;   rh   r>   r   r?   _load_types_test_data  s
    )z#PandasSQLTest._load_types_test_datac                    s   |  d |  td | j  td | j }dddddd	d
dd
dd
dddddd	d
dd d d
g}|D ]* |  |d  fdd|d D  qh| | d S )Ntypes_test_datar/   r0   firstr   z2000-01-01 00:00:00-08:00i wi2g333333$@r   Fr+   r   z2000-06-01 00:00:00-07:00i 'Pi\2r)   c                    s   g | ]} | qS r>   r>   )rN   fielddr>   r?   rP     s     z/PandasSQLTest._load_raw_sql.<locals>.<listcomp>r*   )r9   rf   rG   rp   rq   r   )r;   rx   rh   r>   r   r?   _load_raw_sql  s@    
 zPandasSQLTest._load_raw_sqlc                 C   s    |   d|  }|d S )Nz SELECT count(*) AS count_1 FROM r   )rf   rG   fetchone)r;   rJ   resultr>   r>   r?   _count_rows  s
    zPandasSQLTest._count_rowsc                 C   s   | j d}| | d S NSELECT * FROM iris)	pandasSQL
read_queryr   r;   r   r>   r>   r?   _read_sql_iris  s    zPandasSQLTest._read_sql_irisc                 C   s4   t d | j }ddg}| jj||d}| | d S )Nr1   r   r|   rj   rp   rq   r   r   r   r;   r)   rk   r   r>   r>   r?   _read_sql_iris_parameter  s    z&PandasSQLTest._read_sql_iris_parameterc                 C   s6   t d | j }ddd}| jj||d}| | d S )Nr2   r   r|   )namelengthrj   r   r   r>   r>   r?   _read_sql_iris_named_parameter  s    
z,PandasSQLTest._read_sql_iris_named_parameterc                 C   s,   t d | j }| jj|d d}| | d S )Nr3   rj   r   )r;   r)   r   r>   r>   r?   (_read_sql_iris_no_parameter_with_percent  s    z6PandasSQLTest._read_sql_iris_no_parameter_with_percentNc                 C   s\   |  d | jj| jd|d | jds.tt| j}| d}||ksNt|  d d S )Nr   r<   r9   r   to_sqlr   	has_tabler   lenr   )r;   r<   num_entriesnum_rowsr>   r>   r?   _to_sql  s    


zPandasSQLTest._to_sqlc                 C   s(   |  d | j| jjd d d d S )Nr   r   )r9   r   r   r   r   rY   r>   r>   r?   _to_sql_empty  s    
zPandasSQLTest._to_sql_emptyc              	   C   sn   |  d | jj| jddd | jds.td}tjt|d | jj| jddd W 5 Q R X |  d d S )Nr   fail	if_existsz"Table 'test_frame1' already existsmatch)	r9   r   r   r   r   r   pytestraises
ValueErrorr;   msgr>   r>   r?   _to_sql_fail  s    
zPandasSQLTest._to_sql_failc                 C   sp   |  d | jj| jddd | jj| jddd | jdsBtt| j}| d}||ksbt|  d d S )Nr   r   r   replacer   r;   r   r   r>   r>   r?   _to_sql_replace  s    


zPandasSQLTest._to_sql_replacec                 C   st   |  d | jj| jddd | jj| jddd | jdsBtdt| j }| d}||ksft|  d d S )Nr   r   r   append   r   r   r>   r>   r?   _to_sql_append   s    

zPandasSQLTest._to_sql_appendc                    sz   g   fdd}|  d | jj| jd|d | jds>t dgksLtt| j}| d}||kslt|  d d S )Nc                    s2    d  fdd|D }|| j | d S )Nr   c                    s   g | ]}t t |qS r>   )dictzip)rN   ry   rc   r>   r?   rP   5  s     zIPandasSQLTest._to_sql_method_callable.<locals>.sample.<locals>.<listcomp>)r   rG   rO   insert)Zpd_tabler6   rc   	data_iterrh   checkr   r?   sample3  s    
z5PandasSQLTest._to_sql_method_callable.<locals>.sampler   r   r   r   )r;   r   r   r   r>   r   r?   _to_sql_method_callable0  s    


z%PandasSQLTest._to_sql_method_callableautoc                 K   sd   |  d | jj| jdfd|i| | jds6tt| j}| d}||ksVt|  d dS )z `to_sql` with the `engine` paramr   engineNr   )r;   r   Zengine_kwargsr   r   r>   r>   r?   _to_sql_with_sql_engineD  s    
 

z%PandasSQLTest._to_sql_with_sql_enginec                 C   sN   |  d | j| jd | jd}|jddd d |j_t	|| j d S )Ntest_frame_roundtrip"SELECT * FROM test_frame_roundtriplevel_0TZinplace)
r9   r   r   r   r   	set_indexr   r   r   assert_frame_equalr;   r   r>   r>   r?   
_roundtripU  s    
zPandasSQLTest._roundtripc                 C   s.   | j d}| }t|dddddg d S )Nr   r|   r}   r~   r   r   )r   rG   r   r   r   r;   Ziris_resultsry   r>   r>   r?   _execute_sqla  s    zPandasSQLTest._execute_sqlc                 C   sH   t jddgdddgdgd}| j|d | d}|dggksDtd S )Nr    @Zline1r   g      ?Zline2r   r   r   r   r   Ztest_to_sql_saves_index)r   from_recordsr   r   _get_index_columnsr   )r;   r   ix_colsr>   r>   r?   _to_sql_save_indexg  s      
z PandasSQLTest._to_sql_save_indexc              	   C   s   | j  }|d W 5 Q R X G dd dt}d}z,| j  }|| |dW 5 Q R X W n |k
rt   Y nX | j d}t|dkst| j  }|| W 5 Q R X | j d}t|dkstd S )	Nz'CREATE TABLE test_trans (A INT, B TEXT)c                   @   s   e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionNrA   rB   rC   r>   r>   r>   r?   DummyExceptions  s   r  z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')errorzSELECT * FROM test_transr   r   )r   Zrun_transactionrG   	Exceptionr   r   r   )r;   transr  Zins_sqlresres2r>   r>   r?   _transaction_testo  s     
zPandasSQLTest._transaction_test)N)r   )rA   rB   rC   __doc__rf   r   fixturerz   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?   re     s6   
#0&

re   c                
   @   s  e Zd ZU dZdZe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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/d0 Zd1d2 Z d3d4 Z!d5d6 Z"ejd7d8d9d:d;d<d=gd>d? Z#d@dA Z$dBdC Z%ejdDdEe&e'e&e'dFgdGdH Z(dIdJ Z)dKdL Z*dMdN Z+dOdP Z,dQdR Z-dSdT Z.dUdV Z/dWdX Z0dYdZ Z1dES )[_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   modec                 C   s   |   | _d S rX   connectr6   rY   r>   r>   r?   ro     s    z_TestSQLApi.setup_connectTautousec                 C   s   |    d S rX   load_test_data_and_sqlr;   rz   r>   r>   r?   setup_method  s    z_TestSQLApi.setup_methodc                 C   s,   |    |   |   |   |   d S rX   )r{   r   r   r   r   rY   r>   r>   r?   r    s
    z"_TestSQLApi.load_test_data_and_sqlc                 C   s   t d| j}| | d S r   rH   r   r6   r   r   r>   r>   r?   test_read_sql_iris  s    z_TestSQLApi.test_read_sql_irisc                 C   s   t d| j}| | d S )NzSELECT * FROM iris_viewr  r   r>   r>   r?   test_read_sql_view  s    z_TestSQLApi.test_read_sql_viewc                 C   s8   d}t j|| jdd}t || j}tt|| d S )Nz/SELECT * FROM iris_view WHERE SepalLength < 0.0   	chunksize)rH   r   r6   r   r   r   )r;   r)   Z
with_batchZwithout_batchr>   r>   r?   &test_read_sql_with_chunksize_no_result  s    z2_TestSQLApi.test_read_sql_with_chunksize_no_resultc                 C   s(   t | jd| j t d| js$td S )Nr   )rH   r   r   r6   r   r   rY   r>   r>   r?   test_to_sql  s    z_TestSQLApi.test_to_sqlc              	   C   s`   t j| jd| jdd t d| js(td}tjt|d t j| jd| jdd W 5 Q R X d S )Nr   r   r   z"Table 'test_frame2' already existsr   )	rH   r   r   r6   r   r   r   r   r   r   r>   r>   r?   test_to_sql_fail  s
    z_TestSQLApi.test_to_sql_failc                 C   sb   t j| jd| jdd t j| jd| jdd t d| js>tt| j}| d}||ks^td S )Nr   r   r   r   rH   r   r   r6   r   r   r   r   r   r>   r>   r?   test_to_sql_replace  s    

z_TestSQLApi.test_to_sql_replacec                 C   sf   t j| jd| jdd t j| jd| jdd t d| js>tdt| j }| d}||ksbtd S )Ntest_frame4r   r   r   r   r   r   r>   r>   r?   test_to_sql_append  s    
z_TestSQLApi.test_to_sql_appendc                 C   s6   t j| jd| jdd t d| j}t| j| d S )NZtest_frame5Fr   zSELECT * FROM test_frame5)rH   r   r   r6   read_sqlr   r   r   r>   r>   r?   test_to_sql_type_mapping  s    z$_TestSQLApi.test_to_sql_type_mappingc                 C   sL   t tjddddd}tj|d| jdd td	| j}t|	 | d S )
Nr  r   dtypeZseriesr   Ztest_seriesFr$  zSELECT * FROM test_series)
r   r   arangerH   r   r6   r   r   r   to_frame)r;   ss2r>   r>   r?   test_to_sql_series  s    z_TestSQLApi.test_to_sql_seriesc                 C   sb   t j| jd| jd t jd| jd}| jj|_|jddd |jt d |j_	t
|| j d S )Nr   conr   r   Tr   )rH   r   r   r6   r   r   r   r   intr   r   r   r   r>   r>   r?   test_roundtrip  s    
z_TestSQLApi.test_roundtripc                 C   s:   t j| jd| jddd t jd| jd}t|| j d S )Nr   Fr   )r0  r   r  r   r/  )rH   r   r   r6   r   r   r   r   r>   r>   r?   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   r/  r|   r}   r~   r   r   )rH   rG   r6   r   r   r   r   r>   r>   r?   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 types_test_datar!   parse_datesi  r   r   r   r   %Y-%m-%d %H:%M:%Sr"   i        i  r,  r#   %Y%m%dz
2010-10-10z
2010-12-12)rH   r   r6   r   r!   r(  r   r   
datetime64r   tolistr   r"   r#   r   r>   r>   r?   test_date_parsing  sh      

    z_TestSQLApi.test_date_parsingr  ignoreraisecoercezread_sql, text, moder5  )r^   fallbackr   r^   c                 C   sH   | j |krD| j| j ddi}||| jdd|iid}t|| d S )Nr!   zdatetime64[ns]errors)r0  r7  )r  r   rq   r   r6   r   r   )r;   r%  textr  r  expectedr   r>   r>   r?   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 )Nr5  r!   r"   )	index_colr7  )rH   r   r6   r   r   r(  r   r   r<  r   r"   r   r>   r>   r?   test_date_and_index]  s    z_TestSQLApi.test_date_and_indexc              	   C   sh   t tddgdd }tt |d| j W 5 Q R X t	d| j}t
|d |d d d S )Nz00:00:01z00:00:03foor)  test_timedeltazSELECT * FROM test_timedeltar   )r   r   r+  r   assert_produces_warningUserWarningr   r6   rH   r   assert_series_equalviewr;   r   r   r>   r>   r?   rJ  j  s
    z_TestSQLApi.test_timedeltac              	   C   s@   t dddgi}d}tjt|d |d| j W 5 Q R X d S )Nay      ?      ?y               @zComplex datatypes not supportedr   Ztest_complex)r   r   r   r   r   r6   )r;   r   r   r>   r>   r?   test_complex_raisess  s    z_TestSQLApi.test_complex_raiseszindex_name,index_label,expected)NNr   )Nother_labelrR  )
index_nameNrS  )rS  rR  rR  )r   N0)Nr   rT  c                 C   sT   t dtdi}||j_d}tj|d| j|d t|| j}|jd |ksPt	d S )Ncol1r   SELECT * FROM test_index_labeltest_index_labelindex_labelr   )
r   ranger   r   rH   r   r6   r   r   r   )r;   rS  rY  rE  
temp_framer)   framer>   r>   r?   test_to_sql_index_labely  s    z#_TestSQLApi.test_to_sql_index_labelc              	   C   sr  t dtditddgd}t|d| j td| j}|jd d	ksNt	|jd
 dks`t	tj|d| jdddgd td| j}|jd d 
 ddgkst	ddg|j_tj|d| jdd td| j}|jd d 
 ddgkst	tj|d| jdddgd td| j}|jd d 
 ddgks:t	d}tjt|d tj|d| jddd W 5 Q R X d S )NrU  r   )ZA0A1)B0ZB1r$  rW  rV  r   r   r   Zlevel_1r   r   r   )r   rY  r   r   r   r   zALength of 'index_label' should match number of levels, which is 2r   )r   rZ  r
   Zfrom_productrH   r   r6   r   r   r   r=  r   namesr   r   r   )r;   r[  r\  r   r>   r>   r?   "test_to_sql_index_label_multiindex  sN    
 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 )Nr   r   r   r   r   r   test_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtriprG  TZcheck_index_type)r   r   r   r6   rH   r   r   r   rO  r>   r>   r?   rb    s      z%_TestSQLApi.test_multiindex_roundtripr(  Nr   r   c                 C   sV   t ddgddggddgd}|d| j ||}tjd	| j|d
}t|| d S )N333333?g333333@ffffff@g333333@r   r   r   test_dtype_argumentz$SELECT A, B FROM test_dtype_argumentr0  r(  )r   r   r6   r   rH   r   r   r   )r;   r(  r   rE  r   r>   r>   r?   rh    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   rH   r   r6   r   r>   r>   r?   test_integer_col_names  s    z"_TestSQLApi.test_integer_col_namesc                 C   s$   t j| jd| jd}d|ks td S )Ntestr/  CREATErH   
get_schemar   r6   r   r;   
create_sqlr>   r>   r?   test_get_schema  s    z_TestSQLApi.test_get_schemac                 C   s&   t j| jd| jdd}d|ks"td S )Nrk  pypi)r0  r_   zCREATE TABLE pypi.rm  ro  r>   r>   r?   test_get_schema_with_schema  s       z'_TestSQLApi.test_get_schema_with_schemac                 C   s^   t ddgddgd}| jdkr&tjnd}tj|d| jd	|id
}d|ksNtd|ksZtd S )Nr   rf  r   皙@rP  br^   INTEGERrk  rv  ri  rl  )r   r  r^   IntegerrH   rn  r6   r   )r;   Zfloat_framer(  rp  r>   r>   r?   test_get_schema_dtypes  s       z"_TestSQLApi.test_get_schema_dtypesc                 C   sh   t ddgddgd}tj|d| jdd}d	}||ks:ttj| jd| jd
dgd}d}||ksdtd S )Nr   rf  r   rt  )Col1ZCol2rk  rz  )r0  rc   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")r   r   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   rH   rn  r6   r   r   )r;   r\  rp  Zconstraint_sentencer>   r>   r?   test_get_schema_keys  s       z _TestSQLApi.test_get_schema_keysc                 C   s  t tjddtdd}|jd| jdd td| j}t  }d	}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   r  Zabcder   Ztest_chunksizeFr$  zselect * from test_chunksizer   r   r  TZignore_indexr   r^   )r   r   randomZrandnlistr   r6   rH   r   r   r   r   r   r   r  r   )r;   r   res1r	  iZsizeschunkres3r>   r>   r?   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   r6   rH   r   r   r   )r;   r   df2r  r>   r>   r?   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      érv  r   Ztest_unicodeFr$  r   r   r6   r   r>   r>   r?   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   rg  re  zd1187b08-4943-4c8d-a7f6Fr$  z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   r   nanr   r6   rH   r   r   r   r;   r   r  r>   r>   r?   test_escaped_table_nameC  s    z#_TestSQLApi.test_escaped_table_name)2rA   rB   rC   r  rq   r   __annotations__ro   r   r  r  r  r  r  r  r  r  r!  r#  r&  r.  r2  r3  r4  r>  markparametrizerH   r%  r   r   rF  rH  rJ  rQ  r]  ra  rb  r1  r   rh  rj  rq  rs  ry  r{  r  r  r  r  r>   r>   r>   r?   r    s   


7


	
2	
	"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d/d0 Zd1d2 Zd3d4 Zd5d6 Zd7S )8
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^   create_enginerY   r>   r>   r?   r  [  s    zTestSQLApi.connectc                 C   sB   t | jd| j ddg}t jd| j|d}|j |ks>td S )N
test_framer   r   r   )rH   r   r   r6   r   r   r=  r   )r;   colsr   r>   r>   r?   test_read_table_columns^  s    z"TestSQLApi.test_read_table_columnsc                 C   s   t | jd| j t jd| jdd}|jjdgks6t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   rc  r   r   r   r   )rG  r   )
rH   r   r   r6   r   r   r`  r   r   r=  r   r>   r>   r?   test_read_table_index_colf  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   rl   )rH   r   r6   r%  r   r   r   )r;   iris_frame1iris_frame2r>   r>   r?   test_read_sql_delegatev  s    z!TestSQLApi.test_read_sql_delegatec              	   C   sr   d}| j | d}| j | tjdd<}td td| j  td| j  t|dksdt	W 5 Q R X d S )	Nz,CREATE TABLE invalid (x INTEGER, y UNKNOWN);z0CREATE TABLE other_table (x INTEGER, y INTEGER);TrecordalwaysZother_tablezSELECT * FROM other_tabler   )
r6   rG   warningscatch_warningssimplefilterrH   r   r   r   r   )r;   Zqrywr>   r>   r?   test_not_reflect_all_tables  s    
z&TestSQLApi.test_not_reflect_all_tablesc              	   C   sF   t jdd0}t d | jd| j t|dks8tW 5 Q R X d S )NTr  r  ZCaseSensitiver   )r  r  r  r   r   r6   r   r   )r;   r  r>   r>   r?   (test_warning_case_insensitive_table_name  s    
z3TestSQLApi.test_warning_case_insensitive_table_namec                 C   s6   ddl m} |j| j}|d}dd |D }|S )Nr   )
reflectionZtest_index_savedc                 S   s   g | ]}|d  qS Zcolumn_namesr>   rN   r  r>   r>   r?   rP     s     z1TestSQLApi._get_index_columns.<locals>.<listcomp>)Zsqlalchemy.enginer  Z	InspectorZfrom_enginer6   get_indexes)r;   tbl_namer  inspixsr>   r>   r?   r     s
    
zTestSQLApi._get_index_columnsc                 C   sR   t dtddgddi}t| j}tjd||d}t|jjd j	t
jsNtd S )Nr   201412120154201412110254Tutc	test_typer\  )r   r   rH   r[   r6   SQLTable
isinstancerO   rW   r   sqltypes	TIMESTAMPr   )r;   r   dbrO   r>   r>   r?   test_sqlalchemy_type_mapping  s    z'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)Zint8SMALLINT)ZInt8r  )Zuint8r  )ZUInt8r  )Zint16r  )ZInt16r  )Zuint16rw  )ZUInt16rw  )int32rw  )ZInt32rw  )Zuint32BIGINT)ZUInt32r  )r   r  )ZInt64r  r   r  rw  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   rP  r   r(  r  r  )r   rH   r[   r6   r  r   rO   rW   rP  r   r   )r;   integerrE  r   r  rO   r   r>   r>   r?   test_sqlalchemy_integer_mapping  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   rP  r  z1Unsigned 64 bit integer datatype is not supportedr   r  r  )r   rH   r[   r6   r   r   r   r  )r;   r  r   r  r>   r>   r?   (test_sqlalchemy_integer_overload_mapping  s     z3TestSQLApi.test_sqlalchemy_integer_overload_mappingc           
   	   C   s   | j }t L}d| }d}|j||ddd t||}t||}d}t||}W 5 Q R X t|| t|| t|| zdd l	}	t
d W n tk
r   Y nX d	}t
jtd
d td| W 5 Q R X d S )Nz
sqlite:///rl   r   F)r   r   r   r   zpg8000 is installedz)postgresql+pg8000://user:pass@host/dbnamepg8000r   zselect * from table)r   r   ensure_cleanr   rH   r%  r   r   r   r  r   skipImportErrorr   )
r;   r   r   Zdb_urirO   r   r   r)   r"  r  r>   r>   r?   test_database_uri_string  s(    
z#TestSQLApi.test_database_uri_stringc                 C   sX   t }| }|d||d|j|d|j|d|j|d|j|d|j}|S )Nrl   SepalLengthZ
SepalWidthZPetalLengthZ
PetalWidthName)r^   r`   ZTableColumnREALTEXT)r;   sametadatarl   r>   r>   r?   _make_iris_table_metadata  s    
z$TestSQLApi._make_iris_table_metadatac                 C   s>   t d}tj|| jddid}t|d }|dhks:td S )Nz#select * from iris where name=:namer   zIris-versicolorrj   r  )r^   rD  rH   r%  r6   setr   )r;   Z	name_textiris_df	all_namesr>   r>   r?   test_query_by_text_obj  s    
z!TestSQLApi.test_query_by_text_objc                 C   s\   |   }t|g|jjtdk}tj|| j	ddid}t
|d }|dhksXtd S )Nr   r   rj   r  )r  r^   selectwhererW   r  Z	bindparamrH   r%  r6   r  r   )r;   rl   Zname_selectr  r  r>   r>   r?   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   r  )r   z%_variationZtest_column_percentageFr$  r   r   r6   rH   r   r   r   r  r>   r>   r?   test_column_with_percentage  s    z&TestSQLApi.test_column_with_percentageN)rA   rB   rC   r  rq   r  r  r  r  r  r  r  r   r  r   r  r  r1  r   r(  r   r  r  r  r  r  r  r  r>   r>   r>   r?   r  M  sL   	
	
	!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 rX   )superr  r6   r  beginZ_EngineToConnMixin__txrH   r[   r   Z_EngineToConnMixin__enginerollbackrS   )r;   rz   r   r6   	__class__r>   r?   r  (  s    



z_EngineToConnMixin.setup_method)rA   rB   rC   r  r   r  r  __classcell__r>   r>   r  r?   r  #  s   
r  c                   @   s   e Zd ZdS )TestSQLApiConnNr  r>   r>   r>   r?   r  :  s   r  c                   @   sj   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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   rB  :memory:c                 C   s
   t |S rX   sqlite3r  )r;   Zdatabaser>   r>   r?   r  I  s    zTestSQLiteFallbackApi.connectc              	   C   sj   t  J}| |}tj| jd|dd |  | |}td|}|  W 5 Q R X t | j| d S )Ntest_frame3_legacyFr$  z!SELECT * FROM test_frame3_legacy;)	r   r  r  rH   r   r   rS   r   r   )r;   r   r6   r   r>   r>   r?   test_sql_open_closeL  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   r  rH   r%  )r;   r6   r   r>   r>   r?   test_con_string_import_error\  s    z2TestSQLiteFallbackApi.test_con_string_import_errorc              	   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   rl   )	rH   r   r6   r%  r   r   r   r   DatabaseError)r;   r  r  r   r>   r>   r?   r  c  s    z,TestSQLiteFallbackApi.test_read_sql_delegatec              	   C   sH   t ddgddggddgd}t  tj|d| jd	d
 W 5 Q R X d S )Nr   r   r   r   rP  zb r   r  Fr$  )r   r   rK  rH   r   r6   r   r>   r>   r?   test_safe_names_warningl  s    
z-TestSQLiteFallbackApi.test_safe_names_warningc                 C   s   t | jd}d|kstd S )Nrk  rl  )rH   rn  r   r   ro  r>   r>   r?   test_get_schema2s  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colr>   r>   r?   _get_sqlite_column_typex  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  r  r  )	r   r   rH   SQLiteDatabaser6   ZSQLiteTableZ
sql_schemar  r   )r;   r   r  rO   r_   r>   r>   r?   test_sqlite_type_mapping  s    z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r  )rA   rB   rC   r  rq   r  r  r  r   r  skipifSQLALCHEMY_INSTALLEDr  r  r  r  r  r  r>   r>   r>   r?   r  ?  s   

	r  c                   @   s,  e Zd ZU dZe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d/d0 Zd1d2 Zd3d4 Zd5d6 Z d7d8 Z!d9d: Z"d;d< Z#d=d> Z$d?d@ Z%dAdB Z&dCdD Z'dEdF Z(dGdH Z)dIdJ Z*dKdL Z+dMdN Z,dOdP Z-dQdR Z.dSdT Z/dUdV Z0dWdX Z1dYdZ Z2d[d\ Z3d]d^ Z4d_d` Z5dadb Z6dcdd Z7dedf Z8ej9:dgdhe;j<gidhe;j< gie;j< gdigdjgdkdl Z=dmdn Z>dodp Z?dqdr Z@dsdt ZAdudv ZBdwdx ZCdyS )z_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.

    rq   Tclassr  Zscopec                 C   s*   |    |   |   }| _|  d S rX   )setup_importsetup_driverr  r6   )clsr6   r>   r>   r?   setup_class  s    z_TestSQLAlchemy.setup_classc                 C   s   |    |   d S rX   )r   r   rY   r>   r>   r?   r    s    z&_TestSQLAlchemy.load_test_data_and_sqlr  c                 C   s   |    d S rX   r  r  r>   r>   r?   r    s    z_TestSQLAlchemy.setup_methodc                 C   s   t std d S )Nr  )r  r   r  r  r>   r>   r?   r    s    z_TestSQLAlchemy.setup_importc                 C   s
   t  d S rX   NotImplementedErrorr  r>   r>   r?   r    s    z_TestSQLAlchemy.setup_driverc                 C   s
   t  d S rX   r  r  r>   r>   r?   r    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)r  r6   rH   r[   r   r^   excZOperationalErrorr   r  rq   rY   r>   r>   r?   ro     s    
z_TestSQLAlchemy.setup_connectc                 C   s   |    d S rX   r   rY   r>   r>   r?   test_read_sql  s    z_TestSQLAlchemy.test_read_sqlc                 C   s   |    d S rX   r   rY   r>   r>   r?   test_read_sql_parameter  s    z'_TestSQLAlchemy.test_read_sql_parameterc                 C   s   |    d S rX   r   rY   r>   r>   r?   test_read_sql_named_parameter  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc                 C   s   |    d S rX   r   rY   r>   r>   r?   r    s    z_TestSQLAlchemy.test_to_sqlc                 C   s   |    d S rX   r   rY   r>   r>   r?   test_to_sql_empty  s    z!_TestSQLAlchemy.test_to_sql_emptyc                 C   s   |    d S rX   r   rY   r>   r>   r?   r    s    z _TestSQLAlchemy.test_to_sql_failc                 C   s   |    d S rX   r   rY   r>   r>   r?   r!    s    z#_TestSQLAlchemy.test_to_sql_replacec                 C   s   |    d S rX   r   rY   r>   r>   r?   r#    s    z"_TestSQLAlchemy.test_to_sql_appendc                 C   s   | j dd d S NZmultir   r  rY   r>   r>   r?   test_to_sql_method_multi  s    z(_TestSQLAlchemy.test_to_sql_method_multic                 C   s   |    d S rX   )r   rY   r>   r>   r?   test_to_sql_method_callable  s    z+_TestSQLAlchemy.test_to_sql_method_callablec                 C   sl   |   }tddddgddddgd}t|}||d t rZt|}|dshtn|dshtd S N      ?       @      @      @ZoneZtwor[  )	r  r   rH   r[   r   r   r   r   r   r;   Z	temp_connr[  r   r  r>   r>   r?   test_create_table  s    
z!_TestSQLAlchemy.test_create_tablec                 C   s   |   }tddddgddddgd}t|}||d t rZt|}|dshtn|dsht|	d t r|drtn|drtd S r  )
r  r   rH   r[   r   r   r   r   r   r9   r  r>   r>   r?   test_drop_table  s    

z_TestSQLAlchemy.test_drop_tablec                 C   s   |    d S rX   r   rY   r>   r>   r?   r2    s    z_TestSQLAlchemy.test_roundtripc                 C   s   |    d S rX   r   rY   r>   r>   r?   r4    s    z _TestSQLAlchemy.test_execute_sqlc                 C   s   t jd| jd}| | d S )Nrl   r/  )rH   r   r6   r   r   r>   r>   r?   test_read_table
  s    z_TestSQLAlchemy.test_read_tablec                 C   s.   t jd| jddgd}t|jjddg d S )Nrl   r  )r0  r   )rH   r   r6   r   r   r   r   r   r>   r>   r?   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_existr/  )r   r   r   rH   r   r6   r   r>   r>   r?   test_read_table_absent_raises  s    z-_TestSQLAlchemy.test_read_table_absent_raisesc                 C   s~   t d| j}t|jjjtjs$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 Nr   )rH   r   r6   r   r$   r(  r   r   r   r   r%   r  r&   Zbool_r'   r(   objectr   r>   r>   r?   test_default_type_conversion  s    z,_TestSQLAlchemy.test_default_type_conversionc                 C   s@   t ddgid}|jd| jdd td| j}t|| d S )Ni64l            )rh   test_bigintFr$  r  rO  r>   r>   r?   r"  %  s    z_TestSQLAlchemy.test_bigintc                 C   s(   t d| j}t|jjjtjs$t	d S r  
rH   r   r6   r   r!   r(  r   r   r<  r   r   r>   r>   r?   test_default_date_load-  s    z&_TestSQLAlchemy.test_default_date_loadc                 C   s&  dd }t d| j}t|ds(td |j}t|js<tt d| jdgd}t|dsbtd |j}t|jsvt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st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)tzr)  z&DateCol loaded with incorrect type -> )r   r(  r   r   r   r   dtr&  r   r   r   rM  )r  Zexpected_datarE  r>   r>   r?   r   9  s    




z:_TestSQLAlchemy.test_datetime_with_timezone.<locals>.checkzselect * from types_test_datar,   z&no column with datetime with time zoner6  r%  r   r  Tr}  r   )r   r6   r7   r   r  r,   r   r(  r   r   r'  r&  r   r  rH   r   r   rM  )r;   r   r   r  rE  r>   r>   r?   test_datetime_with_timezone4  s@    "

  


z+_TestSQLAlchemy.test_datetime_with_timezonec                 C   s   t dtddddi}|jd| jdd | jd	krH|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   z
US/Pacific)periodsr&  Ztest_datetime_tzFr$  r   r%  zSELECT * FROM test_datetime_tzr   r   r   )r   r   r   r6   rq   r'  Z
tz_convertZtz_localizerH   r   r   r   r   r  locr   r   r   )r;   rE  r   r>   r>   r?   %test_datetime_with_timezone_roundtrip  s    

z5_TestSQLAlchemy.test_datetime_with_timezone_roundtripc                 C   s\   t dtdddidgd}|jd| jdd 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   r6   rH   r   pdZNaTr   r   )r;   rh   r   rE  r>   r>   r?   test_out_of_bounds_datetime  s
    z+_TestSQLAlchemy.test_out_of_bounds_datetimec                 C   s`   t ddddd }tdtdi|d}|jd| jdd	 tjd| jdd
}tj	||dd d S )Nz
2018-01-01r  Z6H)r*  freqnumsr$  Z	foo_tableZ	info_daterX  rc  F)Zcheck_names)
r   Z
_with_freqr   rZ  r   r6   rH   r   r   r   )r;   datesrE  r   r>   r>   r?   "test_naive_datetimeindex_roundtrip  s
    z2_TestSQLAlchemy.test_naive_datetimeindex_roundtripc                 C   sJ  t d| j}| jdkrtntj}t|jj	j
|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 )
Nr   r   r!   r6  r8  formatr"   r,  unit)rH   r   r6   rq   r  r   r<  r   r!   r(  r   r   r"   )r;   r   Zexpected_typer>   r>   r?   r>    sF      
      
z!_TestSQLAlchemy.test_date_parsingc                 C   s   t tdddtdd}|d| j 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 )Nr)  r   r*  r  re  test_datetimer   r   )ZaxisSELECT * FROM test_datetimer   r+  r   )r   r   r   r*  r   r6   rH   r   Zdropr   r   r   rq   r  r,  r   r   r   rO  r>   r>   r?   r7    s    
z_TestSQLAlchemy.test_datetimec                 C   s   t tdddtdd}tj|jd< |jd| jdd	 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 )Nr)  r   r6  r  re  r   r   r7  Fr$  r8  r   r+  r   rA  )rC  )r   r   r   r*  r  r,  r   r6   rH   r   r   r   r   rq   r  r   r   r   rO  r>   r>   r?   test_datetime_NaT  s    
z!_TestSQLAlchemy.test_datetime_NaTc                 C   sd   t tdddtdddgdgd}|jd| jdd td| j}|d }t|d }t|| d S )	N  r   r   rP  r   	test_dateFr$  )r   r   r   r6   r   r   r   rM  )r;   r   r  r   rE  r>   r>   r?   test_datetime_date  s    "z"_TestSQLAlchemy.test_datetime_datec                 C   s   t tdddtdddgdgd}|jd| jdd	 td| j}t|| t }t	j|d
|dd	 t	
d|}|dd }t|| t	j|d| jdd	 | jdkrt	
d| j}|dd }t|| t	d| j}t|| d S )N	   r   r      rP  r   	test_timeFr$  Z
test_time2zSELECT * FROM test_time2c                 S   s
   |  dS Nz%H:%M:%S.%fstrftime_r>   r>   r?   <lambda>      z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>Z
test_time3r   zSELECT * FROM test_time3c                 S   s
   |  dS rA  rB  rD  r>   r>   r?   rF    rG  )r   r   r   r6   r   r   r   TestSQLiteFallbackr  rH   r   applymaprq   )r;   r   r  Zsqlite_connrefr>   r>   r?   test_datetime_time  s     "
z"_TestSQLAlchemy.test_datetime_timec                 C   s`   t dtjd}t dtjd}t||d}|jd| jdd td| j}t	j
||ddd	 d S )
Ni  r'  g        )s1r-  Ztest_read_writeFr$  T)Zcheck_dtypeZcheck_exact)r   r   r  float32r   r   r6   rH   r   r   r   )r;   rL  r-  r   r  r>   r>   r?   test_mixed_dtype_insert  s    z'_TestSQLAlchemy.test_mixed_dtype_insertc                 C   sf   t dddgdtjdgd}|jd| jdd	 td| j}t|| t	d
| j}t|| d S )Nr   r   r   r   rg  re  test_nanFr$  SELECT * FROM test_nan)
r   r   r  r   r6   rH   r   r   r   r   rO  r>   r>   r?   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 td| j}t|| |d 	d	|d< d |d< t
d
| j}t|| d S )Nr   r   r   re  rO  Fr$  r   r  rP  )r   r   r  r   r6   rH   r   r   r   r   r   rO  r>   r>   r?   test_nan_fullcolumn6  s     z#_TestSQLAlchemy.test_nan_fullcolumnc                 C   sp   t dddgddtjgd}|jd| jdd	 d |jd
< td| j}t	|| t
d| j}t	|| d S )Nr   r   r   rP  rv  re  rO  Fr$  r   r   rP  )r   r   r  r   r6   r,  rH   r   r   r   r   rO  r>   r>   r?   test_nan_stringE  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  r>   r  r>   r>   r?   rP   [  s     z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)r^   r   r6   r  )r;   r  r   r  r  r>   r>   r?   r   U  s
    

z"_TestSQLAlchemy._get_index_columnsc                 C   s   |    d S rX   r  rY   r>   r>   r?   test_to_sql_save_index^  s    z&_TestSQLAlchemy.test_to_sql_save_indexc                 C   s   |    d S rX   r
  rY   r>   r>   r?   test_transactionsa  s    z!_TestSQLAlchemy.test_transactionsc                 C   sr   |    d}tj| j|| jd}| jjd d }| | | j| t|| j}t	j
||dd | | d S )Ntest_get_schema_create_tabler/  r   Frd  )r   rH   rn  r   r6   r   r9   rG   r   r   r   )r;   r=   rp  Zblank_test_dfreturned_dfr>   r>   r?   rY  d  s    
z,_TestSQLAlchemy.test_get_schema_create_tablec           	   	   C   s~  ddg}ddg}t ||d}|d| j |jd| jdtjid tjj| jd	}|  |jd j	d j
}t|tjs|td
}tjt|d |jd| jdtid W 5 Q R X |jd| jdtdid |  |jd j	d j
}t|tjst|jdkst|jd| jtjd tjj| jd	}|  |jd j	d j
}|jd j	d j
}t|tjshtt|tjsztd S )Nr   r   g?Tg?Nr   
dtype_testdtype_test2r'  r\   z&The type of B is not a SQLAlchemy typer   r  Zdtype_test3
   single_dtype_test)r   r   r6   r^   r  r_   r`   ra   rb   r   r   r  r   r   r   r   r   Stringr   )	r;   r  rh   r   rd   Zsqltyper   ZsqltypeaZsqltypebr>   r>   r?   
test_dtypet  s0     z_TestSQLAlchemy.test_dtypec                 C   s   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 t|| j}tjj	| jd
}|
  | jdkrtj}ntj}|j| j}t|d j|stt|d jtjstt|d jtjstt|d jtjstd S )NT  r  r   r  r'  r   BoolDateIntFloatnotna_dtype_testr\   r   re  rf  rg  rh  )r   r   r   r   r6   rH   r   r^   r_   r`   ra   rq   r  rx  Booleanrb   r   r  r   r   DateTimerh  )r;   r  r   r=   rZ  rd   Zmy_typecol_dictr>   r>   r?   test_notna_dtype  s&    


z _TestSQLAlchemy.test_notna_dtypec              	   C   sR  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tjddid td	| j}t	|d j
d dt	|d j
d dksttjj| jd}|  |jd	 j}t|d jt|d jkstt|d jtjstt|d jtjstt|d jtjs6tt|d jtjsNtd S )NgYB?rM  r'  float64r  r  r   )f32f64
f64_as_f32i32r!  Ztest_dtypesFr   rq     )Z	precision)r   r   r(  rp  r      r\   ro  rr  r!  )r   r   r   r6   r^   rh  rH   r   r   roundr   r   r_   r`   ra   rb   r   r   r   r  r  rx  Z
BigInteger)r;   Vr   r  rd   rl  r>   r>   r?   test_double_precision  s4    
0 z%_TestSQLAlchemy.test_double_precisionc                    sH   dd dd   fdd}t ddd	d
gid| j || j d S )Nc                 S   s   d}t j|| dS )Nz'SELECT test_foo_data FROM test_foo_datar/  )rH   r   )
connectionr)   r>   r>   r?   rI    s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.fooc                 S   s   |j d| dd d S )Ntest_foo_datar   )r   r0  r   )r   )rx  rh   r>   r>   r?   bar  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.barc              
      s\   |   J}| 6 t r.|} || n|}| | W 5 Q R X W 5 Q R X d S rX   )r  r  r   Zrun_callable)Zconnectabler6   Zfoo_datarz  rI  r>   r?   main  s    


z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainry  r   r   r   r  )r;   r|  r>   r{  r?   test_connectable_issue_example  s
    z._TestSQLAlchemy.test_connectable_issue_exampleinputrI  rz  )rI  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 n,|jd
| jdd 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   rq   pymysqlVERSIONr   r   r  ZxfailnodeZ
add_markerr   r   r   r6   rH   r   r   Zassert_equal)r;   r~  rv   r   r  r  r   r  r>   r>   r?   test_to_sql_with_negative_npinf  s    
z/_TestSQLAlchemy.test_to_sql_with_negative_npinfc           	   	   C   s   d}t d|gi}t }G dd d|}tj| jd}| }|jJ | }|j	| |
||d |  tjt|jg|d}W 5 Q R X t|| d S )NzHello, World!spamc                   @   s>   e Zd ZdZddgiZejejddZeje	dddZ
d	S )
z7_TestSQLAlchemy.test_temporary_table.<locals>.TemporaryZ	temp_testprefixes	TEMPORARYT)Zprimary_keyr?  F)ZnullableN)rA   rB   rC   Z__tablename__Z__table_args__r^   r  rx  idZUnicoder  r>   r>   r>   r?   	Temporary
  s   
r  r\   )r  )rH   r0  )r   r   Zdeclarative_base
sa_sessionZsessionmakerr6   Ztransactionrx  Z	__table__createaddflushrH   r   r^   r  r  r   r   )	r;   Z	test_datarE  ZBaser  Sessionr   r6   r   r>   r>   r?   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   r   r   r>   r>   r?   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^   r.  option_contextr   rY   r>   r>   r?   test_options_sqlalchemy!  s    z'_TestSQLAlchemy.test_options_sqlalchemyc              	   C   s$   t dd |   W 5 Q R X d S )Nr  r   r  rY   r>   r>   r?   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   )r  r   r   r   r.  r  rY   r>   r>   r?   test_options_get_engine-  s    z'_TestSQLAlchemy.test_options_get_enginec                 C   s   d S rX   r>   rY   r>   r>   r?   "test_get_engine_auto_error_message8  s    z2_TestSQLAlchemy.test_get_engine_auto_error_messageN)DrA   rB   rC   r  r   r  r   r  r  r  r  classmethodr  r  r  ro   r  r  r  r  r
  r  r!  r#  r  r  r  r  r2  r4  r  r  r  r   r"  r$  r(  r-  r/  r3  r>  r7  r:  r=  rK  rN  rQ  rR  rT  r   rV  rX  rY  rb  rm  rw  r}  r  r  r   infr  r  r  r  r  r  r  r>   r>   r>   r?   r    s   






	N
$
	#(
r  c                   @   s   e Zd Zdd ZdS )_TestSQLAlchemyConnc                 C   s   t d d S )Nz4Nested transactions rollbacks don't work with Pandas)r   r  rY   r>   r>   r?   rX  @  s    z%_TestSQLAlchemyConn.test_transactionsN)rA   rB   rC   rX  r>   r>   r>   r?   r  ?  s   r  c                   @   sD   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S )_TestSQLiteAlchemyzL
    Test the sqlalchemy backend against an in-memory sqlite database.

    r   c                 C   s
   t dS r  r  r  r>   r>   r?   r  L  s    z_TestSQLiteAlchemy.connectc                 C   s
   d | _ d S rX   )driverr  r>   r>   r?   r  P  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  rH   r   r6   r   r$   r(  r   r   r   r   r%   r  r&   r'   r(   r   r>   r>   r?   r   U  s    z/_TestSQLiteAlchemy.test_default_type_conversionc                 C   s(   t d| j}t|jjjtjr$t	d S r  r#  r   r>   r>   r?   r$  d  s    z)_TestSQLiteAlchemy.test_default_date_loadc              	   C   sj   t dddgidd}|jd| jdd tjd	d
.}td td| j t|dks\t	W 5 Q R X d S )NrP  r   r   r   r'  Ztest_bigintwarningFr$  Tr  r  r   )
r   r   r6   r  r  r  rH   r   r   r   )r;   r   r  r>   r>   r?   test_bigint_warningj  s    
z&_TestSQLiteAlchemy.test_bigint_warningN)rA   rB   rC   r  rq   r  r  r  r   r$  r  r>   r>   r>   r?   r  D  s   

r  c                   @   s@   e Zd ZdZdZdZedd Zedd Zdd	 Z	d
d Z
dS )_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:/pandas)connect_args)r^   r  r  portr  r  r>   r>   r?   r  ~  s    z_TestMySQLAlchemy.connectc                 C   s$   t d}d| _d|jjji| _d S )Nr  Zclient_flag)r   importorskipr  	constantsZCLIENTZMULTI_STATEMENTSr  )r  r  r>   r>   r?   r    s    
z_TestMySQLAlchemy.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   r>   r>   r?   r     s    z._TestMySQLAlchemy.test_default_type_conversionc           	      C   s   dd l }tdddgdddgd}|jd	| jd
d d}| j }| }z||}|  W n  |jk
r   |	   Y nX t
d| j}t|| t
d| j}t|| d S )Nr   r   r   r   皙?r   g333333?ru  Ztest_procedureFr$  zDROP PROCEDURE IF EXISTS get_testdb;

        CREATE PROCEDURE get_testdb ()

        BEGIN
            SELECT * FROM test_procedure;
        ENDzCALL get_testdb();)r  r   r   r6   r  r  rG   rI   rR   r  rH   r   r   r   r%  )	r;   r  r   procrx  r  Zr1r  r	  r>   r>   r?   test_read_procedure  s     

z%_TestMySQLAlchemy.test_read_procedureN)rA   rB   rC   r  rq   r  r  r  r  r   r  r>   r>   r>   r?   r  u  s   

r  c                   @   s@   e Zd ZdZdZdZedd Zedd Zdd	 Z	d
d Z
dS )_TestPostgreSQLAlchemyzF
    Test the sqlalchemy backend against an PostgreSQL database.

    r   i8  c                 C   s   t d| j d| j dS )Nzpostgresql+z://postgres:postgres@localhost:r  )r^   r  r  r  r  r>   r>   r?   r    s    z_TestPostgreSQLAlchemy.connectc                 C   s   t d d| _d S )NZpsycopg2)r   r  r  r  r>   r>   r?   r    s    
z#_TestPostgreSQLAlchemy.setup_driverc              	   C   s&  t ddgddgddgd}| jd | jd	 |jd
| jdd |jd| jddd |jd| jddd 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 |jd| jdddd |jd| jdddd tjd| jdd}tt||gdd| t| jtjjr"|  }tj|dd}	tj||	d}
|
j|ddd |
j|dddd |
j|dddd tjd| jdd}|
d}t|| d S )Nr   r   r  r   rP  nrU  col2Zcol3z$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}  )rd   Ztest_schema_other2)r   r   )r   r6   rG   r   rH   r   r   r   r   r   r   r   r  r^   r   ZEnginer  r`   r[   Z
read_table)r;   r   r  r	  r  Zres4r   r  Zengine2rd   Zpdsqlr>   r>   r?   test_schema_support  sn         
z*_TestPostgreSQLAlchemy.test_schema_supportc                 C   sV   dd }t ddgddgddgd	}|jd
| jd|d td
| j}t|| d S )Nc              	   S   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 d S )
Nr   z, c                 s   s   | ]}d | d V  qdS )"Nr>   )rN   kr>   r>   r?   	<genexpr>#	  s     zl_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<genexpr>.zCOPY z (z) FROM STDIN WITH CSV)rH   file)rx  rF   r   ri   writer	writerowsseekjoinr_   r   Zcopy_expert)rO   r6   rc   r   Z
dbapi_connrK   Zs_bufr  r   rJ   Z	sql_queryr>   r>   r?   psql_insert_copy	  s    



zY_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_method.<locals>.psql_insert_copyr   r   r  r   rP  r  r  Ztest_copy_insertF)r   r<   r  )r;   r  rE  r   r>   r>   r?   (test_copy_from_callable_insertion_method	  s       z?_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_methodN)rA   rB   rC   r  rq   r  r  r  r  r  r  r>   r>   r>   r?   r    s   

Gr  c                   @   s   e Zd ZdS )TestMySQLAlchemyNr  r>   r>   r>   r?   r  4	  s   r  c                   @   s   e Zd ZdS )TestMySQLAlchemyConnNr  r>   r>   r>   r?   r  :	  s   r  c                   @   s   e Zd ZdS )TestPostgreSQLAlchemyNr  r>   r>   r>   r?   r  @	  s   r  c                   @   s   e Zd ZdS )TestPostgreSQLAlchemyConnNr  r>   r>   r>   r?   r  F	  s   r  c                   @   s   e Zd ZdS )TestSQLiteAlchemyNr  r>   r>   r>   r?   r  L	  s   r  c                   @   s   e Zd ZdS )TestSQLiteAlchemyConnNr  r>   r>   r>   r?   r  Q	  s   r  c                   @   s   e Zd ZdZdZedd Z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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/d0 Zd1d2 Zd3d4 Zd5d6 Z d7S )8rH  zG
    Test the fallback mode against an in-memory sqlite database.

    r   c                 C   s
   t dS Nr  r  r  r>   r>   r?   r  c	  s    zTestSQLiteFallback.connectc                 C   s   |   | _d S rX   r  rY   r>   r>   r?   ro   g	  s    z TestSQLiteFallback.setup_connectc                 C   s   t | j| _|   d S rX   )rH   r  r6   r   r   rY   r>   r>   r?   r  j	  s    z)TestSQLiteFallback.load_test_data_and_sqlTr  c                 C   s   |    d S rX   r  r  r>   r>   r?   r  n	  s    zTestSQLiteFallback.setup_methodc                 C   s   |    d S rX   r  rY   r>   r>   r?   r  r	  s    z TestSQLiteFallback.test_read_sqlc                 C   s   |    d S rX   r  rY   r>   r>   r?   r  u	  s    z*TestSQLiteFallback.test_read_sql_parameterc                 C   s   |    d S rX   r  rY   r>   r>   r?   r  x	  s    z0TestSQLiteFallback.test_read_sql_named_parameterc                 C   s   |    d S rX   r  rY   r>   r>   r?   r  {	  s    zTestSQLiteFallback.test_to_sqlc                 C   s   |    d S rX   r	  rY   r>   r>   r?   r
  ~	  s    z$TestSQLiteFallback.test_to_sql_emptyc                 C   s   |    d S rX   r  rY   r>   r>   r?   r  	  s    z#TestSQLiteFallback.test_to_sql_failc                 C   s   |    d S rX   r  rY   r>   r>   r?   r!  	  s    z&TestSQLiteFallback.test_to_sql_replacec                 C   s   |    d S rX   r  rY   r>   r>   r?   r#  	  s    z%TestSQLiteFallback.test_to_sql_appendc                 C   s   | j dd d S r  r  rY   r>   r>   r?   r  	  s    z+TestSQLiteFallback.test_to_sql_method_multic                 C   s\   t ddddgddddgd}| j|d | jds<t| jd | jdrXtd S )Nr  r  r  r  r  Zdrop_test_frame)r   r   r   r   r   r9   )r;   r[  r>   r>   r?   test_create_and_drop_table	  s    z-TestSQLiteFallback.test_create_and_drop_tablec                 C   s   |    d S rX   r  rY   r>   r>   r?   r2  	  s    z!TestSQLiteFallback.test_roundtripc                 C   s   |    d S rX   r  rY   r>   r>   r?   r4  	  s    z#TestSQLiteFallback.test_execute_sqlc                 C   sx   t tdddtdddgdgd}|jd| jdd td	| j}| jd
kr^t||t	 n| jdkrtt|| d S )Nr;  r   r   rP  r   r<  Fr$  zSELECT * FROM test_dater   r   )
r   r   r   r6   r   rq   r   r   r   r   r  r>   r>   r?   r=  	  s    "

z%TestSQLiteFallback.test_datetime_datec                 C   sh   t tdddtdddgdgd}|jd| jdd	 td
| j}| jdkrd|dd }t|| d S )Nr>  r   r   r?  rP  r   r@  Fr$  zSELECT * FROM test_timer   c                 S   s
   |  dS rA  rB  rD  r>   r>   r?   rF  	  rG  z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>)	r   r   r   r6   r   rq   rI  r   r   )r;   r   r  rE  r>   r>   r?   rK  	  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())rH   r   r6   r   r   r=  )r;   r  r  r  Zix_nameZix_infor>   r>   r?   r   	  s    

z%TestSQLiteFallback._get_index_columnsc                 C   s   |    d S rX   rU  rY   r>   r>   r?   rV  	  s    z)TestSQLiteFallback.test_to_sql_save_indexc                 C   s   |    d S rX   rW  rY   r>   r>   r?   rX  	  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  )r6   rG   r   )
r;   rO   r  ZrecsZcidr   ctypeZnot_nulldefaultpkr>   r>   r?   r  	  s
    
z*TestSQLiteFallback._get_sqlite_column_typec              	   C   s   | j dkrtd ddg}ddg}t||d}|d| j |jd	| jdd
id | dddksht| d	dd
ks|td}tjt	|d |jd| jdt
id W 5 Q R X |jd| jd
d | ddd
kst| ddd
kstd S )Nr   Not applicable to MySQL legacyr   r   r[  r\  r   r]  r^  STRINGr'  rw  z!B \(<class 'bool'>\) not a stringr   r  r`  )rq   r   r  r   r   r6   r  r   r   r   r   )r;   r  rh   r   r   r>   r>   r?   rb  	  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dks|t	| |ddkst	| |ddkst	| |ddkst	d S )Nr   r  Trc  r  r   r  r'  r   rd  ri  re  rw  rf  r  rg  rh  r  )
rq   r   r  r   r   r   r   r6   r  r   )r;   r  r   r=   r>   r>   r?   rm  	  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 ]h\}}||| j t|| j t ddgddggd|gd}d|d}||| j t|| j qdd S )Nr   r   r   r   rP  rv  r   z$Empty table or column name specifiedr    ztest_weird_name]ztest_weird_name[ztest_weird_name`ztest_weird_name"ztest_weird_name'z_b.test_weird_name_01-30z"_b.test_weird_name_01-30"Z99beginswithnumberZ12345r  Ztest_weird_col_namer   )	r   r   r   r   r   r6   	enumeraterH   Ztable_exists)r;   r   r   ZndxZ
weird_namer  Zc_tblr>   r>   r?   test_illegal_names	  s.    z%TestSQLiteFallback.test_illegal_namesN)!rA   rB   rC   r  rq   r  r  ro   r  r   r  r  r  r  r  r  r
  r  r!  r#  r  r  r2  r4  r=  rK  r   rV  rX  r  rb  rm  r  r>   r>   r>   r?   rH  Z	  s:   



rH  c                 C   s
   |  dS )z Returns date in YYYYMMDD format.r;  rB  )r'  r>   r>   r?   date_format
  s    r  z'{}'z{:.8f}z{:d}c                 C   s   dS )NZNULLr>   )xr>   r>   r?   rF  (
  rG  rF  z{:.10f}z'{!s}'c                 G   sJ   g }|D ]4}t |tr"t|r"d }tt| }||| q| t| S rX   )r  r   r   _formattersr   r   tuple)rH   argsZprocessed_argsarg	formatterr>   r>   r?   format_query.
  s    r  c                 C   s,   t j| ||d }|dkr dS t|S dS )z#Replace removed sql.tquery function)r0  rK   N)rH   rG   rQ   r  )r)   r0  rK   r  r>   r>   r?   tquery:
  s    r  c                   @   sx   e Zd Zejdddd Zdd Zdd Zd	d
 Zdd Z	dd Z
dd Zdd Zdd Zdd Zdd Zdd ZdS )TestXSQLiteTr  c                 c   s2   |j | _td| _d V  |j | _td| _d S r  )functionr<   r  r  r6   )r;   rv   ru   r>   r>   r?   r  E
  s
    zTestXSQLite.setup_methodc                 C   s   t  }| | d S rX   r   makeTimeDataFrame_check_roundtripr;   r\  r>   r>   r?   
test_basicP
  s    zTestXSQLite.test_basicc           	      C   s   t  }tj|jd< t|d}| j }|	| | j }d}|
 D ]"\}}t|f| }t||d qJ| j  tjd| jd}|j|_t j||dd d S )	Nr   r   rk  (INSERT INTO test VALUES (%s, %s, %s, %s)rK   select * from testr/  MbP?Zrtolr   r  r   r  r   rH   rn  r6   rF   rG   Ziterrowsr  r  rI   r%  r   r   )	r;   r\  rp  rK   rx   idxry   fmt_sqlr   r>   r>   r?   test_write_row_by_rowT
  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 )Nrk  z$INSERT INTO test VALUES (?, ?, ?, ?)r   rj   r  r   )r   r  rH   rn  r6   rF   rG   r   r  rI   r%  r   r   )r;   r\  rp  rK   rx   ry   r   r>   r>   r?   test_executei
  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  }tj|dddgd	}| }d
|kst| j	 }|
| d S )Nrk   r   r   r   r   DATETIMEr   r   zPRIMARY KEY ("A", "B")r   r  rH   rn  
splitlinesr  r   r   r6   rF   rG   )r;   r\  rp  lineslinetokensrK   r>   r>   r?   test_schemax
  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),INSERT INTO test VALUES("foo", "baz", 2.567)zExecution failed on sqlr   (INSERT INTO test VALUES("foo", "bar", 7))r6   rF   rG   rH   r   r   r  r;   rp  rK   r>   r>   r?   test_execute_fail
  s    	

zTestXSQLite.test_execute_failc              	   C   sZ   d}| j  }|| td| j  | j   ttj t	d| j d W 5 Q R X d S )Nr  r  r  r/  )
r6   rF   rG   rH   rS   r   Zexternal_error_raisedr  ProgrammingErrorr  r  r>   r>   r?   test_execute_closed_connection
  s    	


z*TestXSQLite.test_execute_closed_connectionc                 C   s   d S rX   r>   rY   r>   r>   r?   test_na_roundtrip
  s    zTestXSQLite.test_na_roundtripc                 C   s   t j|d| jdd t d| j}|j|_|}t|| dgt| |d< | }t	t
t|d }| |d< t j|d	| jdd t jd
| jdd}| }||_d|j_t|| d S )N
test_tableFr   r0  r   select * from test_tablerP  txtr_  Idxtest_table2select * from test_table2rc  )rH   r   r6   r%  r   r   r   r   r  r	   r   r*  r   )r;   r\  r   rE  frame2Znew_idxr>   r>   r?   r  
  s    zTestXSQLite._check_roundtripc                 C   s*   t dtdi}tj|| jddd d S )NFromr  testkeywordsFr0  r   r   r   r   ZonesrH   r   r6   r   r>   r>   r?   test_keyword_as_column_names
  s    z(TestXSQLite.test_keyword_as_column_namesc                 C   sl   t ddgdgd}tj|| jddd | j}tdd	 |d
D }|dksPttd
|}t	|| d S )Nr   r   Zc0r   mono_dfFr  c                 s   s   | ]}|d  V  qdS )r   Nr>   )rN   Zmy_c0r>   r>   r?   r  
  s     z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr   )
r   rH   r   r6   sumrG   r   r%  r   r   )r;   r  Zcon_xZthe_sumr   r>   r>   r?   test_onecolumn_of_integer
  s    z%TestXSQLite.test_onecolumn_of_integerc              	      s  t ddgddgd}t dddgd	d
dgd}d}d| } f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 t| jddddgks.t|| tj| j|ddd t| jdddgkshttj| j|ddd t| jddddddgkst|| d S ) Nr   r   r   r   rU  r  r   r   r  r   r   r   table_if_existsSELECT * FROM c                    s     |  dS z
            Drops tables created from individual tests
            so no dependencies arise from sequential tests
            Nr9   Ztest_table_to_droprY   r>   r?   clean_up
  s    z,TestXSQLite.test_if_exists.<locals>.clean_upz*'notvalidvalue' is not valid for if_existsr   notvalidvaluer\  r0  r   r   r   z&Table 'table_if_exists' already existsr   Fr\  r0  r   r   r   r/  r9  rS  r   r   r   r   r  r   r   	r   r   r   r   rH   r   r6   r  r   )r;   df_if_exists_1df_if_exists_2rJ   
sql_selectr  r   r>   rY   r?   test_if_exists
  s    
      zTestXSQLite.test_if_existsN)rA   rB   rC   r   r  r  r  r  r  r  r  r  r  r  r  r  r  r>   r>   r>   r?   r  C
  s   


r  zFgh-13611: there is no support for MySQL if SQLAlchemy is not installedc                   @   s   e Zd Zejdd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S ) 
TestXMySQLTr  r  c              
   C   s   t d}|jddddd z|jdd W nZ |jk
rZ } ztd|W 5 d }~X Y n. |jk
r } ztd	|W 5 d }~X Y nX d S 
Nr  	localhostrootr  pandas)hostuserpasswdr  )Zread_default_groupzCreate a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.zCannot connect to database. Create a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.)r   r  r  r  RuntimeErrorrR   )r  r  errr>   r>   r?   r  ,  s     
zTestXMySQL.setup_classr  c              
   C   s   t d}|jddddd z|jdd W nZ |jk
rZ } ztd|W 5 d }~X Y n. |jk
r } ztd	|W 5 d }~X Y nX |j| _d S r  )r   r  r  r  r!  rR   r  r<   )r;   rv   ru   r  r"  r>   r>   r?   r  @  s"    
zTestXMySQL.setup_methodc                 C   s   t  }| | d S rX   r  r  r>   r>   r?   r  V  s    zTestXMySQL.test_basicc           
      C   s   t  }tj|jd< d}t|d}| j }|	| |	| d}|
 D ]"\}}t|f| }t||d qN| j  tjd| jd}	|j|	_t j|	|dd	 d S )
Nr  DROP TABLE IF EXISTS testrk  r  r  r  r/  r  r  r  )
r;   r\  drop_sqlrp  rK   rx   r  ry   r  r   r>   r>   r?   r  Z  s    



z TestXMySQL.test_write_row_by_rowc                 C   st   t  }d|j_d}| j }|| tj|d| jd d}d}t	|| j|dd}t
|}t |d | | d S )Nr   r#  rk  )r   r0  r  r  )rH   r0  r  rG  )r   r  r   r   r6   rF   rG   rH   r   r   rt   r   )r;   r\  r$  rK   r)   r  Z	chunk_genZchunk_dfr>   r>   r?   test_chunksize_read_typeo  s     

   z#TestXMySQL.test_chunksize_read_typec              	   C   s   t  }d}t|d}| j }t  tdd |	| W 5 Q R X |	| d}|j
d j }tj	|| jt|d | j  td| j}|jd d	 |_t ||d d	  d S )
Nr#  rk  r?  Unknown table.*r  r   rj   r  r   )r   r  rH   rn  r6   rF   r  r  filterwarningsrG   r   r   r=  r  rI   r%  r   r   )r;   r\  r$  rp  rK   rx   ry   r   r>   r>   r?   r  ~  s    



zTestXMySQL.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  }d}tj|ddd	gd
}| }d|kst| j	 }|
| |
| d S )Nrk  r  r   r   r   r   r  r#  r   r   zPRIMARY KEY (`A`, `B`)r  )r;   r\  rp  r  r  r  r$  rK   r>   r>   r?   r    s    


zTestXMySQL.test_schemac              	   C   sn   d}d}| j  }|| || td| j  td| j  tjtdd td| j  W 5 Q R X d S )Nr#  
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a(5), b(5))
        );
        r  r  <insert message here>r   r  )r6   rF   rG   rH   r   r   r  )r;   r$  rp  rK   r>   r>   r?   r    s    	


zTestXMySQL.test_execute_failc              	   C   sv   d}d}| j  }|| || td| j  | j   tjtdd td| j d W 5 Q R X | 	|| d S )Nr#  r(  r  r)  r   r  r/  )
r6   rF   rG   rH   rS   r   r   r  r  r  )r;   rv   ru   r$  rp  rK   r>   r>   r?   r    s    	



z)TestXMySQL.test_execute_closed_connectionc                 C   s   d S rX   r>   rY   r>   r>   r?   r    s    zTestXMySQL.test_na_roundtripc              	   C   s>  d}| j  }t  tdd || W 5 Q R X tj|d| j dd td| j }|j	|_	|j	j
|j	_
|}t|| dgt| |d	< | }ttt|d
 }||d< d}| j  }t  tdd || W 5 Q R X tj|d| j dd tjd| j dd}| }||_	|j	j|j	_t|| d S )NzDROP TABLE IF EXISTS test_tabler?  r&  r  Fr  r  rP  r  r_  r  z DROP TABLE IF EXISTS test_table2r  r  rc  )r6   rF   r  r  r'  rG   rH   r   r%  r   r   r   r   r   r  r	   r   r*  r`  )r;   r\  r$  rK   r   rE  r  r   r>   r>   r?   r    s4    



zTestXMySQL._check_roundtripc                 C   s,   t dtdi}tj|| jdddd d S )Nr  r  r   r   F)r0  r   r   r   r  r   r>   r>   r?   r    s        z'TestXMySQL.test_keyword_as_column_namesc              	      s  t ddgddgd}t dddgd	d
dgd}d}d| } fdd}tjtdd tj| j|dd W 5 Q R X || tj| j|ddd tjtd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 t| jddddgks(t|| tj| j|ddd t| jdddgksbttj| j|ddd t| jddddddgkst|| d S )Nr   r   r   r   r  r   r   r  r   r   r   r  r	  c                    s     |  dS r
  r  r  rY   r>   r?   r    s    z+TestXMySQL.test_if_exists.<locals>.clean_upr)  r   r  r  r   Fr  r   r/  r9  rS  r  r  r  r   r  )r;   r  r  rJ   r  r  r>   rY   r?   r    s    
   zTestXMySQL.test_if_existsN)rA   rB   rC   r   r  r  r  r  r  r%  r  r  r  r  r  r  r  r  r>   r>   r>   r?   r  &  s   


"r  )NN)\r  ri   r   r   r   rg   r   r  r  Znumpyr   r   Zpandas.core.dtypes.commonr   r   r  r.  r   r	   r
   r   r   r   r   r   r   r   Zpandas._testingZ_testingr   Zpandas.io.sqlrH   r   r   r   r   r   r^   r   Zsqlalchemy.extr   Zsqlalchemy.ormr   r  Zsqlalchemy.schemaZsqlalchemy.sql.sqltypesr  r  r  rp   r5   rD   rU   rZ   re   r  r  Zsingler  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  rH  r  r4  r   Zstr_bytesr   r1  r   rn  r   r  r  r  r  r  r  r>   r>   r>   r?   <module>   s  0
&7   	  s   C UO     51Fy B         
	 c