3
Êb_Ú‚ ã               @   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#Z"ddl$jj%Z%ddl&m'Z' dd	l(m)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dmd œd!dnd œd"dod œdœd$d%d&dœd'd(d)dœd*d+id,œZ-G d-d.„ d.ƒZ.G d/d0„ d0e.ƒZ/G d1d2„ d2e.ƒZ0G d3d4„ d4e.ƒZ1G d5d6„ d6ƒZ2G d7d8„ d8e2ƒZ3ej4j5ej4j6e+ d9d:�G d;d<„ d<e1e3ƒƒƒZ7G d=d>„ d>ƒZ8ej4j5G d?d@„ d@e8e7ƒƒZ9ej4j5G dAdB„ dBe0e3ƒƒZ:G dCdD„ dDe1e2ƒZ;G dEdF„ dFe8e;ƒZ<G dGdH„ dHƒZ=G dIdJ„ dJƒZ>G dKdL„ dLƒZ?ej4j5ej4j@G dMdN„ dNe>e;ƒƒƒZAej4j5ej4j@G dOdP„ dPe>e<ƒƒƒZBej4j5ej4j@G dQdR„ dRe?e;ƒƒƒZCej4j5ej4j@G dSdT„ dTe?e<ƒƒƒZDej4j5G dUdV„ dVe=e;ƒƒZEej4j5G dWdX„ dXe=e<ƒƒZFej4j5G dYdZ„ dZe0e2ƒƒZGd[d\„ ZHed]jIeJd]jIe
jKd]jIeLd]jIeMd^jIeNd_jIeOdƒd`da„ e
jPdbjIeQdcjIi	ZRddde„ ZSdpdfdg„ZTej4j5G dhdi„ die0ƒƒZUej4j5ej4j@ej4jVdjd:�G dkdl„ dle/ƒƒƒƒZWdS )qa  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
                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Ú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© r5   ú:/tmp/pip-build-jkwsxyiy/pandas/pandas/tests/io/test_sql.pyÚteardown_methodÏ   s    
zMixInBase.teardown_methodN)Ú__name__Ú
__module__Ú__qualname__r7   r5   r5   r5   r6   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)r2   Ú
table_nameÚcurr5   r5   r6   r0   Ø   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   r5   )Ú.0Útabler5   r5   r6   ú
<listcomp>à   s    z.MySQLMixIn._get_all_tables.<locals>.<listcomp>)r-   r<   r=   Úfetchall)r2   rA   r5   r5   r6   r/   Ý   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.errrF   r-   Úclose)r2   rF   r5   r5   r6   r1   â   s
    zMySQLMixIn._close_connN)r8   r9   r:   r0   r/   r1   r5   r5   r5   r6   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?   )r2   r@   r5   r5   r6   r0   ì   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   r5   )rB   rC   r5   r5   r6   rD   ô   s    z/SQLiteMixIn._get_all_tables.<locals>.<listcomp>)r-   r=   rE   )r2   Úcr5   r5   r6   r/   ò   s    zSQLiteMixIn._get_all_tablesc             C   s   | j jƒ  d S )N)r-   rG   )r2   r5   r5   r6   r1   ö   s    zSQLiteMixIn._close_connN)r8   r9   r:   r0   r/   r1   r5   r5   r5   r6   rH   ë   s   rH   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-   r0   )r2   r@   r5   r5   r6   r0   û   s    zSQLAlchemyMixIn.drop_tablec             C   s&   t jj| jd�}|jƒ  |jjƒ }|S )N)Úbind)Ú
sqlalchemyÚschemaÚMetaDatar-   ÚreflectÚtablesÚkeys)r2   ÚmetaZ
table_listr5   r5   r6   r/   þ   s    
zSQLAlchemyMixIn._get_all_tablesc             C   s   d S )Nr5   )r2   r5   r5   r6   r1     s    zSQLAlchemyMixIn._close_connN)r8   r9   r:   r0   r/   r1   r5   r5   r5   r6   rJ   ú   s   rJ   c               @   sÐ   e Zd ZdZdd„ Zejd4g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d5d 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dS )6Ú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<   )r2   r5   r5   r6   Ú	_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&   )rV   Úparamr.   Úsetup_connectr0   rU   r=   ÚSQL_STRINGSÚflavorÚopenrX   ÚreaderÚnext)	r2   ÚdatapathÚrequestrV   Ziris_csv_fileZiris_csvr\   ÚinsÚrowr5   r5   r6   Úload_iris_data  s    




zPandasSQLTest.load_iris_datac             C   s&   | j dƒ | jƒ jtd | j ƒ d S )NZ	iris_viewr+   )r0   rU   r=   ra   rb   )r2   r5   r5   r6   Ú_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)r2   Ú
iris_frameZpytyperi   r5   r5   r6   Ú_check_iris_loaded_frame,  s    
z&PandasSQLTest._check_iris_loaded_framec             C   s,   dddddg}dd d"d#g}t ||d�| _d S )$NÚindexÚAÚBÚCÚDú2000-01-03 00:00: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)r2   r‹   rW   r5   r5   r6   Ú_load_test1_data3  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)rx   ry   rz   r{   ÚEr˜   )r   Údictr   Útest_frame2)r2   Údfr5   r5   r6   Ú_load_test2_dataV  s    



zPandasSQLTest._load_test2_datac             C   s(   dddg}ddddg}t ||d�| _d S )Nrw   rx   ry   ú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)r2   r‹   rW   r5   r5   r6   Ú_load_test3_datad  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 r5   r5   )rB   Úfield)Údr5   r6   rD   �  s    z/PandasSQLTest._load_raw_sql.<locals>.<listcomp>r#   )r0   rU   r=   ra   rb   )r2   rh   rW   r5   )r±   r6   Ú_load_raw_sqlo  s4    


zPandasSQLTest._load_raw_sqlc             C   s    | j ƒ jd|› �ƒjƒ }|d S )Nz SELECT count(*) AS count_1 FROM r   )rU   r=   Úfetchone)r2   r@   Úresultr5   r5   r6   Ú_count_rows“  s    zPandasSQLTest._count_rowsc             C   s   | j jdƒ}| j|ƒ d S )NzSELECT * FROM iris)Ú	pandasSQLÚ
read_queryrv   )r2   ru   r5   r5   r6   Ú_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@)rZ   )ra   rb   r¶   r·   rv   )r2   r"   rZ   ru   r5   r5   r6   Ú_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)rZ   )ra   rb   r¶   r·   rv   )r2   r"   rZ   ru   r5   r5   r6   Ú_read_sql_iris_named_parameter¥  s    
z,PandasSQLTest._read_sql_iris_named_parameterNc             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‘   )r3   )r0   r¶   Úto_sqlr‘   Ú	has_tablerq   Úlenrµ   )r2   r3   Únum_entriesÚnum_rowsr5   r5   r6   Ú_to_sql«  s    


zPandasSQLTest._to_sqlc             C   s(   | j dƒ | jj| jjd d… dƒ d S )Nr‘   r   )r0   r¶   r½   r‘   rm   )r2   r5   r5   r6   Ú_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)	r0   r¶   r½   r‘   r¾   rq   ÚpytestÚraisesÚ
ValueError)r2   Úmsgr5   r5   r6   Ú_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)r0   r¶   r½   r‘   r¾   rq   r¿   rµ   )r2   rÀ   rÁ   r5   r5   r6   Ú_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ž   )r0   r¶   r½   r‘   r¾   rq   r¿   rµ   )r2   rÀ   rÁ   r5   r5   r6   Ú_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 r5   )r™   Úzip)rB   ri   )rR   r5   r6   rD   ë  s    zIPandasSQLTest._to_sql_method_callable.<locals>.sample.<locals>.<listcomp>)rÎ   r=   rC   Úinsert)Zpd_tabler-   rR   Ú	data_iterrW   )Úcheck)rR   r6   Úsampleé  s    
z5PandasSQLTest._to_sql_method_callable.<locals>.sampler‘   )r3   r”   )r0   r¶   r½   r‘   r¾   rq   r¿   rµ   )r2   rÔ   rÀ   rÁ   r5   )rÓ   r6   Ú_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)
r0   r¶   r½   r‘   r·   Ú	set_indexrw   rº   rr   Úassert_frame_equal)r2   r´   r5   r5   r6   Ú
_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³   rr   rs   )r2   Úiris_resultsri   r5   r5   r6   Ú_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ž   ç      ø?Úline2rx   ry   rz   )r‹   rw   Ztest_to_sql_saves_index)r”   rÞ   rß   )rž   rà   rá   )r   Úfrom_recordsr¶   r½   Ú_get_index_columnsrq   )r2   r›   Úix_colsr5   r5   r6   Ú_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)r8   r9   r:   r5   r5   r5   r6   Ú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¿   rq   )r2   Útransræ   Zins_sqlÚresÚres2r5   r5   r6   Ú_transaction_test  s     
zPandasSQLTest._transaction_test)rV   rW   rX   rY   )N)r8   r9   r:   Ú__doc__rU   rÇ   Úfixturerj   rk   rv   r’   rœ   r­   r²   rµ   r¸   r¹   r¼   rÂ   rÃ   rË   rÍ   rÏ   rÕ   rÛ   rÝ   rå   rì   r5   r5   r5   r6   rT     s.   #$
rT   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-   )r2   r5   r5   r6   r`   F  s    z_TestSQLApi.setup_connectT)Úautousec             C   s   | j ƒ  d S )N)Úload_test_data_and_sql)r2   rj   r5   r5   r6   Úsetup_methodI  s    z_TestSQLApi.setup_methodc             C   s,   | j ƒ  | jƒ  | jƒ  | jƒ  | jƒ  d S )N)rk   r’   rœ   r­   r²   )r2   r5   r5   r6   rò   M  s
    z"_TestSQLApi.load_test_data_and_sqlc             C   s   t jd| jƒ}| j|ƒ d S )NzSELECT * FROM iris)r>   r   r-   rv   )r2   ru   r5   r5   r6   Útest_read_sql_irisT  s    z_TestSQLApi.test_read_sql_irisc             C   s   t jd| jƒ}| j|ƒ d S )NzSELECT * FROM iris_view)r>   r   r-   rv   )r2   ru   r5   r5   r6   Útest_read_sql_viewX  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¾   rq   )r2   r5   r5   r6   Útest_to_sql\  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¾   rq   rÇ   rÈ   rÉ   )r2   rÊ   r5   r5   r6   Ú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 jd| jƒs>t‚t| jƒ}| jdƒ}||ks^t‚d S )Nr¬   rÄ   )rÅ   rÌ   )r>   r½   r‘   r-   r¾   rq   r¿   rµ   )r2   rÀ   rÁ   r5   r5   r6   Útest_to_sql_replaceh  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¾   rq   r¿   rµ   )r2   rÀ   rÁ   r5   r5   r6   Útest_to_sql_appends  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)rw   zSELECT * FROM test_frame5)r>   r½   r¬   r-   Úread_sqlrr   rÚ   )r2   r´   r5   r5   r6   Ú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)rw   zSELECT * FROM test_series)
r   ro   Úaranger>   r½   r-   r   rr   rÚ   Úto_frame)r2   ÚsÚs2r5   r5   r6   Ú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   rw   rÙ   ÚastypeÚintrº   rr   rÚ   )r2   r´   r5   r5   r6   Ú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  rw   Ú	chunksizez"SELECT * FROM test_frame_roundtrip)r  )r>   r½   r‘   r-   r   rr   rÚ   )r2   r´   r5   r5   r6   Ú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³   rr   rs   )r2   rÜ   ri   r5   r5   r6   Ú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-   rn   r   rÿ   rl   ro   Ú
datetime64rq   ÚtolistÚpdr   r   r   )r2   r›   r5   r5   r6   Ú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-   rn   rw   rÿ   rl   ro   r  rq   r   )r2   r›   r5   r5   r6   Ú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  rr   Úassert_produces_warningÚUserWarningr½   r-   r>   r   Úassert_series_equalr  )r2   r›   r´   r5   r5   r6   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-   )r2   r›   rÊ   r5   r5   r6   Útest_complex_raisesô  s    z_TestSQLApi.test_complex_raiseszindex_name,index_label,expectedNrw   Ú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   Úrangerw   rº   r>   r½   r-   r   r‹   rq   )r2   r  r!  ÚexpectedÚ
temp_framer"   Úframer5   r5   r6   Ú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)rw   r   zSELECT * FROM test_index_labelr   r×   r”   Zlevel_1rÌ   rx   ry   )rÅ   r!  rž   )rÅ   rz   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‹   rq   r  rw   ÚnamesrÇ   rÈ   rÉ   )r2   r$  r%  rÊ   r5   r5   r6   Ú"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á   rx   ry   rz   )r‹   rw   Útest_multiindex_roundtripz'SELECT * FROM test_multiindex_roundtrip)r  T)Úcheck_index_type)r”   r-  rß   )rž   r.  rá   )r   râ   r½   r-   r>   r   rr   rÚ   )r2   r›   r´   r5   r5   r6   r/  E  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-   )r2   r›   r5   r5   r6   Útest_integer_col_namesR  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-   rq   )r2   Ú
create_sqlr5   r5   r6   Útest_get_schemaV  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  ÚbrM   ÚINTEGERr2  r7  )r  rÿ   r3  )r   r]   rM   ÚIntegerr>   r4  r-   rq   )r2   Zfloat_framerÿ   r5  r5   r5   r6   Útest_get_schema_dtypesZ  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Col2r2  r;  )r  rR   z'CONSTRAINT test_pk PRIMARY KEY ("Col1")rx   ry   z)CONSTRAINT test_pk PRIMARY KEY ("A", "B"))r   r>   r4  r-   rq   r‘   )r2   r%  r5  Zconstraint_sentencer5   r5   r6   Útest_get_schema_keysc  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)rw   zselect * from test_chunksizer   rž   )r	  T)Úignore_indexr”   rM   )r   ro   ÚrandomZrandnÚlistr½   r-   r>   r   r   r¿   rq   rr   rÚ   r]   r   )r2   r›   Úres1rë   ÚiÚsizesÚchunkÚres3r5   r5   r6   Útest_chunksize_readp  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_namerG  ÚcategoryÚtest_categoricalF)rw   zSELECT * FROM test_categorical)	r   Úcopyr  r½   r-   r>   r   rr   rÚ   )r2   r›   Údf2rê   r5   r5   r6   rI  ’  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“   õ   Ã©r7  )r‹   Ztest_unicodeF)rw   )r   r½   r-   )r2   r›   r5   r5   r6   Ú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@)rx   ry   zd1187b08-4943-4c8d-a7f6F)rw   z'SELECT * FROM `d1187b08-4943-4c8d-a7f6`)	r   ro   Únanr½   r-   r>   r   rr   rÚ   )r2   r›   rê   r5   r5   r6   Útest_escaped_table_name¨  s    z#_TestSQLApi.test_escaped_table_name)NNrw   )Nr  r  )r  Nr  )r  r  r  )r   Nr  )Nr   r  )(r8   r9   r:   rí   rb   Ústrr]   r`   rÇ   rî   ró   rò   rô   rõ   rö   r÷   rø   rú   rü   r  r  r
  r  r  r  r  r  ÚmarkZparametrizer&  r,  r/  r1  r6  r:  r<  rF  rI  rM  rO  r5   r5   r5   r6   rï   2  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   rM   c             C   s
   t jdƒS )Nzsqlite:///:memory:)rM   Úcreate_engine)r2   r5   r5   r6   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_framerx   ry   )r‹   )r>   r½   r‘   r-   r   r‹   r  rq   )r2   Úcolsr´   r5   r5   r6   Ú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 )	NrU  rw   )r  rx   ry   rz   r{   )r  r‹   )
r>   r½   r‘   r-   r   rw   r+  rq   r‹   r  )r2   r´   r5   r5   r6   Ú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û   rr   rÚ   r   )r2   Úiris_frame1Úiris_frame2r5   r5   r6   Ú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¿   rq   )r2   ZqryÚwr5   r5   r6   Ú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   )r^  r_  r`  r‘   r½   r-   r¿   rq   )r2   ra  r5   r5   r6   Ú(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_namesr5   )rB   rB  r5   r5   r6   rD     s    z1TestSQLApi._get_index_columns.<locals>.<listcomp>)Úsqlalchemy.enginerd  Ú	InspectorÚfrom_enginer-   Úget_indexes)r2   Útbl_namerd  ÚinspÚixsr5   r5   r6   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>   rK   r-   ZSQLTableÚ
isinstancerC   rI   rl   ÚsqltypesÚ	TIMESTAMPrq   )r2   r›   ÚdbrC   r5   r5   r6   Ú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Å   rw   zSELECT * FROM irisr   zpg8000 is installedz)postgresql+pg8000://user:pass@host/dbnameÚpg8000)rÆ   zselect * from table)r‘   rr   Úensure_cleanr½   r>   rû   r   r   rÚ   rv  rÇ   ÚskipÚImportErrorrÈ   )
r2   r‘   rº   Zdb_urirC   rš   r¬   r"   rù   rv  r5   r5   r6   Ú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)rM   rO   ZTableÚColumnÚREALÚTEXT)r2   ÚsaÚmetadatar[   r5   r5   r6   Ú_make_iris_table_metadata6  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)rZ   r|  )rM   Útextr>   rû   r-   Úsetrq   )r2   Z	name_textÚiris_dfÚ	all_namesr5   r5   r6   Útest_query_by_text_objE  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)rZ   r|  )r‚  rM   ÚselectÚwhererI   r|  Z	bindparamr>   rû   r-   r„  rq   )r2   r[   Zname_selectr…  r†  r5   r5   r6   Útest_query_by_select_objL  s    z#TestSQLApi.test_query_by_select_objN)r8   r9   r:   rí   rb   r]   rð   rW  rX  r[  rb  rc  rã   ru  rz  r‚  r‡  rŠ  r5   r5   r5   r6   rS  ²  s   		!rS  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>   rK   r¶   Z_EngineToConnMixin__engineÚrollbackrG   )r2   rj   Úenginer-   )Ú	__class__r5   r6   ró   ]  s    



z_EngineToConnMixin.setup_method)r8   r9   r:   rí   rÇ   rî   ró   Ú__classcell__r5   r5   )r�  r6   r‹  X  s   r‹  c               @   s   e Zd ZdS )ÚTestSQLApiConnN)r8   r9   r:   r5   r5   r5   r6   r’  q  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ð   )r2   Zdatabaser5   r5   r6   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)rw   z!SELECT * FROM test_frame3_legacy;)	rr   rw  rð   r>   r½   r¬   rG   r   rÚ   )r2   rº   r-   r´   r5   r5   r6   Útest_sql_open_closeƒ  s    


z)TestSQLiteFallbackApi.test_sql_open_closezSQLAlchemy is installed)rR  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È   ry  r>   rû   )r2   r-   rÊ   r5   r5   r6   Ú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û   rr   rÚ   rÇ   rÈ   ZDatabaseError)r2   rY  rZ  rÊ   r5   r5   r6   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)rw   )r   rr   r  r>   r½   r-   )r2   r›   r5   r5   r6   Útest_safe_names_warning£  s    
z-TestSQLiteFallbackApi.test_safe_names_warningc             C   s   t j| jdƒ}d|kst‚d S )Nr2  r3  )r>   r4  r‘   rq   )r2   r5  r5   r5   r6   Ú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É   )r2   rN   ÚcolumnÚcolr5   r5   r6   Ú_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   rm  rn  T)ro  rp  )r%  rs  )	r   r   r>   ÚSQLiteDatabaser-   ZSQLiteTableZ
sql_schemar¡  rq   )r2   r›   rt  rC   rN   r5   r5   r6   Útest_sqlite_type_mapping¶  s    z.TestSQLiteFallbackApi.test_sqlite_type_mappingN)r•  )r8   r9   r:   rí   rb   r]   rð   r˜  rÇ   rQ  ÚskipifÚSQLALCHEMY_INSTALLEDr™  r[  rš  r›  r¡  r£  r5   r5   r5   r6   r“  v  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„ Z8dfS )gÚ_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-   r5   r5   r6   Úsetup_classÑ  s    z_TestSQLAlchemy.setup_classc             C   s   | j ƒ  | jƒ  d S )N)r²   r’   )r2   r5   r5   r6   rò   Ø  s    z&_TestSQLAlchemy.load_test_data_and_sql)rñ   c             C   s   | j ƒ  d S )N)rò   )r2   rj   r5   r5   r6   ró   Ü  s    z_TestSQLAlchemy.setup_methodc             C   s   t stjdƒ d S )NzSQLAlchemy not installed)r¥  rÇ   rx  )r«  r5   r5   r6   r©  à  s    z_TestSQLAlchemy.setup_importc             C   s
   t ƒ ‚d S )N)ÚNotImplementedError)r«  r5   r5   r6   rª  æ  s    z_TestSQLAlchemy.setup_driverc             C   s
   t ƒ ‚d S )N)r­  )r«  r5   r5   r6   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>   rK   r¶   rM   ÚexcZOperationalErrorrÇ   rx  rb   )r2   r5   r5   r6   r`   î  s    
z_TestSQLAlchemy.setup_connectc             C   s   | j ƒ  d S )N)r¸   )r2   r5   r5   r6   Útest_read_sql÷  s    z_TestSQLAlchemy.test_read_sqlc             C   s   | j ƒ  d S )N)r¹   )r2   r5   r5   r6   Útest_read_sql_parameterú  s    z'_TestSQLAlchemy.test_read_sql_parameterc             C   s   | j ƒ  d S )N)r¼   )r2   r5   r5   r6   Útest_read_sql_named_parameterý  s    z-_TestSQLAlchemy.test_read_sql_named_parameterc             C   s   | j ƒ  d S )N)rÂ   )r2   r5   r5   r6   rö      s    z_TestSQLAlchemy.test_to_sqlc             C   s   | j ƒ  d S )N)rÃ   )r2   r5   r5   r6   Útest_to_sql_empty  s    z!_TestSQLAlchemy.test_to_sql_emptyc             C   s   | j ƒ  d S )N)rË   )r2   r5   r5   r6   r÷     s    z _TestSQLAlchemy.test_to_sql_failc             C   s   | j ƒ  d S )N)rÍ   )r2   r5   r5   r6   rø   	  s    z#_TestSQLAlchemy.test_to_sql_replacec             C   s   | j ƒ  d S )N)rÏ   )r2   r5   r5   r6   rú     s    z"_TestSQLAlchemy.test_to_sql_appendc             C   s   | j dd� d S )NZmulti)r3   )rÂ   )r2   r5   r5   r6   Útest_to_sql_method_multi  s    z(_TestSQLAlchemy.test_to_sql_method_multic             C   s   | j ƒ  d S )N)rÕ   )r2   r5   r5   r6   Ú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>   rK   r½   r¾   rq   )r2   Ú	temp_connr$  r¶   r5   r5   r6   Ú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>   rK   r½   r¾   rq   r0   )r2   r·  r$  r¶   r5   r5   r6   Útest_drop_table   s    

z_TestSQLAlchemy.test_drop_tablec             C   s   | j ƒ  d S )N)rÛ   )r2   r5   r5   r6   r  0  s    z_TestSQLAlchemy.test_roundtripc             C   s   | j ƒ  d S )N)rÝ   )r2   r5   r5   r6   r  3  s    z _TestSQLAlchemy.test_execute_sqlc             C   s   t jd| jd�}| j|ƒ d S )Nr[   )r  )r>   r   r-   rv   )r2   ru   r5   r5   r6   Útest_read_table6  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-   rr   rs   r‹   rt   )r2   ru   r5   r5   r6   rW  :  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-   )r2   rÊ   r5   r5   r6   Útest_read_table_absent_raises@  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jƒs|t	‚d S )Nr®   )r>   r   r-   rn   r   rÿ   rl   ro   rp   rq   r   Úintegerr   Zbool_r    r!   Úobject)r2   r›   r5   r5   r6   Útest_default_type_conversionE  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ž   é>   )rW   Útest_bigintF)rw   l            )r   r½   r-   r>   r   rr   rÚ   )r2   r›   r´   r5   r5   r6   rÁ  Q  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-   rn   r   rÿ   rl   ro   r  rq   )r2   r›   r5   r5   r6   Útest_default_date_loadY  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   rq   r   rP  ÚdtrÄ  r   rº   rr   r  )r   Zexpected_datar#  r5   r5   r6   rÓ   e  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Ç   rx  r$   r   rÿ   rq   rP  rÅ  rÄ  r   r@  r>   r   rr   r  )r2   rÓ   r›   r   r#  r5   r5   r6   Útest_datetime_with_timezone`  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 )Nrx   z2013-01-01 09:00:00r•   z
US/Pacific)ÚperiodsrÄ  Ztest_datetime_tzF)rw   r   rÃ  zSELECT * FROM test_datetime_tzr   r   )r   rx   )r   r   r½   r-   rb   rÅ  Z
tz_convertZtz_localizer>   r   rr   rÚ   r   rq  ÚlocrP  rq   r   )r2   r#  r´   r5   r5   r6   Ú%test_datetime_with_timezone_roundtrip°  s    

z5_TestSQLAlchemy.test_datetime_with_timezone_roundtripc             C   sZ   t 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)rw   Z	foo_tableZ	info_date)r!  )r  F)Zcheck_names)	r   r   r"  r½   r-   r>   r   rr   rÚ   )r2   Údatesr#  r´   r5   r5   r6   Ú"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-   rb   r½  ro   r  rn   r   rÿ   rl   rq   r   )r2   r›   Zexpected_typer5   r5   r6   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      @)rx   ry   Útest_datetimerw   r”   )ZaxiszSELECT * FROM test_datetimer   r   rx   )r   rx   )r   r   ro   r   r½   r-   r>   r   Zdroprr   rÚ   r   rb   rq  rÈ  rP  rq   r   )r2   r›   r´   r5   r5   r6   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      @)rx   ry   r”   rx   rÐ  F)rw   zSELECT * FROM test_datetimer   r   Zcoerce)Úerrors)r”   rx   )r   rx   )r   r   ro   r   rN  rÈ  r½   r-   r>   r   rr   rÚ   r   rb   rq  rP  rq   r   )r2   r›   r´   r5   r5   r6   Ú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)rw   )r   r   r½   r-   r   r   rr   r  )r2   r›   rê   r´   r#  r5   r5   r6   Ú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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)rw   Z
test_time2zSELECT * FROM test_time2c             S   s
   | j dƒS )Nz%H:%M:%S.%f)Ústrftime)Ú_r5   r5   r6   Ú<lambda>8  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Ù  r5   r5   r6   rÚ  >  s    )r   r   r½   r-   r   rr   rÚ   ÚTestSQLiteFallbackrð   r>   r   Úapplymaprb   )r2   r›   rê   Zsqlite_connÚrefr5   r5   r6   Ú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)rw   T)Zcheck_dtypeZcheck_exacti   i  )r   ro   Úint32Úfloat32r   r½   r-   r>   r   rr   rÚ   )r2   rß  r  r›   rK  r5   r5   r6   Útest_mixed_dtype_insertC  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@)rx   ry   Útest_nanF)rw   zSELECT * FROM test_nan)
r   ro   rN  r½   r-   r>   r   rr   rÚ   r   )r2   r›   r´   r5   r5   r6   Útest_nan_numericO  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ž   )rx   ry   rã  F)rw   ry   r½  zSELECT * FROM test_nan)r   ro   rN  r½   r-   r>   r   rr   rÚ   r  r   )r2   r›   r´   r5   r5   r6   Útest_nan_fullcolumn\  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  r7  )rx   ry   rã  F)rw   ry   zSELECT * FROM test_nan)rž   ry   )r   ro   rN  r½   r-   rÈ  r>   r   rr   rÚ   r   )r2   r›   r´   r5   r5   r6   Útest_nan_stringk  s    
z_TestSQLAlchemy.test_nan_stringc             C   s6   ddl m} |jj| jƒ}|j|ƒ}dd„ |D ƒ}|S )Nr   )rd  c             S   s   g | ]}|d  ‘qS )re  r5   )rB   rB  r5   r5   r6   rD   €  s    z6_TestSQLAlchemy._get_index_columns.<locals>.<listcomp>)rf  rd  rg  rh  r-   ri  )r2   rj  rd  rk  rl  r5   r5   r6   rã   {  s
    
z"_TestSQLAlchemy._get_index_columnsc             C   s   | j ƒ  d S )N)rå   )r2   r5   r5   r6   Útest_to_sql_save_indexƒ  s    z&_TestSQLAlchemy.test_to_sql_save_indexc             C   s   | j ƒ  d S )N)rì   )r2   r5   r5   r6   Ú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)r0  )r­   r>   r4  r¬   r-   rm   r0   r=   r   rr   rÚ   )r2   r4   r5  Zblank_test_dfÚreturned_dfr5   r5   r6   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 )Nrx   ry   çš™™™™™é?TçÍÌÌÌÌÌì?)r‹   Ú
dtype_testÚdtype_test2)rÿ   )rL   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-   rM   r  rN   rO   rP   rQ   r‹   rl   rq  rq   rÇ   rÈ   rÉ   rP  ÚStringr»   )	r2   rV  rW   r›   rS   ZsqltyperÊ   ZsqltypeaZsqltypebr5   r5   r6   Ú
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)rL   r   ró  rô  rõ  rö  )r   r   r   r½   r-   r>   r   rM   rN   rO   rP   rb   rr  r9  ÚBooleanrQ   r‹   rq  rl   rq   ÚDateTimerö  )r2   rV  r›   r4   rê  rS   Zmy_typeÚcol_dictr5   r5   r6   Ú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)rw   rÅ   rÿ   rþ  r   é   )rL   rý  r   r¿  )r   r   r½   r-   rM   rö  r>   r   ro   Úroundrm   rq   rN   rO   rP   rQ   r‹   rP  rl   rq  rr  r9  Z
BigInteger)r2   ÚVr›   rê   rS   rú  r5   r5   r6   Ú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"   r5   r5   r6   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  rW   r5   r5   r6   Ú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  r5   r6   Úmainÿ  s    


z<_TestSQLAlchemy.test_connectable_issue_example.<locals>.mainr  r   r”   rž   )r   r½   r-   )r2   r	  r5   )r  r  r6   Útest_connectable_issue_exampleô  s
    z._TestSQLAlchemy.test_connectable_issue_examplec       	      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)r8   r9   r:   Z__tablename__Z__table_args__rM   r}  r9  ÚidZUnicoder  r5   r5   r5   r6   Ú	Temporary  s   
r  )rL   )r  )r>   r  )r   r   Zdeclarative_baseÚ
sa_sessionZsessionmakerr-   Ztransactionr  Z	__table__ÚcreateÚaddÚflushr>   r   rM   rˆ  r  rr   rÚ   )	r2   Z	test_datar#  ZBaser  ÚSessionr   r-   r›   r5   r5   r6   Útest_temporary_table  s    "z$_TestSQLAlchemy.test_temporary_tableN)9r8   r9   r:   rí   rP  rb   rÇ   rî   r¬  rò   ró   Úclassmethodr©  rª  rð   r`   r¯  r°  r±  rö   r²  r÷   rø   rú   r³  r´  r¸  r¹  r  r  rº  rW  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  r5   r5   r5   r6   r¦  Æ  sd   
	P
$
#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Ç   rx  )r2   r5   r5   r6   rè     s    z%_TestSQLAlchemyConn.test_transactionsN)r8   r9   r:   rè  r5   r5   r5   r6   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 jdƒS )Nzsqlite:///:memory:)rM   rT  )r«  r5   r5   r6   rð   ,  s    z_TestSQLiteAlchemy.connectc             C   s
   d | _ d S )N)Údriver)r«  r5   r5   r6   rª  0  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-   rn   r   rÿ   rl   ro   rp   rq   r   r¼  r   r    r!   )r2   r›   r5   r5   r6   r¾  5  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-   rn   r   rÿ   rl   ro   r  rq   )r2   r›   r5   r5   r6   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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)rw   T)r\  r]  r   )
r   r½   r-   r^  r_  r`  r>   r   r¿   rq   )r2   r›   ra  r5   r5   r6   Útest_bigint_warningJ  s    
z&_TestSQLiteAlchemy.test_bigint_warningN)r8   r9   r:   rí   rb   r  rð   rª  r¾  rÂ  r  r5   r5   r5   r6   r  $  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   d}t j|j| jd�| jd�S )Nz/mysql+{driver}://root@localhost/pandas_nosetest)r  )Úconnect_args)rM   rT  rÎ  r  r  )r«  Úurlr5   r5   r6   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  r5   r5   r6   rª  d  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-   rn   r   rÿ   rl   ro   rp   rq   r   r¼  r   r    r!   )r2   r›   r5   r5   r6   r¾  j  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  r7  Ztest_procedureF)rw   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?   rF   rŽ  r>   r   rr   rÚ   rû   )	r2   r  r›   Úprocr  ré   Zr1rA  rë   r5   r5   r6   Útest_read_procedurey  s     

z%_TestMySQLAlchemy.test_read_procedureN)
r8   r9   r:   rí   rb   r  rð   rª  r¾  r"  r5   r5   r5   r6   r  U  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   d}t j|j| jd�ƒS )Nz8postgresql+{driver}://postgres@localhost/pandas_nosetest)r  )rM   rT  rÎ  r  )r«  r  r5   r5   r6   rð   ¢  s    z_TestPostgreSQLAlchemy.connectc             C   s   t jdƒ d| _d S )NZpsycopg2)rÇ   r  r  )r«  r5   r5   r6   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)rw   Ztest_schema_public_explicitÚpublic)rw   rN   Ztest_schema_otherÚother)rN   z!Table test_schema_other not found)rÆ   )rN   rw   rÌ   )rN   rw   rÅ   rÎ   T)r>  )rS   Ztest_schema_other2)rw   rÅ   )r   r-   r=   r½   r>   r   rr   rÚ   rÇ   rÈ   rÉ   r   rq  rM   r�  ZEnginerð   rO   rK   Z
read_table)r2   r›   rA  rë   rE  Zres4rÊ   rê   Zengine2rS   Zpdsqlr5   r5   r6   Ú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 )ú"Nr5   )rB   Úkr5   r5   r6   ú	<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   rX   ÚwriterÚ	writerowsÚseekÚjoinrN   rº   Zcopy_expert)rC   r-   rR   rÒ   Z
dbapi_connrA   Zs_bufr/  r‹   r@   Z	sql_queryr5   r5   r6   Ú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)rw   r3   )r   r½   r-   r>   r   rr   rÚ   )r2   r3  r#  r´   r5   r5   r6   Ú(test_copy_from_callable_insertion_methodó  s    z?_TestPostgreSQLAlchemy.test_copy_from_callable_insertion_methodN)
r8   r9   r:   rí   rb   r  rð   rª  r)  r4  r5   r5   r5   r6   r#  š  s   Gr#  c               @   s   e Zd ZdS )ÚTestMySQLAlchemyN)r8   r9   r:   r5   r5   r5   r6   r5    s   r5  c               @   s   e Zd ZdS )ÚTestMySQLAlchemyConnN)r8   r9   r:   r5   r5   r5   r6   r6    s   r6  c               @   s   e Zd ZdS )ÚTestPostgreSQLAlchemyN)r8   r9   r:   r5   r5   r5   r6   r7    s   r7  c               @   s   e Zd ZdS )ÚTestPostgreSQLAlchemyConnN)r8   r9   r:   r5   r5   r5   r6   r8  #  s   r8  c               @   s   e Zd ZdS )ÚTestSQLiteAlchemyN)r8   r9   r:   r5   r5   r5   r6   r9  )  s   r9  c               @   s   e Zd ZdS )ÚTestSQLiteAlchemyConnN)r8   r9   r:   r5   r5   r5   r6   r:  .  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d5S )6rÛ  zG
    Test the fallback mode against an in-memory sqlite database.

    r   c             C   s
   t jdƒS )Nz:memory:)r–  rð   )r«  r5   r5   r6   rð   @  s    zTestSQLiteFallback.connectc             C   s   | j ƒ | _d S )N)rð   r-   )r2   r5   r5   r6   r`   D  s    z TestSQLiteFallback.setup_connectc             C   s   t j| jƒ| _| jƒ  d S )N)r>   r¢  r-   r¶   r’   )r2   r5   r5   r6   rò   G  s    z)TestSQLiteFallback.load_test_data_and_sqlT)rñ   c             C   s   | j ƒ  d S )N)rò   )r2   rj   r5   r5   r6   ró   K  s    zTestSQLiteFallback.setup_methodc             C   s   | j ƒ  d S )N)r¸   )r2   r5   r5   r6   r¯  O  s    z TestSQLiteFallback.test_read_sqlc             C   s   | j ƒ  d S )N)r¹   )r2   r5   r5   r6   r°  R  s    z*TestSQLiteFallback.test_read_sql_parameterc             C   s   | j ƒ  d S )N)r¼   )r2   r5   r5   r6   r±  U  s    z0TestSQLiteFallback.test_read_sql_named_parameterc             C   s   | j ƒ  d S )N)rÂ   )r2   r5   r5   r6   rö   X  s    zTestSQLiteFallback.test_to_sqlc             C   s   | j ƒ  d S )N)rÃ   )r2   r5   r5   r6   r²  [  s    z$TestSQLiteFallback.test_to_sql_emptyc             C   s   | j ƒ  d S )N)rË   )r2   r5   r5   r6   r÷   ^  s    z#TestSQLiteFallback.test_to_sql_failc             C   s   | j ƒ  d S )N)rÍ   )r2   r5   r5   r6   rø   a  s    z&TestSQLiteFallback.test_to_sql_replacec             C   s   | j ƒ  d S )N)rÏ   )r2   r5   r5   r6   rú   d  s    z%TestSQLiteFallback.test_to_sql_appendc             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¾   rq   r0   )r2   r$  r5   r5   r6   Útest_create_and_drop_tableg  s    z-TestSQLiteFallback.test_create_and_drop_tablec             C   s   | j ƒ  d S )N)rÛ   )r2   r5   r5   r6   r  t  s    z!TestSQLiteFallback.test_roundtripc             C   s   | j ƒ  d S )N)rÝ   )r2   r5   r5   r6   r  w  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)rw   zSELECT * FROM test_dater   r   )
r   r   r½   r-   r   rb   rr   rÚ   r  rP  )r2   r›   rê   r5   r5   r6   rÔ  z  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)rw   zSELECT * FROM test_timer   c             S   s
   | j dƒS )Nz%H:%M:%S.%f)rØ  )rÙ  r5   r5   r6   rÚ  Œ  s    z7TestSQLiteFallback.test_datetime_time.<locals>.<lambda>)	r   r   r½   r-   r   rb   rÜ  rr   rÚ   )r2   r›   rê   r#  r5   r5   r6   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  )r2   rj  rl  rä   Zix_nameZix_infor5   r5   r6   rã   �  s    z%TestSQLiteFallback._get_index_columnsc             C   s   | j ƒ  d S )N)rå   )r2   r5   r5   r6   rç  ›  s    z)TestSQLiteFallback.test_to_sql_save_indexc             C   s   | j ƒ  d S )N)rì   )r2   r5   r5   r6   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(r=  zTable z	, column z
 not found)r-   r=   rÉ   )
r2   rC   rŸ  ZrecsZcidrº   ÚctypeZnot_nullÚdefaultÚpkr5   r5   r6   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 legacyrx   ry   çš™™™™™é?TçÍÌÌÌÌÌì?)r‹   rí  rî  ÚSTRING)rÿ   r8  z!B \(<class 'bool'>\) not a string)rÆ   rç   rð  )rA  T)rB  N)rb   rÇ   rx  r   r½   r-   r¡  rq   rÈ   rÉ   Úbool)r2   rV  rW   r›   rÊ   r5   r5   r6   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ó  r8  rô  rs  rõ  rö  r~  )
rb   rÇ   rx  r   r   r   r½   r-   r¡  rq   )r2   rV  r›   r4   r5   r5   r6   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  r7  )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12345rL  Ztest_weird_col_namer±   )	r   rÇ   rÈ   rÉ   r½   r-   Ú	enumerater>   Ztable_exists)r2   r›   rÊ   ZndxZ
weird_namerK  Zc_tblr5   r5   r6   Útest_illegal_namesÒ  s*    z%TestSQLiteFallback.test_illegal_namesN) r8   r9   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¡  rò  rû  rG  r5   r5   r5   r6   rÛ  7  s4   
rÛ  c             C   s
   | j dƒS )z Returns date in YYYYMMDD format.z%Y%m%d)rØ  )rÅ  r5   r5   r6   Údate_formatõ  s    rH  z'{}'z{:.8f}z{:d}c             C   s   dS )NZNULLr5   )Úxr5   r5   r6   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)rq  Úfloatr   Ú_formattersrl   rÎ   Útuple)r>   ÚargsZprocessed_argsÚargÚ	formatterr5   r5   r6   Úformat_query	  s    
rP  c             C   s,   t j| ||d�jƒ }|dkr dS t|ƒS dS )z#Replace removed sql.tquery function)r  rA   N)r>   r=   rE   r@  )r"   r  rA   rê   r5   r5   r6   Ú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jdƒ| _d V  |j | _tjdƒ| _d S )Nz:memory:)Úfunctionr3   r–  rð   r-   )r2   rg   rf   r5   r5   r6   ró   !	  s
    zTestXSQLite.setup_methodc             C   s   t jƒ }| j|ƒ d S )N)rr   ÚmakeTimeDataFrameÚ_check_roundtrip)r2   r%  r5   r5   r6   Ú
test_basic,	  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   r2  z(INSERT INTO test VALUES (%s, %s, %s, %s))rA   zselect * from test)r  T)Úcheck_less_precise)r   r   )rr   rT  ro   rN  rm   r>   r4  r-   r<   r=   ÚiterrowsrP  rQ  r?   rû   rw   rÚ   )	r2   r%  r5  rA   rh   Úidxri   Úfmt_sqlr´   r5   r5   r6   Útest_write_row_by_row0	  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 )Nr2  z$INSERT INTO test VALUES (?, ?, ?, ?)r   )rZ   zselect * from testr”   )rr   rT  r>   r4  r-   r<   r=   rm   rL  r?   rû   rw   rÚ   )r2   r%  r5  rA   rh   ri   r´   r5   r5   r6   Útest_executeE	  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 )Nr2  ú rž   r   rx   r”   ÚDATETIMEry   )rR   zPRIMARY KEY ("A", "B"))rr   rT  r>   r4  Ú
splitlinesr�  r¿   rq   r-   r<   r=   )r2   r%  r5  ÚlinesÚlÚtokensrA   r5   r5   r6   Útest_schemaT	  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è   )r2   r5  rA   r5   r5   r6   Útest_execute_faild	  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>   rG   rÇ   rÈ   rè   rQ  )r2   r5  rA   r5   r5   r6   Útest_execute_closed_connectionw	  s    	


z*TestXSQLite.test_execute_closed_connectionc             C   s   d S )Nr5   )r2   r5   r5   r6   Ú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  rw   zselect * from test_tabler  Útxtrï  ÚIdxÚtest_table2zselect * from test_table2)r  )r>   r½   r-   rû   rw   rr   rÚ   r¿   rJ  r	   ro   r   rº   )r2   r%  r´   r#  Úframe2Znew_idxr5   r5   r6   rU  �	  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º   rw   )r   ro   Úonesr>   r½   r-   )r2   r›   r5   r5   r6   Ú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º   rw   c             s   s   | ]}|d  V  qdS )r   Nr5   )rB   Zmy_c0r5   r5   r6   r,  ®	  s    z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr•   )
r   r>   r½   r-   Úsumr=   rq   rû   rr   rÚ   )r2   rp  Zcon_xZthe_sumr´   r5   r5   r6   Ú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ž   rx   ry   )r  r%  r•   r“   rý   rz   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)r0   )Útest_table_to_drop)r2   r5   r6   Ú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Å   rw   )r  rÎ   )r”   rx   )rž   ry   )r•   rz   )r“   r{   )rý   r˜   )r”   rx   )rž   ry   )r”   rx   )rž   ry   )r•   rz   )r“   r{   )rý   r˜   )	r   rÇ   rÈ   rÉ   r>   r½   r-   rQ  rq   )r2   Údf_if_exists_1Údf_if_exists_2r@   Ú
sql_selectru  rÊ   r5   )r2   r6   Útest_if_existsµ	  sl    
zTestXSQLite.test_if_existsN)r8   r9   r:   rÇ   rî   ró   rV  r[  r\  rc  rd  re  rf  rU  ro  rr  rz  r5   r5   r5   r6   rR  	  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   sl   t jdƒ}|jddddd� y|jdd� W n: |jk
rJ   td	ƒ‚Y n |jk
rf   td
ƒ‚Y nX d S )Nr  Ú	localhostÚrootrE  Úpandas_nosetest)ÚhostÚuserÚpasswdrt  Ú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ÚRuntimeErrorrF   )r«  r  r5   r5   r6   r¬  
  s    

zTestXMySQL.setup_class)rñ   c             C   st   t jdƒ}|jddddd� y|jdd� W n: |jk
rJ   td	ƒ‚Y n |jk
rf   td
ƒ‚Y nX |j| _d S )Nr  r|  r}  rE  r~  )r  r€  r�  rt  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…  rF   rS  r3   )r2   rg   rf   r  r5   r5   r6   ró   
  s    

zTestXMySQL.setup_methodc             C   s   t jƒ }| j|ƒ d S )N)rr   rT  rU  )r2   r%  r5   r5   r6   rV  2
  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 testr2  z(INSERT INTO test VALUES (%s, %s, %s, %s))rA   zselect * from test)r  T)rW  )r   r   )rr   rT  ro   rN  rm   r>   r4  r-   r<   r=   rX  rP  rQ  r?   rû   rw   rÚ   )
r2   r%  Údrop_sqlr5  rA   rh   rY  ri   rZ  r´   r5   r5   r6   r[  6
  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 )Nrw   zDROP TABLE IF EXISTS testr2  )rº   r  zselect * from testrý   )r>   r  r	  r  )rr   rT  rw   rº   r-   r<   r=   r>   r½   r  r   re   rÚ   )r2   r%  r†  rA   r"   r	  Z	chunk_genZchunk_dfr5   r5   r6   Útest_chunksize_read_typeI
  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 testr2  ÚignorezUnknown table.*z(INSERT INTO test VALUES (%s, %s, %s, %s)r   )rZ   zselect * from testr”   )rr   rT  r>   r4  r-   r<   r^  r_  Úfilterwarningsr=   rm   rt   r  rL  r?   rû   rw   rÚ   )r2   r%  r†  r5  rA   rh   ri   r´   r5   r5   r6   r\  X
  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 )Nr2  r]  rž   r   rx   r”   r^  zDROP TABLE IF EXISTS testry   )rR   zPRIMARY KEY (`A`, `B`))rr   rT  r>   r4  r_  r�  r¿   rq   r-   r<   r=   )r2   r%  r5  r`  ra  rb  r†  rA   r5   r5   r6   rc  k
  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è   )r2   r†  r5  rA   r5   r5   r6   rd  }
  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>   rG   rÇ   rÈ   rè   rQ  ró   )r2   rg   rf   r†  r5  rA   r5   r5   r6   re  ’
  s    	



z)TestXMySQL.test_execute_closed_connectionc             C   s   d S )Nr5   )r2   r5   r5   r6   rf  ª
  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.*rg  F)rº   r  rw   zselect * from test_tabler  rh  rï  ri  z DROP TABLE IF EXISTS test_table2rj  zselect * from test_table2)r  )r-   r<   r^  r_  r‰  r=   r>   r½   rû   rw   rº   rr   rÚ   r¿   rJ  r	   ro   r   r+  )r2   r%  r†  rA   r´   r#  rk  rw   r5   r5   r6   rU  ­
  s4    



zTestXMySQL._check_roundtripc             C   s,   t dtjdƒiƒ}tj|| jdddd� d S )Nrl  rý   rm  rÌ   F)r  rº   rÅ   rw   )r   ro   rn  r>   r½   r-   )r2   r›   r5   r5   r6   ro  Ï
  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ž   rx   ry   )r  r%  r•   r“   rý   rz   r{   r˜   rs  zSELECT * FROM c                s   ˆ j | ƒ dS )z
            Drops tables created from individual tests
            so no dependencies arise from sequential tests
            N)r0   )rt  )r2   r5   r6   ru  Û
  s    z+TestXMySQL.test_if_exists.<locals>.clean_upz<insert message here>)rÆ   rv  )r%  r  rº   rÅ   rÄ   F)r%  r  rº   rÅ   rw   rÌ   )r  rÎ   )r”   rx   )rž   ry   )r•   rz   )r“   r{   )rý   r˜   )r”   rx   )rž   ry   )r”   rx   )rž   ry   )r•   rz   )r“   r{   )rý   r˜   )	r   rÇ   rÈ   rÉ   r>   r½   r-   rQ  rq   )r2   rw  rx  r@   ry  ru  r5   )r2   r6   rz  Õ
  sp    
zTestXMySQL.test_if_existsN)r8   r9   r:   rÇ   rî   r¬  ró   rV  r[  r‡  r\  rc  rd  re  rf  rU  ro  rz  r5   r5   r5   r6   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í   rX   r   r   r   rV   r   r–  r^  Znumpyro   rÇ   Zpandas.core.dtypes.commonr   r   r‚  r  r   r	   r
   r   r   r   r   r   r   r   Zpandas._testingZ_testingrr   Zpandas.io.sqlr>   r   r   rM   Zsqlalchemy.schemaZsqlalchemy.sql.sqltypesrr  Zsqlalchemy.extr   Zsqlalchemy.ormr   r  r¥  ry  ra   r,   r;   rH   rJ   rT   rï   rQ  Zsingler¤  rS  r‹  r’  r“  r¦  r  r  r  r#  rt  r5  r6  r7  r8  r9  r:  rÛ  rH  rÎ  rP  Zstr_ÚbytesrJ  r  rl   rü  rD  rK  rP  rQ  rR  rx  r{  r5   r5   r5   r6   Ú<module>   s  0


                         

	  ,    %

O    ]1Ew


 >

	 c