3
Ud4                @   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)sessionTFzCREATE TABLE iris (
                "SepalLength" REAL,
                "SepalWidth" REAL,
                "PetalLength" REAL,
                "PetalWidth" REAL,
                "Name" TEXT
            )zCREATE TABLE iris (
                `SepalLength` DOUBLE,
                `SepalWidth` DOUBLE,
                `PetalLength` DOUBLE,
                `PetalWidth` DOUBLE,
                `Name` VARCHAR(200)
            )a  CREATE TABLE iris (
                "SepalLength" DOUBLE PRECISION,
                "SepalWidth" DOUBLE PRECISION,
                "PetalLength" DOUBLE PRECISION,
                "PetalWidth" DOUBLE PRECISION,
                "Name" VARCHAR(200)
            ))sqlitemysql
postgresqlz&INSERT INTO iris VALUES(?, ?, ?, ?, ?)z.INSERT INTO iris VALUES(%s, %s, %s, %s, "%s");z,INSERT INTO iris VALUES(%s, %s, %s, %s, %s);a  CREATE TABLE types_test_data (
                    "TextCol" TEXT,
                    "DateCol" TEXT,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" REAL,
                    "IntCol" INTEGER,
                    "BoolCol" INTEGER,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" INTEGER
                )a  CREATE TABLE types_test_data (
                    `TextCol` TEXT,
                    `DateCol` DATETIME,
                    `IntDateCol` INTEGER,
                    `IntDateOnlyCol` INTEGER,
                    `FloatCol` DOUBLE,
                    `IntCol` INTEGER,
                    `BoolCol` BOOLEAN,
                    `IntColWithNull` INTEGER,
                    `BoolColWithNull` BOOLEAN
                )a  CREATE TABLE types_test_data (
                    "TextCol" TEXT,
                    "DateCol" TIMESTAMP,
                    "DateColWithTz" TIMESTAMP WITH TIME ZONE,
                    "IntDateCol" INTEGER,
                    "IntDateOnlyCol" INTEGER,
                    "FloatCol" DOUBLE PRECISION,
                    "IntCol" INTEGER,
                    "BoolCol" BOOLEAN,
                    "IntColWithNull" INTEGER,
                    "BoolColWithNull" BOOLEAN
                )zo
                INSERT INTO types_test_data
                VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
                TextColDateCol
IntDateColIntDateOnlyColFloatColIntColBoolColIntColWithNullBoolColWithNull)queryfieldszz
                INSERT INTO types_test_data
                VALUES("%s", %s, %s, %s, %s, %s, %s, %s, %s)
                z|
                INSERT INTO types_test_data
                VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                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   L/var/www/html/virt/lib64/python3.6/site-packages/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:00N\^?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:00GͿ?2r¹e?g>ݦ4?ёR?2000-01-06 00:00:00?{mX?$?nk3Am?
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i2g333333$@r   F)
r   r   r$   r   r   r   r   r   r    r!   z2000-01-04 00:00:00z2000-06-01 00:00:00-07:00i 'Pi\2r"   c                s   g | ]} | qS 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st| j j }|j| W d Q R X | j jd}t|dkstd S )	Nz'CREATE TABLE test_trans (A INT, B TEXT)c               @   s   e Zd ZdS )z7PandasSQLTest._transaction_test.<locals>.DummyExceptionN)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st	t jd| jdd	id}t|jjjtjst	|jj
 tjddddddtjddddddgkst	t jd| jd
gd}t|jjjtjst	|j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st	t jd| jddid}t|jjjtjst	|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st	ddg|j_tj|d| jdd tjd	| j}|jd d j
 ddgkst	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st|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 }qW 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st|jj	 ddgkst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st	||j t jtt jd| jdd	d
d}|j}t|jst	t
|jjdkst	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stnbt| jrt| 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rt|jd tst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stt jd| jdddiid}t|jj	j
tjstt jd| jdgd}t|jj	j
tjstt jd| jddid}t|jj	j
tjstt jd| jdd	diid}t|jj	j
tjsFtd S )
Nr   r   r   )r  z%Y-%m-%d %H:%M:%Sformatr   r  unit)r?   r   r.   rc   r  rp   r  ro   r   r  rm   rr   r   )r3   r   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rt|jd tst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rt|jd tst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rt	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st|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rtj}ntj}|j| j}t|d j|stt|d jtjstt|d jtjstt|d jtjstd S )NTi  r   r   r  )r  g?)BoolDateIntFloatnotna_dtype_test)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sttjj| jd}|j  |jd	 j}t|d jt|d jkstt|d jtjstt|d jtjstt|d jtjs6tt|d jtjsNtd S )NgYB?r  )r  float64r   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   zDROP 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_bufr7  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  r1  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   rA  L  s   rA  c               @   s   e Zd ZdS )TestSQLiteAlchemyConnN)r9   r:   r;   r6   r6   r6   r7   rB  Q  s   rB  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(rE  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st| jdddkst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  )rI  T)rJ  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st	| j|ddkst	| j|ddkst	d S )Nr   zNot applicable to MySQL legacyTi  r   r   r  )r  g?)r  r  r  r  r  r  r:  r  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 xt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  rC  r
  r  r  r  r   r  r  r  r  r  rO  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    rP  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    
rX  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    rY  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  gMbP?)rtol)r   r   )rs   r\  rp   rP  rn   r?   r6  r.   r=   r>   iterrowsrX  rY  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   rT  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st| 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   rY  )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   r4  	  s    z8TestXSQLite.test_onecolumn_of_integer.<locals>.<genexpr>zselect * from mono_dfr   )
r   r?   r   r.   sumr>   rr   r   rs   r   )r3   rx  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 st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st|| 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.   rY  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^  rc  rd  rk  rl  rm  rn  r]  rw  rz  r  r6   r6   r6   r7   rZ  F	  s   rZ  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rootrM  pandas_nosetest)hostuserpasswdrw  pandas)read_default_groupzCreate a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.zCannot connect to database. Create a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.)r   r'  r   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  rM  r  )r  r  r  rw  r  )r  zCreate a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.zCannot connect to database. Create a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.)r   r'  r   r  r  rG   r[  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  gMbP?)r_  )r   r   )rs   r\  rp   rP  rn   r?   r6  r.   r=   r>   r`  rX  rY  r@   r   rx   r   )
r3   r'  drop_sqlr7  rB   ri   ra  rj   rb  r   r6   r6   r7   rc  ]
  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  rT  r@   r   rx   r   )r3   r'  r  r7  rB   ri   rj   r   r6   r6   r7   rd  
  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st| jj	 }|j
| |j
| d S )Nr4  re  r   r   ry   r   rf  zDROP TABLE IF EXISTS testrz   )rS   zPRIMARY KEY (`A`, `B`))rs   r\  r?   r6  rg  r  r   rr   r.   r=   r>   )r3   r'  r7  rh  ri  rj  r  rB   r6   r6   r7   rk  
  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   rl  
  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   rY  r   )r3   rh   rg   r  r7  rB   r6   r6   r7   rm  
  s    	



z)TestXMySQL.test_execute_closed_connectionc             C   s   d S )Nr6   )r3   r6   r6   r7   rn  
  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.*ro  F)r   r  rx   zselect * from test_tabler  rp  r  rq  z DROP TABLE IF EXISTS test_table2rr  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%  rs  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 )Nrt  r   ru  r   F)r  r   r   rx   )r   rp   rv  r?   r   r.   )r3   r   r6   r6   r7   rw  
  s    z'TestXMySQL.test_keyword_as_column_namesc                s  t ddgddgd}t dddgd	d
dgd}d}d| } fdd}tjtdd tj| j|dd W d Q R X || tj| j|ddd tjtdd tj| j|dd W d Q R X tj| j|ddd t| jdddgksttj| j|ddd t| jddddgks(t|| tj| j|ddd t| jddd gksbttj| j|ddd t| jdd!d"d#d$d%gkst|| d S )&Nr   r   ry   rz   )r!  r-  r   r   r   r{   r|   r   r{  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.   rY  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^  rc  r  rd  rk  rl  rm  rn  r]  rw  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@  rA  rB  r  rP  r  rR  Zstr_bytesrR  r	  rm   r  rL  rS  rX  rY  rZ  r{  r  r6   r6   r6   r7   <module>   s  0


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