Automatically Build Database Table based on a Class with SQLAlchemy

Question

I'm really new to SQLAlchemy, and I love it. Right now I'm doing a lot of things manually and I want to do things more 'pythonically' and dynamic.

So as an example, I have this short script that manually creates/defines a table and then a function that inserts data into that table.

database connection

import os
from sqlalchemy import *
from sqlalchemy import schema, types
from sqlalchemy.ext.declarative import declarative_base  

db_url = os.environ.get('DATABASE_URL')
engine = create_engine(db_url)
Base = declarative_base(engine)
meta = Base.metadata

table definition

file_paths = Table('file_paths', meta,
    Column('table_id', Integer, primary_key = True),
    Column('fullpath', String(255)),
    Column('filename', String(255)),
    Column('extension', String(255)),
    Column('created', String(255)),
    Column('modified', String(255)),
    Column('size', Integer),
    Column('owner', String(255)),
    Column('permissions', Integer),
    mysql_engine='InnoDB',
)
file_paths.drop(engine, checkfirst = False)
file_paths.create(engine, checkfirst = True)

insert function takes a string and a list as arguments

def push_to_db(fullpath, fileInfo):
    i = file_paths.insert()
    i.execute(  fullpath    = str(fullpath), 
            filename    = str(fileInfo[0]),
            extension   = str(fileInfo[1]),
            created     = str(fileInfo[2]),
            modified    = str(fileInfo[3]),
            size        = str(fileInfo[4]),
            owner       = str(fileInfo[5]),
            permissions = str(fileInfo[6]),
         )

This works but it's ugly and taken right out of a tutorial I found somewhere online. My goal is to make these operations dynamic.

example class

class FileMeta(object):
    def __init__(self, fullPathFileName, filename):
        self.fullPathFileName = fullPathFileName
        self.filename = filename
        self.extension = os.path.splitext(self.filename)[1].lower()
        ...

    def fileMetaList(self):
        return [self.filename, self.extension, self.created, self.modified,\
                self.size, self.owner, self.permissions]

So here's the scenario: given a class object

define the table dynamically according to the class member variables
- column numbers and names should correspond to variable names
- or correspond to the index of that variable in a list of class variables
write a function that can insert data from the class into the corresponding dynamically created table

My intuition tells me this is what SQLAlchemy would be good for. Can someone tell me a good tutorial or reference that can outline this process?

You mean declarative object-relational configuration?

Martijn Pieters
– Martijn Pieters

2013-05-15 21:27:17 +00:00
Commented May 15, 2013 at 21:27 — Martijn Pieters
– Martijn Pieters, Commented May 15, 2013 at 21:27
I'm not sure what I mean. I'm a total noob to SQLAlchemy.

frankV
– frankV

2013-05-15 22:14:10 +00:00
Commented May 15, 2013 at 22:14 — frankV
– frankV, Commented May 15, 2013 at 22:14

Martijn Pieters · Accepted Answer · 2018-11-12 18:07:46Z

23

You want to use the declarative extension instead:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class FilePaths(Base):
    __tablename__ = 'file_paths'
    __table_args__ = {'mysql_engine':'InnoDB'}

    table_id = Column(Integer, primary_key=True)
    fullpath = Column(String(255))
    filename = Column(String(255))
    extension = Column(String(255))
    created = Column(String(255))
    modified = Column(String(255))
    size = Column(Integer)
    owner = Column(String(255))
    permissions = Column(Integer)

Base.metadata.create_all(engine)

You can define your own __init__() as needed, as well other methods, then create instances of these to insert new rows.

See the SQLAlchemy's own ORM tutorial.

edited Nov 12, 2018 at 18:07

answered May 15, 2013 at 21:31

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

6 Comments

Martijn Pieters Over a year ago

@laycat: this is what the OP asked for: a class definition that reflects the table row as an object. You can now use FilePath instances and they reflect database row contents transparently.

Dusty Boshoff Over a year ago

Would Base's AutoMap() also work for the dynamic creation of the tables? docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html

Martijn Pieters Over a year ago

@DustyBoshoff looks like it is designed for that purpose, yes. Note that that extension was added in 0.9.1; this answer was written in the 0.8 days.

Martijn Pieters Over a year ago

@DustyBoshoff: actually, automapper creates the declarative classes from an existing schema (albeit rudimentary ones); the OP was trying to create the classes manually (which gives you much more flexibility and control), but was using the wrong tools to do so.

alkanschtein Over a year ago

is there a way to do that with classes from other files eg file_paths.py has class FilePaths and just to import it

|

mejimaru · Accepted Answer · 2021-03-24 21:12:28Z

5

Add Automap extension :

from sqlalchemy.ext.automap import automap_base

# SQLAlchemy
engine = create_engine(DATABASE_URL)
metadata = MetaData()

Base = automap_base()
Base.prepare(engine, reflect=True)

answered Mar 24, 2021 at 21:12

mejimaru

631 silver badge4 bronze badges

Collectives™ on Stack Overflow

Automatically Build Database Table based on a Class with SQLAlchemy

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related