sqlalchemy existing database query

by

You seem to have an impression that SQLAlchemy can only work with a database structure created by SQLAlchemy (probably using MetaData.create_all()) – this is not correct. SQLAlchemy can work perfectly with a pre-existing database, you just need to define your models to match database tables. One way to do that is to use reflection, as Ilja Everilä suggests:

from sqlalchemy import Table
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class MyClass(Base):
    __table__ = Table('mytable', Base.metadata,
                    autoload=True, autoload_with=some_engine)

(which, in my opinion, would be totally fine for one-off scripts but may lead to incredibly frustrating bugs in a “real” application if there’s a potential that the database structure may change over time)

Another way is to simply define your models as usual taking care to define your models to match the database tables, which is not that difficult. The benefit of this approach is that you can map only a subset of database tables to you models and even only a subset of table columns to your model’s fields. Suppose you have 10 tables in the database but only interested in users table from where you only need id, name and email fields:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    email = sa.Column(sa.String)

(note how we didn’t need to define some details which are only needed to emit correct DDL, such as the length of the String fields or the fact that the email field has an index)

SQLAlchemy will not emit INSERT/UPDATE queries unless you create or modify models in your code. If you want to ensure that your queries are read-only you may create a special user in the database and grant that user SELECT privileges only. Alternatively/in addition, you may also experiment with rolling back the transaction in your application code.

More Related Contents:

  1. How to get column names from SQLAlchemy result (declarative syntax)
  2. SQLAlchemy printing raw SQL from create()
  3. Difference between filter and filter_by in SQLAlchemy
  4. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  5. SQLAlchemy IN clause
  6. Flask SQLAlchemy query, specify column names
  7. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  8. How can I bind a list to a parameter in a custom query in SQLAlchemy?
  9. How to disable SQLAlchemy caching?
  10. memory-efficient built-in SqlAlchemy iterator/generator?
  11. AttributeError: can’t set attribute when connecting to sqlite database with flask-sqlalchemy
  12. Case Insensitive Flask-SQLAlchemy Query
  13. sqlalchemy unique across multiple columns
  14. How to create a new database using SQLAlchemy?
  15. Bulk Insert A Pandas DataFrame Using SQLAlchemy
  16. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  17. Python, SQLAlchemy pass parameters in connection.execute
  18. How can I use psycopg2.extras in sqlalchemy?
  19. Multi-threaded use of SQLAlchemy
  20. How to discover table properties from SQLAlchemy mapped object
  21. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  22. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  23. SQLAlchemy Joinedload filter column
  24. creating a database outside the application context
  25. SQLAlchemy func.count on boolean column
  26. Using SQLAlchemy models in and out of Flask
  27. SQLAlchemy column name with space
  28. Connecting to SQL Server 2012 using sqlalchemy and pyodbc
  29. using sqlalchemy to load csv file into a database
  30. pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

Leave a Comment