How to filter a SQL query on columns in SQLAlchemy?

Question

I am new to flask-alchemy. I want to filter a SQL query on the values of a LOC_CODE column. I made db.session.query(schools).filter_by(LOC_CODE='X270').first(). But the compiler returns:

(base) C:\Users\antoi\Documents\Programming\musicaltroupefinder>python hello_world.py
C:\ProgramData\Anaconda3\lib\site-packages\flask_sqlalchemy\__init__.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
 * Serving Flask app "hello_world" (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
the total number of school is  3281
[2019-12-18 11:08:31,831] ERROR in app: Exception on / [GET]
Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\_collections.py", line 210, in __getattr__
    return self._data[key]
KeyError: 'LOC_CODE'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\base.py", line 399, in _entity_descriptor
    return getattr(entity, key)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\_collections.py", line 212, in __getattr__
    raise AttributeError(key)
AttributeError: LOC_CODE

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "hello_world.py", line 37, in index
    school = db.session.query(schools).filter_by(LOC_CODE='X270').first()
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 1800, in filter_by
    for key, value in kwargs.items()
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 1800, in <listcomp>
    for key, value in kwargs.items()
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\base.py", line 402, in _entity_descriptor
    "Entity '%s' has no property '%s'" % (description, key)
sqlalchemy.exc.InvalidRequestError: Entity 'schools' has no property 'LOC_CODE'
127.0.0.1 - - [18/Dec/2019 11:08:31] "[1m[35mGET / HTTP/1.1[0m" 500 -

However, I have this column in the database:

sqlite> SELECT * FROM SCHOOLS ORDER BY ROWID ASC LIMIT 1
   ...>
   ...> ;
0,,O,0,OAQK,"Victory Schools, DBA The Charter School of Excelle",Elementary,2,0,0,260 WARBURTON AVE,NY,10701,,,,"0K,01,02,03,04",YONKERS,260 WARBURTON AVE,1006,YONKERS,"-73.897156,40.94465",119,"260 WARBURTON AVE, YONKERS, NY, 10701",0,Exact,Match,40.94465,-73.897156,"260 WARBURTON AVE, YONKERS, NY, 10701",R,NY,36,139742928,402,10701
sqlite> PRAGMA table_info(schools);
0|,ATS_CODE,BORO,BORONUM,LOC_CODE,SCHOOLNAME,SCH_TYPE,MANAGED_BY,GEO_DISTRI,ADMIN_DIST,ADDRESS,STATE_CODE,ZIP,PRINCIPAL,PRIN_PH,FAX,GRADES,City,address2,block,city2,coordinates,county_fips,geocoded_address,id,is_exact,is_match,latitude,longitude,returned_address,side,state,state_fips,tiger_line,tract,zipcode|TEXT|0||0

Here's my entire code:

from flask import Flask  # pip install flask
from flask import render_template
from flask_sqlalchemy import SQLAlchemy


from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker    

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydb.db'
db = SQLAlchemy(app)

schools = db.Table("schools",db.metadata, autoload = True, autoload_with = db.engine)  


@app.route("/")
def index():
    results = db.session.query(schools).count()
    print("the total number of school is ", db.session.query(schools).count())
    school = db.session.query(schools).filter_by(LOC_CODE='X270').first()
    print("School's name is", school.SCHOOLNAME)
    return render_template("index.html")

@app.route("/map")
def shoelaces():
    return "This works now!"

@app.route("/about")
def about():
    return "All about my website"

if __name__ == "__main__":
    app.run()

But I couldn't tell SQLAlchemy that we are lazy and that he should learn on his own about the database, we use this line:

I based it on this tutorial but I couldn't tell SQLAlchemy that he was lazy and that he should learn on his own about the database with a line db.Model.metadata.reflect(db.engine).

With classes

I also tried with a class:

(base) C:\Users\antoi\Documents\Programming\musicaltroupefinder>python hello_world.py
C:\ProgramData\Anaconda3\lib\site-packages\flask_sqlalchemy\__init__.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
 * Serving Flask app "hello_world" (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
[2019-12-20 13:03:58,460] ERROR in app: Exception on / [GET]
Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: schools.LOC_CODE

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\ProgramData\Anaconda3\lib\site-packages\flask\app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "hello_world.py", line 32, in index
    school = School.query.filter(School.LOC_CODE == 'X270').all()
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3186, in all
    return list(self)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3342, in __iter__
    return self._execute_and_instances(context)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3367, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: schools.LOC_CODE
[SQL: SELECT schools."LOC_CODE" AS "schools_LOC_CODE"
FROM schools
WHERE schools."LOC_CODE" = ?]
[parameters: ('X270',)]
(Background on this error at: http://sqlalche.me/e/e3q8)
127.0.0.1 - - [20/Dec/2019 13:03:58] "[1m[35mGET / HTTP/1.1[0m" 500 -

Here is the code

from flask import Flask  # pip install flask
from flask import render_template
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker


from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydb.db'
db = SQLAlchemy(app)

class School(db.Model):
    __tablename__ = 'schools'
    # __table_args__ = { 'extend_existing': True }
    LOC_CODE = db.Column(db.Text, primary_key=True)   


@app.route("/")
def index():
    school = School.query.filter(School.LOC_CODE == 'X270').first()
    print("School's name is", school.SCHOOLNAME)
    return render_template("index.html")

in your second attempt you are creating a `schools` table and a `School` table via the class. Try combining them using the __tablename__ parameter of the class. — Attack68, Dec 19 '19 at 21:15

hSin · Accepted Answer · 2019-12-27T17:35:08.067

The error has to do with how you utilize db or session. In your third code snippet, the configuration is handled by the School class, and your hashed out line is important to the subsequent queries through it. It is also important for utilizing the reflect method on the db.engine configuration. Switching filter() to filter_by() will also yield the result. I've split my .py files into two, one to create the sqlite db and another for the Flask session. Minimal changes were required to get no error and expected result, tested on both scripts' ending lines.

#create_db.py

from sqlalchemy import create_engine, MetaData, Table, Column, String
from sqlalchemy.orm import mapper, sessionmaker

engine= create_engine('sqlite:///example.db')
metadata = MetaData(engine)


table = Table('schools', metadata,
            Column('name', String),
            Column('LOC_CODE', String))

metadata.create_all()
ins = table.insert().values(name = 'Victory',
                            LOC_CODE = 'X270'
                            )

conn = engine.connect()
conn.execute(ins)

Session = sessionmaker(bind=engine)
session = Session()

schools = table


# Test session below
results = session.query(schools).count()
filt = session.query(schools).filter_by(LOC_CODE='X270').first()


print(results)
print(filt)

#filter_by_test.py

from flask import Flask
from flask import render_template
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy import create_engine, MetaData, Table, Column, String



app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'

db = SQLAlchemy(app)

db.Model.metadata.reflect(db.engine)

class School(db.Model):
    __tablename__ = 'schools'
    __table_args__ = {'extend_existing': True}
    LOC_CODE = db.Column(db.Text, primary_key = True)


@app.route("/")
def hello():
    print("Total number of schools is", School.query.count())

    school = School.query.filter_by(LOC_CODE='X270').first()
    print(school.name)

hello()

#combined.py

from flask import Flask
from flask import render_template
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy import create_engine, MetaData, Table, Column, String

import sqlite3
import pandas as pd

#data = 'schools-geocoded - schools-geocoded.csv'
#df = pd.read_csv(data)
#con = sqlite3.connect('example2.db')

#df.to_sql("schools", con)


app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example2.db'

db = SQLAlchemy(app)

db.Model.metadata.reflect(db.engine)

class School(db.Model):
    __tablename__ = 'schools'
    __table_args__ = {'extend_existing': True}
    LOC_CODE = db.Column(db.Text, primary_key = True)


@app.route("/")
def hello():
    print("Total number of schools is", School.query.count())

    school = School.query.filter_by(LOC_CODE='X270').first()
    print(school.SCHOOLNAME)

hello()

Thanks for your answer I still can't apply it to my database file which you can find [here](https://drive.google.com/file/d/14gUI-BsYC-mJjN53bSzAwW_TYK8V1ljg/view?usp=sharing). I don't think it miscoded [the csv file](https://drive.google.com/open?id=188HbjB8LabxlsXbWepqjzH-SYbYbabONWL0Yybywzws) — Revolucion for Monica, Dec 27 '19 at 17:19
I updated the answer with a 3rd script `combined.py`. Remove the hash lines for creation of db from csv. It yields expected results. — hSin, Dec 27 '19 at 17:37
Thanks, in that case two columns in the csv file will create some issues as far as they are duplicates with two others. How can I skip them ? (It is `city` and `address`) — Revolucion for Monica, Dec 27 '19 at 17:42
Before the `df.to_sql` line you can drop the columns. `df.drop(columns=['address2', 'city2'])` or `df.drop(['address2', 'city2'], axis=1)`. — hSin, Dec 27 '19 at 17:48

l33tHax0r · Answer 2 · 2019-12-23T06:37:51.683

0

I was able to get past your first error by running these commands from the python console first, then starting up the flask application. This creates the database that is used by your code in the given example. If you have an existing database you will need to change the URI to where it is at. The URI you are using is the one that will be created by using this command. I would look over the Flask-SQLAlchemy documentation to see how to connect to an existing database if desired.

from yourapplication import db
db.create_all()

Source: https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/

edited Dec 23 '19 at 06:37

answered Dec 23 '19 at 06:30

l33tHax0r

1,384
1
15
31

I have an existing database. It is in the same folder as the script. However even when specifying the full path to `mydb.db` I still get the same error. – Revolucion for Monica Dec 26 '19 at 13:53
@IggyPass Something is wrong with your database file. I tried a couple of ways of creating the databases including the way the tutorial includes and they all work. I would go through the tutorial and try creating with TablePlus – l33tHax0r Dec 27 '19 at 02:24
Okay, I'm on it. Yet I can't create again my table using [my csv file](https://docs.google.com/spreadsheets/d/188HbjB8LabxlsXbWepqjzH-SYbYbabONWL0Yybywzws/edit?usp=sharing) even when not importing the duplicates on TablePlus – Revolucion for Monica Dec 27 '19 at 12:47
@IggyPass what is your current error? Did you change all the things to text as well ? I excluded using the reflect method in my code – l33tHax0r Dec 27 '19 at 16:35
On TablePlus ? `create table failed` – Revolucion for Monica Dec 27 '19 at 16:54
[Here](https://drive.google.com/file/d/14gUI-BsYC-mJjN53bSzAwW_TYK8V1ljg/view?usp=sharing) is the former dattabase file – Revolucion for Monica Dec 27 '19 at 17:03
for the database ? I used the format `mydb.db` for the former attempt which I give as a link. – Revolucion for Monica Dec 27 '19 at 17:14
the tutorial says to name it a certain name, try that – l33tHax0r Dec 27 '19 at 17:37

How to filter a SQL query on columns in SQLAlchemy?

With classes

2 Answers2