Python: programmatically supplying column names and datatypes to schema definition

user_7064053 0
Viewed 82

I am writing to snowflake through python. Once I create a final pandas dataframe, I am creating schema definitions with each columns and the data type I want it to be:

schema_name= [('col1', 'string'), ('col2', 'string'), ('col3', 'date'), ('col3', 'float')...]

which I then pass it to write to Snowflake, I want to avoid this manual coding so when any column name changes in the dataframe it automatically updates in schema definition as well. How do I accomplish this?

pandas python
1 Answers

Let's say you have following dataframe:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'ID': [23,24,25,26,27],
    'x': [0.0,2.2,2.3,2.4,2.5],
    'y': ['a','b','c','d','e']
})

Using following expression

list(zip(df.columns, df.dtypes))

following output will be retrieved:

[('ID', dtype('int64')), ('x', dtype('float64')), ('y', dtype('O'))]

Slightly modified the expression

list(zip(df.columns, [dtype.name for dtype in df.dtypes]))

will provide following output:

[('ID', 'int64'), ('x', 'float64'), ('y', 'object')]

If there should be some mapping between pandas dtypes and types used in snowflake, a mapping function could be provided:

# dictionary containing pandas dtype names as keys and target type names as values
mappings = {
    'int64': 'int',
    'float64': 'float'
}

# function retrieving values from dictionary or some default value
def mapping(typename):
  if typename in mappings:
    return mappings[typename]
  return 'unknown'

# mapping of types
list(zip(df.columns, [mapping(dtype.name) for dtype in df.dtypes]))

That will output

[('ID', 'int'), ('x', 'float'), ('y', 'unknown')]
user_141686230
Related