ENH: add more postgres support - started adding some oracle code
State: closed


Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
Blah, I need to merge in my branch to sql so that other people can build on that structure rather than the one that's on pydata/sql... can you take a look at my sql branch (jtratner/sql @ 41d250b5b87c15b66554a3c7e73cbbd6171b77f1) - that's probably where we're going to end up rather than the bit of a jumble that's currently under pydata/sql.

ok, ill take a look

On Fri, Oct 25, 2013 at 8:24 PM, Jeff Tratner notifications@github.comwrote:

> Blah, I need to merge in my branch to sql so that other people can build
> on that structure rather than the one that's on pydata/sql... can you take
> a look at my sql branch (jtratner/sql @ 41d250bhttps://github.com/pydata/pandas/commit/41d250b5b87c15b66554a3c7e73cbbd6171b77f1)
> - that's probably where we're going to end up rather than the bit of a
>   jumble that's currently under pydata/sql.
> 
> —
> Reply to this email directly or view it on GitHubhttps://github.com/pydata/pandas/pull/5323#issuecomment-27135106
> .

I added cStringIO and csv as imports to sql_legacy.py  __write_postgres now uses psycopg2.copy_from, and is much faster.  The downside is i had to check dtypes for numpy.object and remove any \n for my particular use case. i find the data i work with has some text fields with \n in them, breaking the csv write. Maybe theres a better way to incorporate the removal of \n in any strings found inside of dataframe.to_csv, then checking dtypes and  csv wouldnt be needed. 

@mangecoeur, can you opine as to how this fits in with #5950?

@y-p this is superceded by the sqlalchemy support, we keep sqlite and mysql only for legacy support (since these were the only ones supported before) #5950 

@rla3rd, @mangecoeur is heading pandas' next move on the sql front and I'm inclined to
go along with his appraisal. Do you disagree on #5950 superseding this and #5315?

@mangecoeur There is still some notion of postgres in your updated version of io.sql. Eg here: https://github.com/mangecoeur/pandas/blob/master/pandas/io/sql.py#L197. 
So, or this should be removed, or the PR #5315  from @rla3rd could be rebased/reworked on your PR to complete the postgres support. But given the move to SQLALchemy to prevent this kind of 'different implemention for each flavor' the first option seems better?

#5950 supercedes #5315

I would like to see #5323 make it into sql_legacy.py in #5950 for one
reason, sqlalchemy does not support postgresql's COPY command for bulk
loading a table into the database.  It is much faster than anything you
could do in SQLAlchemy, other than a conn.execute('COPY FROM ...'), but
that defeats the purpose of the ORM in the 1st place.

On Fri, 24 Jan 2014 08:45:39 -0800
y-p notifications@github.com wrote:

> @rla3rd, @mangecoeur is heading pandas' next move on the sql front
> and I'm inclined to go along with his appraisal. Do you disagree on
> #5950 superseding this and #5315?
> 
> ---
> 
> Reply to this email directly or view it on GitHub:
> https://github.com/pydata/pandas/pull/5323#issuecomment-33239118

@mangecoeur , I know SQLalchemy allows you to send raw SQL to the database,
do you expose a way to do that through the new API?

I've had to do bulk inserts in the past, it makes a huge difference.

ping @mangecoeur 

@y-p You can use the raw SQL in the new API
I really don't think anything should be added to the legacy code - that's sort of the point of it being "legacy". We already have a slightly confused situation where we have "legacy support"  which takes care of SQLite/MySQL without the SQLAlchemy dependancy but which uses the new OOP architecture - this is different from "sql_legacy.py" which just has the old stuff in it.

What would be better would be to enhance the new system with DB-specific code in special cases. For example we could detect when Postgres is being used and issue a COPY FROM statement instead of SELECT. SQLAlchemy would still be very useful in that it provides type information through table reflection so we can do appropriate type convertions.

So I would suggest stabilizing the new code first and then introducing performance optimizaitons later.

@jorisvandenbossche @mangecoeur @hayd 

are we keeping this one open, closing, ?

Closing
On Feb 16, 2014 4:35 PM, "jreback" notifications@github.com wrote:

> @jorisvandenbossche https://github.com/jorisvandenbossche @mangecoeurhttps://github.com/mangecoeur
> @hayd https://github.com/hayd
> 
> are we keeping this one open, closing, ?
> 
> ## 
> 
> Reply to this email directly or view it on GitHubhttps://github.com/pydata/pandas/pull/5323#issuecomment-35214415
> .