Adapting Pandas data type using Psycopg2

As a data scientist you may probably use Pandas a lot to play with your data.

While it’s quite easy to read data from PostgreSQL combined with Psycopg2:

1
2
3
4
5
import psycopg2
import pandas as pd
with psycopg2.connect('some dsn') as conn:
data = pd.read_sql_query('your sql', conn)

but writing the data back to PostgreSQL we need to create some adapters. As the Pandas API doc(scroll down to bottom) shows, you may do:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import psycopg2
from psycopg2 import extensions
import numpy as np
def to_float(x):
"""psycopg2 interpolation to float"""
return extensions.AsIs('%f' % float(x))
def to_int(x):
"""psycopg2 interpolation to int"""
return extensions.AsIs('%d' % int(x))
extensions.register_adapter(np.floating, to_float)
extensions.register_adapter(np.integer, to_int)

Notice we adapt the base type as Pandas internal storing of float/int may be a little different from what you see in ‘dtype’

Zhanzhao Deo Liang wechat
欢迎关注我的个人订阅号: deoXdeo
今天的午餐全赖有你支持!