SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?


Asked by: First Name637 | Posted: 06-12-2021






Answer 1

To add to the answer from Ali A, this means you need to have nullable=True in your column definition, so that NULL is allowed in the column. For example:

email_address = Column(String, nullable=True)

SQLAlchemy docs for Tables and Columns, excerpt from v1.2 doc:

nullable – When set to False, will cause the “NOT NULL” phrase to be added when generating DDL for the column. When True, will normally generate nothing (in SQL this defaults to “NULL”), except in some very specific backend-specific edge cases where “NULL” may render explicitly. Defaults to True unless primary_key is also True, in which case it defaults to False. This parameter is only used when issuing CREATE TABLE statements.

Answered by: Julian899 | Posted: 07-01-2022



Answer 2

This is a database schema issue, not an SQLAlchemy issue. If your database schema has a column which cannot be NULL, you must put something (i.e. not None) into there. Or change your schema to allow NULL in those columns.

Wikipedia has an article about NULL and an article which describes non-NULL constraints

Answered by: Vivian232 | Posted: 07-01-2022



Answer 3

In case the default are declared in the schema (but with a NOT NULL check), you can declare them with a FetchedValue

from sqlalchemy.schema import FetchedValue


class Foo(Base):
    date_created = Column(DateTime, server_default=FetchedValue())

Answered by: Fiona399 | Posted: 07-01-2022



Similar questions

python - How to print all columns in SQLAlchemy ORM

Using SQLAlchemy, I am trying to print out all of the attributes of each model that I have in a manner similar to: SELECT * from table; However, I would like to do something with each models instance information as I get it. So far the best that I've been able to come up with is: for m in session.query(model).all(): print [getattr(m, x.__str__().split('.')[1]) for x in ...


python - sqlalchemy joined alias doesn't have columns from both tables

All I want is the count from TableA grouped by a column from TableB, but of course I need the item from TableB each count is associated with. Better explained with code: TableA and B are Model objects. I'm trying to follow this syntax as best I can. Trying to run this query: sq = sessio...


python - how to select only some columns in SQLAlchemy?

This is my python code: cx=sqlalchemy.create_engine(u"mysql://username:password@ipaddress/database?charset=utf8") metadata=sqlalchemy.MetaData(cx) orm_obj=sqlalchemy.Table(u'I_POI',metadata,autoload=True) sql=orm_obj.select(u'poi_id,poi_name').where(u'poi_id>1 and poi_id>0').limit(3).offset(0) resultz=sql.execute() for i in resultz: print i [DB] ...


python - SQLAlchemy how to check if a value is between the values in two columns?

How can I check if the condition value is between column1 and column2? MySQL: select * from tablename where '2014-01-01' between start_date and end_date SQLAlchemy: select( [table.c.id], and_('2014-01-01'.between(start_date, end_date)) ) is it possible ?


python - get table columns from sqlAlchemy table model

I have a table where I would like to fetch all the column names however after browsing the interwebs I could not find a way that works. This is what my table looks like: class myTable(Base): __tablename__ = 'myTable' col1 = Column(Integer, primary_key=True) col2 = Column(Unicode(10)) col3 = Column(Integer) col4 = Column(Numeric(10, 6)) col5 = Column(Numeric(6,3)) col6 = Column(...


python - SQLAlchemy to join to same table on a column, but also need data from other columns in result

Assuming I have a model defined as so: class DstEntry(Base): __tablename__ = 'dst_entry' id = Column('id', DBKeyType, primary_key=True) name = Column(String(64)) ip_addr = Column(IPAddress) logical_device_ip = Column(String(64)) And I have data like so: id name ip_addr logical_device_ip -------------------------------------------------- 1 ...


python - SQLAlchemy join - return all columns of one table

I am currently joining two tables returning rows where a column is in a list: a = Table('a', server_metadata, autoload=True) b = Table('b', server_metadata, autoload=True) tickers = ['1', '2'] res = b.join(a).select().where(asset.c.code.in_(tickers)).execute() This returns the correct rows and all the columns from both a and b. How can I only return all the colu...


python - How to update all object columns in SqlAlchemy?

I have a table of Users(more than 15 columns) and sometimes I need to completely update all the user attributes.For xample, I want to replace user_in_db = session.query(Users).filter_by(user_twitter_iduser.user_twitter_id).first() with some other object. I have found the following solution : session.query(User).filter_by(id=123).update({"name": user.name}) but...


python - return NOT NULL columns from class with sqlalchemy

In python sqlalchemy I would like to get the nullable=False columns of my class, including JSONB types. Here's my example class: class MyClass(): __tablename__ = 'myclass' id = Column("id", primary_key=True) name = Column(String, nullable=False) body = Column(JSONB, nullable=False) irrelevant = Column(String, n...


python - Load subset of joined columns in SQLAlchemy

I'm trying to load a subset of columns from multiple joined tables in SQLAlchemy and can't figure out the magic syntax. Here's a simple example of what I'm trying to do. It results in an error (below): from sqlalchemy import create_engine from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, load_on...


python - How do I tell sqlalchemy to ignore certain (say, null) columns on INSERT

I have a legacy database that creates default values for several columns using a variety of stored procedures. It would be more or less prohibitive to try and track down the names and add queries to my code, not to mention a maintenance nightmare. What I would like is to be able to tell sqlalchemy to ignore the columns that I don't really care about. Unfortunately, it doesn't. Instead it provides nul...


python - How do I join two ORM tables in sqlalchemy, but get both columns back?

I've got some code that looks an awful lot like this: import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base engine = sa.create_engine('sqlite:///:memory:', echo=True) Base = declarative_base() class OneThing(Base): __tablename__ = 'one' id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.Text) value = sa.Column(sa.Text) class OtherThing(Base): ...


python - SqlAlchemy using a query with join, how to get only columns from one entity instead of both?

I have two models Employee and UnionEmployees. I can join the two based on employeeid like this: query = session.query(Employee,UnionEmployees)\ .filter(Employee.employeeid==UnionEmployees.employeeid)\ .filter_by(union_status='active') Looking at the SQL under the hood using statement.compile sql = str(query.statement.compile(dialect=mysql.dialect())) I am...


python - How to set the value of a column in sqlalchemy based on two other columns of the same model?

i am building a web application. i have the following model. class Staff(db.Model): id = db.Column(db.Integer, primary_key=True) store_id = db.Column(db.Integer, db.ForeignKey('store.id')) first_name = db.Column(db.String(64)) last_name = db.Column(db.String(64)) username = db.Column(db.String(64)) cell_phone = db.Column(db.Integer) email = db.Column(db.String(64)) position =...


python - sqlalchemy how to divide 2 columns from different table

I have 2 tables named as company_info and company_income: company_info : | id | company_name | staff_num | year | |----|--------------|-----------|------| | 0 | A | 10 | 2010 | | 1 | A | 10 | 2011 | | 2 | A | 20 | 2012 | | 3 | B | 20 | 2010 | | 4 | B | 5 | 2011 |


Python sqlalchemy query label or alias result of two columns as new column name

I have two tables and one of the table has two columns and a row will only contain data in one of the columns: Table 2: | column1 | column2 | ------------------------- | something | Null | ------------------------- | Null | something | ------------------------- I am trying to do something like: q = session.query(Table1.column1, (Table2.column1, Table2.column2).lab...


python - How to divide two columns in sqlalchemy

How generate this SQL, with sqlalchemy SELECT SUM(click) / SUM(impression) as my_indicator FROM stats; session.query( func.sum(table.click).label("click"), func.sum(table.impression).label("impression"), # ??? sum(click) / sum(impression) ??? ).group_by(table.type)


python - How to print mySQL columns with sqlalchemy

We want to fetch data from our mySQL database, and we're using python (sqlalchemy) to do so. We're then saving the data on pandas dataframes. So far we're receiving data, but the column names are not included, and is automatically just indexed instead. How can we include column names, so that the true names are included and not just numbers from 0-5. import pandas as pd from pandas.io import sql from sqla...


python - How to filter a SQL query on columns in SQLAlchemy?

I am new to flask-alchemy. I want to filter a SQL query on the values of a LOC_CODE column. I made db.session.query(schools).filter_by(LOC_CODE='X270').first(). But the compiler returns: (base) C:\Users\antoi\Documents\Programming\musicaltroupefinder>python hello_world.py C:\ProgramData\Anaconda3\lib\site-packages\flask_sqlalchemy\__init__.py:835: FSADeprecationWarning: SQLALCHE...


python - Sqlalchemy max by 2 columns

I need to be able to perform min operation of 2 columns in sqlalchemy. These 2 columns could be from 2 different tables. Pure SQL analog of the operation would be: Select Id, Case When Col1 < Col2 Then Col1 Else Col2 End As TheMin From TableName I've been going through the docs and did not find a straightforward way to do that. How this can be...


python - How do I join three tables with SQLalchemy and keeping all of the columns in one of the tables?

So, I have three tables: The class defenitions: engine = create_engine('sqlite://test.db', echo=False) SQLSession = sessionmaker(bind=engine) Base = declarative_base() class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = Column(String) pubDate = Column(DateTime) class User(Base): ...


python - How to print all columns in SQLAlchemy ORM

Using SQLAlchemy, I am trying to print out all of the attributes of each model that I have in a manner similar to: SELECT * from table; However, I would like to do something with each models instance information as I get it. So far the best that I've been able to come up with is: for m in session.query(model).all(): print [getattr(m, x.__str__().split('.')[1]) for x in ...


python - sqlalchemy joined alias doesn't have columns from both tables

All I want is the count from TableA grouped by a column from TableB, but of course I need the item from TableB each count is associated with. Better explained with code: TableA and B are Model objects. I'm trying to follow this syntax as best I can. Trying to run this query: sq = sessio...


python - how to select only some columns in SQLAlchemy?

This is my python code: cx=sqlalchemy.create_engine(u"mysql://username:password@ipaddress/database?charset=utf8") metadata=sqlalchemy.MetaData(cx) orm_obj=sqlalchemy.Table(u'I_POI',metadata,autoload=True) sql=orm_obj.select(u'poi_id,poi_name').where(u'poi_id>1 and poi_id>0').limit(3).offset(0) resultz=sql.execute() for i in resultz: print i [DB] ...


python - SQLAlchemy add columns to query() on mapped class

I'm wanting to do pagination, I'm using PostgreSQL. To avoid doing the query twice, I'm using the feature described at https://stackoverflow.com/a/8242764 How can I add the full_count column to the list of columns fetched in my query. I can use query.add_columns, but that column is not accessible after calling query.all() ...


python - SQLAlchemy how to check if a value is between the values in two columns?

How can I check if the condition value is between column1 and column2? MySQL: select * from tablename where '2014-01-01' between start_date and end_date SQLAlchemy: select( [table.c.id], and_('2014-01-01'.between(start_date, end_date)) ) is it possible ?


python - get table columns from sqlAlchemy table model

I have a table where I would like to fetch all the column names however after browsing the interwebs I could not find a way that works. This is what my table looks like: class myTable(Base): __tablename__ = 'myTable' col1 = Column(Integer, primary_key=True) col2 = Column(Unicode(10)) col3 = Column(Integer) col4 = Column(Numeric(10, 6)) col5 = Column(Numeric(6,3)) col6 = Column(...


python - SQLAlchemy to join to same table on a column, but also need data from other columns in result

Assuming I have a model defined as so: class DstEntry(Base): __tablename__ = 'dst_entry' id = Column('id', DBKeyType, primary_key=True) name = Column(String(64)) ip_addr = Column(IPAddress) logical_device_ip = Column(String(64)) And I have data like so: id name ip_addr logical_device_ip -------------------------------------------------- 1 ...


python - SQLAlchemy join - return all columns of one table

I am currently joining two tables returning rows where a column is in a list: a = Table('a', server_metadata, autoload=True) b = Table('b', server_metadata, autoload=True) tickers = ['1', '2'] res = b.join(a).select().where(asset.c.code.in_(tickers)).execute() This returns the correct rows and all the columns from both a and b. How can I only return all the colu...


mysql - Max number of columns using Table() for SQLAlchemy Core - Python

I am using SQLAlchemy Core in Python to build MySQL tables. I use the Table() function as follows: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#define-and-create-tables As you can see, each column is given as an argument of the function. The problem is that Python functions only handle a ...


python - How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?


python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?


python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...


python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?


python - SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily ac...


python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter(Record.id.in_(seq)).all() Is there a better way to do it?


python - How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?


python - SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language? Many thanks -- honzas


python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...


python - How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database: engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True) I can populate dtdb with tables, FKs, etc by: metadata.create_all(engine) Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL sta...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top