3
®yb4‰ ã               @   s  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jZddljjZddlm Z m!Z! y<ddl"Z"ddl#m$Z$ dd	l%m&Z' ddl(Z"ddl)jj*Z*d
Z+W n e,k
�r   dZ+Y nX ddddœddddœddddœddpd œd!dqd œd"drd œdœd$d%d&dœd'd(d)dœd*d+d,dœd-d.id/œZ-G d0d1„ d1ƒZ.G d2d3„ d3e.ƒZ/G d4d5„ d5e.ƒZ0G d6d7„ d7e.ƒZ1G d8d9„ d9ƒZ2G d:d;„ d;e2ƒZ3ej4j5ej4j6e+ d<d=�G d>d?„ d?e1e3ƒƒƒZ7G d@dA„ dAƒZ8ej4j5G dBdC„ dCe8e7ƒƒZ9ej4j5G dDdE„ dEe0e3ƒƒZ:G dFdG„ dGe1e2ƒZ;G dHdI„ dIe8e;ƒZ<G dJdK„ dKƒZ=G dLdM„ dMƒZ>G dNdO„ dOƒZ?ej4j5ej4j@G dPdQ„ dQe>e;ƒƒƒZAej4j5ej4j@G dRdS„ dSe>e<ƒƒƒZBej4j5ej4j@G dTdU„ dUe?e;ƒƒƒZCej4j5ej4j@G dVdW„ dWe?e<ƒƒƒZDej4j5G dXdY„ dYe=e;ƒƒZEej4j5G dZd[„ d[e=e<ƒƒZFej4j5G d\d]„ d]e0e2ƒƒZGd^d_„ ZHed`jIeJd`jIe
jKd`jIeLd`jIeMdajIeNdbjIeOdƒdcdd„ e
jPdejIeQdfjIi	ZRdgdh„ ZSdsdidj„ZTej4j5G dkdl„ dle0ƒƒZUej4j5ej4j@ej4jVdmd=�G dndo„ doe/ƒƒƒƒZWdS )ta  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)Úread_sql_queryÚread_sql_table)Údeclarative)ÚsessionTFzËCREATE TABLE iris (
                "SepalLength" REAL,
                "SepalWidth" REAL,
                "PetalLength" REAL,
                "PetalWidth" REAL,
                "Name" TEXT
            )zÛCREATE 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)
                ÚDateColWithTzz1SELECT * 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| jƒ D ]}| j|ƒ qW | jƒ  d S )NÚconn)ÚhasattrÚ_get_all_tablesÚ
drop_tableÚ_close_conn)ÚselfÚmethodÚtbl© r6   ú</tmp/pip-install-4cy230dp/pandas/pandas/tests/io/test_sql.pyÚteardown_methodÔ   s    
zMixInBase.teardown_methodN)Ú__name__Ú
__module__Ú__qualname__r8   r6   r6   r6   r7   r-   Ó   s   r-   c               @   s$   e Zd Zdd„ Zdd„ Zdd„ ZdS )Ú
MySQLMixInc             C   s.   | j jƒ }|jdtj|ƒ› �ƒ | j jƒ  d S )NzDROP TABLE IF EXISTS )r.   ÚcursorÚexecuteÚsqlZ_get_valid_mysql_nameÚcommit)r3   Ú
table_nameÚcurr6   r6   r7   r1   Ý   s    
zMySQLMixIn.drop_tablec             C   s&   | j jƒ }|jdƒ dd„ |jƒ D ƒS )NzSHOW TABLESc             S   s   g | ]}|d  ‘qS )r   r6   )Ú.0Útabler6   r6   r7   ú
<listcomp>å   s    z.MySQLMixIn._get_all_tables.<locals>.<listcomp>)r.   r=   r>   Úfetchall)r3   rB   r6   r6   r7   r0   â   s    

zMySQLMixIn._get_all_tablesc             C   s4   ddl m} y| jjƒ  W n |k
r.   Y nX d S )Nr   )ÚError)Zpymysql.errrG   r.   Úclose)r3   rG   r6   r6   r7   r2   ç   s
    zMySQLMixIn._close_connN)r9   r:   r;   r1   r0   r2   r6   r6   r6   r7   r<   Ü   s   r<   c               @   s$   e Zd Zdd„ Zdd„ Zdd„ ZdS )ÚSQLiteMixInc             C   s&   | j jdtj|ƒ› �ƒ | j jƒ  d S )NzDROP TABLE IF EXISTS )r.   r>   r?   Z_get_valid_sqlite_namer@   )r3   rA   r6   r6   r7   r1   ñ   s    zSQLiteMixIn.drop_tablec             C   s   | j jdƒ}dd„ |jƒ D ƒS )Nz1SELECT name FROM sqlite_master WHERE type='table'c             S   s   g | ]}|d  ‘qS )r   r6   )rC   rD   r6   r6   r7   rE   ù   s    z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r.   r>   rF   )r3   Úcr6   r6   r7   r0   ÷   s    zSQLiteMixIn._get_all_tablesc             C   s   | j jƒ  d S )N)r.   rH   )r3   r6   r6   r7   r2   û   s    zSQLiteMixIn._close_connN)r9   r:   r;   r1   r0   r2   r6   r6   r6   r7   rI   ð   s   rI   c               @   s$   e Zd Zdd„ Zdd„ Zdd„ ZdS )ÚSQLAlchemyMixInc             C   s   t j| jƒj|ƒ d S )N)r?   ÚSQLDatabaser.   r1   )r3   rA   r6   r6   r7   r1      s    zSQLAlchemyMixIn.drop_tablec             C   s&   t jj| jd�}|jƒ  |jjƒ }|S )N)Úbind)Ú
sqlalchemyÚschemaÚMetaDatar.   ÚreflectÚtablesÚkeys)r3   ÚmetaZ
table_listr6   r6   r7   r0     s    
zSQLAlchemyMixIn._get_all_tablesc             C   s   d S )Nr6   )r3   r6   r6   r7   r2   	  s    zSQLAlchemyMixIn._close_connN)r9   r:   r;   r1   r0   r2   r6   r6   r6   r7   rK   ÿ   s   rK   c               @   sØ   e Zd ZdZdd„ Zejd6g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d7d"d#„Zd$d%„ Zd&d'„ Zd(d)„ Zd*d+„ Zd,d-„ Zd.d/„ Zd0d1„ Zd2d3„ Zd4d5„ Zd!S )8ÚPandasSQLTestzT
    Base class with common private methods for SQLAlchemy and fallback cases.

    c             C   s    t | jdƒr| jS | jjƒ S d S )Nr>   )r/   r.   r=   )r3   r6   r6   r7   Ú	_get_exec  s    zPandasSQLTest._get_execÚioÚdataÚcsvúiris.csv)Úparamsc       	      C   s¤   dd l }||jŽ }t| dƒs$| jƒ  | jdƒ | jƒ jtd | j ƒ |j	|dd d��D}t
j|ƒ}t|ƒ td | j }x|D ]}| jƒ j||ƒ q~W W d Q R X d S )Nr   r.   Úirisr%   Úr)ÚmodeÚnewliner&   )rW   Úparamr/   Úsetup_connectr1   rV   r>   ÚSQL_STRINGSÚflavorÚopenrY   ÚreaderÚnext)	r3   ÚdatapathÚrequestrW   Ziris_csv_fileZiris_csvr]   ÚinsÚrowr6   r6   r7   Úload_iris_data  s    




zPandasSQLTest.load_iris_datac             C   s&   | j dƒ | jƒ jtd | j ƒ d S )NZ	iris_viewr,   )r1   rV   r>   rb   rc   )r3   r6   r6   r7   Ú_load_iris_view-  s    
zPandasSQLTest._load_iris_viewc             C   sB   |j d j}|jd }t|tjƒs&t‚tj|j	dddddgƒ d S )Nr   gffffff@g      @gffffffö?gš™™™™™É?zIris-setosa)
ZdtypesÚtypeÚilocÚ
issubclassÚnpÚfloatingÚAssertionErrorÚtmÚequalContentsÚvalues)r3   Ú
iris_frameZpytyperj   r6   r6   r7   Ú_check_iris_loaded_frame1  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:00çNÿ\^ï?ç9\›x`|@g%Ú TO×?gá(nIŽò?ú2000-01-04 00:00:00çî}ÒCÄð?gc×^ÜV¥?g~q"B¶Ä?çˆ”æÐ4Ë?ú2000-01-05 00:00:00çGà Í¿èß?ç2rÂ¹eç?gˆŽ>Ý¦4á?çÑ‘öÙRŠõ?ú2000-01-06 00:00:00çÔ?{mXìñ?ç$ôó­ùù?çnk3¥AÓm?çØ
S«›å?)Úcolumnsç%Ú TO×¿çá(nIŽò¿)r}   r~   r   r�   rŽ   çc×^ÜV¥¿ç~q"B¶Ä¿)r€   r�   r�   r�   r‚   çˆŽ>Ý¦4á¿)rƒ   r„   r…   r‘   r†   )r‡   rˆ   r‰   rŠ   r‹   )r   Útest_frame1)r3   rŒ   rX   r6   r6   r7   Ú_load_test1_data8  s,                    zPandasSQLTest._load_test1_datac          
   C   sX   t 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jklgš™™™™™ñ?gÍÌÌÌÌÌ@gš™™™™™@g333333@FTz
1990-11-22z
1991-10-26z
1993-11-26z
1995-12-12)ry   rz   r{   r|   ÚEr™   )r   Údictr   Útest_frame2)r3   Údfr6   r6   r7   Ú_load_test2_data[  s    



zPandasSQLTest._load_test2_datac             C   s(   dddg}ddddg}t ||d�| _d S )Nrx   ry   rz   ú2000-01-03 00:00:00é   é   r•   gZØÓÍÿ?ú2000-01-04 00:00:00é   gc×^ÜV¥?ú2000-01-05 00:00:00é N  ç2rÂ¹eç?ú2000-01-06 00:00:00i3p ç$ôó­ùù?)rŒ   l        éÿÿÿçZØÓÍÿ¿)rž   r¨   r©   éãÿÿÿçc×^ÜV¥¿)r¡   rª   r«   )r£   r¤   r¥   éÍ�ûÿ)r¦   r¬   r§   )r   Útest_frame3)r3   rŒ   rX   r6   r6   r7   Ú_load_test3_datai  s    
zPandasSQLTest._load_test3_datac                sœ   | j dƒ | jƒ j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 ]*‰ | jƒ j|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ði’·2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 r6   r6   )rC   Úfield)Údr6   r7   rE   •  s    z/PandasSQLTest._load_raw_sql.<locals>.<listcomp>r#   )r1   rV   r>   rb   rc   )r3   ri   rX   r6   )r²   r7   Ú_load_raw_sqlt  s4    


zPandasSQLTest._load_raw_sqlc             C   s    | j ƒ jd|› �ƒjƒ }|d S )Nz SELECT count(*) AS count_1 FROM r   )rV   r>   Úfetchone)r3   rA   Úresultr6   r6   r7   Ú_count_rows˜  s    zPandasSQLTest._count_rowsc             C   s   | j jdƒ}| j|ƒ d S )NzSELECT * FROM iris)Ú	pandasSQLÚ
read_queryrw   )r3   rv   r6   r6   r7   Ú_read_sql_iris   s    zPandasSQLTest._read_sql_irisc             C   s4   t d | j }ddg}| jj||d�}| j|ƒ d S )Nr)   zIris-setosagffffff@)r[   )rb   rc   r·   r¸   rw   )r3   r"   r[   rv   r6   r6   r7   Ú_read_sql_iris_parameter¤  s    z&PandasSQLTest._read_sql_iris_parameterc             C   s6   t d | j }dddœ}| jj||d�}| j|ƒ d S )Nr*   zIris-setosagffffff@)ÚnameÚlength)r[   )rb   rc   r·   r¸   rw   )r3   r"   r[   rv   r6   r6   r7   Ú_read_sql_iris_named_parameterª  s    
z,PandasSQLTest._read_sql_iris_named_parameterc             C   s,   t d | j }| jj|d d�}| j|ƒ d S )Nr+   )r[   )rb   rc   r·   r¸   rw   )r3   r"   rv   r6   r6   r7   Ú(_read_sql_iris_no_parameter_with_percent°  s    z6PandasSQLTest._read_sql_iris_no_parameter_with_percentNc             C   s\   | j dƒ | jj| jd|d� | jjdƒs.t‚t| jƒ}| jdƒ}||ksNt‚| j dƒ d S )Nr’   )r4   )r1   r·   Úto_sqlr’   Ú	has_tablerr   Úlenr¶   )r3   r4   Únum_entriesÚnum_rowsr6   r6   r7   Ú_to_sqlµ  s    


zPandasSQLTest._to_sqlc             C   s(   | j dƒ | jj| jjd d… dƒ d S )Nr’   r   )r1   r·   r¿   r’   rn   )r3   r6   r6   r7   Ú_to_sql_emptyÂ  s    
zPandasSQLTest._to_sql_emptyc             C   sn   | j dƒ | jj| jddd� | jjdƒs.t‚d}tjt|d�� | jj| jddd� W d Q R X | j dƒ d S )Nr’   Úfail)Ú	if_existsz"Table 'test_frame1' already exists)Úmatch)	r1   r·   r¿   r’   rÀ   rr   ÚpytestÚraisesÚ
ValueError)r3   Úmsgr6   r6   r7   Ú_to_sql_failÆ  s    
zPandasSQLTest._to_sql_failc             C   sp   | j dƒ | jj| jddd� | jj| jddd� | jjdƒsBt‚t| jƒ}| jdƒ}||ksbt‚| j dƒ d S )Nr’   rÆ   )rÇ   Úreplace)r1   r·   r¿   r’   rÀ   rr   rÁ   r¶   )r3   rÂ   rÃ   r6   r6   r7   Ú_to_sql_replaceÒ  s    


zPandasSQLTest._to_sql_replacec             C   st   | j dƒ | jj| jddd� | jj| jddd� | jjdƒsBt‚dt| jƒ }| jdƒ}||ksft‚| j dƒ d S )Nr’   rÆ   )rÇ   ÚappendrŸ   )r1   r·   r¿   r’   rÀ   rr   rÁ   r¶   )r3   rÂ   rÃ   r6   r6   r7   Ú_to_sql_appendà  s    

zPandasSQLTest._to_sql_appendc                sz   g ‰ ‡ fdd„}| j dƒ | jj| jd|d� | jjdƒs>t‚ˆ dgksLt‚t| jƒ}| jdƒ}||kslt‚| j dƒ d S )Nc                s2   ˆj dƒ ‡ fdd„|D ƒ}|j| jjƒ |ƒ d S )Nr•   c                s   g | ]}t tˆ |ƒƒ‘qS r6   )rš   Úzip)rC   rj   )rS   r6   r7   rE   õ  s    zIPandasSQLTest._to_sql_method_callable.<locals>.sample.<locals>.<listcomp>)rÐ   r>   rD   Úinsert)Zpd_tabler.   rS   Ú	data_iterrX   )Úcheck)rS   r7   Úsampleó  s    
z5PandasSQLTest._to_sql_method_callable.<locals>.sampler’   )r4   r•   )r1   r·   r¿   r’   rÀ   rr   rÁ   r¶   )r3   rÖ   rÂ   rÃ   r6   )rÕ   r7   Ú_to_sql_method_callableð  s    


z%PandasSQLTest._to_sql_method_callablec             C   sN   | j dƒ | jj| jdƒ | jjdƒ}|jddd� d |j_tj	|| jƒ d S )NÚtest_frame_roundtripz"SELECT * FROM test_frame_roundtripÚlevel_0T)Úinplace)
r1   r·   r¿   r’   r¸   Ú	set_indexrx   r»   rs   Úassert_frame_equal)r3   rµ   r6   r6   r7   Ú
_roundtrip  s    
zPandasSQLTest._roundtripc             C   s.   | j jdƒ}|jƒ }tj|dddddgƒ d S )NzSELECT * FROM irisgffffff@g      @gffffffö?gš™™™™™É?zIris-setosa)r·   r>   r´   rs   rt   )r3   Úiris_resultsrj   r6   r6   r7   Ú_execute_sql  s    zPandasSQLTest._execute_sqlc             C   sH   t jddgddd	gdgd
�}| jj|dƒ | jdƒ}|dggksDt‚d S )Nr•   çÍÌÌÌÌÌ @Úline1rŸ   ç      ø?Úline2ry   rz   r{   )rŒ   rx   Ztest_to_sql_saves_index)r•   rà   rá   )rŸ   râ   rã   )r   Úfrom_recordsr·   r¿   Ú_get_index_columnsrr   )r3   rœ   Úix_colsr6   r6   r7   Ú_to_sql_save_index  s
    
z PandasSQLTest._to_sql_save_indexc             C   sÒ   | j jƒ �}|jdƒ W d Q R X G dd„ dtƒ}d}y,| j jƒ �}|j|ƒ |dƒ‚W d Q R X W n |k
rt   Y nX | j jdƒ}t|ƒdks’t‚| j jƒ �}|j|ƒ W d Q R X | j jdƒ}t|ƒdksÎt‚d S )	Nz'CREATE TABLE test_trans (A INT, B TEXT)c               @   s   e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionN)r9   r:   r;   r6   r6   r6   r7   ÚDummyException"  s   rè   z/INSERT INTO test_trans (A,B) VALUES (1, 'blah')ÚerrorzSELECT * FROM test_transr   r•   )r·   Zrun_transactionr>   Ú	Exceptionr¸   rÁ   rr   )r3   Útransrè   Zins_sqlÚresÚres2r6   r6   r7   Ú_transaction_test  s     
zPandasSQLTest._transaction_test)rW   rX   rY   rZ   )N)r9   r:   r;   Ú__doc__rV   rÉ   Úfixturerk   rl   rw   r“   r�   r®   r³   r¶   r¹   rº   r½   r¾   rÄ   rÅ   rÍ   rÏ   rÑ   r×   rÝ   rß   rç   rî   r6   r6   r6   r7   rU     s0   #$
rU   c            
   @   s(  e Zd ZU dZdZe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ejjd)dFdGdHdIdJdKgƒd0d1„ ƒZd2d3„ Zd4d5„ Zd6d7„ Z d8d9„ Z!d:d;„ Z"d<d=„ Z#d>d?„ Z$d@dA„ Z%dBdC„ Z&dDdE„ Z'd*S )LÚ_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   c             C   s   | j ƒ | _d S )N)Úconnectr.   )r3   r6   r6   r7   ra   P  s    z_TestSQLApi.setup_connectT)Úautousec             C   s   | j ƒ  d S )N)Úload_test_data_and_sql)r3   rk   r6   r6   r7   Úsetup_methodS  s    z_TestSQLApi.setup_methodc             C   s,   | j ƒ  | jƒ  | jƒ  | jƒ  | jƒ  d S )N)rl   r“   r�   r®   r³   )r3   r6   r6   r7   rô   W  s
    z"_TestSQLApi.load_test_data_and_sqlc             C   s   t jd| jƒ}| j|ƒ d S )NzSELECT * FROM iris)r?   r   r.   rw   )r3   rv   r6   r6   r7   Útest_read_sql_iris^  s    z_TestSQLApi.test_read_sql_irisc             C   s   t jd| jƒ}| j|ƒ d S )NzSELECT * FROM iris_view)r?   r   r.   rw   )r3   rv   r6   r6   r7   Útest_read_sql_viewb  s    z_TestSQLApi.test_read_sql_viewc             C   s(   t j| jd| jƒ t jd| jƒs$t‚d S )Nr’   )r?   r¿   r’   r.   rÀ   rr   )r3   r6   r6   r7   Útest_to_sqlf  s    z_TestSQLApi.test_to_sqlc             C   s`   t j| jd| jdd� t j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È   )	r?   r¿   r’   r.   rÀ   rr   rÉ   rÊ   rË   )r3   rÌ   r6   r6   r7   Útest_to_sql_failj  s
    z_TestSQLApi.test_to_sql_failc             C   sb   t j| jd| jdd� t j| jd| jdd� t jd| jƒs>t‚t| jƒ}| jdƒ}||ks^t‚d S )Nr­   rÆ   )rÇ   rÎ   )r?   r¿   r’   r.   rÀ   rr   rÁ   r¶   )r3   rÂ   rÃ   r6   r6   r7   Útest_to_sql_replacer  s    

z_TestSQLApi.test_to_sql_replacec             C   sf   t j| jd| jdd� t j| jd| jdd� t jd| jƒs>t‚dt| jƒ }| jdƒ}||ksbt‚d S )NÚtest_frame4rÆ   )rÇ   rÐ   rŸ   )r?   r¿   r’   r.   rÀ   rr   rÁ   r¶   )r3   rÂ   rÃ   r6   r6   r7   Útest_to_sql_append}  s    
z_TestSQLApi.test_to_sql_appendc             C   s6   t j| jd| jdd� t jd| jƒ}tj| j|ƒ d S )NZtest_frame5F)rx   zSELECT * FROM test_frame5)r?   r¿   r­   r.   Úread_sqlrs   rÜ   )r3   rµ   r6   r6   r7   Ú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jd	| jƒ}tj|j	ƒ |ƒ d S )
Né   Úint64)ÚdtypeZseries)r»   Ztest_seriesF)rx   zSELECT * FROM test_series)
r   rp   Úaranger?   r¿   r.   r   rs   rÜ   Úto_frame)r3   ÚsÚs2r6   r6   r7   Ú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jtƒ d |j_	t
j|| jƒ d S )NrØ   )Úconz"SELECT * FROM test_frame_roundtriprÙ   T)rÚ   )r?   r¿   r’   r.   r   rx   rÛ   ÚastypeÚintr»   rs   rÜ   )r3   rµ   r6   r6   r7   Útest_roundtrip•  s    
z_TestSQLApi.test_roundtripc             C   s:   t j| jd| jddd� t jd| jd�}tj|| jƒ d S )NrØ   FrŸ   )r  rx   Ú	chunksizez"SELECT * FROM test_frame_roundtrip)r  )r?   r¿   r’   r.   r   rs   rÜ   )r3   rµ   r6   r6   r7   Útest_roundtrip_chunksize   s    z$_TestSQLApi.test_roundtrip_chunksizec             C   s2   t jd| jd�}|jƒ }tj|dddddgƒ d S )NzSELECT * FROM iris)r  gffffff@g      @gffffffö?gš™™™™™É?zIris-setosa)r?   r>   r.   r´   rs   rt   )r3   rÞ   rj   r6   r6   r7   Útest_execute_sql«  s    z_TestSQLApi.test_execute_sqlc          	   C   s  t jd| jƒ}t|jjjtjƒ s&t	‚t jd| jdgd�}t|jjjtjƒsPt	‚|jj
ƒ tjddddddƒtjddddddƒgks†t	‚t jd| jdd	id�}t|jjjtjƒs²t	‚|jj
ƒ tjddddddƒtjddddddƒgksèt	‚t jd| jd
gd�}t|jjjtjƒ�st	‚|jj
ƒ tjddddddƒtjddddddƒgk�sLt	‚t jd| jd
did�}t|jjjtjƒ�szt	‚|jj
ƒ tjddddddƒtjddddddƒgk�s²t	‚t jd| jddid�}t|jjjtjƒ�sàt	‚|jj
ƒ tjdƒtjdƒgk�st	‚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)r?   r   r.   ro   r   r  rm   rp   Ú
datetime64rr   ÚtolistÚpdr   r   r   )r3   rœ   r6   r6   r7   Útest_date_parsing±  sH    z_TestSQLApi.test_date_parsingc             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  )r?   r   r.   ro   rx   r  rm   rp   r  rr   r   )r3   rœ   r6   r6   r7   Útest_date_and_indexè  s    z_TestSQLApi.test_date_and_indexc             C   sh   t tddgdd�ƒjƒ }tjtƒ� |jd| jƒ W d Q R X tj	d| jƒ}tj
|d |d jdƒƒ d S )Nz00:00:01z00:00:03Úfoo)r»   Útest_timedeltazSELECT * FROM test_timedeltar   )r   r   r  rs   Úassert_produces_warningÚUserWarningr¿   r.   r?   r   Úassert_series_equalr  )r3   rœ   rµ   r6   r6   r7   r  õ  s
    z_TestSQLApi.test_timedeltac          
   C   s@   t dddgiƒ}d}tjt|d�� |jd| jƒ W d Q R X d S )	NÚar•   y              ð?y               @zComplex datatypes not supported)rÈ   Ztest_complexy      ð?      ð?)r   rÉ   rÊ   rË   r¿   r.   )r3   rœ   rÌ   r6   r6   r7   Útest_complex_raisesþ  s    z_TestSQLApi.test_complex_raiseszindex_name,index_label,expectedNrx   Úother_labelÚ
index_namer   Ú0c             C   sT   t dtdƒiƒ}||j_d}tj|d| j|d� tj|| jƒ}|jd |ksPt	‚d S )NÚcol1r”   zSELECT * FROM test_index_labelÚtest_index_label)Úindex_labelr   )
r   Úrangerx   r»   r?   r¿   r.   r   rŒ   rr   )r3   r  r#  ÚexpectedÚ
temp_framer"   Úframer6   r6   r7   Útest_to_sql_index_label  s    z#_TestSQLApi.test_to_sql_index_labelc             C   sr  t dtdƒitjddgƒd�}tj|d| jƒ tjd	| jƒ}|jd
 dksNt	‚|jd dks`t	‚tj|d| jdddgd� tjd	| jƒ}|jd d… j
ƒ ddgks¦t	‚ddg|j_tj|d| jdd� tjd	| jƒ}|jd d… j
ƒ ddgksòt	‚tj|d| jdddgd� tjd	| jƒ}|jd d… j
ƒ 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”   ÚA0ÚA1ÚB0ÚB1)rx   r"  zSELECT * FROM test_index_labelr   rÙ   r•   Zlevel_1rÎ   ry   rz   )rÇ   r#  rŸ   )rÇ   r{   r|   zALength of 'index_label' should match number of levels, which is 2)rÈ   )r)  r*  )r+  r,  )r   r$  r
   Zfrom_productr?   r¿   r.   r   rŒ   rr   r  rx   ÚnamesrÉ   rÊ   rË   )r3   r&  r'  rÌ   r6   r6   r7   Ú"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
�}|jd| jƒ tjd| jddgd�}tj||dd� d S )Nr•   çÍÌÌÌÌÌ @rá   rŸ   ç      ø?rã   ry   rz   r{   )rŒ   rx   Útest_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtrip)r  T)Úcheck_index_type)r•   r/  rá   )rŸ   r0  rã   )r   rä   r¿   r.   r?   r   rs   rÜ   )r3   rœ   rµ   r6   r6   r7   r1  O  s    z%_TestSQLApi.test_multiindex_roundtripc             C   s4   t ddgddggddgd�}tj|d| jdd	� d S )
Nr•   rŸ   r–   r”   r   )rŒ   Ztest_frame_integer_col_namesrÎ   )rÇ   )r   r?   r¿   r.   )r3   rœ   r6   r6   r7   Ú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)r?   Ú
get_schemar’   r.   rr   )r3   Ú
create_sqlr6   r6   r7   Útest_get_schema`  s    z_TestSQLApi.test_get_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  ÚbrN   ÚINTEGERr4  r9  )r  r  r5  )r   r^   rN   ÚIntegerr?   r6  r.   rr   )r3   Zfloat_framer  r7  r6   r6   r7   Útest_get_schema_dtypesd  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Col2r4  r=  )r  rS   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")ry   rz   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   r?   r6  r.   rr   r’   )r3   r'  r7  Zconstraint_sentencer6   r6   r7   Útest_get_schema_keysm  s    z _TestSQLApi.test_get_schema_keysc             C   s$  t tjjddƒtdƒd�}|jd| jdd� tjd| jƒ}t ƒ }d	}ddddd
g}xFtjd| jdd�D ]0}t	||gdd�}t
|ƒ|| ksŽt‚|d7 }qfW tj||ƒ | 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 }qÞW tj||ƒ d S )Né   rÿ   Zabcde)rŒ   Ztest_chunksizeF)rx   zselect * from test_chunksizer   rŸ   )r  T)Úignore_indexr•   rN   )r   rp   ÚrandomZrandnÚlistr¿   r.   r?   r   r   rÁ   rr   rs   rÜ   r^   r   )r3   rœ   Úres1rí   ÚiÚsizesÚchunkÚres3r6   r6   r7   Útest_chunksize_readz  s*    z_TestSQLApi.test_chunksize_readc             C   sd   t dddgdddgdœƒ}|jƒ }|d jdƒ|d< |jd	| jd
d� tjd| jƒ}tj||ƒ d S )Nr•   rŸ   r–   zJohn P. Doez	Jane Dove)Z	person_idÚperson_namerI  ÚcategoryÚtest_categoricalF)rx   zSELECT * FROM test_categorical)	r   Úcopyr  r¿   r.   r?   r   rs   rÜ   )r3   rœ   Údf2rì   r6   r6   r7   rK  œ  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”   õ   Ã©r9  )rŒ   Ztest_unicodeF)rx   )r   r¿   r.   )r3   rœ   r6   r6   r7   Ú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jd
| jƒ}tj||ƒ d S )Nr   r•   rŸ   gš™™™™™É?gffffff@)ry   rz   zd1187b08-4943-4c8d-a7f6F)rx   z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   rp   Únanr¿   r.   r?   r   rs   rÜ   )r3   rœ   rì   r6   r6   r7   Útest_escaped_table_name²  s    z#_TestSQLApi.test_escaped_table_name)NNrx   )Nr  r  )r  Nr  )r  r  r  )r   Nr   )Nr   r   )(r9   r:   r;   rï   rc   Ústrr^   ra   rÉ   rð   rõ   rô   rö   r÷   rø   rù   rú   rü   rþ   r  r
  r  r  r  r  r  r  ÚmarkÚparametrizer(  r.  r1  r3  r8  r<  r>  rH  rK  rO  rQ  r6   r6   r6   r7   rñ   <  sN   
7	2	"rñ   zSQLAlchemy not installed)Úreasonc               @   sx   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dd„ Zdd„ Zdd„ Zdd„ ZdS )Ú
TestSQLApizŸ
    Test the public API as it would be used directly

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

    r   rN   c             C   s
   t jdƒS )Nzsqlite:///:memory:)rN   Úcreate_engine)r3   r6   r6   r7   rò   Ê  s    zTestSQLApi.connectc             C   sB   t j| jd| jƒ ddg}t jd| j|d�}|jjƒ |ks>t‚d S )NÚ
test_framery   rz   )rŒ   )r?   r¿   r’   r.   r   rŒ   r  rr   )r3   Úcolsrµ   r6   r6   r7   Útest_read_table_columnsÍ  s    z"TestSQLApi.test_read_table_columnsc             C   sª   t j| 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s�t‚|jj	ƒ ddgks¦t‚d S )	NrX  rx   )r  ry   rz   r{   r|   )r  rŒ   )
r?   r¿   r’   r.   r   rx   r-  rr   rŒ   r  )r3   rµ   r6   r6   r7   Útest_read_table_index_colÕ  s    z$TestSQLApi.test_read_table_index_colc             C   sT   t jd| jƒ}t jd| jƒ}tj||ƒ t jd| jƒ}t jd| jƒ}tj||ƒ d S )NzSELECT * FROM irisr\   )r?   r   r.   rý   rs   rÜ   r   )r3   Úiris_frame1Úiris_frame2r6   r6   r7   Útest_read_sql_delegateå  s    z!TestSQLApi.test_read_sql_delegatec          
   C   sr   d}| j j|ƒ d}| j j|ƒ tjdd��<}tjdƒ tjd| j ƒ tj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   )
r.   r>   ÚwarningsÚcatch_warningsÚsimplefilterr?   r   r   rÁ   rr   )r3   ZqryÚwr6   r6   r7   Útest_not_reflect_all_tablesî  s    
z&TestSQLApi.test_not_reflect_all_tablesc          
   C   sF   t jdd��0}t jdƒ | jjd| jƒ t|ƒdks8t‚W d Q R X d S )NT)r_  r`  ZCaseSensitiver   )ra  rb  rc  r’   r¿   r.   rÁ   rr   )r3   rd  r6   r6   r7   Ú(test_warning_case_insensitive_table_nameþ  s    
z3TestSQLApi.test_warning_case_insensitive_table_namec             C   s6   ddl m} |jj| jƒ}|jdƒ}dd„ |D ƒ}|S )Nr   )Ú
reflectionZtest_index_savedc             S   s   g | ]}|d  ‘qS )Úcolumn_namesr6   )rC   rD  r6   r6   r7   rE     s    z1TestSQLApi._get_index_columns.<locals>.<listcomp>)Úsqlalchemy.enginerg  Ú	InspectorÚfrom_enginer.   Úget_indexes)r3   Útbl_namerg  ÚinspÚixsr6   r6   r7   rå     s
    
zTestSQLApi._get_index_columnsc             C   sR   t dtddgdd�iƒ}tj| jƒ}tjd||d�}t|jjd j	t
jƒsNt‚d S )Nr   Ú201412120154Ú201412110254T)ÚutcÚ	test_type)r'  )r   r   r?   rL   r.   ZSQLTableÚ
isinstancerD   rJ   rm   ÚsqltypesÚ	TIMESTAMPrr   )r3   rœ   ÚdbrD   r6   r6   r7   Útest_sqlalchemy_type_mapping  s
    z'TestSQLApi.test_sqlalchemy_type_mappingc       
      C   sÞ   | j }tjƒ �L}d| }d}|j||ddd� tj||ƒ}tj||ƒ}d}tj||ƒ}W d Q R X tj||ƒ tj||ƒ tj||ƒ ydd l	}	t
jdƒ W n tk
r®   Y nX d	}t
jtd
d�� tjd|ƒ W d Q R X d S )Nz
sqlite:///r\   rÎ   F)rÇ   rx   zSELECT * FROM irisr   zpg8000 is installedz)postgresql+pg8000://user:pass@host/dbnameÚpg8000)rÈ   zselect * from table)r’   rs   Úensure_cleanr¿   r?   rý   r   r   rÜ   ry  rÉ   ÚskipÚImportErrorrÊ   )
r3   r’   r»   Zdb_urirD   r›   r­   r"   rû   ry  r6   r6   r7   Útest_database_uri_string  s(    
z#TestSQLApi.test_database_uri_stringc          
   C   sX   t }|jƒ }|jd||jd|jƒ|jd|jƒ|jd|jƒ|jd|jƒ|jd|jƒƒ}|S )Nr\   ÚSepalLengthZ
SepalWidthZPetalLengthZ
PetalWidthÚName)rN   rP   ZTableÚColumnÚREALÚTEXT)r3   ÚsaÚmetadatar\   r6   r6   r7   Ú_make_iris_table_metadata@  s    z$TestSQLApi._make_iris_table_metadatac             C   s>   t j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[   r  )rN   Útextr?   rý   r.   Úsetrr   )r3   Z	name_textÚiris_dfÚ	all_namesr6   r6   r7   Útest_query_by_text_objO  s    
z!TestSQLApi.test_query_by_text_objc             C   s\   | j ƒ }tj|gƒj|jjtjdƒkƒ}tj|| j	ddid�}t
|d ƒ}|dhksXt‚d S )Nr»   zIris-setosa)r[   r  )r…  rN   ÚselectÚwhererJ   r  Z	bindparamr?   rý   r.   r‡  rr   )r3   r\   Zname_selectrˆ  r‰  r6   r6   r7   Útest_query_by_select_objV  s    z#TestSQLApi.test_query_by_select_objN)r9   r:   r;   rï   rc   r^   rò   rZ  r[  r^  re  rf  rå   rx  r}  r…  rŠ  r�  r6   r6   r6   r7   rV  ¼  s   		!rV  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ƒ  | j}|jƒ }|jƒ | _tj|ƒ| _|| _	|| _d V  | jj
ƒ  | jjƒ  | j	| _tj| j	ƒ| _d S )N)Úsuperrô   r.   rò   ÚbeginZ_EngineToConnMixin__txr?   rL   r·   Z_EngineToConnMixin__engineÚrollbackrH   )r3   rk   Úenginer.   )Ú	__class__r6   r7   rõ   g  s    



z_EngineToConnMixin.setup_method)r9   r:   r;   rï   rÉ   rð   rõ   Ú__classcell__r6   r6   )r“  r7   rŽ  b  s   rŽ  c               @   s   e Zd ZdS )ÚTestSQLApiConnN)r9   r:   r;   r6   r6   r6   r7   r•  y  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   Úfallbackú:memory:c             C   s
   t j|ƒS )N)Úsqlite3rò   )r3   Zdatabaser6   r6   r7   rò   ˆ  s    zTestSQLiteFallbackApi.connectc             C   sj   t jƒ �J}| j|ƒ}tj| jd|dd� |jƒ  | j|ƒ}tjd|ƒ}|jƒ  W d Q R X t j| j|ƒ d S )NÚtest_frame3_legacyF)rx   z!SELECT * FROM test_frame3_legacy;)	rs   rz  rò   r?   r¿   r­   rH   r   rÜ   )r3   r»   r.   rµ   r6   r6   r7   Útest_sql_open_close‹  s    


z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installed)rU  c          
   C   s2   d}d}t jt|d�� tjd|ƒ W d Q R X d S )Nz&mysql://root@localhost/pandas_nosetestz-Using URI string without sqlalchemy installed)rÈ   zSELECT * FROM iris)rÉ   rÊ   r|  r?   rý   )r3   r.   rÌ   r6   r6   r7   Útest_con_string_import_error›  s    z2TestSQLiteFallbackApi.test_con_string_import_errorc          
   C   sZ   t jd| jƒ}t jd| jƒ}tj||ƒ d}tjt j|d�� t jd| jƒ W d Q R X d S )NzSELECT * FROM irisz9Execution failed on sql 'iris': near "iris": syntax error)rÈ   r\   )	r?   r   r.   rý   rs   rÜ   rÉ   rÊ   ZDatabaseError)r3   r\  r]  rÌ   r6   r6   r7   r^  ¢  s    z,TestSQLiteFallbackApi.test_read_sql_delegatec             C   sH   t ddgddggddgd�}tjƒ � tj|d| jd	d
� W d Q R X d S )Nr•   rŸ   r–   r”   r  zb )rŒ   rš  F)rx   )r   rs   r  r?   r¿   r.   )r3   rœ   r6   r6   r7   Útest_safe_names_warning«  s    
z-TestSQLiteFallbackApi.test_safe_names_warningc             C   s   t j| jdƒ}d|kst‚d S )Nr4  r5  )r?   r6  r’   rr   )r3   r7  r6   r6   r7   Útest_get_schema2²  s    z&TestSQLiteFallbackApi.test_get_schema2c             C   sJ   x4|j dƒD ]&}|j ƒ d jdƒ|kr|j ƒ d S qW td|› d�ƒ‚d S )NÚ
r   z""r•   zColumn z
 not found)ÚsplitÚstriprË   )r3   rO   ÚcolumnÚcolr6   r6   r7   Ú_get_sqlite_column_type·  s    z-TestSQLiteFallbackApi._get_sqlite_column_typec             C   sT   t dtddgdd�iƒ}tj| jƒ}tjd||d�}|jƒ }| j|dƒdksPt‚d S )	Nr   rp  rq  T)rr  rs  )r'  rv  )	r   r   r?   ÚSQLiteDatabaser.   ZSQLiteTableZ
sql_schemar¤  rr   )r3   rœ   rw  rD   rO   r6   r6   r7   Útest_sqlite_type_mapping¾  s    z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r˜  )r9   r:   r;   rï   rc   r^   rò   r›  rÉ   rS  ÚskipifÚSQLALCHEMY_INSTALLEDrœ  r^  r�  rž  r¤  r¦  r6   r6   r6   r7   r–  ~  s   
	r–  c               @   s   e Zd ZU dZe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.d/„ Zd0d1„ Zd2d3„ Zd4d5„ Z d6d7„ Z!d8d9„ Z"d:d;„ Z#d<d=„ Z$d>d?„ Z%d@dA„ Z&dBdC„ Z'dDdE„ Z(dFdG„ Z)dHdI„ Z*dJdK„ Z+dLdM„ Z,dNdO„ Z-dPdQ„ Z.dRdS„ Z/dTdU„ Z0dVdW„ Z1dXdY„ Z2dZd[„ Z3d\d]„ Z4d^d_„ Z5d`da„ Z6dbdc„ Z7ddde„ Z8ej9j:dfdge;j<gidge;j< gie;j< gdhgdiœgƒdjdk„ ƒZ=dldm„ Z>dnS )oÚ_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.

    TÚclass)ró   Úscopec             C   s$   | j ƒ  | jƒ  | jƒ }|jƒ  d S )N)Úsetup_importÚsetup_driverrò   )Úclsr.   r6   r6   r7   Úsetup_classÙ  s    z_TestSQLAlchemy.setup_classc             C   s   | j ƒ  | jƒ  d S )N)r³   r“   )r3   r6   r6   r7   rô   à  s    z&_TestSQLAlchemy.load_test_data_and_sql)ró   c             C   s   | j ƒ  d S )N)rô   )r3   rk   r6   r6   r7   rõ   ä  s    z_TestSQLAlchemy.setup_methodc             C   s   t stjdƒ d S )NzSQLAlchemy not installed)r¨  rÉ   r{  )r®  r6   r6   r7   r¬  è  s    z_TestSQLAlchemy.setup_importc             C   s
   t ƒ ‚d S )N)ÚNotImplementedError)r®  r6   r6   r7   r­  î  s    z_TestSQLAlchemy.setup_driverc             C   s
   t ƒ ‚d S )N)r°  )r®  r6   r6   r7   rò   ò  s    z_TestSQLAlchemy.connectc             C   sX   y&| j ƒ | _tj| jƒ| _| jj ƒ  W n, tjjk
rR   tj	d| j
› d�ƒ Y nX d S )NzCan't connect to z server)rò   r.   r?   rL   r·   rN   ÚexcZOperationalErrorrÉ   r{  rc   )r3   r6   r6   r7   ra   ö  s    
z_TestSQLAlchemy.setup_connectc             C   s   | j ƒ  d S )N)r¹   )r3   r6   r6   r7   Útest_read_sqlÿ  s    z_TestSQLAlchemy.test_read_sqlc             C   s   | j ƒ  d S )N)rº   )r3   r6   r6   r7   Útest_read_sql_parameter  s    z'_TestSQLAlchemy.test_read_sql_parameterc             C   s   | j ƒ  d S )N)r½   )r3   r6   r6   r7   Útest_read_sql_named_parameter  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc             C   s   | j ƒ  d S )N)rÄ   )r3   r6   r6   r7   rø     s    z_TestSQLAlchemy.test_to_sqlc             C   s   | j ƒ  d S )N)rÅ   )r3   r6   r6   r7   Útest_to_sql_empty  s    z!_TestSQLAlchemy.test_to_sql_emptyc             C   s   | j ƒ  d S )N)rÍ   )r3   r6   r6   r7   rù     s    z _TestSQLAlchemy.test_to_sql_failc             C   s   | j ƒ  d S )N)rÏ   )r3   r6   r6   r7   rú     s    z#_TestSQLAlchemy.test_to_sql_replacec             C   s   | j ƒ  d S )N)rÑ   )r3   r6   r6   r7   rü     s    z"_TestSQLAlchemy.test_to_sql_appendc             C   s   | j dd� d S )NÚmulti)r4   )rÄ   )r3   r6   r6   r7   Útest_to_sql_method_multi  s    z(_TestSQLAlchemy.test_to_sql_method_multic             C   s   | j ƒ  d S )N)r×   )r3   r6   r6   r7   Útest_to_sql_method_callable  s    z+_TestSQLAlchemy.test_to_sql_method_callablec             C   sN   | j ƒ }tddddgddddgdœƒ}tj|ƒ}|j|dƒ |jdƒsJt‚d S )Ng      ð?g       @g      @g      @)ÚoneÚtwor&  )rò   r   r?   rL   r¿   rÀ   rr   )r3   Ú	temp_connr&  r·   r6   r6   r7   Útest_create_table  s    
z!_TestSQLAlchemy.test_create_tablec             C   sh   | j ƒ }tddddgddddgdœƒ}tj|ƒ}|j|dƒ |jdƒsJt‚|jdƒ |jdƒ sdt‚d S )Ng      ð?g       @g      @g      @)r¹  rº  r&  )rò   r   r?   rL   r¿   rÀ   rr   r1   )r3   r»  r&  r·   r6   r6   r7   Útest_drop_table(  s    

z_TestSQLAlchemy.test_drop_tablec             C   s   | j ƒ  d S )N)rÝ   )r3   r6   r6   r7   r
  8  s    z_TestSQLAlchemy.test_roundtripc             C   s   | j ƒ  d S )N)rß   )r3   r6   r6   r7   r  ;  s    z _TestSQLAlchemy.test_execute_sqlc             C   s   t jd| jd�}| j|ƒ d S )Nr\   )r  )r?   r   r.   rw   )r3   rv   r6   r6   r7   Útest_read_table>  s    z_TestSQLAlchemy.test_read_tablec             C   s.   t jd| jddgd�}tj|jjddgƒ d S )Nr\   r~  )r  rŒ   )r?   r   r.   rs   rt   rŒ   ru   )r3   rv   r6   r6   r7   rZ  B  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Ë   r?   r   r.   )r3   rÌ   r6   r6   r7   Útest_read_table_absent_raisesH  s    z-_TestSQLAlchemy.test_read_table_absent_raisesc             C   s~   t j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¯   )r?   r   r.   ro   r   r  rm   rp   rq   rr   r   Úintegerr   Zbool_r    r!   Úobject)r3   rœ   r6   r6   r7   Útest_default_type_conversionM  s    z,_TestSQLAlchemy.test_default_type_conversionc             C   s@   t ddgid�}|jd| jdd� tjd| jƒ}tj||ƒ d S )	NÚi64rŸ   é>   )rX   Útest_bigintF)rx   l            )r   r¿   r.   r?   r   rs   rÜ   )r3   rœ   rµ   r6   r6   r7   rÅ  Y  s    z_TestSQLAlchemy.test_bigintc             C   s(   t jd| jƒ}t|jjjtjƒs$t	‚d S )Nr¯   )
r?   r   r.   ro   r   r  rm   rp   r  rr   )r3   rœ   r6   r6   r7   Útest_default_date_loada  s    z&_TestSQLAlchemy.test_default_date_loadc             C   s0  dd„ }t jd| jƒ}t|dƒs*tjdƒ |j}t|jƒs>t	‚t jd| jdgd�}t|dƒsftjdƒ |j}t|jƒszt	‚t
|jjƒdksŽt	‚||jƒ t jtt jd| jdd	�ƒd
d�}|j}t|jƒsÌt	‚t
|jjƒdksàt	‚tjd| jƒ}|j}t|jƒ�st	‚tj|j|jƒ tjd| jƒ}||jƒ d S )Nc             S   sš   t | jƒr4| d tdƒkst‚| d tdƒks–t‚nbt| jƒr†t| jjƒdksRt‚tddd�tddd�g}t|| j	d�}t
j| |ƒ 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   rr   r   rR  ÚdtrÈ  r   r»   rs   r  )r£  Zexpected_datar%  r6   r6   r7   rÕ   m  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)r@  r¯   )r  r   r.   r/   rÉ   r{  r$   r   r  rr   rR  rÉ  rÈ  r   rB  r?   r   rs   r  )r3   rÕ   rœ   r£  r%  r6   r6   r7   Útest_datetime_with_timezoneh  s8    "




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jd
ƒ|d< n|d jjd ƒ|d< tj	d| jƒ}t
j||ƒ tjd| jƒ}| jdkr²t|jd tƒs¢t‚t|d ƒ|d< t
j||ƒ d S )Nry   z2013-01-01 09:00:00r–   z
US/Pacific)ÚperiodsrÈ  Ztest_datetime_tzF)rx   r   rÇ  zSELECT * FROM test_datetime_tzr   r   )r   ry   )r   r   r¿   r.   rc   rÉ  Z
tz_convertZtz_localizer?   r   rs   rÜ   r   rt  ÚlocrR  rr   r   )r3   r%  rµ   r6   r6   r7   Ú%test_datetime_with_timezone_roundtrip¸  s    

z5_TestSQLAlchemy.test_datetime_with_timezone_roundtripc             C   s`   t jdtdddƒidgd�}|jd| jdd� tjd| jƒ}t jt jgdgd�}tj	||ƒ d S )	Nr   i'  r•   r   )rx   Ztest_datetime_obbF)rŒ   )
r  r   r   r¿   r.   r?   r   ZNaTrs   rÜ   )r3   rX   rµ   r%  r6   r6   r7   Útest_out_of_bounds_datetimeÓ  s
    z+_TestSQLAlchemy.test_out_of_bounds_datetimec             C   s`   t dddd�j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)rx   Z	foo_tableZ	info_date)r#  )r  F)Zcheck_names)
r   Z
_with_freqr   r$  r¿   r.   r?   r   rs   rÜ   )r3   Údatesr%  rµ   r6   r6   r7   Ú"test_naive_datetimeindex_roundtripÛ  s
    z2_TestSQLAlchemy.test_naive_datetimeindex_roundtripc             C   sJ  t j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ƒsŒt‚t jd| jdddiid�}t|jj	j
tjƒs¼t‚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ƒ�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)r?   r   r.   rc   rÁ  rp   r  ro   r   r  rm   rr   r   )r3   rœ   Zexpected_typer6   r6   r7   r  å  s,    z!_TestSQLAlchemy.test_date_parsingc             C   sº   t tddd�tjdƒdœƒ}|jd| jƒ tjd| jƒ}|jddd	�}t	j
||ƒ tjd
| jƒ}|jddd	�}| jdkrªt|jd tƒsŒt‚t|d ƒ|d< t	j
||ƒ nt	j
||ƒ d S )Nz2013-01-01 09:00:00r–   )rË  g      @)ry   rz   Útest_datetimerx   r•   )ZaxiszSELECT * FROM test_datetimer   r   ry   )r   ry   )r   r   rp   r  r¿   r.   r?   r   Zdroprs   rÜ   r   rc   rt  rÌ  rR  rr   r   )r3   rœ   rµ   r6   r6   r7   rÕ  	  s    
z_TestSQLAlchemy.test_datetimec             C   s²   t tddd�tjdƒdœƒ}tj|jd< |jd| jd	d
� tj	d| jƒ}t
j||ƒ tjd| jƒ}| jdkr¢t|jd tƒs€t‚t|d dd�|d< t
j||ƒ nt
j||ƒ d S )Nz2013-01-01 09:00:00r–   )rË  g      @)ry   rz   r•   ry   rÕ  F)rx   zSELECT * FROM test_datetimer   r   Zcoerce)Úerrors)r•   ry   )r   ry   )r   r   rp   r  rP  rÌ  r¿   r.   r?   r   rs   rÜ   r   rc   rt  rR  rr   r   )r3   rœ   rµ   r6   r6   r7   Ú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j||ƒ d S )	NiÞ  r•   rŸ   r  )rŒ   Ú	test_dateF)rx   )r   r   r¿   r.   r   r   rs   r  )r3   rœ   rì   rµ   r%  r6   r6   r7   Útest_datetime_date2  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j||ƒ tjƒ }t	j|d
|dd	� t	j
d|ƒ}|jdd„ ƒ}tj||ƒ t	j|d| jdd	� | jdkrÒt	j
d| jƒ}|jdd„ ƒ}tj||ƒ t	jd| jƒ}tj||ƒ d S )Né	   r   r•   é   r  )rŒ   Ú	test_timeF)rx   Z
test_time2zSELECT * FROM test_time2c             S   s
   | j dƒS )Nz%H:%M:%S.%f)Ústrftime)Ú_r6   r6   r7   Ú<lambda>H  s    z4_TestSQLAlchemy.test_datetime_time.<locals>.<lambda>Z
test_time3r   zSELECT * FROM test_time3c             S   s
   | j dƒS )Nz%H:%M:%S.%f)rÝ  )rÞ  r6   r6   r7   rß  N  s    )r   r   r¿   r.   r   rs   rÜ   ÚTestSQLiteFallbackrò   r?   r   Úapplymaprc   )r3   rœ   rì   Zsqlite_connÚrefr6   r6   r7   Ú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jd| jƒ}t	j
||dd
d� d S )NrŸ   r  r•   )r  g        )Ús1r  Ztest_read_writeF)rx   T)Zcheck_dtypeZcheck_exacti   i  )r   rp   Úint32Úfloat32r   r¿   r.   r?   r   rs   rÜ   )r3   rä  r  rœ   rM  r6   r6   r7   Útest_mixed_dtype_insertS  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jd| jƒ}tj||ƒ tj	d
| jƒ}tj||ƒ d S )Nr   r•   rŸ   gš™™™™™É?gffffff@)ry   rz   Útest_nanF)rx   zSELECT * FROM test_nan)
r   rp   rP  r¿   r.   r?   r   rs   rÜ   r   )r3   rœ   rµ   r6   r6   r7   Ú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jd| jƒ}tj||ƒ |d j	d	ƒ|d< d |d< tj
d
| jƒ}tj||ƒ d S )Nr   r•   rŸ   )ry   rz   rè  F)rx   rz   rÁ  zSELECT * FROM test_nan)r   rp   rP  r¿   r.   r?   r   rs   rÜ   r  r   )r3   rœ   rµ   r6   r6   r7   Útest_nan_fullcolumnl  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jd| jƒ}tj	||ƒ tj
d| jƒ}tj	||ƒ d S )Nr   r•   rŸ   r  r9  )ry   rz   rè  F)rx   rz   zSELECT * FROM test_nan)rŸ   rz   )r   rp   rP  r¿   r.   rÌ  r?   r   rs   rÜ   r   )r3   rœ   rµ   r6   r6   r7   Útest_nan_string{  s    
z_TestSQLAlchemy.test_nan_stringc             C   s6   ddl m} |jj| jƒ}|j|ƒ}dd„ |D ƒ}|S )Nr   )rg  c             S   s   g | ]}|d  ‘qS )rh  r6   )rC   rD  r6   r6   r7   rE   �  s    z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)ri  rg  rj  rk  r.   rl  )r3   rm  rg  rn  ro  r6   r6   r7   rå   ‹  s
    
z"_TestSQLAlchemy._get_index_columnsc             C   s   | j ƒ  d S )N)rç   )r3   r6   r6   r7   Útest_to_sql_save_index“  s    z&_TestSQLAlchemy.test_to_sql_save_indexc             C   s   | j ƒ  d S )N)rî   )r3   r6   r6   r7   Útest_transactions–  s    z!_TestSQLAlchemy.test_transactionsc             C   sr   | j ƒ  d}tj| j|| jd�}| jjd d… }| j|ƒ | jj|ƒ tj|| jƒ}t	j
||dd� | j|ƒ d S )NÚtest_get_schema_create_table)r  r   F)r2  )r®   r?   r6  r­   r.   rn   r1   r>   r   rs   rÜ   )r3   r5   r7  Zblank_test_dfÚreturned_dfr6   r6   r7   rî  ™  s    
z,_TestSQLAlchemy.test_get_schema_create_tablec       	      C   s~  ddg}ddg}t ||d�}|jd| jƒ |jd| jdtjid	� tjj| jd
�}|jƒ  |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jdƒid	� |jƒ  |jd j	d j
}t|tjƒsöt‚|jdk�st‚|jd| jtjd	� tjj| jd
�}|jƒ  |jd j	d j
}|jd j	d j
}t|tjƒ�sht‚t|tjƒ�szt‚d S )Nry   rz   çš™™™™™é?TçÍÌÌÌÌÌì?)rŒ   Ú
dtype_testÚdtype_test2)r  )rM   z&The type of B is not a SQLAlchemy type)rÈ   ré   Zdtype_test3é
   Úsingle_dtype_test)rð  T)rñ  N)r   r¿   r.   rN   r‚  rO   rP   rQ   rR   rŒ   rm   rt  rr   rÉ   rÊ   rË   rR  ÚStringr¼   )	r3   rY  rX   rœ   rT   ZsqltyperÌ   ZsqltypeaZsqltypebr6   r6   r7   Ú
test_dtype©  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|| jƒ tj|| jƒ}tjj	| jd
�}|j
ƒ  | jdkrŒtj}ntj}|j| j}t|d j|ƒs²t‚t|d jtjƒsÈt‚t|d jtjƒsÞt‚t|d jtjƒsôt‚d S )NTiÜ  rÿ   r•   rÁ  )r  gš™™™™™ñ?)ÚBoolÚDateÚIntÚFloatÚnotna_dtype_test)rM   r   rø  rù  rú  rû  )r   r   r   r¿   r.   r?   r   rN   rO   rP   rQ   rc   ru  r;  ÚBooleanrR   rŒ   rt  rm   rr   ÚDateTimerû  )r3   rY  rœ   r5   rï  rT   Zmy_typeÚcol_dictr6   r6   r7   Ú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jd	| jƒ}tj	|d j
d dƒtj	|d j
d dƒksªt‚tjj| jd�}|jƒ  |jd	 j}t|d jƒt|d jƒksît‚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 )NgñYÉBÊÀó?ræ  )r  Úfloat64rÿ   rå  r   )Úf32Úf64Ú
f64_as_f32Úi32rÃ  Ztest_dtypesFrÎ   r  é   )Z	precision)rx   rÇ   r  r  r   é   )rM   r  r  rÃ  )r   r   r¿   r.   rN   rû  r?   r   rp   Úroundrn   rr   rO   rP   rQ   rR   rŒ   rR  rm   rt  ru  r;  Z
BigInteger)r3   ÚVrœ   rì   rT   rÿ  r6   r6   r7   Ú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ƒjd| jƒ || jƒ d S )Nc             S   s   d}t j|| d�S )Nz'SELECT test_foo_data FROM test_foo_data)r  )r?   r   )Ú
connectionr"   r6   r6   r7   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  rX   r6   r6   r7   Úbar  s    z;_TestSQLAlchemy.test_connectable_issue_example.<locals>.barc                sB   | j ƒ �0}|jƒ � |jˆƒ}|jˆ |ƒ W d Q R X W d Q R X d S )N)rò   r�  Zrun_callable)Zconnectabler.   Zfoo_data)r  r  r6   r7   Úmain  s    


z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr  r   r•   rŸ   )r   r¿   r.   )r3   r  r6   )r  r  r7   Útest_connectable_issue_example  s
    z._TestSQLAlchemy.test_connectable_issue_exampleÚinputr  r  )r  Zinfe0c             C   sv   t j|ƒ}| jdkrFd}tjt|d�� |jd| jdd� W d Q R X n,|jd| jdd� tj	d| jƒ}t
j||ƒ d S )Nr   zinf cannot be used with MySQL)rÈ   ZfoobarF)rx   )r  r   rc   rÉ   rÊ   rË   r¿   r.   r?   r   rs   Zassert_equal)r3   r  rœ   rÌ   rì   r6   r6   r7   Útest_to_sql_with_negative_npinf  s    

z/_TestSQLAlchemy.test_to_sql_with_negative_npinfc       	      C   s¤   d}t d|giƒ}tjƒ }G dd„ d|ƒ}tj| jd�}|ƒ }|j�J |jƒ }|jj	|ƒ |j
||d�ƒ |jƒ  tjtj|jgƒ|d�}W d Q R X tj||ƒ 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j	dƒdd�Z
d	S )
z7_TestSQLAlchemy.test_temporary_table.<locals>.TemporaryZ	temp_testÚprefixesÚ	TEMPORARYT)Zprimary_keyrÛ  F)ZnullableN)r9   r:   r;   Z__tablename__Z__table_args__rN   r€  r;  ÚidZUnicoder  r6   r6   r6   r7   Ú	Temporary/  s   
r  )rM   )r  )r?   r  )r   r   Zdeclarative_baseÚ
sa_sessionZsessionmakerr.   Ztransactionr  Z	__table__ÚcreateÚaddÚflushr?   r   rN   r‹  r  rs   rÜ   )	r3   Z	test_datar%  ZBaser  ÚSessionr   r.   rœ   r6   r6   r7   Útest_temporary_table*  s    "z$_TestSQLAlchemy.test_temporary_tableN)?r9   r:   r;   rï   rR  rc   rÉ   rð   r¯  rô   rõ   Úclassmethodr¬  r­  rò   ra   r²  r³  r´  rø   rµ  rù   rú   rü   r·  r¸  r¼  r½  r
  r  r¾  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  rS  rT  rp   Úinfr  r  r6   r6   r6   r7   r©  Î  sl   
	P
$
#4r©  c               @   s   e Zd Zdd„ ZdS )Ú_TestSQLAlchemyConnc             C   s   t jdƒ d S )Nz4Nested transactions rollbacks don't work with Pandas)rÉ   r{  )r3   r6   r6   r7   rí  B  s    z%_TestSQLAlchemyConn.test_transactionsN)r9   r:   r;   rí  r6   r6   r6   r7   r  A  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 jdƒS )Nzsqlite:///:memory:)rN   rW  )r®  r6   r6   r7   rò   N  s    z_TestSQLiteAlchemy.connectc             C   s
   d | _ d S )N)Údriver)r®  r6   r6   r7   r­  R  s    z_TestSQLiteAlchemy.setup_driverc             C   s€   t j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¯   )r?   r   r.   ro   r   r  rm   rp   rq   rr   r   rÀ  r   r    r!   )r3   rœ   r6   r6   r7   rÂ  W  s    z/_TestSQLiteAlchemy.test_default_type_conversionc             C   s*   t jd| jƒ}t|jjjtjƒ s&t	‚d S )Nr¯   )
r?   r   r.   ro   r   r  rm   rp   r  rr   )r3   rœ   r6   r6   r7   rÆ  f  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jdƒ tjd| jƒ t|ƒdks\t	‚W d Q R X d S )Nr  r•   rŸ   r   )r  Ztest_bigintwarningF)rx   T)r_  r`  r   )
r   r¿   r.   ra  rb  rc  r?   r   rÁ   rr   )r3   rœ   rd  r6   r6   r7   Útest_bigint_warningl  s    
z&_TestSQLiteAlchemy.test_bigint_warningN)r9   r:   r;   rï   rc   r  rò   r­  rÂ  rÆ  r"  r6   r6   r6   r7   r   F  s   r   c               @   s<   e 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   c             C   s   t jd| j› d�| jd�S )Nzmysql+z!://root@localhost/pandas_nosetest)Úconnect_args)rN   rW  r!  r$  )r®  r6   r6   r7   rò     s    z_TestMySQLAlchemy.connectc             C   s$   t jdƒ}d| _d|jjji| _d S )NÚpymysqlZclient_flag)rÉ   Úimportorskipr!  Ú	constantsZCLIENTZMULTI_STATEMENTSr$  )r®  r%  r6   r6   r7   r­  †  s    
z_TestMySQLAlchemy.setup_driverc             C   s€   t j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¯   )r?   r   r.   ro   r   r  rm   rp   rq   rr   r   rÀ  r   r    r!   )r3   rœ   r6   r6   r7   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jƒ }|jƒ }y|j|ƒ}|jƒ  W n  |jk
r€   |j	ƒ  ‚ Y nX t
jd| jƒ}tj||ƒ t
jd| jƒ}tj||ƒ d S )Nr   r•   rŸ   r–   gš™™™™™¹?gš™™™™™É?g333333Ó?)r  r9  Ztest_procedureF)rx   z‘DROP PROCEDURE IF EXISTS get_testdb;

        CREATE PROCEDURE get_testdb ()

        BEGIN
            SELECT * FROM test_procedure;
        ENDzCALL get_testdb();)r%  r   r¿   r.   rò   r�  r>   r@   rG   r‘  r?   r   rs   rÜ   rý   )	r3   r%  rœ   Úprocr  rë   Zr1rC  rí   r6   r6   r7   Útest_read_procedure›  s     

z%_TestMySQLAlchemy.test_read_procedureN)
r9   r:   r;   rï   rc   r  rò   r­  rÂ  r)  r6   r6   r6   r7   r#  w  s   r#  c               @   s<   e 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   c             C   s   t jd| j› d�ƒS )Nzpostgresql+z%://postgres@localhost/pandas_nosetest)rN   rW  r!  )r®  r6   r6   r7   rò   Ä  s    z_TestPostgreSQLAlchemy.connectc             C   s   t jdƒ d| _d S )NZpsycopg2)rÉ   r&  r!  )r®  r6   r6   r7   r­  Ê  s    
z#_TestPostgreSQLAlchemy.setup_driverc             C   s&  t ddgddgddgdœƒ}| jjdƒ | jjd	ƒ |jd
| jdd� |jd| jddd� |jd| jddd� tjd
| jƒ}tj||ƒ tjd| jƒ}tj||ƒ tjd| jdd�}tj||ƒ tjd| jdd�}tj||ƒ d}tj	t
|d�� tjd| jdd� W d Q R X | jjdƒ | j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jt||gdd�|ƒ t| jtjjƒ�r"| jƒ }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�}|
jdƒ}tj||ƒ 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)rx   Ztest_schema_public_explicitÚpublic)rx   rO   Ztest_schema_otherÚother)rO   z!Table test_schema_other not found)rÈ   )rO   rx   rÎ   )rO   rx   rÇ   rÐ   T)r@  )rT   Ztest_schema_other2)rx   rÇ   )r   r.   r>   r¿   r?   r   rs   rÜ   rÉ   rÊ   rË   r   rt  rN   r’  ZEnginerò   rP   rL   Z
read_table)r3   rœ   rC  rí   rG  Zres4rÌ   rì   Zengine2rT   Zpdsqlr6   r6   r7   Ú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jd
| jƒ}tj||ƒ d S )Nc             S   s–   |j }|jƒ �~}tƒ }tj|ƒ}|j|ƒ |jdƒ dj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 )ú"Nr6   )rC   Úkr6   r6   r7   ú	<genexpr>#  s    zl_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_method.<locals>.psql_insert_copy.<locals>.<genexpr>Ú.zCOPY z (z) FROM STDIN WITH CSV)r?   Úfile)r  r=   r   rY   ÚwriterÚ	writerowsÚseekÚjoinrO   r»   Zcopy_expert)rD   r.   rS   rÔ   Z
dbapi_connrB   Zs_bufr6  rŒ   rA   Z	sql_queryr6   r6   r7   Ú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)rx   r4   )r   r¿   r.   r?   r   rs   rÜ   )r3   r:  r%  rµ   r6   r6   r7   Ú(test_copy_from_callable_insertion_method  s    z?_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_methodN)
r9   r:   r;   rï   rc   r  rò   r­  r0  r;  r6   r6   r6   r7   r*  ¼  s   Gr*  c               @   s   e Zd ZdS )ÚTestMySQLAlchemyN)r9   r:   r;   r6   r6   r6   r7   r<  4  s   r<  c               @   s   e Zd ZdS )ÚTestMySQLAlchemyConnN)r9   r:   r;   r6   r6   r6   r7   r=  :  s   r=  c               @   s   e Zd ZdS )ÚTestPostgreSQLAlchemyN)r9   r:   r;   r6   r6   r6   r7   r>  @  s   r>  c               @   s   e Zd ZdS )ÚTestPostgreSQLAlchemyConnN)r9   r:   r;   r6   r6   r6   r7   r?  F  s   r?  c               @   s   e Zd ZdS )ÚTestSQLiteAlchemyN)r9   r:   r;   r6   r6   r6   r7   r@  L  s   r@  c               @   s   e Zd ZdS )ÚTestSQLiteAlchemyConnN)r9   r:   r;   r6   r6   r6   r7   rA  Q  s   rA  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 jdƒS )Nz:memory:)r™  rò   )r®  r6   r6   r7   rò   c  s    zTestSQLiteFallback.connectc             C   s   | j ƒ | _d S )N)rò   r.   )r3   r6   r6   r7   ra   g  s    z TestSQLiteFallback.setup_connectc             C   s   t j| jƒ| _| jƒ  d S )N)r?   r¥  r.   r·   r“   )r3   r6   r6   r7   rô   j  s    z)TestSQLiteFallback.load_test_data_and_sqlT)ró   c             C   s   | j ƒ  d S )N)rô   )r3   rk   r6   r6   r7   rõ   n  s    zTestSQLiteFallback.setup_methodc             C   s   | j ƒ  d S )N)r¹   )r3   r6   r6   r7   r²  r  s    z TestSQLiteFallback.test_read_sqlc             C   s   | j ƒ  d S )N)rº   )r3   r6   r6   r7   r³  u  s    z*TestSQLiteFallback.test_read_sql_parameterc             C   s   | j ƒ  d S )N)r½   )r3   r6   r6   r7   r´  x  s    z0TestSQLiteFallback.test_read_sql_named_parameterc             C   s   | j ƒ  d S )N)rÄ   )r3   r6   r6   r7   rø   {  s    zTestSQLiteFallback.test_to_sqlc             C   s   | j ƒ  d S )N)rÅ   )r3   r6   r6   r7   rµ  ~  s    z$TestSQLiteFallback.test_to_sql_emptyc             C   s   | j ƒ  d S )N)rÍ   )r3   r6   r6   r7   rù   �  s    z#TestSQLiteFallback.test_to_sql_failc             C   s   | j ƒ  d S )N)rÏ   )r3   r6   r6   r7   rú   „  s    z&TestSQLiteFallback.test_to_sql_replacec             C   s   | j ƒ  d S )N)rÑ   )r3   r6   r6   r7   rü   ‡  s    z%TestSQLiteFallback.test_to_sql_appendc             C   s   | j dd� d S )Nr¶  )r4   )rÄ   )r3   r6   r6   r7   r·  Š  s    z+TestSQLiteFallback.test_to_sql_method_multic             C   s^   t ddddgddddgdœƒ}| jj|dƒ | jjdƒs<t‚| jjdƒ | jjdƒ sZt‚d S )Ng      ð?g       @g      @g      @)r¹  rº  Zdrop_test_frame)r   r·   r¿   rÀ   rr   r1   )r3   r&  r6   r6   r7   Útest_create_and_drop_tableŽ  s    z-TestSQLiteFallback.test_create_and_drop_tablec             C   s   | j ƒ  d S )N)rÝ   )r3   r6   r6   r7   r
  ›  s    z!TestSQLiteFallback.test_roundtripc             C   s   | j ƒ  d S )N)rß   )r3   r6   r6   r7   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j||jt	ƒƒ n| jdkrttj||ƒ d S )NiÞ  r•   rŸ   r  )rŒ   rØ  F)rx   zSELECT * FROM test_dater   r   )
r   r   r¿   r.   r   rc   rs   rÜ   r  rR  )r3   rœ   rì   r6   r6   r7   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|jdd„ ƒ}tj||ƒ d S )NrÚ  r   r•   rÛ  r  )rŒ   rÜ  F)rx   zSELECT * FROM test_timer   c             S   s
   | j dƒS )Nz%H:%M:%S.%f)rÝ  )rÞ  r6   r6   r7   rß  ³  s    z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>)	r   r   r¿   r.   r   rc   rá  rs   rÜ   )r3   rœ   rì   r%  r6   r6   r7   rã  ¬  s    "
z%TestSQLiteFallback.test_datetime_timec             C   sX   t jdd|› d� | jƒ}g }x4|jD ]*}t jd|› d�| jƒ}|j|jjƒ ƒ q&W |S )Nz1SELECT * FROM sqlite_master WHERE type = 'index' zAND tbl_name = 'ú'zPRAGMA index_info(ú))r?   r   r.   r»   rÐ   r  )r3   rm  ro  ræ   Zix_nameZix_infor6   r6   r7   rå   ¶  s    z%TestSQLiteFallback._get_index_columnsc             C   s   | j ƒ  d S )N)rç   )r3   r6   r6   r7   rì  Â  s    z)TestSQLiteFallback.test_to_sql_save_indexc             C   s   | j ƒ  d S )N)rî   )r3   r6   r6   r7   rí  Å  s    z$TestSQLiteFallback.test_transactionsc       
      C   sT   | j jd|› d�ƒ}x$|D ]\}}}}}}	||kr|S qW td|› d|› d�ƒ‚d S )NzPRAGMA table_info(rD  zTable z	, column z
 not found)r.   r>   rË   )
r3   rD   r¢  ZrecsZcidr»   ÚctypeZnot_nullÚdefaultÚpkr6   r6   r7   r¤  È  s
    z*TestSQLiteFallback._get_sqlite_column_typec             C   sî   | j dkrtjdƒ ddg}ddg}t||d�}|jd	| jƒ |jd
| jddid� | jd	dƒdksht‚| j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� | jddƒdksÖt‚| jddƒdksêt‚d S )Nr   zNot applicable to MySQL legacyry   rz   çš™™™™™é?TçÍÌÌÌÌÌì?)rŒ   rò  ró  ÚSTRING)r  r:  z!B \(<class 'bool'>\) not a string)rÈ   ré   rõ  )rH  T)rI  N)rc   rÉ   r{  r   r¿   r.   r¤  rr   rÊ   rË   Úbool)r3   rY  rX   rœ   rÌ   r6   r6   r7   r÷  Ï  s    

 zTestSQLiteFallback.test_dtypec             C   s¼   | j dkrtj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|| jƒ | j|dƒdks|t	‚| j|dƒdks�t	‚| j|dƒdks¤t	‚| j|dƒdks¸t	‚d S )Nr   zNot applicable to MySQL legacyTiÜ  rÿ   r•   rÁ  )r  gš™™™™™ñ?)rø  rù  rú  rû  rü  rø  r:  rù  rv  rú  rû  r�  )
rc   rÉ   r{  r   r   r   r¿   r.   r¤  rr   )r3   rY  rœ   r5   r6   r6   r7   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	�� |jd
| jƒ W d Q R X xˆtddddddddddg
ƒD ]h\}}|j|| jƒ tj|| jƒ t ddgddggd|gd�}d|d›�}|j|| jƒ tj|| jƒ qfW d S )Nr•   rŸ   r–   r”   r  r9  )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12345rN  Ztest_weird_col_namer²   )	r   rÉ   rÊ   rË   r¿   r.   Ú	enumerater?   Ztable_exists)r3   rœ   rÌ   ZndxZ
weird_namerM  Zc_tblr6   r6   r7   Útest_illegal_namesù  s*    z%TestSQLiteFallback.test_illegal_namesN)!r9   r:   r;   rï   rc   r  rò   ra   rô   rÉ   rð   rõ   r²  r³  r´  rø   rµ  rù   rú   rü   r·  rB  r
  r  rÙ  rã  rå   rì  rí  r¤  r÷  r   rN  r6   r6   r6   r7   rà  Z  s6   
rà  c             C   s
   | j dƒS )z Returns date in YYYYMMDD format.z%Y%m%d)rÝ  )rÉ  r6   r6   r7   Údate_format	  s    rO  z'{}'z{:.8f}z{:d}c             C   s   dS )NZNULLr6   )Úxr6   r6   r7   rß  (	  s    rß  z{:.10f}z'{!s}'c             G   sN   g }x<|D ]4}t |tƒr$t|ƒr$d}tt|ƒ }|j||ƒƒ q
W | t|ƒ S )z

    N)rt  Úfloatr   Ú_formattersrm   rÐ   Útuple)r?   ÚargsZprocessed_argsÚargÚ	formatterr6   r6   r7   Úformat_query.	  s    
rW  c             C   s,   t j| ||d�jƒ }|dkr dS t|ƒS dS )z#Replace removed sql.tquery function)r  rB   N)r?   r>   rF   rB  )r"   r  rB   rì   r6   r6   r7   Útquery=	  s    rX  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jdƒ| _d V  |j | _tjdƒ| _d S )Nz:memory:)Úfunctionr4   r™  rò   r.   )r3   rh   rg   r6   r6   r7   rõ   H	  s
    zTestXSQLite.setup_methodc             C   s   t jƒ }| j|ƒ d S )N)rs   ÚmakeTimeDataFrameÚ_check_roundtrip)r3   r'  r6   r6   r7   Ú
test_basicS	  s    zTestXSQLite.test_basicc       	      C   s¨   t jƒ }tj|jd	< tj|dƒ}| jjƒ }|j	|ƒ | jjƒ }d}x.|j
ƒ D ]"\}}t|f|žŽ }t||d� qLW | jjƒ  tjd| jd�}|j|_t j||dd� d S )
Nr   r4  z(INSERT INTO test VALUES (%s, %s, %s, %s))rB   zselect * from test)r  gü©ñÒMbP?)Úrtol)r   r   )rs   r[  rp   rP  rn   r?   r6  r.   r=   r>   ÚiterrowsrW  rX  r@   rý   rx   rÜ   )	r3   r'  r7  rB   ri   Úidxrj   Úfmt_sqlrµ   r6   r6   r7   Útest_write_row_by_rowW	  s    



z!TestXSQLite.test_write_row_by_rowc             C   sŒ   t jƒ }tj|dƒ}| jjƒ }|j|ƒ d}|jd }tj|| jt|ƒd� | jj	ƒ  tj
d| jƒ}|jd d… |_t j||d d… ƒ d S )Nr4  z$INSERT INTO test VALUES (?, ?, ?, ?)r   )r[   zselect * from testr•   )rs   r[  r?   r6  r.   r=   r>   rn   rS  r@   rý   rx   rÜ   )r3   r'  r7  rB   ri   rj   rµ   r6   r6   r7   Útest_executel	  s    



zTestXSQLite.test_executec             C   s¤   t jƒ }tj|dƒ}|jƒ }x>|D ]6}|jdƒ}t|ƒdkr"|d dkr"|d dks"t‚q"W t jƒ }tj|dddgd	�}|jƒ }d
|ksŒt‚| jj	ƒ }|j
|ƒ d S )Nr4  ú rŸ   r   ry   r•   ÚDATETIMErz   )rS   zPRIMARY KEY ("A", "B"))rs   r[  r?   r6  Ú
splitlinesr   rÁ   rr   r.   r=   r>   )r3   r'  r7  ÚlinesÚlÚtokensrB   r6   r6   r7   Útest_schema{	  s    


zTestXSQLite.test_schemac          
   C   s\   d}| j jƒ }|j|ƒ tjd| j ƒ tjd| j ƒ tjtƒ� tj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(INSERT INTO test VALUES("foo", "bar", 7))r.   r=   r>   r?   rÉ   rÊ   rê   )r3   r7  rB   r6   r6   r7   Útest_execute_fail‹	  s    	

zTestXSQLite.test_execute_failc             C   sX   d}| j jƒ }|j|ƒ tjd| j ƒ | j jƒ  tjtƒ� 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  )	r.   r=   r>   r?   rH   rÉ   rÊ   rê   rX  )r3   r7  rB   r6   r6   r7   Útest_execute_closed_connectionž	  s    	


z*TestXSQLite.test_execute_closed_connectionc             C   s   d S )Nr6   )r3   r6   r6   r7   Útest_na_roundtrip±	  s    zTestXSQLite.test_na_roundtripc             C   sÂ   t j|d| jdd� t jd| jƒ}|j|_|}tj||ƒ dgt|ƒ |d< |jƒ }t	t
jt|ƒƒƒd }|jƒ |d< t j|d	| jdd� t jd
| jdd�}|jƒ }||_d|j_tj||ƒ d S )NÚ
test_tableF)r»   r  rx   zselect * from test_tabler  Útxtrô  ÚIdxÚtest_table2zselect * from test_table2)r  )r?   r¿   r.   rý   rx   rs   rÜ   rÁ   rL  r	   rp   r  r»   )r3   r'  rµ   r%  Úframe2Znew_idxr6   r6   r7   r\  ´	  s    zTestXSQLite._check_roundtripc             C   s*   t dtjdƒiƒ}tj|| jddd� d S )NÚFromrÿ   ÚtestkeywordsF)r  r»   rx   )r   rp   Úonesr?   r¿   r.   )r3   rœ   r6   r6   r7   Ú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	„ |jd
ƒD ƒƒ}|dksPt‚tjd
|ƒ}tj	||ƒ d S )Nr•   rŸ   Zc0)rŒ   Úmono_dfF)r  r»   rx   c             s   s   | ]}|d  V  qdS )r   Nr6   )rC   Zmy_c0r6   r6   r7   r3  Õ	  s    z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr–   )
r   r?   r¿   r.   Úsumr>   rr   rý   rs   rÜ   )r3   rw  Zcon_xZthe_sumrµ   r6   r6   r7   Ú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� süt‚tj|ˆ j|ddd� t|ˆ jd�dddgk�s0t‚||ƒ tj|ˆ j|ddd� t|ˆ jd�d d!gk�sjt‚tj|ˆ j|ddd� t|ˆ jd�d"d#d$d%d&gk�s¢t‚||ƒ d S )'Nr•   rŸ   ry   rz   )r!  r,  r–   r”   rÿ   r{   r|   r™   Útable_if_existszSELECT * FROM c                s   ˆ j | ƒ dS )z
            Drops tables created from individual tests
            so no dependencies arise from sequential tests
            N)r1   )Útest_table_to_drop)r3   r6   r7   Ú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Ç   rx   )r  rÐ   )r•   ry   )rŸ   rz   )r–   r{   )r”   r|   )rÿ   r™   )r•   ry   )rŸ   rz   )r•   ry   )rŸ   rz   )r–   r{   )r”   r|   )rÿ   r™   )	r   rÉ   rÊ   rË   r?   r¿   r.   rX  rr   )r3   Údf_if_exists_1Údf_if_exists_2rA   Ú
sql_selectr|  rÌ   r6   )r3   r7   Útest_if_existsÜ	  sl    
zTestXSQLite.test_if_existsN)r9   r:   r;   rÉ   rð   rõ   r]  rb  rc  rj  rk  rl  rm  r\  rv  ry  r�  r6   r6   r6   r7   rY  F	  s   rY  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 jdƒ}|jddddd� y|jdd� W nZ |jk
rZ } ztd	ƒ|‚W Y d d }~X n. |jk
r† } ztd
ƒ|‚W Y d d }~X nX d S )Nr%  Ú	localhostÚrootrL  Úpandas_nosetest)ÚhostÚuserÚpasswdrw  Úpandas)Úread_default_groupz—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.z³Cannot 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ò   ÚProgrammingErrorÚRuntimeErrorrG   )r®  r%  Úerrr6   r6   r7   r¯  /
  s    
zTestXMySQL.setup_class)ró   c             C   s”   t jdƒ}|jddddd� y|jdd� W nZ |jk
rZ } ztd	ƒ|‚W Y d d }~X n. |jk
r† } ztd
ƒ|‚W Y d d }~X nX |j| _d S )Nr%  rƒ  r„  rL  r…  )r†  r‡  rˆ  rw  r‰  )rŠ  z—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.z³Cannot 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‹  rŒ  rG   rZ  r4   )r3   rh   rg   r%  r�  r6   r6   r7   rõ   C
  s    
zTestXMySQL.setup_methodc             C   s   t jƒ }| j|ƒ d S )N)rs   r[  r\  )r3   r'  r6   r6   r7   r]  Y
  s    zTestXMySQL.test_basicc       
      C   s¬   t jƒ }tj|jd
< d}tj|dƒ}| jjƒ }|j	|ƒ |j	|ƒ d}x.|j
ƒ D ]"\}}t|f|žŽ }t||d� qPW | jjƒ  tjd| jd�}	|j|	_t j|	|dd	� d S )Nr   zDROP TABLE IF EXISTS testr4  z(INSERT INTO test VALUES (%s, %s, %s, %s))rB   zselect * from test)r  gü©ñÒMbP?)r^  )r   r   )rs   r[  rp   rP  rn   r?   r6  r.   r=   r>   r_  rW  rX  r@   rý   rx   rÜ   )
r3   r'  Údrop_sqlr7  rB   ri   r`  rj   ra  rµ   r6   r6   r7   rb  ]
  s    



z TestXMySQL.test_write_row_by_rowc             C   sv   t jƒ }d|j_d}| jjƒ }|j|ƒ tj|d| jd� d}d}t	j
|| j|dd�}t|ƒ}t j|d |… |ƒ d S )Nrx   zDROP TABLE IF EXISTS testr4  )r»   r  zselect * from testrÿ   )r?   r  r  r  )rs   r[  rx   r»   r.   r=   r>   r?   r¿   r  r   rf   rÜ   )r3   r'  rŽ  rB   r"   r  Z	chunk_genZchunk_dfr6   r6   r7   Útest_chunksize_read_typer
  s    

z#TestXMySQL.test_chunksize_read_typec             C   sÀ   t jƒ }d}tj|dƒ}| jjƒ }tjƒ � tjddƒ |j	|ƒ W d Q R X |j	|ƒ d}|j
d jjƒ }tj	|| jt|ƒd� | jjƒ  tjd| jƒ}|jd d	… |_t j||d d	… ƒ d S )
NzDROP TABLE IF EXISTS testr4  ÚignorezUnknown table.*z(INSERT INTO test VALUES (%s, %s, %s, %s)r   )r[   zselect * from testr•   )rs   r[  r?   r6  r.   r=   ra  rb  Úfilterwarningsr>   rn   ru   r  rS  r@   rý   rx   rÜ   )r3   r'  rŽ  r7  rB   ri   rj   rµ   r6   r6   r7   rc  �
  s    



zTestXMySQL.test_executec             C   s²   t jƒ }tj|dƒ}|jƒ }x>|D ]6}|jdƒ}t|ƒdkr"|d dkr"|d dks"t‚q"W t jƒ }d}tj|ddd	gd
�}|jƒ }d|ks�t‚| jj	ƒ }|j
|ƒ |j
|ƒ d S )Nr4  rd  rŸ   r   ry   r•   re  zDROP TABLE IF EXISTS testrz   )rS   zPRIMARY KEY (`A`, `B`))rs   r[  r?   r6  rf  r   rÁ   rr   r.   r=   r>   )r3   r'  r7  rg  rh  ri  rŽ  rB   r6   r6   r7   rj  ”
  s    



zTestXMySQL.test_schemac          
   C   sj   d}d}| j jƒ }|j|ƒ |j|ƒ tjd| j ƒ tjd| j ƒ tjtƒ� tj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 INTO test VALUES("foo", "bar", 7))r.   r=   r>   r?   rÉ   rÊ   rê   )r3   rŽ  r7  rB   r6   r6   r7   rk  ¦
  s    	


zTestXMySQL.test_execute_failc             C   sr   d}d}| j jƒ }|j|ƒ |j|ƒ tjd| j ƒ | j jƒ  tjtƒ� td| j d� W d Q R X | j	||ƒ 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select * from test)r  )
r.   r=   r>   r?   rH   rÉ   rÊ   rê   rX  rõ   )r3   rh   rg   rŽ  r7  rB   r6   r6   r7   rl  »
  s    	



z)TestXMySQL.test_execute_closed_connectionc             C   s   d S )Nr6   )r3   r6   r6   r7   rm  Ó
  s    zTestXMySQL.test_na_roundtripc             C   s>  d}| j jƒ }tjƒ � tjddƒ |j|ƒ W d Q R X tj|d| j dd� tjd| j ƒ}|j	|_	|j	j
|j	_
|}tj||ƒ dgt|ƒ |d	< |jƒ }ttjt|ƒƒƒd
 }||d< d}| j jƒ }tjƒ � tjddƒ |j|ƒ W d Q R X tj|d| j dd� tjd| j dd�}|jƒ }||_	|j	j|j	_tj||ƒ d S )NzDROP TABLE IF EXISTS test_tabler�  zUnknown table.*rn  F)r»   r  rx   zselect * from test_tabler  ro  rô  rp  z DROP TABLE IF EXISTS test_table2rq  zselect * from test_table2)r  )r.   r=   ra  rb  r‘  r>   r?   r¿   rý   rx   r»   rs   rÜ   rÁ   rL  r	   rp   r  r-  )r3   r'  rŽ  rB   rµ   r%  rr  rx   r6   r6   r7   r\  Ö
  s4    



zTestXMySQL._check_roundtripc             C   s,   t dtjdƒiƒ}tj|| jdddd� d S )Nrs  rÿ   rt  rÎ   F)r  r»   rÇ   rx   )r   rp   ru  r?   r¿   r.   )r3   rœ   r6   r6   r7   rv  ø
  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sôt‚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�sšt‚||ƒ d S )&Nr•   rŸ   ry   rz   )r!  r,  r–   r”   rÿ   r{   r|   r™   rz  zSELECT * FROM c                s   ˆ j | ƒ dS )z
            Drops tables created from individual tests
            so no dependencies arise from sequential tests
            N)r1   )r{  )r3   r6   r7   r|    s    z+TestXMySQL.test_if_exists.<locals>.clean_upz<insert message here>)rÈ   r}  )r'  r  r»   rÇ   rÆ   F)r'  r  r»   rÇ   rx   rÎ   )r  rÐ   )r•   ry   )rŸ   rz   )r–   r{   )r”   r|   )rÿ   r™   )r•   ry   )rŸ   rz   )r•   ry   )rŸ   rz   )r–   r{   )r”   r|   )rÿ   r™   )	r   rÉ   rÊ   rË   r?   r¿   r.   rX  rr   )r3   r~  r  rA   r€  r|  r6   )r3   r7   r�  þ
  sp    
zTestXMySQL.test_if_existsN)r9   r:   r;   rÉ   rð   r¯  rõ   r]  rb  r�  rc  rj  rk  rl  rm  r\  rv  r�  r6   r6   r6   r7   r‚  )
  s   "r‚  )	r   r   r   r   r   r   r   r    r!   )	r   r   r   r   r   r   r   r    r!   )
r   r   r$   r   r   r   r   r   r    r!   )NN)Xrï   rY   r   r   r   rW   r   r™  ra  Znumpyrp   rÉ   Zpandas.core.dtypes.commonr   r   r‰  r  r   r	   r
   r   r   r   r   r   r   r   Zpandas._testingZ_testingrs   Zpandas.io.sqlr?   r   r   rN   Zsqlalchemy.extr   Zsqlalchemy.ormr   r  Zsqlalchemy.schemaZsqlalchemy.sql.sqltypesru  r¨  r|  rb   r-   r<   rI   rK   rU   rñ   rS  Zsingler§  rV  rŽ  r•  r–  r©  r  r   r#  r*  rw  r<  r=  r>  r?  r@  rA  rà  rO  rÓ  rR  Zstr_ÚbytesrQ  r	  rm   r  rK  rR  rW  rX  rY  r{  r‚  r6   r6   r6   r7   Ú<module>   s  0


                         

	  1    %

O    w1Ex


 B

	 c