Simple SELECT statement on existing table with SQLAlchemy
Solution 1
Found this while trying to figure out the same thing.
Here's how the answer appears to me. I'd LOVE to know if/where I'm wrong, or if this is all built into pandas by now.
To select data from a table via SQLAlchemy, you need to build a representation of that table within SQLAlchemy. If Jupyter Notebook's response speed is any indication, that representation isn't filled in (with data from your existing database) until the query is executed.
You need Table
to build a table. You need select
to select data from the database. You need metadata
... for reasons that aren't clear, even in the docs (http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData).
from sqlalchemy import create_engine, select, MetaData, Table, and_
engine = create_engine("dburl://user:pass@database/schema")
metadata = MetaData(bind=None)
table = Table(
'table_name',
metadata,
autoload=True,
autoload_with=engine
)
stmt = select([
table.columns.column1,
table.columns.column2]
).where(and_(
table.columns.column1 == 'filter1',
table.columns.column2 == 'filter2'
)
connection = engine.connect()
results = connection.execute(stmt).fetchall()
You can then iterate over the results. See SQLAlchemy query to return only n results? on how to return one or only a few rows of data, which is useful for slower/larger queries.
for result in results:
print(result)
I checked this with a local database, and the SQLAlchemy results are not equal to the raw SQL results. The difference, for my data set, was in how the numbers were formatted. SQL returned float64 (e.g., 633.07
), while SQLAlchemy returned objects (I think Decimal
, e.g. 633.0700000000
.)
Some help from here: https://www.datacamp.com/courses/introduction-to-relational-databases-in-python
Edit 2021-02-14: Updated the code above to add some more examples. Have not tested.
Solution 2
Since the original question has two columns in the select statement, and it can confuse some people on how to write using that:
from sqlalchemy import and_
stmt = select([users.columns.name,users.columns.age])
stmt= stmt.where(and_(name=='joe',age==100)
for res in connection.execute(stmt):
print(res)
Comments
-
ryantuck about 3 years
Nowhere on the internet does there exist a simple few-line tutorial on a simple
SELECT
statement for SQLAlchemy 1.0.Assuming I've established my database connection using
create_engine()
, and my database tables already exist, I'd like to know how to execute the following query:select name, age from users where name = 'joe' and age = 100