首页 > itarticle > adapting pandas data type using psycopg2

adapting pandas data type using psycopg2

admin 11月 14, 2020 0

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:

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:

import psycopg2
from psycopg2 import extensions
import numpy as np
def (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’