<h4 dir="auto">Code Sample</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; pd.to_datetime('2017-01-01T15:00:00-05:00')
... Timestamp('2017-01-01 20:00:00')  # This is a timezone-naive Timestamp, but was converted to UTC"><pre class="notranslate"><span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">pd</span>.<span class="pl-en">to_datetime</span>(<span class="pl-s">'2017-01-01T15:00:00-05:00'</span>)
... <span class="pl-v">Timestamp</span>(<span class="pl-s">'2017-01-01 20:00:00'</span>)  <span class="pl-c"># This is a timezone-naive Timestamp, but was converted to UTC</span></pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">Given a timezone-aware string (e.g., in ISO8601 format), <code class="notranslate">pd.to_datetime</code> converts the string to UTC time, but doesn't set the <code class="notranslate">tzinfo</code> to UTC.</p>
<p dir="auto">This makes it difficult to differentiate between string that are truly timezone-naive strings (e.g., '2017-01-01') and timezone-aware strings (e.g., '2017-01-01T15:00:00-05:00').</p>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">Option 1: Convert the string to UTC (current behavior) and also set the timezone to UTC:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; pd.to_datetime('2017-01-01T15:00:00-05:00')
... Timestamp('2017-01-01 20:00:00+0000', tz='UTC')"><pre class="notranslate"><span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">pd</span>.<span class="pl-en">to_datetime</span>(<span class="pl-s">'2017-01-01T15:00:00-05:00'</span>)
... <span class="pl-v">Timestamp</span>(<span class="pl-s">'2017-01-01 20:00:00+0000'</span>, <span class="pl-s1">tz</span><span class="pl-c1">=</span><span class="pl-s">'UTC'</span>)</pre></div>
<p dir="auto">Option 2: Don't convert to UTC and retain timezone. This is the behavior of <code class="notranslate">dateutil.parser.parse</code>:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; pd.to_datetime(parser.parse('2017-01-01T15:00:00-0500'))
... Timestamp('2017-01-01 15:00:00-0500', tz='tzoffset(None, -18000)')"><pre class="notranslate"><span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">pd</span>.<span class="pl-en">to_datetime</span>(<span class="pl-s1">parser</span>.<span class="pl-en">parse</span>(<span class="pl-s">'2017-01-01T15:00:00-0500'</span>))
... <span class="pl-v">Timestamp</span>(<span class="pl-s">'2017-01-01 15:00:00-0500'</span>, <span class="pl-s1">tz</span><span class="pl-c1">=</span><span class="pl-s">'tzoffset(None, -18000)'</span>)</pre></div>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
INSTALLED VERSIONS
------------------
commit: None
python: 3.5.3.final.0
python-bits: 64
OS: Linux
OS-release: 4.1.35-pv-ts1
machine: x86_64
processor: 
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8
<p dir="auto">pandas: 0.20.3<br>
pytest: 3.1.2<br>
pip: 9.0.1<br>
setuptools: 27.2.0<br>
Cython: 0.25.2<br>
numpy: 1.12.1<br>
scipy: 0.19.1<br>
xarray: None<br>
IPython: 6.1.0<br>
sphinx: 1.6.2<br>
patsy: 0.4.1<br>
dateutil: 2.6.0<br>
pytz: 2017.2<br>
blosc: None<br>
bottleneck: 1.2.1<br>
tables: 3.3.0<br>
numexpr: 2.6.2<br>
feather: None<br>
matplotlib: 2.0.2<br>
openpyxl: 2.4.7<br>
xlrd: 1.0.0<br>
xlwt: None<br>
xlsxwriter: 0.9.6<br>
lxml: None<br>
bs4: 4.6.0<br>
html5lib: 0.999<br>
sqlalchemy: 1.1.11<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.9.6<br>
s3fs: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>
<p dir="auto">Suppose you have an existing SQL table called <code class="notranslate">person_age</code>, where <code class="notranslate">id</code> is the primary key:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
    age
id	
1	18
2	42"><pre class="notranslate">    <span class="pl-s1">age</span>
<span class="pl-s1">id</span>	
<span class="pl-c1">1</span>	<span class="pl-c1">18</span>
<span class="pl-c1">2</span>	<span class="pl-c1">42</span></pre></div>
<p dir="auto">and you also have new data in a <code class="notranslate">DataFrame</code> called <code class="notranslate">extra_data</code></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="	age
id	
2	44
3	95"><pre class="notranslate">	<span class="pl-s1">age</span>
<span class="pl-s1">id</span>	
<span class="pl-c1">2</span>	<span class="pl-c1">44</span>
<span class="pl-c1">3</span>	<span class="pl-c1">95</span></pre></div>
<p dir="auto">then it would be useful to have an option on <code class="notranslate">extra_data.to_sql()</code> that allows to pass the DataFrame to SQL with an <code class="notranslate">INSERT</code> or <code class="notranslate">UPDATE</code> option on the rows, based on the <code class="notranslate">primary key</code>.</p>
<p dir="auto">In this case, the <code class="notranslate">id=2</code> row would get updated to  <code class="notranslate">age=44</code> and the <code class="notranslate">id=3</code> row would get added</p>
<h4 dir="auto">Expected Output</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="	age
id	
1	18
2	44
3	95"><pre class="notranslate">	<span class="pl-s1">age</span>
<span class="pl-s1">id</span>	
<span class="pl-c1">1</span>	<span class="pl-c1">18</span>
<span class="pl-c1">2</span>	<span class="pl-c1">44</span>
<span class="pl-c1">3</span>	<span class="pl-c1">95</span></pre></div>
<h4 dir="auto">(Maybe) helpful code references</h4>
<ul dir="auto">
<li><a href="http://stackoverflow.com/questions/7889183/sqlalchemy-insert-or-update-example" rel="nofollow">Use <code class="notranslate">merge</code> from SQLAlchemy</a>?</li>
<li>The query: '''INSERT or REPLACE into person_age (id, age) values (?,?,?) ''' in <a href="http://stackoverflow.com/questions/23574614/appending-pandas-dataframe-to-sqlite-table-by-primary-key" rel="nofollow">this question</a></li>
</ul>
<p dir="auto">I looked at <code class="notranslate">pandas</code> <code class="notranslate">sql.py</code> sourcecode to come up with a solution, but I couldn't follow.</p>
<h4 dir="auto">Code to replicate the example above</h4>
<p dir="auto">(Apologies for mixing <code class="notranslate">sqlalchemy</code> and <code class="notranslate">sqlite</code></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd
from sqlalchemy import create_engine
import sqlite3
conn = sqlite3.connect('example.db')
 
c = conn.cursor()
c.execute('''DROP TABLE IF EXISTS person_age;''')
c.execute('''
          CREATE TABLE person_age
          (id INTEGER PRIMARY KEY ASC, age INTEGER NOT NULL)
          ''')
conn.commit()
conn.close()

##### Create original table

engine = create_engine(&quot;sqlite:///example.db&quot;)
sql_df = pd.DataFrame({'id' : [1, 2], 'age' : [18, 42]})

sql_df.to_sql('person_age', engine, if_exists='append', index=False)


#### Extra data to insert/update

extra_data = pd.DataFrame({'id' : [2, 3], 'age' : [44, 95]})
extra_data.set_index('id', inplace=True)

#### extra_data.to_sql()  with row update or insert option

expected_df = pd.DataFrame({'id': [1, 2, 3], 'age': [18, 44, 95]})
expected_df.set_index('id', inplace=True)"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>
<span class="pl-k">import</span> <span class="pl-s1">sqlite3</span>
<span class="pl-s1">conn</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlite3</span>.<span class="pl-en">connect</span>(<span class="pl-s">'example.db'</span>)
 
<span class="pl-s1">c</span> <span class="pl-c1">=</span> <span class="pl-s1">conn</span>.<span class="pl-en">cursor</span>()
<span class="pl-s1">c</span>.<span class="pl-en">execute</span>(<span class="pl-s">'''DROP TABLE IF EXISTS person_age;'''</span>)
<span class="pl-s1">c</span>.<span class="pl-en">execute</span>(<span class="pl-s">'''</span>
<span class="pl-s">          CREATE TABLE person_age</span>
<span class="pl-s">          (id INTEGER PRIMARY KEY ASC, age INTEGER NOT NULL)</span>
<span class="pl-s">          '''</span>)
<span class="pl-s1">conn</span>.<span class="pl-en">commit</span>()
<span class="pl-s1">conn</span>.<span class="pl-en">close</span>()

<span class="pl-c">##### Create original table</span>

<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite:///example.db"</span>)
<span class="pl-s1">sql_df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'id'</span> : [<span class="pl-c1">1</span>, <span class="pl-c1">2</span>], <span class="pl-s">'age'</span> : [<span class="pl-c1">18</span>, <span class="pl-c1">42</span>]})

<span class="pl-s1">sql_df</span>.<span class="pl-en">to_sql</span>(<span class="pl-s">'person_age'</span>, <span class="pl-s1">engine</span>, <span class="pl-s1">if_exists</span><span class="pl-c1">=</span><span class="pl-s">'append'</span>, <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)


<span class="pl-c">#### Extra data to insert/update</span>

<span class="pl-s1">extra_data</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'id'</span> : [<span class="pl-c1">2</span>, <span class="pl-c1">3</span>], <span class="pl-s">'age'</span> : [<span class="pl-c1">44</span>, <span class="pl-c1">95</span>]})
<span class="pl-s1">extra_data</span>.<span class="pl-en">set_index</span>(<span class="pl-s">'id'</span>, <span class="pl-s1">inplace</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-c">#### extra_data.to_sql()  with row update or insert option</span>

<span class="pl-s1">expected_df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'id'</span>: [<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">3</span>], <span class="pl-s">'age'</span>: [<span class="pl-c1">18</span>, <span class="pl-c1">44</span>, <span class="pl-c1">95</span>]})
<span class="pl-s1">expected_df</span>.<span class="pl-en">set_index</span>(<span class="pl-s">'id'</span>, <span class="pl-s1">inplace</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)</pre></div>