ENH: sql support for writing NaN + datetime64 values (GH2754, GH7103)
State: closed

Closes #2754 
Closes #7103, #7936 (supercedes PR #8205 for this)
Closes #7567

Database drivers expect `None` to translate this to `NULL`. 
Therefore, dataframe is cast to object dtype to convert `NaN` to `None`.

To check: performance impact. In principle should also not needed for integer colums (can't have NaNs)

Kind of API change: it did already work for postgresql (psycopg2), but it was translated to postgresql's `NaN` and now to `NULL` which is something different (but I think this is actually an improvement)


Labels:
name: Missing-data
description: np.nan, pd.NaT, pd.NA, dropna, isnull, interpolate
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
@jreback What are potential side-effects of doing such a `astype(object)`? 

Eg I noticed that `datetime64` columns are converted to `datetime.datetime` (which I find a bit strange), and `timedelta64` columns to ints. 
Is there a rationale of what can be expected of `astype(object)`?

`astype(object)` if very specific. (in that the conversions are what you are seeing - not that `Timedelta` will coerce to `timedelta`, simlar to `Timestamp` -> `datetime`).

I wrote in your other thread that you might simply want to create a direct ndarray of the column and then just process it how you need.

Yes, we now do `np.asscalar(i)` on every individual value when iterating over the dataframe to insert in the database (see https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L601 and https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L641). 
But actually, just doing `astype(object)` (which already seems to convert it from numpy types to native python types) and then not anymore the `asscalar` checking, seems to be a lot faster (for a float column about 25% faster)

@jorisvandenbossche yep, you might be better off inferring columns that are `object` to start (you know all other columns dtypes already, so you know what to do). Then treating those which are inferred in a weird way (e.g. `mixed` or something) specially (which normally you shouldn't have anyhow, and maybe you should just raise on weirdness like that anyhow).

I know you guys did `itertuples` but that is by definition very slow.

I would like to see you go block by block and create a numpy array (or even a list) of the values you are going to insert. Then you can chunk on that.

See here: https://github.com/pydata/pandas/blob/master/pandas/core/format.py#L1416

This is almost exactly what you need to do (except that `to_native_types` converts to a string, but similar idea). Then the writer takes the block order and writes it in the column order (its a simple transformation).

@jreback Thanks for the pointers. It's actually the first time that I go a bit into the internals of pandas, so some questions:
- Is `itertuples` that slow if you actually need tuples? (in fact: we need a dict, not a tuple)
  
  ```
  data_list = []
  for t in frame.iloc[start_i:end_i].itertuples():
      data = dict((k, v) for k, v in zip(keys, t[1:]))
      data_list.append(data)
  ```
  
  vs
  
  ```
  data_list = [dict((k, v) for k, v in zip(keys, row)) for row in frame.values[start_i:end_i]]
  ```
  
  It is indeed a bit faster to directly iterate the array, but not that much.
- What is the advantage of going block by block? (instead of just making the list of dicts directly from the `frame.values`? Speed? Or also something else? 
  Using a block by block approach to create the list of arrays and then list of dicts:
  
  ```
  ncols = len(keys)
  col_list = [None] * ncols
  blocks = frame._data.blocks
  
  for i in range(len(blocks)):
      b = blocks[i]
      d = np.array(b.values, dtype=object)
      mask = pd.isnull(d)
      d[mask] = None
  
      for col_loc, col in zip(b.mgr_locs, d):
          col_list[col_loc] = col
  
  data_list = [dict((k, v) for k, v in zip(keys, row)) for row in zip(*col_list)]
  ```
  
  is indeed somewhat faster than 
  
  ```
  frame = frame.astype(object)
  data_list = [dict((k, v) for k, v in zip(keys, row)) for row in frame.values]
  ```
  
  or
  
  ```
  frame = frame.astype(object)
  
  col_list = []
  for col in frame:
      col_list.append(frame[col].values)
  
  data_list = [dict((k, v) for k, v in zip(keys, row)) for row in zip(*col_list)]
  ```
- But the advantage of the two above approaches instead of going block by block, is that `frame.astype(object)` seems to do the thing I need (numpy numeric types to native types, datetime64 to datetime.datetime, ..), while `values.astype(object)` (witch in fact happens with the block approach, as it works there with numpy arrays) does not really do the same. 
  Example (assuming `df` a mixed dtype dataframe with a datetime64 column 'date'):
  
  ```
  In [177]: df['date'].astype(object).values
  Out[177]: 
  array([datetime.datetime(2012, 1, 1, 0, 0),
       datetime.datetime(2012, 1, 2, 0, 0),
       datetime.datetime(2012, 1, 3, 0, 0),
       datetime.datetime(2012, 1, 4, 0, 0),
       datetime.datetime(2012, 1, 5, 0, 0)], dtype=object)
  
  In [178]: df['date'].values.astype(object)
  Out[178]: 
  array([1325376000000000000L, 1325462400000000000L, 1325548800000000000L,
       1325635200000000000L, 1325721600000000000L], dtype=object)
  ```
  
  So if I am going through the data block by block I will have to special case the handling of the data based on the dtype/blocktype.
  
  To make the complexity complete (for me at least), if you do `.values` on the whole dataframe, you even get a third result (no int or datetime.datetime, but Timestamps):
  
  ```
  In [183]: df.values
  Out[183]: 
  array([[0.0, Timestamp('2012-01-01 00:00:00')],
       [5.2, Timestamp('2012-01-02 00:00:00')],
       [63.2, Timestamp('2012-01-03 00:00:00')],
       [25.0, Timestamp('2012-01-04 00:00:00')],
       [12.2, Timestamp('2012-01-05 00:00:00')]], dtype=object)
  ```

The main speed-up will be of not having to call the `maybe_asscalar` anymore on each individual value:

```
def maybe_asscalar(self, i):
    try:
        return np.asscalar(i)
    except AttributeError:
        return i
```

As this is not needed anymore if I cast to `object` somewhere as above (are there case where this will not be equivalent?).

@jorisvandenbossche so depends on what you need for different dtypes.

In pytables, the requirements were this:

need specific conversions for a particular dtype, but don't want/need to go thru `object` dtype and/or numpy does NOT do the righ thing. E.g. `datetime64[ns]` -> `int64` (maybe with some tz conversions and such). strings -> a fixed length numpy string dtype. So block-by-block makes sense. Of course I had to present this to `PyTables` as a rec-array so this makes it easy (no point in going to tuples / other python structures). 

in csv, was slightly different. Perf is really everything here. The actual conversions are not time consuming (e.g. replaceing NaT to 'NaT' and stringifying). The actual writing is the bottlneck, so that's in cython. Its indexing list-of-list with preallocation (so not constantly creating/destroying memory). 

So nothing wrong with tuples/dicts. It that's what you need in the final format for passing to the driver then nothing you can do. That said it _may_ make sense to convert by dtype (block). You can do whatever you need at that point (e.g. using numpy directly or whatever). The only thing I would caution is this, using `df.values` essentially copies everything an coerces everything to `object`). Much better to do thsi block by block (and convert each dtype individually).

What I need in the end for the sql insert `execute`: a list of dicts (like records in `to_dict(outtype='records')`. So I need to do something like `[dict((k, v) for k, v in zip(keys, row)) for row in zip(*col_list)]` I think, the question is how to create the `col_list` or similar.  
What conversions I need: all numpy dtypes should be converted to native python types (for this reason `np.asscalar` (`.item()`) was used). Am I correct to assume that `astype(object)` does the same on a numpy array? (I can't really find this somewhere in the docs, but the fact that `np.asscalar` is not vectorized is one of the bottlenecks, and `astype(object)` _seems_ to do the right thinkg but much faster)

You say "The only thing I would caution is this, using `df.values` essentially copies everything and coerces everything to object). Much better to do this block by block (and convert each dtype individually).". Is this different for `df.astype(object)` (it gives another result!) or for `block.values.astype(object)` or `np.array(block.values, dtype=object)` (what I do in the block by block version above)? 

Actually, the reason that `df['date'].values.astype(object)` gave ints, is that this is an array with `datetime64[ns]` nanosecond resolution. If you first convert this to microseconds `[us]` then it gets coerced to `datetime.datetime` (datetime.datetime has no nanoseconds). But still, I find it a bit strange that `df.values` gives something else than `df.astype(object).values` (as you say that `df.values` coerces everything to object)

`df.astype(object).values` and `df.values` are not necessary the same thing because the first goes thru the `asobject` mechanism (which coerces for example `Timestamp` -> `datetime`). While the second just takes the object (whatever it is and puts in in a numpy object array). So the `Timestamps` are not changed.

OK, I get that. 
Another question, assuming a DatetimeBlock `block`, to get an array of values to insert in sql, I can do `block.astype(object).values` (gives datetime.datetime) or `block.values.astype(object)` (gives ints, so have to do `astype('M8[us]').astype(object)` to get datetimes. Or something else?). Any difference between both?

In any case `b.values.astype('M8[us]').astype(object)` is a lot faster as `block.astype(object).values`

@jreback can you have a look? I tried to get the data to insert in sql using blocks. I still have to run the vbenches, but normally it should be a good bit faster.

@jorisvandenbossche minor comment, but looks good!

I ran the vbenches:

```
-------------------------------------------------------------------------------
Test name                                    | head[ms] | base[ms] |  ratio   |
-------------------------------------------------------------------------------
sql_write_fallback                           | 106.5431 | 258.0450 |   0.4129 |
sql_string_write_fallback                    |  58.3140 | 121.6860 |   0.4792 |
packers_write_sql                            | 2607.6457 | 5189.1503 |   0.5025 |
sql_datetime_write_sqlalchemy                | 178.1230 | 328.7040 |   0.5419 |
sql_string_write_sqlalchemy                  |  92.7503 | 162.8133 |   0.5697 |
sql_float_write_fallback                     |  61.7257 | 104.0957 |   0.5930 |
sql_write_sqlalchemy                         | 237.5257 | 392.7880 |   0.6047 |
sql_float_write_sqlalchemy                   | 105.4697 | 139.4690 |   0.7562 |
sql_float_read_query_sqlalchemy              |  13.4517 |  14.5890 |   0.9220 |
packers_read_sql                             | 510.0226 | 545.4030 |   0.9351 |
sql_read_table_sqlalchemy                    |  39.6350 |  41.5033 |   0.9550 |
sql_read_query_fallback                      |  27.3767 |  28.3880 |   0.9644 |
sql_read_query_sqlalchemy                    |  35.5783 |  36.1260 |   0.9848 |
sql_datetime_read_as_native_sqlalchemy       |  27.8057 |  28.0294 |   0.9920 |
sql_datetime_read_and_parse_sqlalchemy       |  23.1274 |  22.9696 |   1.0069 |
sql_float_read_table_sqlalchemy              |  16.0140 |  15.1150 |   1.0595 |
sql_float_read_query_fallback                |   7.5050 |   6.9203 |   1.0845 |
```

So all 'write' vbenches are between 0.41 and 0.76. And the time for the general `packers_to_sql` has halved!

@jorisvandenbossche awesome!

Still slower than the other packers, but at this moment, about 60% (sqlite3 fallback) to 75% (sqlalchemy engine) is spent by sqlite3/sqlalchmey execute itself, so something we can't speed up ourselves.