Dynamic Datasets and SQLAlchemy <p>I am refactoring some old SQLite3 SQL statements in Python into SQLAlchemy. In our framework, we have the following SQL statements that takes in a dict with certain known keys and potentially any number of unexpected keys and values (depending what information was provided). </p> <pre><code>import sqlite3 import sys def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d def Create_DB(db): # Delete the database from os import remove remove(db) # Recreate it and format it as needed with sqlite3.connect(db) as conn: conn.row_factory = dict_factory conn.text_factory = str cursor = conn.cursor() cursor.execute("CREATE TABLE [Listings] ([ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [timestamp] REAL NOT NULL DEFAULT(( datetime ( 'now' , 'localtime' ) )), [make] VARCHAR, [model] VARCHAR, [year] INTEGER);") def Add_Record(db, data): with sqlite3.connect(db) as conn: conn.row_factory = dict_factory conn.text_factory = str cursor = conn.cursor() #get column names already in table cursor.execute("SELECT * FROM 'Listings'") col_names = list(map(lambda x: x[0], cursor.description)) #check if column doesn't exist in table, then add it for i in data.keys(): if i not in col_names: cursor.execute("ALTER TABLE 'Listings' ADD COLUMN '{col}' {type}".format(col=i, type='INT' if type(data[i]) is int else 'VARCHAR')) #Insert record into table cursor.execute("INSERT INTO Listings({cols}) VALUES({vals});".format(cols = str(data.keys()).strip('[]'), vals=str([data[i] for i in data]).strip('[]') )) #Database filename db = 'test.db' Create_DB(db) data = {'make': 'Chevy', 'model' : 'Corvette', 'year' : 1964, 'price' : 50000, 'color' : 'blue', 'doors' : 2} Add_Record(db, data) data = {'make': 'Chevy', 'model' : 'Camaro', 'year' : 1967, 'price' : 62500, 'condition' : 'excellent'} Add_Record(db, data) </code></pre> <p>This level of dynamicism is necessary because there's no way we can know what additional information will be provided, but, regardless, it's important that we store all information provided to us. This has never been a problem because in our framework, as we've never expected an unwieldy number of columns in our tables.</p> <p>While the above code works, it's obvious that it's not a clean implementation and thus why I'm trying to refactor it into SQLAlchemy's cleaner, more robust ORM paradigm. I started going through SQLAlchemy's official tutorials and various examples and have arrived at the following code:</p> <pre><code>from sqlalchemy import Column, String, Integer from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker Base = declarative_base() class Listing(Base): __tablename__ = 'Listings' id = Column(Integer, primary_key=True) make = Column(String) model = Column(String) year = Column(Integer) engine = create_engine('sqlite:///') session = sessionmaker() session.configure(bind=engine) Base.metadata.create_all(engine) data = {'make':'Chevy', 'model' : 'Corvette', 'year' : 1964} record = Listing(**data) s = session() s.add(record) s.commit() s.close() </code></pre> <p>and it works beautifully with that data dict. Now, when I add a new keyword, such as</p> <pre><code>data = {'make':'Chevy', 'model' : 'Corvette', 'year' : 1964, 'price' : 50000} </code></pre> <p>I get a <code>TypeError: 'price' is an invalid keyword argument for Listing</code> error. To try and solve the issue, I modified the class to be dynamic, too:</p> <pre><code>class Listing(Base): __tablename__ = 'Listings' id = Column(Integer, primary_key=True) make = Column(String) model = Column(String) year = Column(Integer) def __checker__(self, data): for i in data.keys(): if i not in [a for a in dir(self) if not a.startswith('__')]: if type(i) is int: setattr(self, i, Column(Integer)) else: setattr(self, i, Column(String)) else: self[i] = data[i] </code></pre> <p>But I quickly realized this would not work at all for several reasons, e.g. the class was already initialized, the data dict cannot be fed into the class without reinitializing it, it's a hack more than anything, et al.). The more I think about it, the less obvious the solution using SQLAlchemy seems to me. So, my main question is, <strong>how do I implement this level of dynamicism using SQLAlchemy?</strong></p> <p>I've researched a bit to see if anyone has a similar issue. The closest I've found was <a href="http://stackoverflow.com/questions/2768607/dynamic-class-creation-in-sqlalchemy">Dynamic Class Creation in SQLAlchemy</a> but it only talks about the constant attributes ("<strong>tablename</strong>" et al.). I believe the unanswered <a href="http://stackoverflow.com/questions/29105206/sqlalchemy-dynamic-attribute-change">SQLalchemy dynamic attribute change</a> may be asking the same question. While Python is not my forte, I consider myself a highly skilled programmer (C++ and JavaScript are my strongest languages) in the context scientific/engineering applications, so I may not hitting the correct Python-specific keywords in my searches.</p> <p>I welcome any and all help.</p>