How to Programming with Flask-sqlalchemy

python,unit-testing,flask,flask-sqlalchemy,py.test

You don't have to built your entire query in one line. You can build it as you go. query = Company.query if size: query = query.filter(Company.size == size) if industry_id: query = query.filter(Company.industry_id == industry_id) result = query.all() If you feel like this is too verbose, you could build a...

Isolating py.test DB sessions in Flask-SQLAlchemy

1. According to Session Basics - SQLAlchemy documentation: commit() is used to commit the current transaction. It always issues flush() beforehand to flush any remaining state to the database; this is independent of the “autoflush” setting. .... So transaction.rollback() in session fixture function does not take effect, because the transaction...

How can I use ndb outside of appengine?

python,database,flask,flask-sqlalchemy,app-engine-ndb

Currently this can work using the Remote API. Note that the process for setting up Remote API is a little complicated and using it has some limitations. This article walks through the steps about how to set it up for db, however the process is the same for ndb....

Flask-SQLAlchemy “MySQL server has gone away” when using HAproxy

python,mysql,flask,flask-sqlalchemy,haproxy

Just to tidy up this question with an answer I'll post what I (think I) did to solve the issues. Problem 1: HAproxy Either increase the HAproxy client timeout value (globally, or in the frontend definition) to a value longer than what MySQL is set to reset on (see this...

flask sqlalchemy update error - update() missing 1 required positional argument: 'self'

python,flask,flask-sqlalchemy,flask-restful

You are calling update on the User class rather than an instance of User. You could instantiate the User before calling update: user = User.query.get(id) user.update(email=args.email) Or you could make update a @classmethod and have it take the cls as the first argument rather than self and it would instantiate...

How to form complex mysql query that has left outer join, aggregate data with group by using SQLAlchemy?

mysql,sqlalchemy,flask-sqlalchemy

from sqlalchemy.sql import func query = ItemList.query.with_entities(ItemList.itemid) query = query.outerjoin(ItemTransactions, ItemTransactions.itemid == ItemList.itemid) query = query.add_columns(func.sum(ItemList.quantitySold).label('total_quantity_sold')) query = query.add_columns(func.max(ItemTransactions.createdAt).label('last_sale_time')) query = query.filter(ItemList.active == "y") query = query.group_by(ItemList.itemid) query = query.order_by(func.sum(ItemList.quantitySold).asc()) ...

How to test redirect to created instance in Flask

python,unit-testing,flask,flask-sqlalchemy,flask-testing

Query the created question object. As a side effect, you can test that the question was created. ... q = Question.query.filter_by(title='What about somestuff in Flask?').first() self.assertRedirects(response, '/questions/%d/' % q.id) ...

Linking tables in SQLAclhemy foreign_keys error [Flask]

json,flask,one-to-many,flask-sqlalchemy

So with the help of @dirn I got it to work by acessing the backref posts that I have made in the Post class under the category which was backref=db.backref('posts', lazy='dynamic')) In my route category/<name>/posts/ which is kind of which will look like @api.route('/category/<int:id>/posts/') def get_category_posts(id): category = Category.query.get_or_404(id) page...

Getting user by primary key in flask

python,flask,jinja2,flask-sqlalchemy

I noticed two things in your code: # this <a href={{url_for('profile')}}> # should be <a href={{url_for('profile', id=contact.id)}}> # otherwise Flask can't find the route, because it needs an id And the other one: {% for detail in Contacts %} There is no such thing as a Contacts variable in your...

one-to-many append attribute error

python-3.x,flask,sqlalchemy,flask-sqlalchemy

You configured the relationship incorrectly. Your picture_ids column will not work for a one to many relationship, since you can only store a single picture_id in that column. So remove that column and use this to set up the gallery: gallery = db.relationship('Picture', backref=db.backref('post', uselist=False)) Then you can remove the...

SQLAlchemy: Specifying session to use for model

flask,sqlalchemy,flask-sqlalchemy

SQLAlchemy sessions provide a no_autoflush context manager. This will suspend any flushes until after you exit the block. model1 = Model(name='spam') db.session.add(model1) # This will flush with db.session.no_autoflush: model2 = Model() db.session.add(model2) # This will not model2.name = 'eggs' db.session.commit() ...

PUT request not working, Flask-RESTful, SQLAlchemy

flask,sqlalchemy,flask-sqlalchemy,put,flask-restful

Change user.key = value to setattr(user, key, value). Instead of setting the attribute you want here (user.email) you're setting user.key. Because user.key is probably not a database column field (and certainly not the one you intend to set), the changes are not serialized to the database when db.session.commit() is called....

Python/SQLAlchemy migration - ValueError: need more than 3 values to unpack when migrating

python,flask,sqlalchemy,flask-sqlalchemy,sqlalchemy-migrate

Yes, td.columns_different is a dict of columns that changed between previous and current revision. This code belongs to sqlalchemy-migrate package. Comment from the source[1]: class TableDiff(object): """ ... ..attribute:: columns_different A dictionary containing information about columns that were found to be different. It maps column names to a :class:`ColDiff` objects...

Flask-SQLAlchemy Query Join relational tables

join,flask,sqlalchemy,flask-sqlalchemy

The error message is telling you that SQLAlchemy can't determine how to join the two tables users and friendships, because there is more than one foreign key linking them. You need to explicitly define the join condition. Try: userList = users.query.join(friendships, users.id==friendships.user_id).add_columns(users.userId, users.name, users.email, friends.userId, friendId).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page,...

Secondary Join Condition Ignored in Insert SQL Alchemy

flask,flask-sqlalchemy,flask-wtforms,flask-login,flask-restful

You cannot sqlalchemy to be able to handle cases like this out-of-the-box. The code below is what I would use in such case: class Player(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True) avatar = db.Column(db.String(120)) skill = db.Column(db.Integer) class Participant(db.Model): player_id = db.Column(db.Integer,...

flask wtforms_alchemy object has no attribute

UserCreateForm will inherit its fields from the columns you define on User. User has no column named password, therefore UserCreateForm doesn't have such a field. You'll have to add the field yourself. from wtforms.fields import PasswordField class UserCreateForm(ModelForm): class Meta: model = User password = PasswordField() The WTForms-Alchemy docs talk...

SQLAlchemy - Filter query, exclude parent where one of many children meet criteria

Query below should do it: q = (session.query(Parent) .filter(Parent.children.any(Child.value.ilike('%{}%'.format(value1)))) .filter(Parent.children.any(Child.value.ilike('%{}%'.format(value2)))) .filter(~Parent.children.any( db.or_(Child.value.ilike('%{}%'.format(value3)), Child.value.ilike('%{}%'.format(value4)), ) )) ) Few points: you need an or for Condition-3 you also should use NOT has any children... (which is done using ~) instead or the not which you have inside. Filter for Condition-3 should be:...

Bulk inserts with Flask-SQLAlchemy

python,flask,flask-sqlalchemy,flask-wtforms

Perhaps you should try to db.session.flush() to send the data to the server, which means any primary keys will be generated. At the end you can db.session.commit() to actually commit the transaction.

Flask and sqlalchemy: Get uploaded file using path stored on database

Well, It's strange but I will answer my own question. The solution is simple, I chose to store only the file names inside the database. Then I created a route to a view that will return the file using the send_from_directory function from flask.ext.uploads @app.route('/boxart/<filename>') def uploaded_boxart(filename): return send_from_directory(app.config['UPLOAD_FOLDER'],filename) So,...

Flask-SQLAlchemy - How to append a value to a column?

python-3.x,flask,sqlalchemy,flask-sqlalchemy

Your models definitions are not what you really want. User.classes_id defines exactly one value. Right answer depends of requirements. What type of relationships must be there? Many User to many Classes (M:M)? In this case you would create models like: user_to_classes = Table('user_to_classes', Base.metadata, Column('class_id', Integer, ForeignKey('class.id')), Column('user_id', Integer, ForeignKey('user.id'))...

Flask - (ProgrammingError) - Foreign key constraint when building postgresql database

python,heroku,flask,sqlalchemy,flask-sqlalchemy

Type mismatch problem: You defined the foreign key as String type for Role table: user_id = db.Column(db.String, db.ForeignKey('user.id')) while it's Integer in User table: id = db.Column(db.Integer, primary_key=True). Change one of them so two fields (Field name and the Foreign Field name) are using compatible field type....

How to create a user 'programmatically' with Flask-user extension?

That's how it basically works: user = User() user.username = request.json['username'] user.first_name = request.json['first_name'] user.last_name = request.json['last_name'] user.email = request.json['email'] user.password = user_manager.hash_password(request.json['password']) db.session.add(user) db.session.commit() ...

Adding columns from joined table

I found the solution. The correct way of querying should be: models.Detail.query.join(details_usages).add_columns(details_usages.c.is_required).all() ...

Flask-SQLAlchemy using global DB instance

Your filter user_groups_n_hosts.end_time < str(date_now) compares a date with a string. Also, you should consider the session as a per-request thing, not a persistent entity across your app. With this in mind, try changing @sched.cron_schedule(second='*/5') def some_decorated_task(): date_now = datetime.datetime.now().date() for item in DBsession.query(user_groups_n_hosts).filter(user_groups_n_hosts.end_time < str(date_now)): print item.id, item.server_users.native_linux_user to...

Where can I find a list of the Flask SQLAlchemy Column types and options?

python,flask,sqlalchemy,flask-sqlalchemy,nested-sets

I think you're looking for the Column and Data Types page in the documentation. A little HTML parsing gives: BIGINT BINARY BLOB BOOLEAN BigInteger Boolean CHAR CLOB Concatenable DATE DATETIME DECIMAL Date DateTime Enum FLOAT Float INT INTEGER Integer Interval LargeBinary MatchType NCHAR NVARCHAR Numeric PickleType REAL SMALLINT SchemaType SmallInteger...

Performing tasks before insert, remove, etc. in flask-sqlalchemy

I ended up implementing nested intervals (instead of nested sets). I hope it's helpful to folks! class Employee(db.Model): id = db.Column(db.Integer, primary_key=True) employee_name = db.Column(db.String(120)) parent = db.Column(db.Integer, db.ForeignKey('employee.id'), index=True) # implements nested intervals with fractions created = db.Column(db.DateTime, index=True) left_num = db.Column(db.Integer, nullable=False) left_den = db.Column(db.Integer, nullable=False) right_num =...

Copying oldtable to newtable with slight differences in newtable?

python,postgresql,sqlalchemy,flask-sqlalchemy

Something like this should be what you're looking for: oldTypeOnes = session.query(oldTable).filter(oldTable.type == 1).all() for oldTypeOne in oldTypeOnes: session.add(newTable(parent_id=oldTypeOne.id, type=oldTypeOne.type, \ name=oldTypeOne.name)) session.commit() ...

Get “insert_id” for one to one relationship in Flask, SqlAlchemy

flask,flask-sqlalchemy

I couldn't find an exact solution. If anyone is wondering, I ended up working around it by executing SQL directly. It's not ideal, but it gets the job done for now. I ended up inserting on row at a time, here's the code below: @app.route('/_add_funding') def add_funding(): funding_type = request.args.get('funding_stage',...

Flask-Babel do not translate anything in a web project

python,flask,flask-sqlalchemy,flask-login,babel

because the actual locale name parsed by babel is "zh_Hans_CN", name your translation directory to be "zh_Hans_CN", and it will be found.

SLQAlchemy, Flask: AttributeError: 'module' object has no attribute 'create_all' [closed]

virtualenv,flask-sqlalchemy

If you’re trying to use the create_all on db, that’d be it. modelx.db.create_all()...

Flask-Sqlalchemy taking non unique values for a 'unique' integer

nginx,flask,flask-sqlalchemy,gunicorn

I suppose you declared email as UNIQUE in the database, but not registration_num. Setting unique=True in the model does not automatically influence the database and SQLAlchemy does not check if there are some rows with that value already. unique=True it is used by create_all or sqlalchemy-migrate.

SQLAlchemy extension isn't registered when running app with Gunicorn

Only when you start your app with python sever.py is the if __name__ == '__main__': block hit, where you're registering your database with your app. You'll need to move that line, db.init_app(app), outside that block....

Getting errors with passing parameters with fliter_by function in SQLAlchemy

python,python-2.7,flask,sqlalchemy,flask-sqlalchemy

Use filter, not filter_by. filter_by is a shortcut to filter that applies the equality op to keyword args, you can't use it for other ops. filter takes operations, such as User.name == 'davidism' or STDcodes.city.startswith(line), and applies them to the query. filter_by is a shortcut that takes keyword arguments instead...

Make only one BestFriend with flask-sqlalchemy

python,database,flask,sqlalchemy,flask-sqlalchemy

To change from a many-to-many relationship to a one-to-one, start by removing the best_friends table. friends = db.Table('friends', db.Column('user_id', db.Integer, db.ForeignKey('user.id')), db.Column('friend_id', db.Integer, db.ForeignKey('user.id')) ) You then want to add a foreign key to User that references another User. class User(db.Model): id = db.Column(db.Integer, primary_key = True) name = db.Column(db.String(50),...

SQLAlchemy relationships, when to use which relationship?

python,sqlite,flask,sqlalchemy,flask-sqlalchemy

Having db.relationship is very helpful in some cases and can help you to avoid long queries. For example Without db.relationship: role = ... # some role Session.query(User).filter_by(role_id=role.role_id).all() Versus with db.relationship: role = ... # some role role.users Concerning relationship options, most useful for you is probably backref. It creates back...

Cannot access db when running code from Interactive Console

python,google-app-engine,flask,sqlalchemy,flask-sqlalchemy

You need to be in an app context (or a request context) to access application bound objects. An easy way to achieve this is to use Flask-Script, which provides a shell command that sets up the application for you. Or use Flask's integration with Click if you are using the...

Flask/SQLAlchemy - Difference between association model and association table for many-to-many relationship?

flask,sqlalchemy,many-to-many,flask-sqlalchemy,model-associations

My apologies, I finally stumbled across the answer in the SQLAlchemy docs... http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#many-to-many ...where they explicitly define the difference: Many to Many adds an association table between two classes. association_table = Table('association', Base.metadata, Column('left_id', Integer, ForeignKey('left.id')), Column('right_id', Integer, ForeignKey('right.id')) ) The association object pattern is a variant on many-to-many: it’s...

Can a Field act as both Foreign and Primary Keys in the same table?

database,foreign-keys,relational-database,primary-key,flask-sqlalchemy

In short - yes. Having the same field as a primary key and a foreign key is used to create a 1:0..1 relationship. A user may have a single record of details, but cannot have multiple records of details, and you cannot have details for users that do not exist...

How to create multiple sqlalchemy connections to the same DB but with different credentials?

mysql,flask,sqlalchemy,flask-sqlalchemy

Recently done this to talk to a postgresdb for login and another mysql db for accessing some data. config.py SQLALCHEMY_OUTER_DATABASE = "mysql+mysqlconnector://" + OUTER_LOGIN + ":" + OUTER_PASSWORD + "@" + OUTER_SERVER + ":" + OUTER_PORT + "/" + OUTER_DATABASE SQLALCHEMY_BINDS = { 'binds': SQLALCHEMY_OUTER_DATABASE } init.py global db_outer =...

Update many to many association table with derived field

python,flask,sqlalchemy,many-to-many,flask-sqlalchemy

I had some help from https://twitter.com/140am on this one. I needed to represent contactgrouping as a Model rather than a Table, so that I could use relationships and access fields directly. class Category(db.Model): resource_fields = { 'id': fields.Integer(attribute='catid'), 'name': fields.String(attribute='catname') } __tablename__ = 'CATEGORY' catid = db.Column(db.Integer, primary_key=True) clientid =...

Arguments of Column in SqlAlchemy Flask

From SQLAlchemy documentation: sqlalchemy.schema.Column(name, type, *args, **kwargs) Where nameis a str and type is an instance subclassing TypeEngine. More information about **kwargs in the documentation....

Flask with SQLAlchemy tables doesn't link

Check out the SQLAlchemy Documentation : Building a Relationship I haven't tried with FLASK but It should works using relationship....

Flask-SQLAlchemy not creating my tables

python,mysql,sqlalchemy,flask-sqlalchemy

on changing from __init__ import db to from modules import db in models.py it worked. when running flask application from outside package one needs to import everything from the package itself and not the individual modules....

Speed up updating items individually

SQLAlchemy caches all the queried items internally, but expires that cache when a commit is issued. So the instance accessed in the next iteration is in the "expired" state and SQLAlchemy re-queries the database. So you're effectively doing: a massive query at the beginning for 12000 items 12000 commits 11999...

Database created empty when models are in a separate file?

flask,flask-sqlalchemy,flask-login

Since defining your model directly in __init__.py works, it follows that you need to import your model(s) into __init__.py. You can add an import in __init__.py after you create an SQLAlchemy object: db = SQLAlchemy() from .models import User This works, but it feels horribly wrong and dirty to me....

AttributeError: 'User' object has no attribute 'is_active'

Your indentation is wrong. Instead of defining methods on User, you've defined module-level functions. class User(db.Model): __tablename__ = "Contacts" id = db.Column(db.Integer, primary_key = True) name = db.Column(db.String(50)) email = db.Column(db.String(50)) age = db.Column(db.Integer) def __init__(self, name, email, age ): self.name = name self.email = email self.age = age def...

sql alchemy: return all unique types of great great grandchildren

sqlalchemy,flask-sqlalchemy,alchemy

Edited post after table definitions: Given your table definitions, this should work: SKUs = session.query(Sku.sku_number) .join(Each).join(InnerCase) .join(OuterCase).join(Pallet) .join(FreightOrderDomestic) .filter(FreightOrderDomestic.id == myOrderNumber) .group_by(Sku).all() However, looking at your table definitions I have some other comments that will hopefully help: You should setup relationships between the tables, so you can easily work with...

Flask: Connecting models to files, e.g. profile pictures [closed]

database-design,flask,flask-sqlalchemy,flask-admin,flask-security

If only one file/picture is required, it should work by adding another column. But if you want to associate multiple files to a specific user, you can create a new table for the relations: files table: ID | USER_ID | FILEPATH =================================================== 1 | 1 | somewhere/on/the/server.jpg 2 | 2...

Flask-SQLAlchemy filter_by and follow multiple backrefs

python,postgresql,flask,flask-sqlalchemy,flask-testing

You have to join the Post and Category first, and i made some small corrections on your tests: db.create_all() e = Editor('Dude') py = Category('Python', e) p = Post('Hello Python!', 'Python is pretty cool', py) db.session.add(py) db.session.add(p) db.session.add(e) print Post.query.filter_by(category=py).all() print Category.query.filter_by(editor=e).all() print Post.query.join(Category).filter_by(editor=e).all() ...

change Test database From sqlite to Postgresql

As the error says, there is no database called "mytest" on the PostgreSQL server you're pointing to. Unlike SQLite which will create a file for the database if it doesn't exist, with PostgreSQL you need to create the database on the server before using it. Assuming PostgreSQL is otherwise set...

display friends name by Flask Many-to-many sqlalchemy

python,flask,jinja2,flask-sqlalchemy,jinja

The name you've chosen, is_friend, is probably causing some of your confusion. Something like friends is a bit clearer. You also don't need the back reference as each User already has the relationship. class User(db.Model): id = db.Column(db.Integer, primary_key = True) name = db.Column(db.String(50), index=True, unique= True) email = db.Column(db.String(50),index=True,...

Switching from SQLite to MySQL with Flask SQLAlchemy

python,mysql,database,sqlite,flask-sqlalchemy

The tutorial pointed by you shows the right way of connecting to MySQL using SQLAlchemy. Below is your code with very little changes: My assumptions are your MySQL server is running on the same machine where Flask is running and the database name is db_name. In case your server is...

Join two tables and show all combinations

python,flask,sqlalchemy,jinja2,flask-sqlalchemy

You have a relationship between Company and Contact defined, so you already have the contacts grouped by company. It would be more efficient in this case to not specify lazy='dynamic', and your backref should probably be named company. contacts = db.relationship('Contact', backref='company') You can query with a joinedload option to...

Flask-admin separate view for models filtered by foreign key

python,flask,sqlalchemy,flask-sqlalchemy,flask-admin

The easiest way to solve this problem that I found was to add a link for each quiz to a page with replies where the filter for the field quiz_id is applied. class Replies_view(ModelView): named_filter_urls = True column_filters = ("quiz_id",) class Quiz_view(ModelView): def _question_formatter(view, context, model, name): return Markup( "<a...

SQLAlchemy with Flask does not commit

python,flask,sqlalchemy,flask-sqlalchemy,flask-wtforms

You should add else statement: if form.validate_on_submit(): ... else: for error in form.errors.itervalues(): flash(error[0]) When you will get error message from form....

Make a relationship between two tables though another

Assuming Condominium is 1:M to Property which is 1:M to Listing and that all foreign keys are well-defined, you can get all Listings for Condominium with id 123 thus: session.query(Listing).join(Property).join(Condominium).filter(Condominium.id=123) ...

Two ways of creating a flask-SQLAlchemy BaseQuery object - only one works, why?

python-2.7,flask,flask-sqlalchemy

You should be able to use db.session.query(Design).all() session.query is how you would perform queries when using SQLAlchemy without Flask-SQLAlchemy. UPDATE To perform a join using Flask-SQLAlchemy's BaseQuery: Design.query.join(YourOtherModel).all() ...

One to many SQL relationship

Flask-SQLAlchemy's documentation covers creating a one-to-many relationship. It provides an example of one Person having many Addresses. class Person(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50)) addresses = db.relationship('Address', backref='person', lazy='dynamic') class Address(db.Model): id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(50)) person_id = db.Column(db.Integer, db.ForeignKey('person.id')) This matches what you have. The...

Tyring to set up modelview with Flask-Admin causes ImportError

python,flask,flask-sqlalchemy,flask-admin

Your code has the line from app.models import User in __init__.py. The problem is that app.models has from . import db. This is a circular import: __init__ tries to import User, which tries to import db, which isn't defined until after __init__ tries to import User. To solve this, move...

How to enforce constraints in `flask-admin`?

python,flask,flask-sqlalchemy,flask-admin

You can implement the on_model_change and on_model_delete functions. So you can check if the data is unique and give a more user friendly message in case a constraint is not satisfied. Here is an example of checking some constraints before the delete/insert/update operation class ExampleView(ModelView): def on_model_delete(self, model): #check constraint...

Select attributes from different joined tables with Flask and SQLAlchemy

python,orm,flask,sqlalchemy,flask-sqlalchemy

q = db.session.query(Phrase.content, Meaning.content).join(Meaning).all() ...

Problems trying to mock a Model within Flask-SQLAlchemy

testing,flask,mocking,flask-sqlalchemy

So, I found a solution after banging my head on the keyboard for a few hours. The problem seems to be the following (if anyone knows better, please correct me). When I run mock.create_autospec(User), the mock module tries to inspect all attributes of User to create the adequate spec for...

Openshift app with flask, sqlalchemy and sqlite - problems with database reverting

python-3.x,sqlite3,flask,openshift,flask-sqlalchemy

Via the note at the top of the OpenShift Cartridge Guide: "Cartridges and Persistent Storage: Every time you push, everything in your remote repo directory is recreated. Store long term items (like an sqlite database) in the OpenShift data directory, which will persist between pushes of your repo. The OpenShift...

with Flask-sqlalchemy, connect models to database without running app

flask,sqlalchemy,flask-sqlalchemy

Study this code https://github.com/mattupstate/overholt there is a link on github explaining the design. What should be your main focus is the usage of flask-script. I think you have to use the factory pattern, but you want to do that anyway if you are doing anything remotly serious. Best of luck...

flask-sqlalchemy, sql count records in a relationship and then join to a users table

python,postgresql,flask-sqlalchemy

select users.*, count(*) over (partition by friendship.user) from users inner join friendship on users.id = friendship.user order by count ...

SQLAlchemy sum of a column btw 2 dates

sqlalchemy,flask-sqlalchemy

start_date, end_date = date(2014, 2, 1), date(2014, 3, 31) q = (db.session.query( db.func.sum(Income.amount).label("total_amount"),) # @note: any of the three below should do the job: .filter(Income.date >= start_date).filter(Income.date <= end_date) # .filter(Income.date.between(start_date, end_date)) # .filter(between(Income.date, start_date, end_date)) # first do: from sqlalchemy.sql.expression import between ) ...

How to order a geospatial query by distance from the query point

sqlalchemy,flask-sqlalchemy,geoalchemy2

Alright, I figured it out, but wow that wasn't obvious, especially if you're not intimately familiar with Geoalchemy2. In my case above, you have to tack: .order_by(func.ST_Distance(cls.geoLoc, geo)) to the end of the code that's in my question. Basically, ST_Distance is a function that takes in a two points and...

change database migrations from sqlalchemy-migrate to Flask-Migrate

python,flask-sqlalchemy,data-migration,alembic,flask-migrate

There are three possible ways to handle this, two are relatively easy, but the last is pretty laborious: 1. Only track future migrations with Flask-Migrate This is the easiest, but less interesting. Just install and setup Flask-Migrate as you would for a new project, and the next time you need...

UndefinedError: 'user' is undefined

flask,jinja2,flask-sqlalchemy,jinja

In user.html you call the variable contact. You probably want to call it user. {% for user in contact %} You'll also need to update the references to contact.id and contact.name. ...

How can I programmatically set the 'target_metadata' required by Alembic for use with the command API?

python,sqlalchemy,database-migration,flask-sqlalchemy,alembic

The long answer is that you don't set metadata there, you set it when creating the MigrationContext. Which requires that you create a Config, then a ScriptDirectory, then an EnvironmentContext first. Then you need to use these objects correctly when running revisions to set up the environment yourself. The short...

Best association approach to connect Sensor model to others

sqlalchemy,flask-sqlalchemy

After two days of experiments I have came to final conclusion. Examples have been taken from this source. "Discriminator on association" (candidate for answer): (+) has backward reference (?) can have 1 parent object (-) complexity "Generic Foreign Key": (+) low complexity (+) has backward reference (?) can have 1...

What is the relationship() function used for in SQLAlchemy

python,cookies,flask,flask-sqlalchemy,flask-login

relationship does not affect the database schema. It provides a convenient way to access related objects. In this instance it allows you to all Child objects that are related to a Parent through the children attribute. backref then adds a parent attribute to all Child objects. By default, related objects...

How to fix itsdangerous.BadTimeSignature Signature Error

You are creating a new secret each time here: from itsdangerous import URLSafeTimedSerializer app.secret_key = gen_random_key() login_serializer = URLSafeTimedSerializer(app.secret_key) Don't do that. Create one secret for your application, and keep using this throughout. Your keys are signed with this server-side secret and then when the cookie is sent back from...

scoped_session object has no attribute 'create_all'

It's connection.create_all(), you added session in the middle. Unrelated to the immediate problem, there are other things that don't look right. You don't need to commit after running create_all. The extension instance is usually named db. The DB class does nothing, just write your create_all function on its own. You...

Get inserted key before commit session

python,flask,sqlalchemy,flask-sqlalchemy,flask-security

You could use flush() to flush changes to the database and thus have your primary-key field updated: parent = Parent() db.session.add(parent) db.session.flush() print parent.id # after flush(), parent object would be automatically # assigned with a unique primary key to its id field child = Child() child.parent_id = parent.id db.session.add(child)...

Associate "external' class model with flask sqlalchemy

I spent a lot of time looking for an answer to this. My conclusion is that it is not at this time possible to integrate what I'm going to call an "abstract" sqlalchemy model into flask-sqlalchemy or flask-security. It was a huge time sink trying to integrate them. I strongly...

Recursively select (with limited depth) relationships in SQLAlchemy using ORM, declarative style, and Association objects

python,postgresql,recursion,sqlalchemy,flask-sqlalchemy

I hope I did not overcomplicate your model, but in order to test the query (which follows below) I used the following model definition: The MODEL: class Room(Base): __tablename__ = 'room' id = Column(Integer, primary_key=True) name = Column(String) exits = association_proxy( 'lnk_exits', 'to_room', # creator=lambda v: Exit(to_room=v), creator=lambda k, v:...

How teardown_request() works with Python- Flask?

teardown_request registers a function to be called at the end of each request whether it was successful or an exception was raised. It is a good place to cleanup request scope objects like a database session/transaction. That is all your code sample is doing. It is safe to use that...

How to select_related() in Flask/SQLAlchemy?

postgresql,flask,sqlalchemy,flask-sqlalchemy

answers = Question.query.options(joinedload(Question.answers)).get(5).answers The expression Question.query.options(joinedload(Question.answers)).get(5) issues the query with a join and evaluates to a Question instance. Accessing answers attribute does not issue any queries. You could also do it more explicitly answers = Answer.query.filter_by(question_id=5).all() ...

Flask-SQLAlchemy close connection

SQLAlchemy sets up a pool of connections that will remain opened for performance reasons. The PostgreSQL has a max_connections config option. If you are exceeding that value, you need to either lower your pool count or raise the max connection count. Given that the default max is 100, and you've...

Flask/SQLalchemy/Jinja2 — nested loop rendering

python,flask,jinja2,flask-sqlalchemy

Your loop didn't quite make sense as it was not an dictionary. Since you have answers in the questions, you can just grab the questions, and automically grab the answers at runtime as it is lazy loaded when you iterate through q.answers in your template. routes.py @app.route('/quiz') def quiz(): questions...

'NoneType' object has no attribute 'owner' when trying to access relationship

python,flask,flask-sqlalchemy,flask-restful

.first() returns None if there were no results. So your query for clicked_toy returned no results. You can use get rather than filter when all you're doing is filtering on the primary key. Flask-SQLAlchemy goes one step further by allowing you to raise 404 if there is no result. clicked_toy...

TypeError: key is not a string

Your problem is in this line: return {id:marshal(question, questionlist_fields)} I think you wanted this: return {'id': marshal(question, questionlist_fields)} In some languages, notably JavaScript, all dict keys are strings, and the syntax allows you to leave the quotes off. In Python, dict keys can be anything you want.* If you want...

Flask foreign_keys still shows AmbiguousForeignKeysError

python,mysql,flask,foreign-keys,flask-sqlalchemy

Finally, I got the workaround after trying to figure out. In my case, I don't have to put backref in Review class. Instead, I should put the User backref in User class itself. So, it should look like below class Review(db.Model): __tablename__ = 'Review' id = db.Column(db.Integer, primary_key = True)...

how to store binary file recieved by Flask into postgres

python,flask,flask-sqlalchemy,psycopg2,werkzeug

The objects in request.files are FileStorage objects. They have the same methods as normal file objects in python. So, to get the contents of the file as binary, just do this: data = request.files['file'].read() Then pass that parameter into the INSERT....

Does db.create_all() check if the database exists?

flask,flask-sqlalchemy

The method only creates tables; so it is up to you to make sure the database is already created. The database has to exist and if the table already exists, you'll receive an error from the database. It will not overwrite or delete tables....

backref lazy='dynamic' - does not support object population - eager loading cannot be applied

flask,sqlalchemy,flask-sqlalchemy

The error message tells you exactly what is "wrong": a lazy='dynamic' relationship can't be eager loaded. Dynamic relationships produce new queries, not collections (lists). It doesn't make sense to pre-load a dynamic relationship because the entire point of a dynamic relationship is to be able to construct other queries. Use...

SQLAlchemy: How to use order_by based on a calculated value?

Even with SqlAlchemy, you have to think in sets of objects and their values. The query you want involves three different sets: Users, their correct answers and their total answers. Want you want is a query like that (warning, that's just a sample, you could write it much better) select...

how to use .query attribute in sqlacodegen models