How to Programming with Flask-sqlalchemy

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...

Base query to work even if parameters not supplied

python,flask,sqlalchemy,flask-sqlalchemy,python-import

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...

Why do I get a 'NameError' with this import?

db.SQLAlchemy(app) is not defining db, and is only attempting to run a SQLAlchemy() method on db. db = SQLAlchemy(app) however, would define db.

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() ...

Get inserted key before commit session

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)...

Bulk inserts with Flask-SQLAlchemy

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

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.

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

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 can I add a foreign key constraint on an existing table column via SQLAlchemy?

python,flask,sqlalchemy,flask-sqlalchemy,alembic

The Alembic op you are looking for is create_foreign_key. op.create_foreign_key( 'fk_location_message_campaign', 'location_messages', 'campaigns', ['campaign_id'], ['id'], ) It is recommended that you use automatic constraint naming so that you can pass None as the name rather than naming it manually....

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....

How to select_related() in Flask/SQLAlchemy?

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

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-Babel do not translate anything in a web project

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.

Isolating py.test DB sessions in Flask-SQLAlchemy

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

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...

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

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

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 =...

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...

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

postgresql,flask,sqlalchemy,flask-sqlalchemy

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...

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...

how to use .query attribute in sqlacodegen models

python,flask-sqlalchemy,flask-wtforms

It sounds like you're used to using Flask-SQLAlchemy, which includes the secret sauce to allow the functionality you mention. The Flask Docs on SQLAlchemy indicate how to get that same functionality: from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True) db_session =...

Python sqlalchemy how to deal with null

SQLAlchemy's driver is mistaking :null as a named bind parameter instead of a value. Simply add a space after the colon to make it clear that null is a value: sql = """update dh_base_measure_get_10 set json='{"username": "test", "measureid": null}' """ ...

change Test database From sqlite to Postgresql

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

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...

Flask with SQLAlchemy tables doesn't link

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

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

Update many to many association table with derived field

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 =...

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...

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:...

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

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

.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...

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

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'))...

Make a relationship between two tables though another

python,heroku,flask,sqlalchemy,flask-sqlalchemy

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) ...

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

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....

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),...

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...

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() ...

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...

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...

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...

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...

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...

Associate "external' class model with flask sqlalchemy

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

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...

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...

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 and sqlalchemy: Get uploaded file using path stored on database

python,flask,flask-sqlalchemy,flask-wtforms

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,...

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...

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...

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...

flask wtforms_alchemy object has no attribute

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

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...

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()...

Database created empty when models are in a separate file?

python,database,sqlalchemy,relationship,flask-sqlalchemy

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....

Relationship between two tables one with 2 foreign keys in flask-sqlalchemy

Below should work: class Messages(db.Model): MessageId = db.Column(db.INTEGER, primary_key=True, autoincrement=True) SenderId = db.Column(db.INTEGER, db.ForeignKey('users.UserId'), nullable=False) ReceiverId = db.Column(db.INTEGER, db.ForeignKey('users.UserId'), nullable=False) Message = db.Column(db.String, nullable=False) # define relationships sender = db.relationship(Users, foreign_keys=[SenderId], backref='sent') receiver = db.relationship(Users, foreign_keys=[ReceiverId], backref='received') Usage example: u0 =...

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...

TypeError: key is not a string

python,flask,flask-sqlalchemy,flask-restful

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-SQLAlchemy not creating my tables

python-3.x,flask,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....

one-to-many append attribute error

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...

Flask-SQLAlchemy filter_by and follow multiple backrefs

python,flask,flask-sqlalchemy,psycopg2,werkzeug

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() ...

how to store binary file recieved by Flask into postgres

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....

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...

How teardown_request() works with Python- Flask?

python,mysql,flask,flask-sqlalchemy,haproxy

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...

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-SQLAlchemy “MySQL server has gone away” when using 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...

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....

How to fix itsdangerous.BadTimeSignature Signature Error

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

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...

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...

Error importing models when trying to run app [duplicate]

Looks like you are in a circular import, as @jwalker commented. Three comments here: You can check blueprints to avoid circular imports in Flask. If the idea is to keep it simple, declare your models within app.py (it is not the ideal, but for initial learning purposes it makes the...

Adding columns from joined table

python,postgresql,recursion,sqlalchemy,flask-sqlalchemy

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() ...

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

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:...

SQLAlchemy extension isn't registered when running app with Gunicorn

nginx,flask,flask-sqlalchemy,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....

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 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()) ...

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...

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) ...

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...

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....

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...

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...

How to create a migration script to add a Geometry column in SQLAlchemy-Migrate?

flask,flask-sqlalchemy,sqlalchemy-migrate,flask-migrate,geoalchemy2

If you are following Miguel Grinberg's tutorial you are using SQLite database. GeoAlchemy2 – if I'm not wrong – supports only PostgreSQL/PostGIS, as @dirn pointed out in the comment. The solution would be to get a PostgreSQL server running. SQLAlchemy deals fine with PostreSQL. Once you got it, just edit your...

What is the relationship() function used for in SQLAlchemy

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...

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...

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....

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,...

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

sqlalchemy,flask-sqlalchemy

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...

SQLAlchemy sum of a column btw 2 dates

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 ) ...

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

sqlalchemy,flask-sqlalchemy,geoalchemy2

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() ...

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

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...

Arguments of Column in SqlAlchemy Flask

sqlalchemy,flask-sqlalchemy

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....

Best association approach to connect Sensor model to others

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...

Default scope for queries using Python + Flask-SqlAlchemy

python,postgresql,flask-sqlalchemy

One of many ways is to add class method. With python Class definition, you are free to add your own methods, by the way. class Resource(db.Model): # .... your existing code @staticmethod def customq(limit, offset): """This method returns an instance of flask_sqlalchemy.BaseQuery""" return Resource.query.filter_by(is_active=1).limit(limit).offset(offset) Then your example code becomes: @app.route('/resources',...

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

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

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() ...

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 =...

Speed up updating items individually

python,mysql,sqlalchemy,flask-sqlalchemy

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...

One to many SQL relationship

python-2.7,flask,flask-sqlalchemy

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...

Secondary Join Condition Ignored in Insert SQL Alchemy

python,flask,jinja2,flask-sqlalchemy,jinja

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,...

display friends name by Flask Many-to-many sqlalchemy

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,...

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() ...

NameError: global name 'User' is not defined

flask,flask-sqlalchemy

You need to import your models into your views module: from models import User ...

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)...

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...

Python Flask SQLalchemy JSON POST Error

python,json,flask-sqlalchemy,flask-restful,flask-restless

You are posting to the URL without a / at the end, but you specified your route with a trailing /. When you do that, Flask issues a redirect to the 'canonical' URL, with the /. Because you are using POST, the post data will be lost, so in debug...

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...

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...

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