FAQ Database Discussion Community


Apply Python Code To Sqlalchemy Filters

python,mysql,sqlalchemy
I'm trying to figure out how to apply python code (like splitting a list) to a sqlalchemy filter. An example is as follows: my database stores a full name as a field in the table. I want to query my database for all people who have a given first name....

contructing a dataframe from joined SQLAlchemy query

python,sqlalchemy
from the query: session.query(Record, RecordMeasurement).filter(Record.record_pkid==RecordMeasurement.record_fkid) i would like to construct 1 dataframe consisting of the values from Record columns: ['datetime', 'record_number'] and RecordMeasurement columns: ['power', 'voltage', 'temperature'] the dataframes column order does not matter. thanks!...

Filtering by exact list of IDs instead of “in_” list of IDs

python,sqlalchemy
Item has one-to-many relation with Condition. I need to query all the Items that have exactly this list of Conditions (no subset, no superset). Following query is obviously not good since found Item.conditions may be a subset of item1.conditions: condition_ids = [x.id for x in item1.conditions] DBSession.query(Item).join(Condition, Item.conditions).filter( Item.sku_id==item1.sku_id).filter(Condition.id.in_(condition_ids)).all() Is...

flask/sqlalchemy - OperationalError: (sqlite3.OperationalError) no such table

python,sqlite3,flask,sqlalchemy
I get the below error sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: s_amodel [SQL: 'INSERT INTO s_amodel My files forms.py from flask_wtf import Form class SAForm(Form): .... models.py from app.app_and_db import db class SAmodel(db.Model): id = db.Column(db.Integer(), primary_key=True) views.py from app.app_and_db import app, db from app.sa.forms import SAForm from app.sa.models import SAmodel...

How to Find Count After Join in SQLAlchemy

mysql,sqlalchemy
I join multiple tables with: session.query(R, RR, RRR).join(R).join(RR).all() I tried: session.query(func.count(R, RR, RRR)).join(R).join(RR) However, this does not appear to be the correct approach to determining the count from tables. I could do len(session.query(R, RR, RRR).join(R).join(RR).all()) but ideally, I won't have to do my counts in memory....

vertica/sqlalchemy - Permission denied for schema public

python,sql,sqlalchemy,vertica
I am able to generate an engine as follows: import pandas as pd import sqlalchemy as sa url = sa.url.URL(drivername='vertica+pyodbc', username='username', password='****', host='vertica') engine = sa.create_engine(url) I can read data, e.g. pd.read_sql_query("SELECT * FROM my_schema.tablename", engine) However when I try to write data: import numpy as np df = pd.DataFrame(np.random.randn(10,...

Get the most recent record for a user

sql,sqlalchemy
Say I have records like this: user_id user_data_field created ------- --------------- ------- 1 some data date_a 2 some data date_b 1 some data date_c 1 some data date_d 2 some data date_e What should I do to get all the user records with most recent dates only (assuming that most...

SQLAlchemy “or” statement with multiple parameters

python,sqlalchemy
I have a query that require to use the "or" | operator : Mymodel.query.filter((Mymodel.a== 'b') | (Mymodel.b == 'c')) That works fine. However, I want my conditions to be put in an array of unkown length : conds = [ Mymodel.a== 'b', Mymodel.b == 'c', Mymodel.c == 'd'] Mymodel.query.filter(???(conds)) Thanks...

SQLAlchemy: Return a record filtered by max value of a column

python,sqlalchemy
All, I'm having an issue with a (surprisingly trivial) SQLA request; goal is to return a record with the maximum value in the counter column: this works fine - returns proper record: m=12 # arbitrary example of a max value of counter = 12 qry = session.query(Data). filter(Data.user_id == user_id,Data.counter...

What is the difference between creating db tables using alembic and defining models in python,sqlalchemy?

python,flask,sqlalchemy,alembic
Im learning flask. So i had this noob question, i could create tables using command line "alembic revision -m' table_name ' " and then defining the versions and migrate using "alembic upgrade head" Also i could create tables in a database by defining class in models.py (SQlalchemy). what is the...

Flask with SQLAlchemy tables doesn't link

python,flask,sqlalchemy,flask-sqlalchemy
I have been struggling with this little SQLAlchemy problem. It tells me that City.address referencing columns aren't associated with a ForeignKeyConstraint. Here is my Models #Address model class Address(db.Model): id = db.Column(db.Integer, primary_key=True) address = db.Column(db.String(100), nullable=False) company_id = db.Column(db.Integer, db.ForeignKey('company.id')) city_id = db.Column(db.Integer, db.ForeignKey('city.id'), nullable=False) event = db.relationship('Address', backref='event',...

Too much selects are made in one-to-one relationships in SQLALchemy

python,sql,database,performance,sqlalchemy
import sqlalchemy as db from sqlalchemy.orm import relationship, sessionmaker from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Entity(Base): __tablename__ = 'entity' id = db.Column(db.Integer, primary_key=True, index=True) a = db.Column(db.Integer, index=True) b = db.Column(db.Integer) foos = relationship('Foo') class Foo(Base): __tablename__ = 'foo' id = db.Column(db.Integer, primary_key=True, index=True) entity_id = db.Column(db.Integer, db.ForeignKey('entity.id'))...

Get All of Single Column from Every Table in Schema

python,postgresql,sqlalchemy
In our system, we have 1000+ tables, each of which has an 'date' column containing DateTime object. I want to get a list containing every date that exists within all of the tables. I'm sure there should be an easy way to do this, but I've very limited knowledge of...

Flask-SQLAlchemy filter_by and follow multiple backrefs

python,flask,sqlalchemy,flask-sqlalchemy
I would like to extend the schema from the Flask-SQLAlchemy Quickstart Simple Relationships example and add a class called Editor. My schema now looks like this: from datetime import datetime from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db' db = SQLAlchemy(app) class Post(db.Model): id...

Bulk inserts with Flask-SQLAlchemy

python,flask,sqlalchemy,flask-sqlalchemy
I'm using Flask-SQLAlchemy to do a rather large bulk insert of 60k rows. I also have a many-to-many relationship on this table, so I can't use db.engine.execute for this. Before inserting, I need to find similar items in the database, and change the insert to an update if a duplicate...

SQL alchemy - specifying relationships when foreign key may not exist

python,sqlalchemy,foreign-keys
I need to set up two tables in a database and I'm struggling to decide how to design the tables in SQL Alchemy. Table 1 contains raw address data, and the source of the address. Raw addresses may appear more than once if they come from different sources. Table 2...

SQLAlchemy multi-schema support

python,python-2.7,sqlalchemy,database-schema
I am trying to create a database API and I am using Python 2.7 and SQLAlchemy. The API has to support multiple database platforms including MSSQL, MySQL, PostgreSQL, and SQLite. The problem with making it multi-platform, is that each database has a slightly different understanding of the word 'schema'. Basically,...

Flask-SQLAlchemy close connection

postgresql,flask,sqlalchemy,flask-sqlalchemy
I am using PostgreSQL and Flas-SQLAlchemy extension for Flask. # app.py app = Flask(__name__) app.config['SQLALCHEMY_POOL_SIZE'] = 20 db = SQLAlchemy(app) # views.py user = User(***) db.session.add(user) db.session.commit() Note that I am not closing the connection as suggested by documentation: You have to commit the session, but you don’t have to...

Displaying objects connections with flask

python,web,flask,sqlalchemy,bootstrap
I have a DB table which refers to itself. For example let's say I have a list of employees, some of them are other employees' bosses. Of course I have SQLAlchemy objects of this table. I want to visualize the connections with a graph - each employee is a node,...

scoped_session object has no attribute 'create_all'

python,flask,sqlalchemy,flask-sqlalchemy
I have the following code that sets up my database with Flask-SQLAlchemy. I'm getting an exception "AttributeError: scoped_session object has no attribute 'create_all'". Can someone please explain to me why I'm getting the error and how I can fix it? :) __init__.py: from flask import Flask app = Flask(__name__) from...

Are transactions in SQLAlchemy thread safe?

python,sqlalchemy
I am developing a web app using SQLAlchemy's expression language, not its orm. I want to use multiple threads in my app, but I'm not sure about thread safety. I am using this section of the documentation to make a connection. I think this is thread safe because I reference...

How to create a new table from select statement in sqlalchemy?

sqlalchemy
I am using sqlalchemy's core features to write some abstraction layer. The layer itself needs to be able to create tables from select statements. Sample code: metadata = MetaData(bind=engine) table = Table(table_name, metadata, autoload=True, autoload_with=engine) s = select(table).where(table.c.column_1 > 10) Now what I want to be able to do is...

Python creating class instance from dictionary

python,class,dictionary,sqlalchemy
I am trying to create class instance from dictionary that has keys more than class has attributes. I already read answers on the same question from this link: Creating class instance properties from a dictionary in Python. The problem is that I can't write __init__ in class definition as I...

Create a `CheckConstraint` within a `UniqueConstraint`

python,postgresql,sqlalchemy
Before creating a new record I want to check a condition that the combination of native_linux_user and is_active is unique but is_active must be True. Multiple native_linux_user with is_active=False may exist, but only one native_linux_user with is_active=True can exist. I tried to use a CheckConstraint within a UniqueConstraint like this,...

How to properly join same table multiple times using sqlalchemy core api?

python,sqlalchemy
I'm trying to join same table several times, using sqlalchemy core api. Here is the code: import sqlparse import sqlalchemy as sa meta = sa.MetaData('sqlite:///:memory:') a = sa.Table( 'a', meta, sa.Column('id', sa.Integer, primary_key=True), ) b = sa.Table( 'b', meta, sa.Column('id', sa.Integer, primary_key=True), sa.Column('x', sa.Integer, sa.ForeignKey(a.c.id)), sa.Column('y', sa.Integer, sa.ForeignKey(a.c.id)), ) meta.create_all()...

Thread safe SQLAlchemy session for Pyramid requests

python,sqlalchemy,thread-safety,pyramid
The article What the Zope Transaction Manager Means To Me (and you), demonstrates a nice way to attach an SQLAlchemy DB session to each request this way: def includeme(config): settings = config.get_settings() engine = engine_from_config(settings) maker = sessionmaker() register(maker) maker.configure(bind=engine) config.add_request_method(lambda request: maker(), 'db_session', reify=True) The problem with this approach...

PyOdbc fails to connect to a sql server instance

python,sqlalchemy,pyodbc
I am trying to connect to a sql server instance using pyodbc version 3.0.6., SQLAlchemy 1.0.4 on Windows 7 using a Python 2.7 (32 bit). I am using a connection string as follows DRIVER={SQL Server};SERVER=mymachinename;DATABASE=mydb;UID=sa;PWD=admin1; but I keep getting this error Could not parse rfc1738 URL from string 'DRIVER={SQL Server};SERVER=mymachinename;DATABASE=mydb;UID=sa;PWD=admin1'...

Inserting unicode message using sqlalchemy and mysql

python,mysql,unicode,sqlalchemy
I am trying to insert a string that has smiley face emojis into a MySQL database. I have the following test that throws an exception. How do I get past this error? Here is the test: def test_write_unicode(self): db_schema = "testing" db_url = sqlalchemy.engine.url.URL(drivername='mysql', host=selah.db_host, database=db_schema, query={ 'read_default_file' : selah.db_config...

PostgreSQL error: cannot use more than 32 columns in an index

python,postgresql,sqlalchemy
Using SQLAlchemy and Python with PostgreSQL as the database. I have a table with more than 32 columns and I am getting the following error: cannot use more than 32 columns in an index Here's a code snippet of how I am creating the tables: class Application(): __tablename__ = 'application'...

How to use make_transient() to duplicate an SQLAlchemy mapped object?

python,sqlalchemy
I know the question how to duplicate or copy a SQLAlchemy mapped object was asked a lot of times. The answer always depends on the needs or how "duplicate" or "copy" is interpreted. This is a specialized version of the question because I got the tip to use make_transient() for...

Testing with SQLAchemy

python,sqlalchemy
I have a class: import sqlalchemy as sa class Foo: def __init__(...): self.engine = sa.create_engine('...') self.conn = self.engine.connect() Inside a class there is a method which manipulates data within a transaction: def add_product(...): with self.conn.begin(): self.conn.execute(self.product_table.insert(), ...) I have tests in py.test so after each test I want to rollback...

list attribute has no order by

python,sql,sqlalchemy
I tried to do a sorting in sqlalchemy query, the parameters come from 'query_sort' which contains a list of sort parameter (field and direction). here is the code def select_all(self, query_paging, query_sort): """ method to select all the transport type""" try: select_all_query =\ self._session.query(TransportType) for s in query_sort: select_all_query =\...

Check database schema matches SQLAlchemy models on application startup

python,sqlalchemy
To prevent human errors, I'd like to check that the current SQL database schema matches the SQLAlchemy models code and there aren't migrations need to run on the application startup. Is there a way to iterate all models on SQLAlchemy and then see if the database schema is that what...

Update many to many association table with derived field

python,flask,sqlalchemy,many-to-many,flask-sqlalchemy
I'm writing a RESTful API using Flask, Flask-SQLalchemy and Flask-Restful. I have models for Contact and Category, and a helper table for mapping a many-to-many relationship between them. contactgrouping = db.Table('CONTACT_GROUPING', db.Column('catid', db.Integer, db.ForeignKey('CATEGORY.catid')), db.Column('contactid', db.Integer, db.ForeignKey('CONTACT.contactid')), db.Column('clientid', db.Integer, db.ForeignKey('CLIENT.clientid')), ) class Contact(db.Model): __tablename__ = 'CONTACT' contactid = db.Column(db.Integer, primary_key=True)...

Cornice schema validation with colanderalchemy

python,validation,sqlalchemy,pyramid,colanderalchemy
Cornice's documentation mentions how to validate your schema using a colander's MappingSchema subclass. How should we use a colanderalchemy schema for the same purpose? Because if we create a schema using colanderalchemy as stated in the documentation, the schema object has already instantiated the colander's class, and I think that...

sqlalchemy one-to-many ORM update error

python,sqlalchemy
I have two tables: Eca_users and Eca_user_emails, one user can have many emails. I recive json with users and their emails. And I wont to load them into MS SQL database. Users can update their emails, so in this json I can get the same users with new (or changed)...

SQLAlchemy: Do I need 2 classes for this?

python,sqlalchemy
I've been following the SQLAlchemy Object Relational Tutorial (http://docs.sqlalchemy.org/en/latest/orm/tutorial.html) and I've created table_def.py, a file that specifies a table containing Player objects (in my case): from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Player(Base): """""" __tablename__ = "players" id = Column(Integer, primary_key=True) firstName = Column(String) lastName = Column(String) college =...

safe parameter bindings in sqlalchemy filter

python,sql,sqlalchemy
I need to pass a partial raw sql query into sqlalchemy filter, like s.query(account).filter("coordinate <@> point(%s,%s) < %s"%(lat,long,distance)) Yes, I'm trying to use earthdistance function in postgresql. Of course, I could use PostGis and GeoAlchemy2, but I want to know the general solution to this kind of problems. I know...

Select range of rows in query

python,sqlalchemy
I have a function that takes page_size and skip as arguments. If page_size = 10 and skip = 2, I want to select 10 rows starting at row 21. I think this has to do with LIMIT and OFFSET. How do I do this in SQLAlchemy?

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

mysql,sqlalchemy,flask-sqlalchemy
How to write the following query using SQLAlchemy? SELECT i.itemid, sum(i.quantitysold) total_quantity_sold, max(t.createdAt) last_sale_time FROM ItemList i LEFT OUTER JOIN ItemTransactions t ON i.itemid = t.itemid WHERE i.active = 'y' GROUP BY i.itemid ORDER BY total_quantity_sold asc; This is what I ended up writing: from sqlalchemy.sql import func as sa_func...

Defining an abstract method in a SQLAlchemy base class

python,sqlalchemy
From http://docs.sqlalchemy.org/en/improve_toc/orm/extensions/declarative/mixins.html#augmenting-the-base I see that you can define methods and attributes in the base class. I'd like to make sure that all the child classes implement a particular method. However, in trying to define an abstract method like so: import abc from sqlalchemy.ext.declarative import declarative_base class Base(metaclass=abc.ABCMeta): @abc.abstractmethod def implement_me(self):...

SQLAlchemy query to find and replace a substring if it exists in a column

python,postgresql,sqlalchemy
I am trying to write a query in SqlAlchemy to find and replace a substring with another string if it exists in a column. What is the best way to achieve this? I am trying to use regexp_replace. I am unable to figure out how to use it. What I...

SQLAlchemy: Specifying session to use for model

flask,sqlalchemy,flask-sqlalchemy
I am using Flask-SQLAlchemy and I need to create a session without auto-flushing for an operation. However, the default scoped session that is created by Flask-SQLAlchemy which is accessed using db.session has auto-flushing turned on. I'm doing bulk updates for 100k rows, and the auto-flushing is causing severe performance issues....

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

flask,sqlalchemy,many-to-many,flask-sqlalchemy,model-associations
I started learning this stuff from the Flask Mega Tutorial. When he gets into Many-to-Many relationships, he creates an association table like this: followers = db.Table('followers', db.Column('follower_id', db.Integer, db.ForeignKey('user.id')), db.Column('followed_id', db.Integer, db.ForeignKey('user.id')) ) As I was searching for ways to add some metadata regarding a specific association between models, I...

Python SQLAlchemy - ForeignKey error

python,mysql,orm,sqlalchemy,foreign-keys
I'm trying to learn Python ORM using SQLAlchemy. I have these MySQL tables: CREATE TABLE address ( id INT NOT NULL AUTO_INCREMENT, street VARCHAR(45) NOT NULL, city VARCHAR(45) NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB; CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, email...

How can I use a CAST in an ExcludeConstraint in SQLAlchemy?

python,postgresql,sqlalchemy
In PostgreSQL I can create a table with an exclusion constraint involving a CAST (the CAST is necessary because the UUID type doesn't have a default operator class for gist): CREATE EXTENSION btree_gist; CREATE TABLE example ( id UUID, some_range INT4RANGE, EXCLUDE USING gist (CAST("id" AS TEXT) WITH =, some_range...

Performing concatenate columns in SQLAlchemy

python,postgresql,sqlalchemy
I am trying to implement concatenate columns function based on given inputs by the user. I have implemented it using sql alchemy update statement: update_statement = table.update().values({ new_column_name: assignable }) where assignable is : assignable = column_1 + column_2 where column_1 and column_2 is a sqlalchemy.sql.column instance It works fine...

SQLAlchemy One-to-many, adding a record

python,sqlalchemy
I've got a query around adding a record with a one-to-many relationship. First off, here are my models, I have a hierarchical one-to-many relationship with project/suite/case/step. class Project(Base): """Project model for reportr""" __tablename__ = 'project' id = Column(Integer, primary_key=True) name = Column(String(80)) # Relationship definitions test_suites = relationship("TestSuite", backref="test_suite") def...

SQLAlchemy not playing nicely with non unique primary keys

python,sqlalchemy
I will start by saying that this is not my database, I did not design or create it, I am simply connecting to it to write an application, do not judge me on the use of non unique indexing! I am connecting to a MSSQL database using the FreeTDS driver...

Drop duplicate Pandas and SQLAlchemy

python,pandas,sqlalchemy
I am trying to make a script for a call centre that wishes to be able to upload millions of records from a csv file to a database, filtering out all duplicate phone numbers from the upload. To do this I am using Pandas and SQLAlchemy df = read_csv('test.csv') rd...

Create table in MySQL based on reflected metadata from MSSQL using SQLAlchemy

sqlalchemy
I'm trying to use SQLAlchemy to copy table schemas between different rdbms - in this example from MSSQL to MySQL. Is there a way to take a table object and copy and convert the metadata to a different dialect? I tried the tometadata() function but the type info for the...

sqlalchemy INSERT from SELECT

python,sqlalchemy,pyramid
I have a trouble. The INSERT from SELECT construction compiles, but not performed. There aren't any error. If look to the log file, you won't SQL like ISERT INTO ... SELECT ... FROM .... This is is my code: DBSession.query(ProductMediaGalleryArchive)\ .filter(ProductMediaGalleryArchive.product_id.in_(pack))\ .delete(synchronize_session=False) query = DBSession.query( ProductMediaGallery.code, ProductMediaGallery.filename, ProductMediaGallery.mimetype, ProductMediaGallery.sha1hash, ProductMediaGallery.position,...

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

python,sqlalchemy,flask-sqlalchemy
My SQL skills are pretty lacking, so I can't figure out how to form the query I need. I've got two db models with a one to many relationship, defined like this: class Parent(db.Model): __tablename__ = 'parent' id = db.Column(db.Integer, primary_key = True) children = db.relationship('Child', backref = 'parent', lazy...

Python + Flask App runs from wrong folder after run by Pycharm on 127.0.0.1

python,sqlite,flask,sqlalchemy,pycharm
Setup: I have created and populated a sqllite database in Pycharm, running Python + Flask + SQLAlchemy. The code works perfectly in the console, but when i run it, i get a: OperationalError: (sqlite3.OperationalError) unable to open database file Problem: When i run print os.getcwd() in Chrome/Flask-Werkzeug: C:\Program Files (x86)\JetBrains\PyCharm...

Database migration sqlaclhemy gives an attributeError

python,flask,sqlalchemy,sqlalchemy-migrate
I've build a flask application with a sqlalchemy based db. I use the migrate script from: Link to mega tutorial But when i run this script i get a attributeError. here is a screenie from the error: Link to picture

SQLAlchemy foreign keys access optimazation

python,mysql,sqlalchemy
I have the very simple Parent - Child (one-to-one) relationship between two tables. When I do a look up by ID everything seems okay. But if I run it for 10k IDs it takes almost 70secs to return the result. After my debugging I discovered that the assigning value of...

Using pysqlcipher with SqlAlchemy?

python,python-2.7,sqlalchemy,sqlcipher
I'm trying to add some code to my program to encrypt the sqlite database I use. I'm trying to prompt the user for password, and use that password to create a new encrypted database if it doesn't exist, or decrypt and load an existing DB. There just doesn't seem to...

Connecting to database using SQLAlchemy

python,sqlalchemy
I'm trying to connect to a database on my local machine. import sqlalchemy engine = sqlalchemy.create_engine('mssql+pyodbc://localhost\\SQLEXPRESS/NCM') It fails with the following error: DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') And also outputs this warning: C:\Miniconda\envs\bees\lib\site-packages\sqlalchemy\connectors\pyodbc.py:82: SAWarning: No driver...

Eve SQLAlchemy query catenation

python,sqlalchemy,eve
I can do http://127.0.0.1:5000/people?where={"lastname":"like(\"Smi%\")"} to get people.lastname LIKE "Smi%" How do I concat two conditions, like where city=XX and pop<1000 ?...

keyword can't be an expression when assigning an argument

python,python-2.7,sqlalchemy
Hello I have this method called save_schedule that takes some arguments (transport_id, departure_id, etc) to be saved to the database. but when I ran the code I got this following error newSchedule = TravelScheduleDetailRepository(self.transport_id=transport_id, self.transport_type=transport_type, self.transport_company_name=transport_company_name, self.departure_city_id=departure_city_id, self.departure_country_id=departure_country_id, self.destination_city_id=destination_city_id, self.destination_country_id=destination_country_id,...

Reflecting Oracle Global Temp Tables Using Pythons SQLAlchemy

python,oracle,sqlalchemy
I am using sqlalchemy and want to reflect the table structure of some global temp tables. from sqlalchemy import MetaData ... meta = MetaData() meta.reflect(bind = engine, only = ['tt_employees'], schema = schema) I enable 'echo=True' when establishing the connection: db_engine = create_engine(engine.url.URL(**params), echo=self._echo) I can reflect all tables except...

smarter sqlalchemy wtf dynamic filter in a flask app

python,flask,sqlalchemy,flask-wtforms
I have a form that filters a table, and i would like the form entry to build a sqlalchemy statement. Because I have the option "No Filter", i can't just have the form field go into the filter('form field here'). But there must be a better way than doing every...

Adding related data to a MySQL database with sqlalchemy

python,mysql,sqlalchemy
class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) name = Column(String(1000)) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) name = Column(String(1000)) class C(Base): __tablename__ = 'c' id = Column(Integer, primary_key=True) b_id = Column(Integer, ForeignKey('b.id')) name = Column(String(1000)) a_id = Column(Integer, ForeignKey('a.id')) b = relationship('B', backref=backref('cb'), foreign_keys =...

How to use `__declare_last__` in SQLAlchemy 1.0?

python,sqlalchemy
I used to register sqlalchemy events in the classmethod __declare_last__. My code looked like this: @classmethod def __declare_last__(cls): @event.listens_for(cls, 'after_udpate') def receive_after_update(mapper, conn, target): ... This worked correctly until I upgraded to SQLAlchemy 1.0, with which this hook was not called and my events were thus not registered. I've read...

SQLAlchemy. 2 different relationships for 1 column

python,sqlalchemy
I have a simple many-to-many relationship with associated table: with following data: matches: users: users_mathces: ONE user can play MANY matches and ONE match can involve up to TWO users I want to realize proper relationships in both "Match" and "User" classes users_matches_table = Table('users_matches', Base.metadata, Column('match_id', Integer, ForeignKey('matches.id', onupdate="CASCADE",...

SQL Alchemy Querying Across Star Schema

python,sqlalchemy
I'm attempting to map an existing star schema database using SQL alchemy and am having trouble structuring my joins. I've read through the docs and stack overflow but haven't found a clear answer. Here is the simple layout of my classes. School and Student are different dimension tables with the...

Set intersection on results of sqlalchemy queries

sql,sqlalchemy
I'm attempting to return the overlap of the results of two ORM queries that would both return a list of integers. I have two tables and I know how to perform the simple filtered queries on them. query1 = session.query(T1.UID).filter(T1.date < now) query2 = session.query(T2.UID).filter(T2.date < now) What I'd like...

Cherrypy use plugin in _cp_dispatch/popargs

python,sqlalchemy,cherrypy
I'm using cherrypy with sqlalchemy in order to build an headless (only CLI client) restful server. I've used the following receipe to bind sqlalchemy to cherrypy engine: https://bitbucket.org/Lawouach/cherrypy-recipes/src/c8290261eefb/web/database/sql_alchemy/ The receipe is slightly modified in order to build the database if it doesn' exists. The server expose several uri such as...

PUT request not working, Flask-RESTful, SQLAlchemy

flask,sqlalchemy,flask-sqlalchemy,put,flask-restful
I believe the issue is with committing the changes to the database (3rd to last line: db.session.commit()). For example take a user: username="Foo", email="[email protected]". If in the PUT request body I put {"email":"[email protected]"}, printing 'user.email' after the assignment reveals that the value is in fact changed. Afterwards however, upon querying...

Flask-Sqlalchemy, Primary key for secondary table in many-to-many relationship

python,postgresql,flask,sqlalchemy,many-to-many
I am trying to build simple many-to-many relationship using Flask-Sqlalchemy for Postgresql Database: from app import db from sqlalchemy.dialects.postgresql import UUID authors_books = db.Table( 'authors_books', db.Column('id', UUID(as_uuid=True), primary_key=True), db.Column('author_id', UUID(as_uuid=True), db.ForeignKey('authors.id')), db.Column('book_id', UUID(as_uuid=True), db.ForeignKey('books.id')), ) class Author(db.Model): __tablename__ = 'authors' # Fields id = db.Column(UUID(as_uuid=True), primary_key=True) first_name =...

Use variable in sqlalchemy ilike statement

python,sqlalchemy,sql-like
I am trying to query my database to find all items that match my variable received_input. At the moment I have: session.query(VenueItem).filter(VenueItem.venue_item_name.ilike("%received_input%")).all() The items in my database may contain uppercase and lowercase characters. I need to ensure that the search is case-insensitive ("ApPle" would return from input of "apple"). I...

Duplicate columns when querying SQLAlchemy into Pandas DF?

python,pandas,sqlalchemy
I'm building a python data library for analysis on top of a star schema database and am having trouble integrating pandas and sqlalchemy because of some duplicate column keys in the data frame. Here's the classes: class Student(Base): __tablename__ = 'DimStudent' id = Column('StudentKey', Integer, primary_key=True) srcstudentid = ('SrcStudentId', Integer)...

SQLAlchemy: Is there a way to track the line number and calling class of a query done through SQLAlchemy?

python,mysql,sql,sqlalchemy
Relatively new to SQL and SQLAlchemy, so please forgive any ignorance on my part as to the proper terminology or syntax. I have a MySQL database to which many queries are made through SQLAlchemy from various files. I want to know from which file and from which line the queries...

Updating a table from another table with multiple columns in sqlalchemy

python,mysql,sqlite,sqlalchemy
I want to update multiple columns of one table according to other multiple columns of another table in SQLAlchemy. I'm using SQLite when testing it, so I can't use the `UPDATE table1 SET col=val WHERE table1.key == table2.key" syntax. In other words, I'm trying to create this sort of update...

Date Format (pgsql)

sqlalchemy,psycopg2
I have an old import script which uses SQLAlchemy (0.8.3) and psycopg2 (2.5.1). Now I am migrating it to a newer server with SQLAlchemy (1.0.5) and psycopg2 (2.5.4). My date format is mostly "dd.MM.yyyy" and I get this error DataError: (psycopg2.DataError) date/time field value out of range: "02.12.2014" LINE 3:...

Why is alembic not autogenerating?

python,flask,sqlalchemy,alembic
I am learning flask . Using sqlalchemy for orm and alembic for migrations Going through and following: http://alembic.readthedocs.org/en/latest/autogenerate.html whenever i pass the command " alembic revision --autogenerate -m 'name' " , this error pops up . whatever i do , i configured the config.py file but i think maybe i...

SQLAlchemy: Check is a given value is in a list

postgresql,sqlalchemy
The problem In PostgreSQL, checking whether a field is in a given list is done using the IN operator: SELECT * FROM stars WHERE star_type IN ('Nova', 'Planet'); What is the SQLAlchemy equivalent for an IN SQL query? What have I tried Python's in db_session.query(Star).filter(Star.star_type in ('Nova', 'Planet')) The query...

Working with an One-to-many relationship in SQLAlchemy efficient way

python,sqlalchemy,one-to-many
I am a beginner SQL user. I have one DB with three tables, the table Person and the tables Hotel and Apartment. Each Person has just one relationship to one Hotel or Apartment but each Hotel or Apartment can have one or more persons assigned to them. Having the following...

SQLAlchemy orm styles, how to make special drive to your connection string

python,sqlalchemy,driver,pypy,pypyodbc
I use pypy, pypyodbc and SQLAlchemy. I have problem of odbc connections. I use: engine = create_engine('mssql+pyodbc://dbuser:[email protected]/dbname', echo = False) Session = sessionmaker(bind=engine) style try to connect the database. The error is: C:\pypy\site-packages\sqlalchemy\connectors\pypyodbc.py:82: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections "No driver name...

One-to-one delete for SQLAlchemy classes with inheritance

python,sqlalchemy
I have a class inheritance scheme as layed out in http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) type = Column(String) __mapper_args__ = {'polymorphic_on': type} class Child(Parent): __tablename__ = 'child' id = Column(Integer, ForeignKey('parent.id'),...

How can I define a constraint on an inherited column in SQLAlchemy?

python,sqlalchemy
I have a class inheritance scheme as layed out in http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance and I'd like to define a constraint that uses columns from both the parent and child classes. from sqlalchemy import ( create_engine, Column, Integer, String, ForeignKey, CheckConstraint ) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Parent(Base): __tablename__ =...

How to add comments feature to posts in a flask web application

python,web-applications,flask,sqlalchemy,flask-wtforms
I am developing a web application with Flask.I am confused how to add comment feature to posts in a web application. Parts of my database models are a give below class Post(db.Model): id = db.Column(db.Integer, primary_key = True) title = db.Column(db.String(140)) body = db.Column(db.String(2000)) timestamp = db.Column(db.DateTime) user_id = db.Column(db.Integer,...

Filtering on SQLAlchmey @hybrid_property not working

sqlalchemy
I have a SQLAlchemy Model as follows and am trying to filter on the isActive property. query(PersonMedications).filter(PersonMedication.isActive==False).all() class PersonMedication(ModelAbstract): __tablename__ = "personMedication" id = db.Column(db.Integer, primary_key=True) startDate = db.Column(db.Date) endDate = db.Column(db.Date) isCanceled = db.Column(db.Boolean) @hybrid_property def isActive(self): if self.isCanceled == True: return False elif self.endDate and self.endDate < datetime.date.today():...

SQLAlchemy: Difficulty Querying my DB

python,sqlalchemy
I'm writing a small Python script / app and am just getting my feet wet with SQLAlchemy. The only class I have is "Player", which is defined as follows: class Player: def __init__(self, rating, college, ranking = None, firstName = None, lastName = None): self.rating = rating self.college = college...

Pass in logical operators (and partial expressions) into a function argument Python

python,sqlalchemy,logical-operators
In SQLAlchemy, it is possible to do something like this: mytable.query.filter(mytable.some_col < 5).all() How can I implement something similar? I want developer users to be able to pass in logical operations to a function. Here's an example: class row_obj: def __init__(self, val1, val2, val3, val4): self.val1 = val1 self.val2 =...

Using a ternary operator in a sqlalchemy filter statement

python,sqlalchemy
Let's say I have a one to many relationship defined in sqlalchemy. My parent table has a bunch of children. And those children have start and end times attached to them. The times are datetime.time objects in which the hours must be between 0 and 23. When the end time...

SQLAlchemy: filter by relationship

python,sqlalchemy
I have 2 tables, User and Object, which have a one-to-many relationship (a User can have many objects ). How can I filter for users that have at least one object, in a pep8 compliant way? This code works, but is not pep8-compliant: query = session.query(User.id) query = query.filter(User.objects !=...

sqlalchemy.orm.exc.FlushError: Instance has a NULL identity key

python,mysql,sqlalchemy
I inteded to submit data to mysql table using sqlalchemy from Python27. when I tried to run this file, it shows an error like this sqlalchemy.orm.exc.FlushError: Instance <TravelScheduleDetailRepository at 0x7f0fc07c8950> has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new...

SqlAlchemy: Convert datetime column to date column

python,datetime,sqlalchemy
I'm trying to convert the datetime column to date def filter_between_dates(query, req, db_col): date_from = req.get_param('date_from') #date_to = req.get_param('date_to') query = query.filter(cast(db_col,DATE) == date_from) return query The error showed: "name 'DATE' is not defined" I tried adding import DATE, and got "ImportError:cannot import name 'DATE'" Maybe I'm doing this wrong...

SQLAlchemy operator for PostgreSQL's aggregate (every)

postgresql,sqlalchemy
PostreSQL features the every() aggregate functions, which filters groups by running a predicate on all group rows. For example, for the following table: star_name | star_type | landed_upon -----------+-----------+------------- Mars | Planet | t Venus | Planet | t Rhea | Moons | f Titan | Moons | t This...

relationship with a sliced and casted value of the column in SQLAlchemy

python,sqlalchemy
Is there any way to create a relationship like this (example data) between Parent and Child based on parent_id and id respectively: Parent parent_id: "A1234" name: "Parent Name" Child id: 1234 how can I add the foreign key to the Child? The parent_id is a String. Is there a way...

Insert value into VARBINARY column using sqlalchemy

sql-server,sqlalchemy
I am using a MS SQL database, together with Python and sqlalchemy. One table has a column defined as CREATE TABLE MyTable ( ... [address] [varbinary] (16) NULL ... The table class MyTable is generated by means of table reflection. Assigning a string value to MyTable.address in my Python code...

SQLAlchemy: querying on all subclasses of a class

python,database,table,sqlalchemy
I have the following code in which two classes inherit from a common parent class (which is not a table). I want to query on parent name so that it will also query in all the subclasses and give a result. class Parent(Base): __abstract__ = True id = Column(Integer, primary_key=True)...

What's the pythonic idiom for making a simple list out of a list of instances?

python,sqlalchemy,list-comprehension
I have a list of record instances returned by SQLAlchemy. While the instances have many attributes, I want a new list with only one of the attributes. The java coder in me says: my_records = query.all() names = [] for my_record in my_records: names.append(my_record.name) ...which works, of course. But What's...

When did sqlalchemy execute the query?

sql,sqlalchemy
As I've just start learning to use sqlalchemy recently, the result of the following code make me confused about when sqlalchemy execute the query: query = db.session.query(MyTable) query = query.filter(...) query = query.limit(...) query = query.offset(...) records = query #records=query.all() for r in records: #do something note the line records...

Many-to-one Flask SqlAlchemy rendering

python,flask,sqlalchemy,flask-wtforms
I'm trying to create categories for my posts. End result should be a user when trying to post, they choose a category, the post goes under that category so it can be indexed there. So for example they'd go on example.com/category/Flask and they'd find all the posts that have the...

How do I compare Python list with Postgres Array in SQLAlchemy?

python,postgresql,sqlalchemy
I have a column in my PSQL table type array of type integers, let's call the column tags.I have a Python list that has type integers, lets' call it categories. How do I run a SQLAlchemy query so that I return any row in my table if the tags column...

SQLAlchemy sum of a column btw 2 dates

sqlalchemy,flask-sqlalchemy
I am not sure if this is possible, but I'm looking for a way to use SQLAlchemy func.sum on a column, but only for rows taht are btw 2 dates. To make it clearer, here is the model class Income(db.Model): __tablename__='incomes' id = db.Column(db.Integer, primary_key=True) date = db.Column(db.DateTime) amount =...

Sqlalchemy column_property math

python,sql,sqlalchemy
I'm working with Invoice and Payment models, trying to determine how much money is due to be paid for an Invoice based on Payments for an Invoice. I have come up with the following, making use of Sqlalchemy's column_proprty: # Total amount for an Invoice Invoice.total_rands = column_property( select([func.sum(InvoiceProduct.sub_total_rands)]).where(InvoiceProduct.invoice_id==Invoice.id).correlate(Invoice) )...

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

python,sqlalchemy,flask-sqlalchemy
I hope the word "Types" is used correctly here. Perhaps I mean "Arguments". Feel free to edit. I am creating a database using Models with Flask with SQLAlchemy, where can I find a list of all the different possible Column arguments such as: account_id = db.Column(db.Integer, nullable=False) I know some...