title: Why are Pandas and GeoPandas able to read a database table using a DBAPI (psycopg2) connection but have to rely on SQLAlchemy to write one?
body: <h1>Context</h1>
  
  <p>I just get into trouble while trying to do some I/O operations on some databases from a <a href="https://www.python.org/" rel="nofollow noreferrer"><code>Python3</code></a> script.    </p>
  
  <p>When I want to <a href="https://www.psycopg.org/docs/module.html#psycopg2.connect" rel="nofollow noreferrer">connect</a> to a database, I habitually use <a href="https://www.psycopg.org" rel="nofollow noreferrer"><code>psycopg2</code></a> in order to handle the <a href="https://www.psycopg.org/docs/connection.html#connection" rel="nofollow noreferrer">connections</a> and <a href="https://www.psycopg.org/docs/cursor.html#cursor" rel="nofollow noreferrer">cursors</a>.    </p>
  
  <p>My data are usually stored as <a href="https://pandas.pydata.org/" rel="nofollow noreferrer"><code>Pandas</code></a> <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html" rel="nofollow noreferrer"><code>DataFrames</code></a> and/or <a href="https://geopandas.org" rel="nofollow noreferrer"><code>GeoPandas</code></a>'s equivalent <a href="https://geopandas.org/reference/geopandas.GeoDataFrame.html" rel="nofollow noreferrer"><code>GeoDataFrames</code></a>.    </p>
  
  <h1>Difficulties</h1>
  
  <h2>In order to <strong>read</strong> data <strong>from</strong> a database table;</h2>
  
  <h3>Using <a href="https://pandas.pydata.org/" rel="nofollow noreferrer"><code>Pandas</code></a>:</h3>
  
  <p>I can rely on its <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html" rel="nofollow noreferrer"><code>.read_sql()</code></a> methods which takes as a parameter <code>con</code>, as stated in the doc:</p>
  
  <pre><code>con : SQLAlchemy connectable (engine/connection) or database str URI
          or DBAPI2 connection (fallback mode)'
          Using SQLAlchemy makes it possible to use any DB supported by that
          library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible
          for engine disposal and connection closure for the SQLAlchemy connectable. See
          `here &lt;https://docs.sqlalchemy.org/en/13/core/connections.html&gt;`_
  </code></pre>
  
  <h3>Using <a href="https://geopandas.org" rel="nofollow noreferrer"><code>GeoPandas</code></a>:</h3>
  
  <p>I can rely on its <a href="https://geopandas.org/io.html?highlight=read_postgis" rel="nofollow noreferrer"><code>.read_postigs()</code></a> methods which takes as a parameter <code>con</code>, as stated in the doc:    </p>
  
  <pre><code>con : DB connection object or SQLAlchemy engine
          Active connection to the database to query.
  </code></pre>
  
  <h2>In order to <strong>write</strong> data <strong>to</strong> a database table;</h2>
  
  <h3>Using <a href="https://pandas.pydata.org/" rel="nofollow noreferrer"><code>Pandas</code></a>:</h3>
  
  <p>I can rely on the <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html" rel="nofollow noreferrer"><code>.to_sql()</code></a> methods which takes as a parameter <code>con</code>, as stated in the doc:    </p>
  
  <pre><code>con : sqlalchemy.engine.Engine or sqlite3.Connection
          Using SQLAlchemy makes it possible to use any DB supported by that
          library. Legacy support is provided for sqlite3.Connection objects. The user
          is responsible for engine disposal and connection closure for the SQLAlchemy
          connectable See `here                 &lt;https://docs.sqlalchemy.org/en/13/core/connections.html&gt;`_
  </code></pre>
  
  <h3>Using <a href="https://geopandas.org" rel="nofollow noreferrer"><code>GeoPandas</code></a>:</h3>
  
  <p>I can rely on the <code>.to_sql()</code> methods (which directly relies on the <code>Pandas</code> <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html" rel="nofollow noreferrer"><code>.to_sql()</code></a>) which takes as a parameter <code>con</code>, as stated in the doc:</p>
  
  <pre><code>con : sqlalchemy.engine.Engine or sqlite3.Connection
          Using SQLAlchemy makes it possible to use any DB supported by that
          library. Legacy support is provided for sqlite3.Connection objects. The user
          is responsible for engine disposal and connection closure for the SQLAlchemy
          connectable See `here                 &lt;https://docs.sqlalchemy.org/en/13/core/connections.html&gt;`_
  </code></pre>
  
  <p>From here, I easily understand that <a href="https://geopandas.org" rel="nofollow noreferrer"><code>GeoPandas</code></a> is built on <a href="https://pandas.pydata.org/" rel="nofollow noreferrer"><code>Pandas</code></a> especially for its <a href="https://geopandas.org/reference/geopandas.GeoDataFrame.html" rel="nofollow noreferrer"><code>GeoDataFrame</code></a> object, which is, shortly, a special <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html" rel="nofollow noreferrer"><code>DataFrame</code></a> that can handle geographic data.</p>
  
  <p>But I'm wondering why do <a href="https://geopandas.org" rel="nofollow noreferrer"><code>GeoPandas</code></a> has the ability to directly takes a <a href="https://www.psycopg.org" rel="nofollow noreferrer"><code>psycopg2</code></a> connection as an argument and not <a href="https://pandas.pydata.org/" rel="nofollow noreferrer"><code>Pandas</code></a> and if it is planned for the latter?   </p>
  
  <p>And why is it neither the case for one nor the other when it comes to <strong>writing</strong> data?<br>
  I would like (as probably many of others<sup>1,2</sup>) to directly give them a <a href="https://www.psycopg.org" rel="nofollow noreferrer"><code>psycopg2</code></a> <a href="https://www.psycopg.org/docs/connection.html#connection" rel="nofollow noreferrer">connections</a> argument instead of relying on <a href="https://www.sqlalchemy.org/" rel="nofollow noreferrer"><code>SQLAlchemy</code></a> <a href="https://docs.sqlalchemy.org/en/13/core/engines.html" rel="nofollow noreferrer">engine</a>.<br>
  Because even is this tool is really great, it makes me use two different frameworks to connect to my database and thus handle two different connection strings (and I personally prefer the way <a href="https://www.psycopg.org/" rel="nofollow noreferrer"><code>psycopg2</code></a> handles the parameters expansion from a dictionary to build a connection string properly such as; <code>psycopg2.connect(**dict_params)</code> vs <a href="https://github.com/sqlalchemy/sqlalchemy/blob/a3145cffe2bdce393795cf9cd51605227be00b7f/lib/sqlalchemy/engine/url.py#L26" rel="nofollow noreferrer">URL</a> injection as explained here for example: <a href="https://stackoverflow.com/questions/55895457/is-it-possible-to-pass-a-dictionary-into-create-engine-function-in-sqlalchemy">Is it possible to pass a dictionary into create_engine function in SQLAlchemy?</a>).</p>
  
  <h1>Workaround</h1>
  
  <ol>
  <li><p>I was first creating my connection string with <a href="https://www.psycopg.org" rel="nofollow noreferrer"><code>psycopg2</code></a> from a dictionary of parameters this way:    </p>
  
  <pre><code>connParams = ("user={}", "password={}", "host={}", "port={}", "dbname={}")
  conn = ' '.join(connParams).format(*dict_params.values())
  </code></pre></li>
  <li><p>Then I figured out it was better and more pythonic this way:    </p>
  
  <pre><code>conn = psycopg2.connect(**dict_params)
  </code></pre></li>
  <li><p>Which I finally replaced by this, so that I can interchangeably use it to build either a <a href="https://www.psycopg.org" rel="nofollow noreferrer"><code>psycopg2</code></a> <a href="https://www.psycopg.org/docs/connection.html#connection" rel="nofollow noreferrer">connections</a>, or a <code>SQLAlchemy</code> engine:    </p>
  
  <pre><code>def connector():
      return psycopg2.connect(**dict_params)
  </code></pre>
  
  <p><strong>a)</strong> Initialize a <a href="https://www.psycopg.org" rel="nofollow noreferrer"><code>psycopg2</code></a> <a href="https://www.psycopg.org/docs/connection.html#connection" rel="nofollow noreferrer">connection</a> is now done by:</p>
  
  <pre><code>conn = connector()
  curs = conn.cursor()
  </code></pre>
  
  <p><strong>b)</strong> And initialize a <a href="https://www.sqlalchemy.org/" rel="nofollow noreferrer"><code>SQLAlchemy</code></a> <a href="https://docs.sqlalchemy.org/en/13/core/engines.html" rel="nofollow noreferrer">engine</a> by:</p>
  
  <pre><code>engine = create_engine('postgresql+psycopg2://', creator=connector)
  </code></pre></li>
  </ol>
  
  <p>(or with any of your flavored <code>db+driver</code>)</p>
  
  <p>This is well documented here:<br>
  <a href="https://docs.sqlalchemy.org/en/13/core/engines.html#custom-dbapi-args" rel="nofollow noreferrer">https://docs.sqlalchemy.org/en/13/core/engines.html#custom-dbapi-args</a><br>
  and here:<br>
  <a href="https://docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine" rel="nofollow noreferrer">https://docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine</a>    </p>
  
  <hr>
  
  <p>[1] <a href="https://stackoverflow.com/questions/47979102/dataframe-to-sql-without-sql-alchemy-engine">Dataframe to sql without Sql Alchemy engine</a><br>
  [2] <a href="https://stackoverflow.com/questions/58664141/how-to-write-data-frame-to-postgres-table-without-using-sqlalchemy-engine">How to write data frame to Postgres table without using SQLAlchemy engine?</a></p>