Say I have a Pandas data frame with records such as:
Time Action User Company User2
---------------------------------------------------
00:02 buy share msmith ACME tjones
00:03 sell share tjones Alpha msmith
...
and I have a database with tables:
ActionType (ID INT IDENTITY(1,1), Name VARCHAR)
Users (ID INT IDENTITY(1,1), Username VARCHAR, CompanyID INT FOREIGN KEY)
Companies (ID INT IDENTITY(1,1), CompanyName VARCHAR)
Events (ID INT IDENTITY(1,1), ActionID INT FOREIGN KEY, UserID INT FOREIGN KEY, CompanyID INT FOREIGN KEY, User2ID INT FOREIGN KEY)
I want to insert the data frame into the events table, but I want it to store the associated ID for each column, rather than the raw text. Is there a way to easily do that through SQLAlchemy (or other RDBMS or ORM packages), or do I need to go row by row and set variables such as
userid = session.query(Users).filter(Users.Username == df.User)
Alternatively, is the best way to handle this through the database? I could accomplish this by inserting the raw pandas data directly into a "staging" table, and then split the data points out into their respective tables using SQL.
That seems doable, I'm just looking to see if there is a more efficient solution through Python?
Bonus (possibly separate) question, how would I go about entering a new value into the tables when it is encountered (i.e. df.User is not in Users table, so I want to INSERT INTO Users VALUES ...)