Search code examples
sqlalchemy

SQLAlchemy core -How do I access an existing Postgresql database?

I have created the below class This seems to give me access to the schema of the table but not to the table itself. Most of the examples I am seeing, including the one in the manual follow the code that was used to create the tables

class DataBase:
    def __init__(self,user, password, db, host='localhost', port=5432):
        '''Initializes the connection and a metadata objects'''
        # We connect with the help of the PostgreSQL URL
        # postgresql://federer:grandestslam@localhost:5432/tennis
        url = 'postgresql://{}:{}@{}:{}/{}'
        url = url.format(user, password, host, port, db)

        # The return value of create_engine() is our connection object
        self.engine = sqlalchemy.create_engine(url, client_encoding='utf8', echo=False)  #echo=True enable logging (This is Python loggin module)
        self.conn=self.engine.connect()
        # We then bind the connection to MetaData()
        self.meta = sqlalchemy.MetaData(bind=self.con, reflect=True)

And then I use

if __name__ == '__main__':
    db=DataBase('nme','nme','nme')   
    db.meta.tables['tablename']

But this is giving me access to the schema of the table I want to insert a record in this table after the table was created

Edit: this worked, thank you

known_devices=Table('known_devices',self.meta,autoload=True)

        ins=known_devices.insert().values(
                      ....        
            )

        result=self.conn.execute(ins)
Solution

  • Looking at https://docs.sqlalchemy.org/en/latest/core/reflection.html and https://docs.sqlalchemy.org/en/latest/core/dml.html#sqlalchemy.sql.expression.Insert.values

    it should be something along the lines of

    yourtable=Table('tablename', meta, autoload=True)
conn.execute(yourtable.insert(), field="content")