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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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