How to access preexisting table with Sqlalchemy

Question

I'm working with scrapy. I want to get access to a sqlalchemy session for a table with a table named 'contacts' according to the docs (http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#getting-a-session ) I have created the following:

engine = create_engine('sqlite:///data.db')
# create a configured "Session" class
Session = sessionmaker(bind=engine)
# create a Session
session = Session()
class ContactSpider(Spider):
.......
 def parse(self, response):
 print('hello')
 session.query(contacts).filter_by(name='ed').all() 

However I am not seeing a way to connect to a preexisting table. How is this done?

• Laurent LAPORTE: Use table/class mapping. Search for "DeclarativeBase" in the doc or tutorial.

Answer 1

You can connect to pre-existing tables via reflection. Unfortunately your question lacks some of the code setup, so below is a general pseudocode example (assuming your table name is contacts)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
# Look up the existing tables from database
Base.metadata.reflect(engine)
# Create class that maps via ORM to the database table
Contact = type('Contact', (Base,), {'__tablename__': 'contacts'})

• user1592380: Thank you that does work, but I'm having trouble understanding the connection b/w a Session and The model object (Contact). I'll ask a follow up. Also why is it called 'declarative_base'
• Ilja Everilä: Reflecting declarative model classes automatically is handled already by automap, which will also create relationships etc. I'd highly recommend using it instead of custom dynamic class creation.