a
    1$ea                     @   s(  d Z ddlZddlZddlZddlZddlmZmZmZ ddl	m
Z ddlmZmZ ddlmZmZmZ ddlZzddlmZ W n ey   eZY n0 ejdd Ze d	d
 Ze dd Ze dd Zdd Zejdd Z ejdd Z!ejdd Z"ejdd Z#G dd dZ$dS )a  
Tests here include reading/writing to different types of spatial databases.
The spatial database tests may not work without additional system
configuration. postGIS tests require a test database to have been setup;
see geopandas.tests.util for more information.
    N)GeoDataFrame	read_fileread_postgis)	_get_conn_write_postgis)create_postgiscreate_spatialitevalidate_boro_df)textc                  C   s   t jd} t| }|S )Nnybb)	geopandasZdatasetsget_pathr   )Z	nybb_pathdf r   W/var/www/html/django/DPS/env/lib/python3.9/site-packages/geopandas/io/tests/test_sql.pydf_nybb   s    r   c                  c   s   t d} ddlm} d}tjd}tjd}tjd}tjd}z| j|||||d	}W n |y~   t d
 Y n0 t	
 $ t	jddd |V  W d   n1 s0    Y  |  dS )z|
    Initiates a connection to a postGIS database that must already exist.
    See create_postgis for more information.
    psycopg2r   )OperationalErrortest_geopandasPGUSER
PGPASSWORDPGHOSTPGPORT)dbnameuserpasswordhostport'Cannot connect with postgresql databaseignorez-pandas only supports SQLAlchemy connectable.*messageN)pytestimportorskipr   r   osenvirongetconnectskipwarningscatch_warningsfilterwarningsclose)r   r   r   r   r   r   r   conr   r   r   connection_postgis#   s&    



$r.   c               
   c   s   t d} ddlm} tjd}tjd}tjd}tjd}d}z(| |jd	|||||d
}|	  W n t
y   t d Y n0 |V  |  dS )zV
    Initiates a connection engine to a postGIS database that must already exist.
    
sqlalchemyr   )URLr   r   r   r   r   zpostgresql+psycopg2)Z
drivernameusernamedatabaser   r   r   r   N)r"   r#   Zsqlalchemy.engine.urlr0   r$   r%   r&   Zcreate_enginecreater'   	Exceptionr(   Zdispose)r/   r0   r   r   r   r   r   r-   r   r   r   engine_postgis?   s.    

r5   c                  c   s   t d} zL| d.}|d |d |d W d   n1 sJ0    Y  W n$ tyz   |  t d Y n0 |V  |  dS )a2  
    Return a memory-based SQLite3 connection with SpatiaLite enabled & initialized.

    `The sqlite3 module must be built with loadable extension support
    <https://docs.python.org/3/library/sqlite3.html#f1>`_ and
    `SpatiaLite <https://www.gaia-gis.it/fossil/libspatialite/index>`_
    must be available on the system as a SQLite module.
    Packages available on Anaconda meet requirements.

    Exceptions
    ----------
    ``AttributeError`` on missing support for loadable SQLite extensions
    ``sqlite3.OperationalError`` on missing SpatiaLite
    sqlite3z:memory:TZmod_spatialitez SELECT InitSpatialMetaData(TRUE)Nz Cannot setup spatialite database)	r"   r#   r'   Zenable_load_extensionZload_extensionexecuter4   r,   r(   )r6   r-   r   r   r   connection_spatialite`   s    


,r8   c                 C   s   t d}|| |r| }t ( tjddd ||  W d    n1 sX0    Y  |j	
|}|d ur|j| dd d S )Nr/   r   z-Did not recognize type 'geometry' of column.*r    T)Z
checkfirst)r"   r#   inspectZ	has_tableZMetaDatar)   r*   r+   ZreflectZtablesr&   Zdrop)Zconn_or_enginetabler/   metadatar   r   r   drop_table_if_exists~   s    

(r<   c                  C   sN   ddl m} m}m} tjd|ddg|ddgddgg| ddgid	d
}|S )Nr   )Point
LineStringMultiLineStringgeometryr   r      rC   )   rD   )   rE   rC   	epsg:4326crs)shapely.geometryr=   r>   r?   r   r   )r=   r>   r?   r   r   r   r   df_mixed_single_and_multi   s    

rJ   c                  C   sP   ddl m} m}m}m} tjd||g d|ddg| ddggidd}|S )	Nr   )r=   r>   PolygonGeometryCollectionr@   )rA   rB   r   rC   rA   rB   rF   rG   )rI   r=   r>   rK   rL   r   r   )r=   r>   rK   rL   r   r   r   r   df_geom_collection   s    

rN   c                  C   s(   ddl m}  tjd| dgidd}|S )Nr   )
LinearRingr@   )rA   rM   rB   )rC   r   rF   rG   )rI   rO   r   r   )rO   r   r   r   r   df_linear_ring   s
    rP   c                  C   sH   ddl m} m}m} tjd|ddg|g d| dddgid	d
}|S )Nr   )r=   r>   rK   r@   r   r   r   rC   rC   rC   )rQ   rR   )r   rC   rC   rC   rD   rF   rG   )rI   r=   r>   rK   r   r   )r=   r>   rK   r   r   r   r   df_3D_geoms   s    



rS   c                   @   s   e 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d Zdd Zdd Zdd  Zd!d" Zd#d$ Zd%d& Zd'd( Zd)d* Zd+d, Zd-d. Zd/d0 Zd1d2 Zd3d4 Zd5d6 Zd7d8 Zd9d: Zd;d< Z e!j"j#e$j%oe$j& d=d>d?d@ Z'dAS )BTestIOc              	   C   s   t dj}|}t|}t||s(J W d    n1 s<0    Y  | F}t|}t||shJ W d    n1 s|0    Y  W d    n1 s0    Y  t t: tt  W d    n1 s0    Y  W d    n1 s0    Y  d S )Nzsqlalchemy.engine.base)	r"   r#   
Connectionget_conn
isinstancer'   raises
ValueErrorobject)selfr5   rU   engineoutputconnr   r   r   test_get_conn   s    
,

JzTestIO.test_get_connc                 C   s6   |}t || d}t||}t| |jd u s2J d S NSELECT * FROM nybb;)r   r   r	   rH   r[   r.   r   r-   sqlr   r   r   r   test_read_postgis_default   s    

z TestIO.test_read_postgis_defaultc                 C   s4   |}d}t |||d d}t|||d}t| d S )Nthe_geomgeom_colra   )r   r   r	   r[   r.   r   r-   rg   rc   r   r   r   r   !test_read_postgis_custom_geom_col   s    z(TestIO.test_read_postgis_custom_geom_colc                 C   s@   |}d}d}t |||d d||}t|||d}t| dS )z6Tests that a SELECT {geom} AS {some_other_geom} works.geomre   rf   zZSELECT borocode, boroname, shape_leng, shape_area,
                    {} as {} FROM nybb;N)r   formatr   r	   )r[   r.   r   r-   Z	orig_geomZout_geomrc   r   r   r   r    test_read_postgis_select_geom_as   s    z'TestIO.test_read_postgis_select_geom_asc                 C   sH   |}d}| |}t||dd d}t||}t| |j|ksDJ dS )z<Tests that an SRID can be read from a geodatabase (GH #451).z	epsg:4269  Zsridra   N)to_crsr   r   r	   rH   )r[   r.   r   r-   rH   Z	df_reprojrc   r   r   r   r   test_read_postgis_get_srid  s    

z!TestIO.test_read_postgis_get_sridc                 C   sD   |}|j }t||dd d}t|||d}t| |j |ks@J dS )z?Tests that a user specified CRS overrides the geodatabase SRID.rm   rn   ra   rG   N)rH   r   r   r	   )r[   r.   r   r-   Zorig_crsrc   r   r   r   r   test_read_postgis_override_srid  s    z&TestIO.test_read_postgis_override_sridc                 C   s.   |}t || d}t||}t|dd d S )Nra   Fcase_sensitiver   r   Zfrom_postgisr	   rb   r   r   r   test_from_postgis_default  s
    
z TestIO.test_from_postgis_defaultc                 C   s:   |}d}t |||d d}tj|||d}t|dd d S )Nre   rf   ra   Frr   rt   rh   r   r   r   !test_from_postgis_custom_geom_col#  s    z(TestIO.test_from_postgis_custom_geom_colc                 C   sF   |}|j j}d|j jd< t|| d|}t|||d}t| dS )z*Tests that geometry with NULL is accepted.Nr   z\SELECT ogc_fid, borocode, boroname, shape_leng, shape_area, AsEWKB("{0}") AS "{0}" FROM nybbrf   )r@   nameZiatr   rk   r   r	   r[   r8   r   r-   rg   rc   r   r   r   r   test_read_postgis_null_geom-  s    
z"TestIO.test_read_postgis_null_geomc                 C   s:   |}|j j}t|| d|}t|||d}t| dS )z/Tests that geometry read as binary is accepted.zaSELECT ogc_fid, borocode, boroname, shape_leng, shape_area, ST_AsBinary("{0}") AS "{0}" FROM nybbrf   N)r@   rw   r   rk   r   r	   rx   r   r   r   test_read_postgis_binary:  s    
zTestIO.test_read_postgis_binaryc                 C   sD   d}|}t || d}tt|||d}t| |jdu s@J dS )zTest chunksize argumentrD   ra   )	chunksizeN)r   pdconcatr   r	   rH   )r[   r.   r   r{   r-   rc   r   r   r   r   test_read_postgis_chunksizeF  s    
z"TestIO.test_read_postgis_chunksizec                 C   sP   |}t || d}tt  tjj|| W d    n1 sB0    Y  d S r`   )r   r"   warnsFutureWarningr   iorc   r   )r[   r.   r   r-   rc   r   r   r   test_read_postgis_privacyT  s
    
z TestIO.test_read_postgis_privacyc                 C   sL   |}d}t || t|||dd tdj|d}t||dd}t| dS )	@Tests that GeoDataFrame can be written to PostGIS with defaults.r   failr-   rw   	if_existsSELECT * FROM {table};r:   r@   rf   Nr<   write_postgisr
   rk   r   r	   r[   r5   r   r\   r:   rc   r   r   r   r   test_write_postgis_default\  s    
z!TestIO.test_write_postgis_defaultc                 C   sL   |}d}t || t|||dd tdj|d}t||dd}t| dS )	z?Tests writing GeoDataFrame to PostGIS with uppercase tablename.Z
aTestTabler   r   zSELECT * FROM "{table}";r   r@   rf   Nr   r   r   r   r   &test_write_postgis_uppercase_tablenamek  s    
z-TestIO.test_write_postgis_uppercase_tablenamec                 C   sp   |  T}d}t|| t|||dd tdj|d}t||dd}t| W d   n1 sb0    Y  dS )	r   Znybb_conr   r   r   r   r@   rf   N)beginr<   r   r
   rk   r   r	   )r[   r5   r   r-   r:   rc   r   r   r   r   (test_write_postgis_sqlalchemy_connectionz  s    

z/TestIO.test_write_postgis_sqlalchemy_connectionc              
   C   sh   |}d}t |||dd zt |||dd W n6 tyb } zdt|v rJn|W Y d}~n
d}~0 0 dS )z[
        Tests that uploading the same table raises error when: if_replace='fail'.
        r   replacer   r   zalready existsN)r   rY   str)r[   r5   r   r\   r:   er   r   r   )test_write_postgis_fail_when_table_exists  s    z0TestIO.test_write_postgis_fail_when_table_existsc                 C   sR   |}d}t |||dd t |||dd tdj|d}t||dd}t| dS )	zV
        Tests that replacing a table is possible when: if_replace='replace'.
        r   r   r   r   r   r@   rf   N)r   r
   rk   r   r	   r   r   r   r   ,test_write_postgis_replace_when_table_exists  s    z3TestIO.test_write_postgis_replace_when_table_existsc                 C   s   |}d}|j \}}t|||dd t|||dd tdj|d}t||dd}|j \}	}
|	|d	 ks~J d
j|d	 |	df|
|ksJ dj||
dfdS )zt
        Tests that appending to existing table produces correct results when:
        if_replace='append'.
        r   r   r   appendr   r   r@   rf   rD   z.There should be {target} rows,found: {current})targetcurrentz1There should be {target} columns,found: {current}N)shaper   r
   rk   r   )r[   r5   r   r\   r:   Z	orig_rowsZ	orig_colsrc   r   Znew_rowsZnew_colsr   r   r   +test_write_postgis_append_when_table_exists  s$    


z2TestIO.test_write_postgis_append_when_table_existsc                 C   s   |}d}|}d|_ tjtdd  t|||dd W d   n1 sF0    Y  tdjd|d	d
}| "}||	 d }W d   n1 s0    Y  |dksJ d| dS )z\
        Tests that GeoDataFrame can be written to PostGIS without CRS information.
        r   Nz%Could not parse CRS from the GeoDataFmatchr   r   6SELECT Find_SRID('{schema}', '{table}', '{geom_col}');publicr@   schemar:   rg   r   zSRID should be 0, found %s)
rH   r"   r   UserWarningr   r
   rk   r'   r7   fetchone)r[   r5   r   r\   r:   rc   r^   target_sridr   r   r   test_write_postgis_without_crs  s    .
0z%TestIO.test_write_postgis_without_crsc           	      C   s   |}d}| d}t|||dd tdjd|dd}| "}|| d	 }W d
   n1 sf0    Y  |dksJ d| d
S )z{
        Tests that GeoDataFrame can be written to PostGIS with ESRI Authority
        CRS information (GH #2414).
        r   zESRI:102003r   r   r   r   r@   r   r   Nis zSRID should be 102003, found %s)ro   r   r
   rk   r'   r7   r   )	r[   r5   r   r\   r:   Zdf_nybb_esrirc   r^   r   r   r   r   &test_write_postgis_with_esri_authority  s    

0z-TestIO.test_write_postgis_with_esri_authorityc           	      C   s   |}d}t |||dd tdj|d}| "}|| d }W d   n1 sX0    Y  tdj|d}t||d	d
}| dksJ |j	 d dksJ dS )zS
        Tests that writing a mix of different geometry types is possible.
        geomtype_testsr   r   @SELECT DISTINCT(GeometryType(geometry)) FROM {table} ORDER BY 1;r   r   Nr   r@   rf   ZGEOMETRYCOLLECTIONrL   )
r   r
   rk   r'   r7   r   r   upper	geom_typeunique)	r[   r5   rN   r\   r:   rc   r^   r   r   r   r   r   &test_write_postgis_geometry_collection  s    
0z-TestIO.test_write_postgis_geometry_collectionc                 C   s   |}d}t |||dd tdj|d}| }|| }W d   n1 sT0    Y  |d d  dksvJ |d	 d  d
ksJ |d d  dksJ dS )zU
        Tests that writing a mix of single and MultiGeometries is possible.
        r   r   r   ?SELECT DISTINCT GeometryType(geometry) FROM {table} ORDER BY 1;r   Nr   
LINESTRINGrC   MULTILINESTRINGrD   POINT)r   r
   rk   r'   r7   fetchallr   )r[   r5   rJ   r\   r:   rc   r^   resr   r   r   'test_write_postgis_mixed_geometry_types  s    
,z.TestIO.test_write_postgis_mixed_geometry_typesc                 C   sv   |}d}t |||dd tdj|d}| "}|| d }W d   n1 sX0    Y  | dksrJ dS )	z2
        Tests that writing a LinearRing.
        r   r   r   r   r   r   Nr   )r   r
   rk   r'   r7   r   r   )r[   r5   rP   r\   r:   rc   r^   r   r   r   r   test_write_postgis_linear_ring*  s    
0z%TestIO.test_write_postgis_linear_ringc           	      C   s  |}d}t |||ddd tdj|d}| "}|| d }W d   n1 sZ0    Y  |d	kspJ td
j|d}| }|| }W d   n1 s0    Y  |d d  dksJ |d d  dksJ |d d  dksJ dS )z3
        Tests writing a LinearRing works.
        r   r   rC   )r-   rw   r   r{   z$SELECT COUNT(geometry) FROM {table};r   r   NrE   r   r   r   rD   r   )r   r
   rk   r'   r7   r   r   r   )	r[   r5   rJ   r\   r:   rc   r^   Zrow_cntr   r   r   r   test_write_postgis_in_chunks?  s.    
0
,z#TestIO.test_write_postgis_in_chunksc           	      C   s   |}d}d}t dj|d}| }|| W d   n1 sD0    Y  t|||d|d t dj||d	}t||d
d}t| dS );
        Tests writing data to alternative schema.
        r   test%CREATE SCHEMA IF NOT EXISTS {schema};r   Nr   r-   rw   r   r   SELECT * FROM {schema}.{table};r   r:   r@   rf   )r
   rk   r   r7   r   r   r	   	r[   r5   r   r\   r:   Zschema_to_userc   r^   r   r   r   r   &test_write_postgis_to_different_schema`  s    
(
z-TestIO.test_write_postgis_to_different_schemac           	      C   s   |}d}d}t dj|d}| }|| W d   n1 sD0    Y  z>t|||d|d t dj||d	}t||d
d}t| W n ty   Y n0 t|||d|d t dj||d	}t||d
d}t| dS )r   r   r   r   r   Nr   r   r   r   r@   rf   r   )r
   rk   r   r7   r   r   r	   rY   r   r   r   r   8test_write_postgis_to_different_schema_when_table_existsw  s6    
(

z?TestIO.test_write_postgis_to_different_schema_when_table_existsc                 C   sR   |}d}t |||dd tdj|d}t||dd}t|jjg dksNJ d	S )
zE
        Tests writing a geometries with 3 dimensions works.
        r   r   r   r   r   r@   rf   )TTTN)r   r
   rk   r   listr@   Zhas_z)r[   r5   rS   r\   r:   rc   r   r   r   r    test_write_postgis_3D_geometries  s    z'TestIO.test_write_postgis_3D_geometriesc                 C   sZ   |}d}|d   }t|||dd tdj|d}t||dd}|d   |ksVJ d	S )
z_
        Tests that the row order in db table follows the order of the original frame.
        Zrow_order_testZBoroCoder   r   r   r   r@   rf   N)tolistr   r
   rk   r   )r[   r5   r   r\   r:   Zcorrect_orderrc   r   r   r   r   test_row_order  s    zTestIO.test_row_orderc                 C   sL   |}d}t || t|||dd tdj|d}t||dd}t| dS )	z`
        Tests that insert works with if_exists='append' when table does not exist yet.
        r   r   r   r   r   r@   rf   Nr   r   r   r   r   test_append_before_table_exists  s    
z&TestIO.test_append_before_table_existsc                 C   sf   |}d}t |||dd |jdd}tjtdd  t |||dd W d	   n1 sX0    Y  d	S )
zS
        Tests that the warning is raised if table CRS differs from frame.
        r   r   r   i  )ZepsgzCRS of the target tabler   r   N)r   ro   r"   rX   rY   )r[   r5   r   r\   r:   Zdf_nybb2r   r   r   test_append_with_different_crs  s    z%TestIO.test_append_with_different_crsz;Duplicate columns are dropped in read_sql with pandas 2.0.x)reasonc                 C   sD   |}d}t t t||dd W d   n1 s60    Y  dS )za
        Tests that a ValueError is raised if an SQL query returns two geometry columns.
        z>select ST_MakePoint(0, 0) as geom, ST_MakePoint(0, 0) as geom;rj   rf   N)r"   rX   rY   r   )r[   r5   r\   rc   r   r   r   $test_duplicate_geometry_column_fails  s    z+TestIO.test_duplicate_geometry_column_failsN)(__name__
__module____qualname__r_   rd   ri   rl   rp   rq   ru   rv   ry   rz   r~   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r"   markZxfailcompatZPANDAS_GE_20ZPANDAS_GE_21r   r   r   r   r   rT      sF   
	
!,rT   )%__doc__r$   r)   Zpandasr|   r   r   r   r   Zgeopandas._compat_compatr   Zgeopandas.io.sqlr   rV   r   r   Zgeopandas.tests.utilr   r   r	   r"   r/   r
   ImportErrorr   Zfixturer   r.   r5   r8   r<   rJ   rN   rP   rS   rT   r   r   r   r   <module>   s>   



 



	
