B
    Y0db                @   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$ yNd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   s2   t | dr.x|  D ]}| | qW |   d S )Nconn)hasattr_get_all_tables
drop_table_close_conn)selfmethodtbl r:   J/var/www/html/venv/lib/python3.7/site-packages/pandas/tests/io/test_sql.pyteardown_method   s    
zMixInBase.teardown_methodN)__name__
__module____qualname__r<   r:   r:   r:   r;   r1      s   r1   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 )r2   cursorexecutesqlZ_get_valid_mysql_namecommit)r7   
table_namecurr:   r:   r;   r5      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>)r2   rA   rB   fetchall)r7   rF   r:   r:   r;   r4      s    

zMySQLMixIn._get_all_tablesc             C   s4   ddl m} y| j  W n |k
r.   Y nX d S )Nr   )Error)Zpymysql.errrK   r2   close)r7   rK   r:   r:   r;   r6      s
    zMySQLMixIn._close_connN)r=   r>   r?   r5   r4   r6   r:   r:   r:   r;   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 )r2   rB   rC   Z_get_valid_sqlite_namerD   )r7   rE   r:   r:   r;   r5      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   r:   )rG   rH   r:   r:   r;   rI     s    z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r2   rB   rJ   )r7   cr:   r:   r;   r4     s    zSQLiteMixIn._get_all_tablesc             C   s   | j   d S )N)r2   rL   )r7   r:   r:   r;   r6   	  s    zSQLiteMixIn._close_connN)r=   r>   r?   r5   r4   r6   r:   r:   r:   r;   rM      s   rM   c               @   s$   e Zd Zdd Zdd Zdd ZdS )SQLAlchemyMixInc             C   s   t | j| d S )N)rC   SQLDatabaser2   r5   )r7   rE   r:   r:   r;   r5     s    zSQLAlchemyMixIn.drop_tablec             C   s&   t jj| jd}|  |j }|S )N)bind)
sqlalchemyschemaMetaDatar2   reflecttableskeys)r7   metaZ
table_listr:   r:   r;   r4     s    
zSQLAlchemyMixIn._get_all_tablesc             C   s   | j   d S )N)r2   Zdispose)r7   r:   r:   r;   r6     s    zSQLAlchemyMixIn._close_connN)r=   r>   r?   r5   r4   r6   r:   r:   r:   r;   rO     s   rO   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 )NrB   )r3   r2   rA   )r7   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D}t	
|}t| td | j }x|D ]}|  || qrW W d Q R X d S )Nr2   irisr)   )newliner*   )paramr3   setup_connectr5   rZ   rB   SQL_STRINGSflavoropenr]   readernext)r7   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_viewr0   )r5   rZ   rB   rc   rd   )r7   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   gffffff@g      @gffffff?g?zIris-setosa)
dtypestypeiloc
issubclassnpfloatingAssertionErrortmequalContentsvalues)r7   
iris_frameZpytyperl   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)z2000-01-03 00:00:00gN\^?g9\x`|@g% TO׿g(nI)z2000-01-04 00:00:00g}C?gc^Vg~q"BĿg4?)z2000-01-05 00:00:00gGͿ?g2r¹e?g>ݦ4gёR?)z2000-01-06 00:00:00g?{mX?g$?gnk3Am?g
S?)columns)r   test_frame1)r7   r   r\   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            ZasdZgsqZyltZjklg?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)r7   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}   )z2000-01-03 00:00:00igZ)z2000-01-04 00:00:00igc^V)z2000-01-05 00:00:00i N  g2r¹e?)z2000-01-06 00:00:00i͏g$?)r   )r   test_frame3)r7   r   r\   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float)	r   r   r   r    r!   r"   r#   r$   r%   bool)
r   r   r(   r   r    r!   r"   r#   r$   r%   )r   r   r   )rW   astype)rd   r   Zflavor_dtypesro   r:   r:   r;   _filter_to_flavor  s<    z>PandasSQLTest._load_types_test_data.<locals>._filter_to_flavorc                s   i | ]} ||qS r:   r:   )rG   rd   )r   r   r:   r;   
<dictcomp>  s   z7PandasSQLTest._load_types_test_data.<locals>.<dictcomp>)r   r   r   )r   
types_test)r7   r\   r:   )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}x2|D ]* |  |d  fdd|d D  qjW | | d S )Ntypes_test_datar+   r,   firstz2000-01-03 00:00:00z2000-01-01 00:00:00-08:00i wi2g333333$@r   F)
r   r   r(   r   r    r!   r"   r#   r$   r%   z2000-01-04 00:00:00z2000-06-01 00:00:00-07:00i 'Pi\2r&   c                s   g | ]} | qS r:   r:   )rG   field)dr:   r;   rI     s    z/PandasSQLTest._load_raw_sql.<locals>.<listcomp>r'   )r5   rZ   rB   rc   rd   r   )r7   rk   r\   r:   )r   r;   _load_raw_sql  s6    


"zPandasSQLTest._load_raw_sqlc             C   s    |   d|  }|d S )Nz SELECT count(*) AS count_1 FROM r   )rZ   rB   fetchone)r7   rE   resultr:   r:   r;   _count_rows  s    zPandasSQLTest._count_rowsc             C   s   | j d}| | d S )NzSELECT * FROM iris)	pandasSQL
read_queryrz   )r7   ry   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 )Nr-   zIris-setosagffffff@)r^   )rc   rd   r   r   rz   )r7   r&   r^   ry   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 )Nr.   zIris-setosagffffff@)namelength)r^   )rc   rd   r   r   rz   )r7   r&   r^   ry   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 )Nr/   )r^   )rc   rd   r   r   rz   )r7   r&   ry   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   )r8   )r5   r   to_sqlr   	has_tableru   lenr   )r7   r8   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   )r5   r   r   r   rq   )r7   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 d Q R X |  d d S )Nr   fail)	if_existsz"Table 'test_frame1' already exists)match)	r5   r   r   r   r   ru   pytestraises
ValueError)r7   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   replace)r5   r   r   r   r   ru   r   r   )r7   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   )r5   r   r   r   r   ru   r   r   )r7   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)rG   rl   )rW   r:   r;   rI   5  s    zIPandasSQLTest._to_sql_method_callable.<locals>.sample.<locals>.<listcomp>)r   rB   rH   insert)Zpd_tabler2   rW   	data_iterr\   )check)rW   r;   sample3  s    
z5PandasSQLTest._to_sql_method_callable.<locals>.sampler   )r8   r   )r5   r   r   r   r   ru   r   r   )r7   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   engineN)r5   r   r   r   r   ru   r   r   )r7   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_roundtripz"SELECT * FROM test_frame_roundtriplevel_0T)inplace)
r5   r   r   r   r   	set_indexr{   r   rv   assert_frame_equal)r7   r   r:   r:   r;   
_roundtripU  s    
zPandasSQLTest._roundtripc             C   s.   | j d}| }t|dddddg d S )NzSELECT * FROM irisgffffff@g      @gffffff?g?zIris-setosa)r   rB   r   rv   rw   )r7   iris_resultsrl   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   g @line1)r   g      ?line2r|   r}   r~   )r   r{   Ztest_to_sql_saves_index)r   from_recordsr   r   _get_index_columnsru   )r7   r   ix_colsr:   r:   r;   _to_sql_save_indexg  s
    
z PandasSQLTest._to_sql_save_indexc          	   C   s   | j  }|d W d Q R X G dd dt}d}y,| j  }|| |dW d Q R X W n |k
rt   Y nX | j d}t|dkst| j  }|| W d 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)r=   r>   r?   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_transactionrB   	Exceptionr   r   ru   )r7   transr   Zins_sqlresres2r:   r:   r;   _transaction_testo  s     
zPandasSQLTest._transaction_test)N)r   )r=   r>   r?   __doc__rZ   r   fixturerm   rn   rz   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;   rY     s4   #0&

rY   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 )N)connectr2   )r7   r:   r:   r;   rb     s    z_TestSQLApi.setup_connectT)autousec             C   s   |    d S )N)load_test_data_and_sql)r7   rm   r:   r:   r;   setup_method  s    z_TestSQLApi.setup_methodc             C   s,   |    |   |   |   |   d S )N)rn   r   r   r   r   )r7   r:   r:   r;   r     s
    z"_TestSQLApi.load_test_data_and_sqlc             C   s   t d| j}| | d S )NzSELECT * FROM iris)rC   r   r2   rz   )r7   ry   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_view)rC   r   r2   rz   )r7   ry   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)rC   r   r2   rv   r   r   )r7   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   )rC   r   r   r2   r   ru   )r7   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 d Q R X d S )Nr   r   )r   z"Table 'test_frame2' already exists)r   )	rC   r   r   r2   r   ru   r   r   r   )r7   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   )rC   r   r   r2   r   ru   r   r   )r7   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   )rC   r   r   r2   r   ru   r   r   )r7   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)rC   r   r   r2   read_sqlrv   r   )r7   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_seriesF)r{   zSELECT * FROM test_series)
r   rs   arangerC   r   r2   r   rv   r   to_frame)r7   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   )conz"SELECT * FROM test_frame_roundtripr   T)r   )rC   r   r   r2   r   r{   r   r   intr   rv   r   )r7   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   )r   r{   r   z"SELECT * FROM test_frame_roundtrip)r   )rC   r   r   r2   r   rv   r   )r7   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 )NzSELECT * FROM iris)r   gffffff@g      @gffffff?g?zIris-setosa)rC   rB   r2   r   rv   rw   )r7   r   rl   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 )NzSELECT * FROM types_test_datar   )parse_datesi  r   r   r   r   z%Y-%m-%d %H:%M:%Sr   i        i  r   r    z%Y%m%dz
2010-10-10z
2010-12-12)rC   r   r2   rr   r   r   rp   rs   
datetime64ru   tolistr   r   r    )r7   r   r:   r:   r;   test_date_parsing  sH    z_TestSQLApi.test_date_parsingr   ignoreraisecoercezread_sql, text, modezSELECT * FROM types_test_data)rR   fallbackr   rR   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)r   r   )r   r   rd   r   r2   rv   r   )r7   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 )NzSELECT * FROM types_test_datar   r   )	index_colr   )rC   r   r2   rr   r{   r   rp   rs   r   ru   r   )r7   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 d Q R X t	d| j}t
|d |d d d S )Nz00:00:01z00:00:03foo)r   test_timedeltazSELECT * FROM test_timedeltar   )r   r   r   rv   assert_produces_warningUserWarningr   r2   rC   r   assert_series_equalview)r7   r   r   r:   r:   r;   r	  j  s
    z_TestSQLApi.test_timedeltac          	   C   s@   t dddgi}d}tjt|d |d| j W d Q R X d S )Nay      ?      ?y               @zComplex datatypes not supported)r   Ztest_complex)r   r   r   r   r   r2   )r7   r   r   r:   r:   r;   test_complex_raisess  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 )Ncol1r   zSELECT * FROM test_index_labeltest_index_label)index_labelr   )
r   ranger{   r   rC   r   r2   r   r   ru   )r7   r  r  r  
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 d Q R X d S )Nr  r   )ZA0A1)B0ZB1)r{   r  zSELECT * FROM test_index_labelr   r   r   Zlevel_1r   r|   r}   )r   r  r   )r   r~   r   zALength of 'index_label' should match number of levels, which is 2)r   )r   r  r
   Zfrom_productrC   r   r2   r   r   ru   r   r{   namesr   r   r   )r7   r  r  r   r:   r:   r;   "test_to_sql_index_label_multiindex  sF    
 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 )N)r   g @r   )r   g      ?r   r|   r}   r~   )r   r{   test_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtrip)r  T)check_index_type)r   r   r   r2   rC   r   rv   r   )r7   r   r   r:   r:   r;   r    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 )Ng333333?g333333@gffffff@g333333@r|   r}   )r   test_dtype_argumentz$SELECT A, B FROM test_dtype_argument)r   r   )r   r   r2   r   rC   r   rv   r   )r7   r   r   r  r   r:   r:   r;   r     s    
z_TestSQLApi.test_dtype_argumentc             C   s4   t ddgddggddgd}tj|d| jdd	 d S )
Nr   r   r   r   r   )r   Ztest_frame_integer_col_namesr   )r   )r   rC   r   r2   )r7   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test)r   CREATE)rC   
get_schemar   r2   ru   )r7   
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 )Nr"  pypi)r   rS   zCREATE TABLE pypi.)rC   r$  r   r2   ru   )r7   r%  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 )Ng?g333333?g @g@)r  brR   INTEGERr"  r)  )r   r   r#  )r   r   rR   IntegerrC   r$  r2   ru   )r7   Zfloat_framer   r%  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 )Ng?g333333?g @g@)Col1ZCol2r"  r-  )r   rW   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")r|   r}   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   rC   r$  r2   ru   r   )r7   r  r%  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}xFtjd| jddD ]0}t	||gdd}t
||| kst|d7 }qfW t|| | jdkr t  }d	}ddddd
g}xHtjd| jddD ]2}t	||gdd}t
||| kst|d7 }qW t|| d S )N   r   Zabcde)r   Ztest_chunksizeF)r{   zselect * from test_chunksizer   r   )r   T)ignore_indexr   rR   )r   rs   randomZrandnlistr   r2   rC   r   r   r   ru   rv   r   r   r   )r7   r   res1r   isizeschunkres3r:   r:   r;   test_chunksize_read  s*    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_namer9  categorytest_categoricalF)r{   zSELECT * FROM test_categorical)	r   copyr   r   r2   rC   r   rv   r   )r7   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      ér)  )r   Ztest_unicodeF)r{   )r   r   r2   )r7   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   g?gffffff@)r|   r}   zd1187b08-4943-4c8d-a7f6F)r{   z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   rs   nanr   r2   rC   r   rv   r   )r7   r   r   r:   r:   r;   test_escaped_table_nameC  s    z#_TestSQLApi.test_escaped_table_name)2r=   r>   r?   r   rd   r   __annotations__rb   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   markparametrizerC   r   r   r   r  r  r	  r  r  r  r  r   r   r   r!  r&  r(  r,  r.  r8  r;  r?  rA  r:   r:   r:   r;   r     sp   
7

	2	"r   z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   rR   c             C   s
   t dS )Nzsqlite:///:memory:)rR   create_engine)r7   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   )rC   r   r   r2   r   r   r   ru   )r7   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 )	NrH  r{   )r  r|   r}   r~   r   )r  r   )
rC   r   r   r2   r   r{   r  ru   r   r   )r7   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 )NzSELECT * FROM irisr_   )rC   r   r2   r   rv   r   r   )r7   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 d 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   )
r2   rB   warningscatch_warningssimplefilterrC   r   r   r   ru   )r7   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 d Q R X d S )NT)rO  rP  ZCaseSensitiver   )rQ  rR  rS  r   r   r2   r   ru   )r7   rT  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 )column_namesr:   )rG   r4  r:   r:   r;   rI     s    z1TestSQLApi._get_index_columns.<locals>.<listcomp>)Zsqlalchemy.enginerW  Z	InspectorZfrom_enginer2   get_indexes)r7   tbl_namerW  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   rC   rP   r2   SQLTable
isinstancerH   rN   rp   sqltypes	TIMESTAMPru   )r7   r   dbrH   r:   r:   r;   test_sqlalchemy_type_mapping  s
    z'TestSQLApi.test_sqlalchemy_type_mappingzinteger, expected)Zint8SMALLINT)ZInt8rg  )Zuint8rg  )ZUInt8rg  )Zint16rg  )ZInt16rg  )Zuint16r*  )ZUInt16r*  )int32r*  )ZInt32r*  )Zuint32BIGINT)ZUInt32ri  )r   ri  )ZInt64ri  r   ri  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`  )r  )r   rC   rP   r2   ra  r   rH   rN   r  rp   ru   )r7   integerr  r   re  rH   r   r:   r:   r;   test_sqlalchemy_integer_mapping  s
    z*TestSQLApi.test_sqlalchemy_integer_mappingrj  Zuint64ZUInt64c          	   C   sN   t ddgdg|d}t| j}tjtdd tjd||d W d Q R X d S )	Nr   r   r  )r   r   z1Unsigned 64 bit integer datatype is not supported)r   r`  )r  )r   rC   rP   r2   r   r   r   ra  )r7   rj  r   re  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 d Q R X t|| t|| t|| ydd l	}	t
d W n tk
r   Y nX d	}t
jtd
d td| W d Q R X d S )Nz
sqlite:///r_   r   F)r   r{   zSELECT * FROM irisr   zpg8000 is installedz)postgresql+pg8000://user:pass@host/dbnamepg8000)r   zselect * from table)r   rv   ensure_cleanr   rC   r   r   r   r   rm  r   skipImportErrorr   )
r7   r   r   Zdb_urirH   r   r   r&   r   rm  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 )Nr_   SepalLengthZ
SepalWidthZPetalLengthZ
PetalWidthName)rR   rT   TableColumnREALTEXT)r7   sametadatar_   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-versicolor)r^   rs  )rR   r  rC   r   r2   setru   )r7   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   zIris-setosa)r^   rs  )rz  rR   selectwhererN   rs  Z	bindparamrC   r   r2   r{  ru   )r7   r_   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_percentageF)r{   )r   r   r2   rC   r   rv   r   )r7   r   r   r:   r:   r;   test_column_with_percentage  s    z&TestSQLApi.test_column_with_percentageN)r=   r>   r?   r   rd   r   r   rJ  rK  rN  rU  rV  r   rf  r   rC  rD  r   rs   r   r   rk  rl  rq  rz  r~  r  r  r:   r:   r:   r;   rF  M  sD   		(
!rF  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.
    T)r   c             #   sn   t    | j}| }| | _t|| _|| _	|| _d V  | j
  | j  | j	| _t| j	| _d S )N)superr   r2   r   beginZ_EngineToConnMixin__txrC   rP   r   Z_EngineToConnMixin__enginerollbackrL   )r7   rm   r   r2   )	__class__r:   r;   r   (  s    



z_EngineToConnMixin.setup_method)r=   r>   r?   r   r   r   r   __classcell__r:   r:   )r  r;   r  #  s   r  c               @   s   e Zd ZdS )TestSQLApiConnN)r=   r>   r?   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   r  :memory:c             C   s
   t |S )N)sqlite3r   )r7   Zdatabaser:   r:   r;   r   I  s    zTestSQLiteFallbackApi.connectc          	   C   sj   t  J}| |}tj| jd|dd |  | |}td|}|  W d Q R X t | j| d S )Ntest_frame3_legacyF)r{   z!SELECT * FROM test_frame3_legacy;)	rv   rn  r   rC   r   r   rL   r   r   )r7   r   r2   r   r:   r:   r;   test_sql_open_closeL  s    


z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installed)rE  c          	   C   s2   d}d}t jt|d td| W d Q R X d S )Nzmysql://root@localhost/pandasz-Using URI string without sqlalchemy installed)r   zSELECT * FROM iris)r   r   rp  rC   r   )r7   r2   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 d Q R X d S )NzSELECT * FROM irisz9Execution failed on sql 'iris': near "iris": syntax error)r   r_   )	rC   r   r2   r   rv   r   r   r   DatabaseError)r7   rL  rM  r   r:   r:   r;   rN  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 d Q R X d S )Nr   r   r   r   r  zb )r   r  F)r{   )r   rv   r
  rC   r   r2   )r7   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 )Nr"  r#  )rC   r$  r   ru   )r7   r%  r:   r:   r;   test_get_schema2s  s    z&TestSQLiteFallbackApi.test_get_schema2c             C   sJ   x4| dD ]&}|  d d|kr|  d S qW td| dd S )N
r   z""r   zColumn z
 not found)splitstripr   )r7   rS   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^  T)r_  r`  )r  rd  )	r   r   rC   SQLiteDatabaser2   ZSQLiteTableZ
sql_schemar  ru   )r7   r   re  rH   rS   r:   r:   r;   test_sqlite_type_mapping  s    z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r  )r=   r>   r?   r   rd   r   r   r  r   rC  skipifSQLALCHEMY_INSTALLEDr  rN  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.

    rd   Tclass)r   scopec             C   s*   |    |   |   }| _|  d S )N)setup_importsetup_driverr   r2   )clsr2   r:   r:   r;   setup_class  s    z_TestSQLAlchemy.setup_classc             C   s   |    |   d S )N)r   r   )r7   r:   r:   r;   r     s    z&_TestSQLAlchemy.load_test_data_and_sql)r   c             C   s   |    d S )N)r   )r7   rm   r:   r:   r;   r     s    z_TestSQLAlchemy.setup_methodc             C   s   t std d S )NzSQLAlchemy not installed)r  r   ro  )r  r:   r:   r;   r    s    z_TestSQLAlchemy.setup_importc             C   s
   t  d S )N)NotImplementedError)r  r:   r:   r;   r    s    z_TestSQLAlchemy.setup_driverc             C   s
   t  d S )N)r  )r  r:   r:   r;   r     s    z_TestSQLAlchemy.connectc             C   sX   y&|   | _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   r2   rC   rP   r   rR   excZOperationalErrorr   ro  rd   )r7   r:   r:   r;   rb     s    
z_TestSQLAlchemy.setup_connectc             C   s   |    d S )N)r   )r7   r:   r:   r;   test_read_sql  s    z_TestSQLAlchemy.test_read_sqlc             C   s   |    d S )N)r   )r7   r:   r:   r;   test_read_sql_parameter  s    z'_TestSQLAlchemy.test_read_sql_parameterc             C   s   |    d S )N)r   )r7   r:   r:   r;   test_read_sql_named_parameter  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc             C   s   |    d S )N)r   )r7   r:   r:   r;   r     s    z_TestSQLAlchemy.test_to_sqlc             C   s   |    d S )N)r   )r7   r:   r:   r;   test_to_sql_empty  s    z!_TestSQLAlchemy.test_to_sql_emptyc             C   s   |    d S )N)r   )r7   r:   r:   r;   r     s    z _TestSQLAlchemy.test_to_sql_failc             C   s   |    d S )N)r   )r7   r:   r:   r;   r     s    z#_TestSQLAlchemy.test_to_sql_replacec             C   s   |    d S )N)r   )r7   r:   r:   r;   r     s    z"_TestSQLAlchemy.test_to_sql_appendc             C   s   | j dd d S )Nmulti)r8   )r   )r7   r:   r:   r;   test_to_sql_method_multi  s    z(_TestSQLAlchemy.test_to_sql_method_multic             C   s   |    d S )N)r   )r7   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 )Ng      ?g       @g      @g      @)onetwor  )	r   r   rC   rP   r   r   r   r   ru   )r7   	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 )Ng      ?g       @g      @g      @)r  r  r  )
r   r   rC   rP   r   r   r   r   ru   r5   )r7   r  r  r   r[  r:   r:   r;   test_drop_table  s    

z_TestSQLAlchemy.test_drop_tablec             C   s   |    d S )N)r   )r7   r:   r:   r;   r     s    z_TestSQLAlchemy.test_roundtripc             C   s   |    d S )N)r   )r7   r:   r:   r;   r     s    z _TestSQLAlchemy.test_execute_sqlc             C   s   t jd| jd}| | d S )Nr_   )r   )rC   r   r2   rz   )r7   ry   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 )Nr_   rr  )r   r   )rC   r   r2   rv   rw   r   rx   )r7   ry   r:   r:   r;   rJ    s    z'_TestSQLAlchemy.test_read_table_columnsc          	   C   s2   d}t jt|d tjd| jd W d Q R X d S )Nz!Table this_doesnt_exist not found)r   Zthis_doesnt_exist)r   )r   r   r   rC   r   r2   )r7   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   )rC   r   r2   rr   r!   r   rp   rs   rt   ru   r"   rj  r#   Zbool_r$   r%   object)r7   r   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            )r\   test_bigintF)r{   )r   r   r2   rC   r   rv   r   )r7   r   r   r:   r:   r;   r  %  s    z_TestSQLAlchemy.test_bigintc             C   s(   t d| j}t|jjjtjs$t	d S )Nr   )
rC   r   r2   rr   r   r   rp   rs   r   ru   )r7   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)tz)r   z&DateCol loaded with incorrect type -> )r   r   r   ru   r   r   dtr  r   r   rv   r  )r  Zexpected_datar  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 zone)r   r  r   )r   T)r0  r   )r   r2   r3   r   ro  r(   r   r   ru   r   r  r  r   r2  rC   r   rv   r  )r7   r   r   r  r  r:   r:   r;   test_datetime_with_timezone4  s6    "




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|   z2013-01-01 09:00:00r   z
US/Pacific)periodsr  Ztest_datetime_tzF)r{   r   r  zSELECT * FROM test_datetime_tzr   )r   r|   )r   r   r   r2   rd   r  Z
tz_convertZtz_localizerC   r   rv   r   r   rb  locr   ru   r   )r7   r  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_obbF)r   )
r   r   r   r2   rC   r   pdZNaTrv   r   )r7   r\   r   r  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nums)r{   Z	foo_tableZ	info_date)r  )r  F)Zcheck_names)
r   Z
_with_freqr   r  r   r2   rC   r   rv   r   )r7   datesr  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   )r   z%Y-%m-%d %H:%M:%Sformatr   r   unit)rC   r   r2   rd   r  rs   r   rr   r   r   rp   ru   r   )r7   r   expected_typer:   r:   r;   r     s,    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 )Nz2013-01-01 09:00:00r   )r  g      @)r|   r}   test_datetimer{   r   )ZaxiszSELECT * FROM test_datetimer   )r   r|   r|   )r   r   rs   r   r   r2   rC   r   Zdroprv   r   r   rd   rb  r  r   ru   r   )r7   r   r   r:   r:   r;   r    s    
z_TestSQLAlchemy.test_datetimec             C   s   t tdddtdd}tj|jd< |jd| jdd	 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 )Nz2013-01-01 09:00:00r   )r  g      @)r|   r}   )r   r|   r  F)r{   zSELECT * FROM test_datetimer   )r   r|   r|   r   )r  )r   r   rs   r   r@  r  r   r2   rC   r   rv   r   r   rd   rb  r   ru   r   )r7   r   r   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 )	Ni  r   r   r  )r   	test_dateF)r{   )r   r   r   r2   r   r   rv   r  )r7   r   r   r   r  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      r  )r   	test_timeF)r{   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 )Nz%H:%M:%S.%f)r  )r  r:   r:   r;   r    r  )r   r   r   r2   r   rv   r   TestSQLiteFallbackr   rC   r   applymaprd   )r7   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_writeF)r{   T)Zcheck_dtypeZcheck_exact)r   rs   rh  float32r   r   r2   rC   r   rv   r   )r7   r  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   g?gffffff@)r|   r}   test_nanF)r{   zSELECT * FROM test_nan)
r   rs   r@  r   r2   rC   r   rv   r   r   )r7   r   r   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   )r|   r}   r  F)r{   r}   r  zSELECT * FROM test_nan)r   rs   r@  r   r2   rC   r   rv   r   r   r   )r7   r   r   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   r  r)  )r|   r}   r  F)r{   )r   r}   zSELECT * FROM test_nan)r   rs   r@  r   r2   r  rC   r   rv   r   r   )r7   r   r   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 )rX  r:   )rG   r4  r:   r:   r;   rI   [  s    z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)rR   r   r2   rY  )r7   rZ  r   r[  r\  r:   r:   r;   r   U  s
    

z"_TestSQLAlchemy._get_index_columnsc             C   s   |    d S )N)r   )r7   r:   r:   r;   test_to_sql_save_index^  s    z&_TestSQLAlchemy.test_to_sql_save_indexc             C   s   |    d S )N)r   )r7   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_table)r   r   F)r  )r   rC   r$  r   r2   rq   r5   rB   r   rv   r   )r7   r9   r%  Zblank_test_dfreturned_dfr:   r:   r;   r  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 d 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?N)r   
dtype_testdtype_test2)r   )rQ   z&The type of B is not a SQLAlchemy type)r   r   Zdtype_test3
   single_dtype_test)r   r   r2   rR   rw  rS   rT   rU   rV   r   rp   rb  ru   r   r   r   r   Stringr   )	r7   rI  r\   r   rX   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 )NTi  r   r   r  )r   g?)BoolDateIntFloatnotna_dtype_test)rQ   r   r  r  r  r  )r   r   r   r   r2   rC   r   rR   rS   rT   rU   rd   rc  r+  BooleanrV   r   rb  rp   ru   DateTimer  )r7   rI  r   r9   r  rX   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?r  )r   float64r   rh  r   )f32f64
f64_as_f32i32r  Ztest_dtypesFr   r     )	precision)r{   r   r   r  r      )rQ   r  r  r  )r   r   r   r2   rR   r  rC   r   rs   roundrq   ru   rS   rT   rU   rV   r   r   rp   rb  rc  r+  Z
BigInteger)r7   Vr   r   rX   r  r:   r:   r;   test_double_precision  s.    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_data)r   )rC   r   )
connectionr&   r:   r:   r;   r    s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.fooc             S   s   |j d| dd d S )Ntest_foo_datar   )r   r   r   )r   )r  r\   r:   r:   r;   bar  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.barc          
      s\   |   J}| 6 t r.|} || n|}| | W d Q R X W d Q R X d S )N)r   r  r   Zrun_callable)Zconnectabler2   Zfoo_data)r  r  r:   r;   main  s    


z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr  r   r   r   )r   r   r2   )r7   r   r:   )r  r  r;   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 d Q R X n,|jd
| jdd td
| j}t|| d S )Nr   r   r   )r   r  r   r  zGH 36465)rE  zinf cannot be used with MySQL)r   ZfoobarF)r{   )r   rd   pymysqlVERSIONr   r   rC  ZxfailnodeZ
add_markerr   r   r   r2   rC   r   rv   Zassert_equal)r7   r  ri   r   r  rC  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 d 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)r=   r>   r?   Z__tablename__Z__table_args__rR   ru  r+  idZUnicoder  r:   r:   r:   r;   	Temporary
  s   
r  )rQ   )r  )rC   r   )r   r   Zdeclarative_base
sa_sessionZsessionmakerr2   Ztransactionr  Z	__table__createaddflushrC   r   rR   r  r  rv   r   )	r7   Z	test_datar  ZBaser  Sessionr   r2   r   r:   r:   r;   test_temporary_table  s    "z$_TestSQLAlchemy.test_temporary_tablec          	   C   s,   d}t jt|d | d W d Q R X d S )Nz*engine must be one of 'auto', 'sqlalchemy')r   Z
bad_engine)r   r   r   r   )r7   r   r:   r:   r;   test_invalid_engine  s    z#_TestSQLAlchemy.test_invalid_enginec          	   C   s$   t dd |   W d Q R X d S )Nzio.sql.enginerR   )r  option_contextr   )r7   r:   r:   r;   test_options_sqlalchemy!  s    z'_TestSQLAlchemy.test_options_sqlalchemyc          	   C   s$   t dd |   W d Q R X d S )Nzio.sql.enginer   )r  r  r   )r7   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 d Q R X tdd* t tdtsntt tdtstW d Q R X d S )NrR   zio.sql.enginer   )rb  r   r   ru   r  r  )r7   r:   r:   r;   test_options_get_engine-  s    z'_TestSQLAlchemy.test_options_get_enginec             C   s   d S )Nr:   )r7   r:   r:   r;   "test_get_engine_auto_error_message8  s    z2_TestSQLAlchemy.test_get_engine_auto_error_messageN)Dr=   r>   r?   r   r   rB  r   r   r  r   r   classmethodr  r  r   rb   r  r  r  r   r  r   r   r   r  r  r  r  r   r   r  rJ  r  r  r  r  r  r  r  r  r   r  r  r  r  r  r  r  r  r   r  r  r  r  r  r  r  rC  rD  rs   infr  r  r  r  r  r  r  r:   r:   r:   r;   r    sv   
	N
$
	#4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   ro  )r7   r:   r:   r;   r  @  s    z%_TestSQLAlchemyConn.test_transactionsN)r=   r>   r?   r  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 )Nzsqlite:///:memory:)rR   rG  )r  r:   r:   r;   r   L  s    z_TestSQLiteAlchemy.connectc             C   s
   d | _ d S )N)driver)r  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 )Nr   )rC   r   r2   rr   r!   r   rp   rs   rt   ru   r"   rj  r#   r$   r%   )r7   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 )Nr   )
rC   r   r2   rr   r   r   rp   rs   r   ru   )r7   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 d Q R X d S )Nr  r   r   r   )r   Ztest_bigintwarningF)r{   T)rO  rP  r   )
r   r   r2   rQ  rR  rS  rC   r   r   ru   )r7   r   rT  r:   r:   r;   test_bigint_warningj  s    
z&_TestSQLiteAlchemy.test_bigint_warningN)r=   r>   r?   r   rd   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:z/pandas)connect_args)rR   rG  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 )Nr   )rC   r   r2   rr   r!   r   rp   rs   rt   ru   r"   rj  r#   r$   r%   )r7   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 }| }y||}|  W n  |jk
r   |	   Y nX t
d| j}t|| t
d| j}t|| d S )Nr   r   r   r   g?g?g333333?)r  r)  Ztest_procedureF)r{   zDROP PROCEDURE IF EXISTS get_testdb;

        CREATE PROCEDURE get_testdb ()

        BEGIN
            SELECT * FROM test_procedure;
        ENDzCALL get_testdb();)r  r   r   r2   r   r  rB   rD   rK   r  rC   r   rv   r   r   )	r7   r  r   procr  r   r1r3  r   r:   r:   r;   test_read_procedure  s     

z%_TestMySQLAlchemy.test_read_procedureN)r=   r>   r?   r   rd   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:z/pandas)rR   rG  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 d 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   g?g?r  n)r  col2col3z$DROP SCHEMA IF EXISTS other CASCADE;zCREATE SCHEMA other;Ztest_schema_publicF)r{   Ztest_schema_public_explicitpublic)r{   rS   Ztest_schema_otherother)rS   z!Table test_schema_other not found)r   )rS   r{   r   )rS   r{   r   r   T)r0  )rX   Ztest_schema_other2)r{   r   )r   r2   rB   r   rC   r   rv   r   r   r   r   r   rb  rR   r   ZEnginer   rT   rP   Z
read_table)r7   r   r3  r   r7  Zres4r   r   Zengine2rX   Zpdsqlr:   r:   r;   test_schema_support  s\    
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 d Q R X d S )
Nr   z, c             s   s   | ]}d | d V  qdS )"Nr:   )rG   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)rC   file)r  rA   r   r]   writer	writerowsseekjoinrS   r   Zcopy_expert)rH   r2   rW   r   Z
dbapi_connrF   Zs_bufr3  r   rE   Z	sql_queryr:   r:   r;   psql_insert_copy	  s    



zY_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_method.<locals>.psql_insert_copyr   r   g?g?r  r(  )r  r)  r*  Ztest_copy_insertF)r{   r8   )r   r   r2   rC   r   rv   r   )r7   r7  r  r   r:   r:   r;   (test_copy_from_callable_insertion_method	  s    z?_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_methodN)r=   r>   r?   r   rd   r!  r  r   r  r-  r8  r:   r:   r:   r;   r'    s   Gr'  c               @   s   e Zd ZdS )TestMySQLAlchemyN)r=   r>   r?   r:   r:   r:   r;   r9  4	  s   r9  c               @   s   e Zd ZdS )TestMySQLAlchemyConnN)r=   r>   r?   r:   r:   r:   r;   r:  :	  s   r:  c               @   s   e Zd ZdS )TestPostgreSQLAlchemyN)r=   r>   r?   r:   r:   r:   r;   r;  @	  s   r;  c               @   s   e Zd ZdS )TestPostgreSQLAlchemyConnN)r=   r>   r?   r:   r:   r:   r;   r<  F	  s   r<  c               @   s   e Zd ZdS )TestSQLiteAlchemyN)r=   r>   r?   r:   r:   r:   r;   r=  L	  s   r=  c               @   s   e Zd ZdS )TestSQLiteAlchemyConnN)r=   r>   r?   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 )8r  zG
    Test the fallback mode against an in-memory sqlite database.

    r   c             C   s
   t dS )Nz:memory:)r  r   )r  r:   r:   r;   r   c	  s    zTestSQLiteFallback.connectc             C   s   |   | _d S )N)r   r2   )r7   r:   r:   r;   rb   g	  s    z TestSQLiteFallback.setup_connectc             C   s   t | j| _|   d S )N)rC   r  r2   r   r   )r7   r:   r:   r;   r   j	  s    z)TestSQLiteFallback.load_test_data_and_sqlT)r   c             C   s   |    d S )N)r   )r7   rm   r:   r:   r;   r   n	  s    zTestSQLiteFallback.setup_methodc             C   s   |    d S )N)r   )r7   r:   r:   r;   r  r	  s    z TestSQLiteFallback.test_read_sqlc             C   s   |    d S )N)r   )r7   r:   r:   r;   r  u	  s    z*TestSQLiteFallback.test_read_sql_parameterc             C   s   |    d S )N)r   )r7   r:   r:   r;   r  x	  s    z0TestSQLiteFallback.test_read_sql_named_parameterc             C   s   |    d S )N)r   )r7   r:   r:   r;   r   {	  s    zTestSQLiteFallback.test_to_sqlc             C   s   |    d S )N)r   )r7   r:   r:   r;   r  ~	  s    z$TestSQLiteFallback.test_to_sql_emptyc             C   s   |    d S )N)r   )r7   r:   r:   r;   r   	  s    z#TestSQLiteFallback.test_to_sql_failc             C   s   |    d S )N)r   )r7   r:   r:   r;   r   	  s    z&TestSQLiteFallback.test_to_sql_replacec             C   s   |    d S )N)r   )r7   r:   r:   r;   r   	  s    z%TestSQLiteFallback.test_to_sql_appendc             C   s   | j dd d S )Nr  )r8   )r   )r7   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 )Ng      ?g       @g      @g      @)r  r  Zdrop_test_frame)r   r   r   r   ru   r5   )r7   r  r:   r:   r;   test_create_and_drop_table	  s    z-TestSQLiteFallback.test_create_and_drop_tablec             C   s   |    d S )N)r   )r7   r:   r:   r;   r   	  s    z!TestSQLiteFallback.test_roundtripc             C   s   |    d S )N)r   )r7   r:   r:   r;   r   	  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 )Ni  r   r   r  )r   r  F)r{   zSELECT * FROM test_dater   r   )
r   r   r   r2   r   rd   rv   r   r   r   )r7   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  r  )r   r  F)r{   zSELECT * FROM test_timer   c             S   s
   |  dS )Nz%H:%M:%S.%f)r  )r  r:   r:   r;   r  	  r  z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>)	r   r   r   r2   r   rd   r  rv   r   )r7   r   r   r  r:   r:   r;   r  	  s    "
z%TestSQLiteFallback.test_datetime_timec             C   sX   t dd| d | j}g }x4|jD ]*}t d| d| j}||j  q&W |S )Nz1SELECT * FROM sqlite_master WHERE type = 'index' zAND tbl_name = ''zPRAGMA index_info())rC   r   r2   r   r   r   )r7   rZ  r\  r   Zix_nameZix_infor:   r:   r;   r   	  s    z%TestSQLiteFallback._get_index_columnsc             C   s   |    d S )N)r   )r7   r:   r:   r;   r  	  s    z)TestSQLiteFallback.test_to_sql_save_indexc             C   s   |    d S )N)r   )r7   r:   r:   r;   r  	  s    z$TestSQLiteFallback.test_transactionsc       
      C   sT   | j d| d}x$|D ]\}}}}}}	||kr|S qW td| d| dd S )NzPRAGMA table_info(rA  zTable z	, column z
 not found)r2   rB   r   )
r7   rH   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 d Q R X |jd| jd
d | ddd
kst| ddd
kstd S )Nr   zNot applicable to MySQL legacyr|   r}   )g?T)g?N)r   r  r  STRING)r   r*  z!B \(<class 'bool'>\) not a string)r   r   r  )rd   r   ro  r   r   r2   r  ru   r   r   r   )r7   rI  r\   r   r   r:   r:   r;   r  	  s    

 zTestSQLiteFallback.test_dtypec             C   s   | j dkrtd tdd gttdddd gtdd gddtd	d gd
}t|}d}||| j | |ddks|t	| |ddkst	| |ddkst	| |ddkst	d S )Nr   zNot applicable to MySQL legacyTi  r   r   r  )r   g?)r  r  r  r  r  r  r*  r  rd  r  r  rv  )
rd   r   ro  r   r   r   r   r2   r  ru   )r7   rI  r   r9   r:   r:   r;   r  	  s    


z#TestSQLiteFallback.test_notna_dtypec             C   s   t ddgddggddgd}d}tjt|d	 |d
| j W d Q R X xt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 qfW d S )Nr   r   r   r   r  r)  )r   z$Empty table or column name specified)r    ztest_weird_name]ztest_weird_name[ztest_weird_name`ztest_weird_name"ztest_weird_name'z_b.test_weird_name_01-30z"_b.test_weird_name_01-30"Z99beginswithnumberZ12345r>  Ztest_weird_col_namer   )	r   r   r   r   r   r2   	enumeraterC   Ztable_exists)r7   r   r   ZndxZ
weird_namer=  Zc_tblr:   r:   r;   test_illegal_names	  s*    z%TestSQLiteFallback.test_illegal_namesN)!r=   r>   r?   r   rd   r  r   rb   r   r   r   r   r  r  r  r   r  r   r   r   r  r?  r   r   r  r  r   r  r  r  r  r  rH  r:   r:   r:   r;   r  Z	  s6   
r  c             C   s
   |  dS )z Returns date in YYYYMMDD format.z%Y%m%d)r  )r  r:   r:   r;   date_format
  s    rI  z'{}'z{:.8f}z{:d}c             C   s   dS )NZNULLr:   )xr:   r:   r;   r  (
  r  r  z{:.10f}z'{!s}'c             G   sN   g }x<|D ]4}t |tr$t|r$d }tt| }||| q
W | t| S )N)rb  r   r   _formattersrp   r   tuple)rC   argsZprocessed_argsarg	formatterr:   r:   r;   format_query.
  s    
rP  c             C   s,   t j| ||d }|dkr dS t|S dS )z#Replace removed sql.tquery function)r   rF   N)rC   rB   rJ   r2  )r&   r   rF   r   r:   r:   r;   tquery:
  s    rQ  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 )TestXSQLiteT)r   c             c   s2   |j | _td| _d V  |j | _td| _d S )Nz:memory:)functionr8   r  r   r2   )r7   ri   rh   r:   r:   r;   r   E
  s
    zTestXSQLite.setup_methodc             C   s   t  }| | d S )N)rv   makeTimeDataFrame_check_roundtrip)r7   r  r:   r:   r;   
test_basicP
  s    zTestXSQLite.test_basicc       	      C   s   t  }tj|jd< t|d}| j }|	| | j }d}x.|
 D ]"\}}t|f| }t||d qLW | 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))rF   zselect * from test)r   gMbP?)rtol)rv   rT  rs   r@  rq   rC   r$  r2   rA   rB   iterrowsrP  rQ  rD   r   r{   r   )	r7   r  r%  rF   rk   idxrl   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 )Nr"  z$INSERT INTO test VALUES (?, ?, ?, ?)r   )r^   zselect * from testr   )rv   rT  rC   r$  r2   rA   rB   rq   rL  rD   r   r{   r   )r7   r  r%  rF   rk   rl   r   r:   r:   r;   test_executei
  s    



zTestXSQLite.test_executec             C   s   t  }t|d}| }x>|D ]6}|d}t|dkr"|d dkr"|d dks"tq"W t  }tj|dddgd	}| }d
|kst| j	 }|
| d S )Nr"   r   r   r|   r   DATETIMEr}   )rW   zPRIMARY KEY ("A", "B"))rv   rT  rC   r$  
splitlinesr  r   ru   r2   rA   rB   )r7   r  r%  lineslinetokensrF   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 d Q R X d S )Nz
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a, b)
        );
        z,INSERT INTO test VALUES("foo", "bar", 1.234)z,INSERT INTO test VALUES("foo", "baz", 2.567)zExecution failed on sql)r   z(INSERT INTO test VALUES("foo", "bar", 7))r2   rA   rB   rC   r   r   r  )r7   r%  rF   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 d Q R X d S )Nz
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a, b)
        );
        z,INSERT INTO test VALUES("foo", "bar", 1.234)zselect * from test)r   )
r2   rA   rB   rC   rL   rv   Zexternal_error_raisedr  ProgrammingErrorrQ  )r7   r%  rF   r:   r:   r;   test_execute_closed_connection
  s    	


z*TestXSQLite.test_execute_closed_connectionc             C   s   d S )Nr:   )r7   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   r   r{   zselect * from test_tabler  txtr  Idxtest_table2zselect * from test_table2)r  )rC   r   r2   r   r{   rv   r   r   r<  r	   rs   r   r   )r7   r  r   r  frame2Znew_idxr:   r:   r;   rU  
  s    zTestXSQLite._check_roundtripc             C   s*   t dtdi}tj|| jddd d S )NFromr   testkeywordsF)r   r   r{   )r   rs   onesrC   r   r2   )r7   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c0)r   mono_dfF)r   r   r{   c             s   s   | ]}|d  V  qdS )r   Nr:   )rG   Zmy_c0r:   r:   r;   r0  
  s    z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr   )
r   rC   r   r2   sumrB   ru   r   rv   r   )r7   rq  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 d Q R X || tj| j|dd d}tjt|d tj| j|dd W d 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}   )r  r)  r   r   r   r~   r   r   table_if_existszSELECT * FROM c                s     |  dS )z
            Drops tables created from individual tests
            so no dependencies arise from sequential tests
            N)r5   )test_table_to_drop)r7   r:   r;   clean_up
  s    z,TestXSQLite.test_if_exists.<locals>.clean_upz*'notvalidvalue' is not valid for if_exists)r   notvalidvalue)r  r   r   r   r   z&Table 'table_if_exists' already existsr   F)r  r   r   r   r{   )r   )r   r|   )r   r}   )r   r~   )r   r   )r   r   r   )	r   r   r   r   rC   r   r2   rQ  ru   )r7   df_if_exists_1df_if_exists_2rE   
sql_selectrv  r   r:   )r7   r;   test_if_exists
  sl    
zTestXSQLite.test_if_existsN)r=   r>   r?   r   r   r   rV  r[  r\  rc  rd  rf  rg  rU  rp  rs  r{  r:   r:   r:   r;   rR  C
  s   rR  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   r  c          
   C   s   t d}|jddddd y|jdd W nZ |jk
rZ } ztd|W d d }~X Y n. |jk
r } ztd	|W d d }~X Y nX d S )
Nr  	localhostrootrF  pandas)hostuserpasswdre  )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   re  RuntimeErrorrK   )r  r  errr:   r:   r;   r  ,  s    
zTestXMySQL.setup_class)r   c          
   C   s   t d}|jddddd y|jdd W nZ |jk
rZ } ztd|W d d }~X Y n. |jk
r } ztd	|W d d }~X Y nX |j| _d S )
Nr  r}  r~  rF  r  )r  r  r  re  )r  zCreate 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   re  r  rK   rS  r8   )r7   ri   rh   r  r  r:   r:   r;   r   @  s    
zTestXMySQL.setup_methodc             C   s   t  }| | d S )N)rv   rT  rU  )r7   r  r:   r:   r;   rV  V  s    zTestXMySQL.test_basicc       
      C   s   t  }tj|jd< d}t|d}| j }|	| |	| d}x.|
 D ]"\}}t|f| }t||d qPW | j  tjd| jd}	|j|	_t j|	|dd	 d S )
N)r   r   zDROP TABLE IF EXISTS testr"  z(INSERT INTO test VALUES (%s, %s, %s, %s))rF   zselect * from test)r   gMbP?)rW  )rv   rT  rs   r@  rq   rC   r$  r2   rA   rB   rX  rP  rQ  rD   r   r{   r   )
r7   r  drop_sqlr%  rF   rk   rY  rl   rZ  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{   zDROP TABLE IF EXISTS testr"  )r   r   zselect * from testr   )rC   r   r   r  )rv   rT  r{   r   r2   rA   rB   rC   r   r   rg   r   )r7   r  r  rF   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 d Q R X |	| d}|j
d j }tj	|| jt|d | j  td| j}|jd d	 |_t ||d d	  d S )
NzDROP TABLE IF EXISTS testr"  r   zUnknown table.*z(INSERT INTO test VALUES (%s, %s, %s, %s)r   )r^   zselect * from testr   )rv   rT  rC   r$  r2   rA   rQ  rR  filterwarningsrB   rq   rx   r   rL  rD   r   r{   r   )r7   r  r  r%  rF   rk   rl   r   r:   r:   r;   r\  ~  s    



zTestXMySQL.test_executec             C   s   t  }t|d}| }x>|D ]6}|d}t|dkr"|d dkr"|d dks"tq"W t  }d}tj|ddd	gd
}| }d|kst| j	 }|
| |
| d S )Nr"  r]  r   r   r|   r   r^  zDROP TABLE IF EXISTS testr}   )rW   zPRIMARY KEY (`A`, `B`))rv   rT  rC   r$  r_  r  r   ru   r2   rA   rB   )r7   r  r%  r`  ra  rb  r  rF   r:   r:   r;   rc    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 d Q R X d S )NzDROP TABLE IF EXISTS testz
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a(5), b(5))
        );
        z,INSERT INTO test VALUES("foo", "bar", 1.234)z,INSERT INTO test VALUES("foo", "baz", 2.567)z<insert message here>)r   z(INSERT INTO test VALUES("foo", "bar", 7))r2   rA   rB   rC   r   r   r   )r7   r  r%  rF   r:   r:   r;   rd    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 d Q R X | 	|| d S )NzDROP TABLE IF EXISTS testz
        CREATE TABLE test
        (
        a TEXT,
        b TEXT,
        c REAL,
        PRIMARY KEY (a(5), b(5))
        );
        z,INSERT INTO test VALUES("foo", "bar", 1.234)z<insert message here>)r   zselect * from test)r   )
r2   rA   rB   rC   rL   r   r   r   rQ  r   )r7   ri   rh   r  r%  rF   r:   r:   r;   rf    s    	



z)TestXMySQL.test_execute_closed_connectionc             C   s   d S )Nr:   )r7   r:   r:   r;   rg    s    zTestXMySQL.test_na_roundtripc          	   C   s>  d}| j  }t  tdd || W d 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 d 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   zUnknown table.*rh  F)r   r   r{   zselect * from test_tabler  ri  r  rj  z DROP TABLE IF EXISTS test_table2rk  zselect * from test_table2)r  )r2   rA   rQ  rR  r  rB   rC   r   r   r{   r   rv   r   r   r<  r	   rs   r   r  )r7   r  r  rF   r   r  rl  r{   r:   r:   r;   rU    s4    



zTestXMySQL._check_roundtripc             C   s,   t dtdi}tj|| jdddd d S )Nrm  r   rn  r   F)r   r   r   r{   )r   rs   ro  rC   r   r2   )r7   r   r:   r:   r;   rp    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 d Q R X || tj| j|ddd tjtdd tj| j|dd W d 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   rt  zSELECT * FROM c                s     |  dS )z
            Drops tables created from individual tests
            so no dependencies arise from sequential tests
            N)r5   )ru  )r7   r:   r;   rv    s    z+TestXMySQL.test_if_exists.<locals>.clean_upz<insert message here>)r   rw  )r  r   r   r   r   F)r  r   r   r   r{   r   )r   )r   r|   )r   r}   )r   r~   )r   r   )r   r   r   )	r   r   r   r   rC   r   r2   rQ  ru   )r7   rx  ry  rE   rz  rv  r:   )r7   r;   r{    sp    
zTestXMySQL.test_if_existsN)r=   r>   r?   r   r   r  r   rV  r[  r  r\  rc  rd  rf  rg  rU  rp  r{  r:   r:   r:   r;   r|  &  s   "r|  )NN)\r   r]   r   r   r   r[   r   r  rQ  numpyrs   r   Zpandas.core.dtypes.commonr   r   r  r  r   r	   r
   r   r   r   r   r   r   r   Zpandas._testingZ_testingrv   Zpandas.io.sqlrC   r   r   r   r   r   rR   r   Zsqlalchemy.extr   Zsqlalchemy.ormr   r  Zsqlalchemy.schemaZsqlalchemy.sql.sqltypesrc  r  rp  rc   r1   r@   rM   rO   rY   r   rC  Zsingler  rF  r  r  r  r  r  r  r  r'  re  r9  r:  r;  r<  r=  r>  r  rI  r  r   Zstr_bytesr   r   rp   r  r   rK  rP  rQ  rR  ro  r|  r:   r:   r:   r;   <module>   s   0



	  s   C U

O     51Fy


 B

	 c