Fix to merge code from `_create_table_statement` and `get_schema`
State: closed

Closes #8097

Reorganized a lot of the SQL schema creation code.  Now database table is not dropped / create when `PandasSQLTable` object is instantiated, but instead when `insert` method is called on it.  This allow us to `get_schema` to generate `CREATE TABLE` code without actually creating the table.
(Includes updates from #8083, so will have to be rebased when that is merged)


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

Comments:
See #8097

@artemyk Thanks for working on this! I already took a brief look, but not yet detailed. There seems to be some good ideas, but it is a bit difficult to review because so many things are touched at once.

Can you try to keep this PR as focused as possible? Eg try not to make cosmetic or PEP8 changes to lines of code that you otherwise won't touch, that makes it easier to review (if there would be a lot to clean up, then a seperate PR is more handy and always welcome!). Also eg trying to get less code repetitions with the execute_insert and get_session methods is maybe a good idea, but again easier to keep for another PR.

Can you maybe also provide some reasoning here in the PR discussion for the things you changed. But I will ask some questions as inline comments.

@jorisvandenbossche Thanks for re-checking the timing.  I do think that the chunksize-handling code should not be reduplicated in two places if at all possible.  I just pushed a slightly different refactoring: now its the tuple iterator that is passed into either the sqlalchemy or the legacy `_execute_insert`.  I don't believe this should add any overhead above the master branch.  What do you think?

yes, that looks better!

@artemyk added some more comments. 
For the `meta` issue. You are right for the case that you provide yourself a meta object, and then do two repeated calls to `to_sql` for the same table. So I think the options are to or 1) indeed do like you now implemented, or 2) not yet create the table statement in the init function, but only in `create` if it is needed. Do you have any preference?

@jorisvandenbossche I don't think 2 is an option, since we need to create the table object in order to call `sql_schema` on it (but we don't necessarily want to insert the table into the database, which would occur in `create`.

@artemyk I think it might be possible, but I can always try if I find some time. Let's try to merge this:
- can you still have a look at this: https://github.com/pydata/pandas/pull/8232/files#r17511871
- you will have to rebase, as #8208 is merged (and there certainly will be some conflicts ..)
- as always: squash, and can you include the issue number in the commit message? (like `(GH8097)`)

@jorisvandenbossche The issue with using the schema from metadata was an error on my part --- there was no need to do that ( https://github.com/pydata/pandas/pull/8232/files#r17511871).
Rebased and squashed.

@artemyk Tests were failing due to `izip` not available in python 3, see my comment. For the rest looking good!

@jorisvandenbossche Great, thanks for taking a look.  Replacing izip with zip, hopefully the tests will pass now.

@artemyk I think this is ready to merge (I already commented enough :-), the last comments on the connection/transaction issues can be addressed in another PR, I will open an issue for that). 
Ready on your part?

@jorisvandenbossche I think everything's ready....

okido, thanks a lot!

Opened a new issue for the connection context issue: #8277