title: Appending Pandas dataframes with time to SQLite3 databases & back
body: <p>I'm trying this:</p>
  
  <pre><code>import pandas as pd
  import sqlite3
  import datetime, pytz
  
  #nowtime=datetime.datetime.now(pytz.utc)
  nowtime=datetime.datetime.now()
  
  print(nowtime)
  df = pd.DataFrame(columns=list('ABCD'))
  df.loc[0]=(3,0.141,"five-nine",nowtime)
  df.loc[1]=(1,0.41,"four-two",nowtime)
  
  print(df)
  
  db = sqlite3.connect(':memory:')
  c = db.cursor()
  c.execute('create table if not exists ABCD ( A integer, B real, C text, D timestamp );')
  c.execute('insert into ABCD (A,B,C, D) values (?,?,?,?);',(1,2.2,'4',nowtime))
  c.executemany('insert into ABCD (A,B,C, D) values (?,?,?,?);',df.to_records(index=False))
  
  db.commit()
  
  print(pd.read_sql('select * from ABCD;',db))
  </code></pre>
  
  <p>and getting this:</p>
  
  <pre><code> 2018-03-07 19:09:58.584953
     A      B          C                          D
  0  3  0.141  five-nine 2018-03-07 19:09:58.584953
  1  1  0.410   four-two 2018-03-07 19:09:58.584953
     A      B          C                           D
  0  1  2.200          4  2018-03-07 19:09:58.584953
  1  3  0.141  five-nine    b'\xa8hx?\t\xb9\x19\x15'
  2  1  0.410   four-two    b'\xa8hx?\t\xb9\x19\x15'
  </code></pre>
  
  <p>Ideally, I'd like to push some data with timestamps into sqlite3 and recover it back into pandas/python/numpy interoperably.</p>
  
  <p>I've seen <a href="https://stackoverflow.com/questions/23574614/appending-pandas-dataframe-to-sqlite-table-by-primary-key">Appending Pandas dataframe to sqlite table by primary key</a> for appending, but I'm not sure how to work with datetime.datetime, pandas Timestamps or numpy.datetime64 times with sqlite3.</p>
  
  <p>Also, there was <a href="https://stackoverflow.com/questions/1829872/how-to-read-datetime-back-from-sqlite-as-a-datetime-instead-of-string-in-python">How to read datetime back from sqlite as a datetime instead of string in Python?</a> but I couldn't figure out how to do it in pandas.</p>
  
  <p>One thing I spent a lot of time on was <a href="https://stackoverflow.com/a/21916253/1653571">https://stackoverflow.com/a/21916253/1653571</a> and the confusing multiple to_datetime()s.</p>
  
  <p>What's a good way to work with times, sqlite3, and pandas?</p>
  
  ####### update:
  
  <p>I tried these changes:</p>
  
  <pre><code>db = sqlite3.connect(':memory:',detect_types=sqlite3.PARSE_DECLTYPES)
  
  #...
  for index,row in df.iterrows():
      print(row)
      c.execute('insert into ABCD (A,B,C,D) values (?,?,?,?);',(row.A,row.B,row.C,row.D.to_pydatetime()))
  
  
  x = pd.read_sql('select *  from ABCD;',db)
  
  print('Type of a pd.read_sql(SQLite3) timestamp  : ',type(x['D'][0]))
  
  x = c.execute('select * from ABCD').fetchall()
  
  print(x)
  print('Type of a sqlite.execute(SQLite3) timestamp  : ',type(x[0][3]))
  </code></pre>
  
  <p>to use the SQLite3 datatypes and to test the returned values:</p>
  
  <pre><code>Type of a pd.read_sql(SQLite3) timestamp  :  &lt;class 'pandas._libs.tslib.Timestamp'&gt;
  [(1, 2.2, '4', datetime.datetime(2018, 3, 8, 14, 46, 2, 520333)), (3, 141.0, 'five-nine', datetime.datetime(2018, 3, 8, 14, 46, 2, 520333)), (1, 41.0, 'four-two', datetime.datetime(2018, 3, 8, 14, 46, 2, 520333))]
  Type of a sqlite.execute(SQLite3) timestamp  :  &lt;class 'datetime.datetime'&gt;
  </code></pre>
  
  <p>Also, as I tried <code>datetime.datetime.now(pytz.utc)</code> to get UTC-aware times, but it broke lots of stuff.  Using <code>datetime.datetime.utcnow()</code> worked much better by returning a not-timezone-aware object that isn't affected by timezones.</p>
  
  <p>Note also the Python sqlite3 documentation on the <code>sqlite3.connect(detect_types=...)</code> parameter.  Enabling <code>detect_types=PARSE_DECLTYPES|PARSE_COLNAMES</code> cues python to run converters on data passed between the systems.</p>
  
  <ul>
  <li><a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_DECLTYPES" rel="nofollow noreferrer">https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_DECLTYPES</a> for <code>create table ... xyzzy timestamp, ...</code> conversions</li>
  <li><a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_COLNAMES" rel="nofollow noreferrer">https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_COLNAMES</a> for <code>select ... date as "dateparsed [datetime]"...</code> conversions  </li>
  </ul>