Insert dictionary into Postgres via SQLAlchemy

Question

I'm trying to load a dictionary into a Postgres database via SQLAlchemy in Python. The dictionary as it current exists is:

FinalData = {'GAME_ID': {0: '0021600457', 1: '0021600457', 2: '0021600457', 3: 
'0021600457', 4: '0021600457', 5: '0021600457'}, 'TEAM_ID': {0: 1610612744, 1: 
1610612744, 2: 1610612744, 3: 1610612744, 4: 1610612744, 5: 1610612744}, 
'TEAM_ABBREVIATION': {0: 'GSW', 1: 'GSW', 2: 'GSW', 3: 'GSW', 4: 'GSW', 5: 
'GSW'}}

I have the following code written (based off other tutorials/questions I've found here and online). I could make this work if I was only inserting 1 records - since I could manually assign the attribute to PlayerStats. But with the dictionary having multiple records, I don't know how to bulk pass them all onto the session statements.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class PlayerStats(Base):
    __tablename__ = 'PlayerStats'

    GAME_ID = Column(String(10), primary_key=True)
    TEAM_ID = Column(String(10))
    TEAM_ABBREVIATION = Column(String(8))

###################################################
## I DON'T KNOW WHAT GOES HERE TO BRIDGE THE GAP ##
###################################################

engine = create_engine('postgres://.........')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(FinalData)  # I'M ASSUMING I NEED TO ITERATE OVER THIS #

I'm sure I'm missing something simple, but how do I pass all contents of the FinalData dictionary onto the SQLAlchemy add statement?

nosklo · Accepted Answer · 2017-11-03 06:28:09Z

1

Your FinalData structure is made of 3 inner dicts, each one keyed by some integer sequence, so you have to unpack it by creating a new PlayerStats object for each key in the subdicts.

In the example below I'm using the integer keys of the FinalData['GAME_ID'] inner dict to search for values in the other two dicts to fill the remaining fields.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class PlayerStats(Base):
    __tablename__ = 'PlayerStats'

    GAME_ID = Column(String(10), primary_key=True)
    TEAM_ID = Column(String(10))
    TEAM_ABBREVIATION = Column(String(8))

###################################################
FinalData = {'GAME_ID': {0: '0021600457', 1: '0021600457', 2: '0021600457', 3: 
'0021600457', 4: '0021600457', 5: '0021600457'}, 'TEAM_ID': {0: 1610612744, 1: 
1610612744, 2: 1610612744, 3: 1610612744, 4: 1610612744, 5: 1610612744}, 
'TEAM_ABBREVIATION': {0: 'GSW', 1: 'GSW', 2: 'GSW', 3: 'GSW', 4: 'GSW', 5: 
'GSW'}}
###################################################

engine = create_engine('postgres://.........')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

for key in FinalData['GAME_ID']:
    p = PlayerStats()
    p.GAME_ID = FinalData['GAME_ID'][key]
    p.TEAM_ID = FinalData['TEAM_ID'].get(key)
    p.TEAM_ABBREVIATION = FinalData['TEAM_ABBREVIATION'].get(key)
    session.add(p)

session.commit()

edited Nov 3, 2017 at 6:28

answered Nov 3, 2017 at 6:21

nosklo

224k58 gold badges300 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Rodman Over a year ago

Great. Thank you!! In terms of best practices, does it make more sense to unpack the inner dictionaries and translate it into 1 large dictionary? If so, is there a correct process to do so?

nosklo Over a year ago

@GeorgeRodman No, I don't think you have to transform the dictionary before processing it, unless you're the one generating the dictionary and could generate it differently. Otherwise just accept it as it is, and convert it directly into database objects as shown above. Intermediate structure would just add to the confusion.

Collectives™ on Stack Overflow

Insert dictionary into Postgres via SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related