[sqlalchemy] Befuddled with FK, ondelete, and delete cascade

Hello, I have a parent and a child class defined like so: # Parent table. class User(Base): __tablename__ = "users" id = Column(Integer) # Child table. class Stat(Base): __tablename__ = "stats" id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

Thanks Jonathan! Yes, all classes derive from the declarative base: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base(metadata=MetaData(naming_convention={...})) class Parent(Base): ... class Child(Base): ... If I understand you correctly, then the

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

So, here’s what I’m experimenting with and it seems to work: @property def children(self): children = (getattr(self, r.key) for r in self.__mapper__.relationships if r.target.name == "child") return [c for c in children if c is not None] I’m not sure if this is the

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

Thank you, Jonathan. I’ve used SQLA’s association proxies before, I’ll take a look again. You bring up a good point, though: Ok, so this isn't a one-to-one relationship, but a many-to-many > relationship. > That’s something I’ve been debating with myself for a while before I posted here: the

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

Hi Jonathan, The line you commented out from the example was either: > > children = relationship("Child") > > children = relationship("Child", back_populates="parent") > > > both of those lines create an iterable list of all the Child objects on > the `children` > Neither of them would work,

[sqlalchemy] Consolidate multiple one-to-one into a list

Hello, Taking the relationship examples from the documentation , suppose I have the following: class Parent(Base): __tablename__ = "parent" id = Column(Integer, primary_key=True) oldest_child_id = Column(Integer,

Automating charset encoding/collation conversion for MySQL db.

Hello, Using a MySQL database keeping Unicode strings under control turned out to be a bit of a challenge. I could have sworn that character encoding and collation are set up correctly, i.e.

Re: How to handle `default` column values before/after commit()

Sorry, I meant flush in the above post and not commit! -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and

How to handle `default` column values before/after commit()

Suppose the following code: # We define a base for all DB objects, currently empty. class _Base: pass Base = declarative_base(cls=_Base, metadata=MetaData(naming_convention=…)) # Then the objects. class User(Base): __tablename__ = "users" id = Column(UUID(), default=uuid.uuid4,

Re: [sqlalchemy] Confusion over session.dirty, query, and flush

I’d like to pick up this topic once more briefly. Suppose I get the “new”, “dirty”, and “deleted” sets as per discussion below, and I’m especially interested in the “dirty” set: is there a way to find out which properties of an object were modified, or only that the object was modified?

Re: File logger fails when used with a Gunicorn/Pyramid .ini file.

I haven’t used env.py before, you’re talking about this: https://pypi.org/project/env.py/ , correct? On Wednesday, September 26, 2018 at 10:06:31 AM UTC+10, Mike Bayer wrote: > > oh, you know that's in the logging. logging.fileConfig() is used for > that and it's actually in your env.py. Just

Re: File logger fails when used with a Gunicorn/Pyramid .ini file.

Huh… I use the -c option: ./bin/alembic -c proj-localhost.ini upgrade head Jens On Monday, September 24, 2018 at 11:21:42 PM UTC+10, Mike Bayer wrote: > > > Looking at the source code, we already have "here": > > if self.config_file_name: > here =

File logger fails when used with a Gunicorn/Pyramid .ini file.

Hello, In my project.ini file I have configured logging to use a file logger as follows: [loggers] keys = root, …, alembic [handlers] keys = console, file [formatters] keys = generic [logger_root] level = INFO handlers = console qualname = [logger_alembic] level = INFO handlers = qualname

Re: [sqlalchemy] Bulk update & performance question

Thank you Simon, that helps! Jens > I think the idea of processing the rows in chunks is to limit the > memory usage of the Python process. Until you call session.flush(), > all your modifications are held in memory. For small numbers of rows > this isn't a problem, but for huge numbers,

[sqlalchemy] Bulk update & performance question

Hello, While looking for a good way to implement a bulk update to numerous rows of the same table, I came across this performance example : session = Session(bind=engine) for chunk in range(0, n,

[sqlalchemy] Delete, bulk delete, and Sessions—some clarification needed.

Hello, I’d like to get some clarification on the following two code examples. First, bulk delete an object: >>> u = dbsession.query(User).first() >>> u.id '0078ccdf7db046179c59bff01199c25e' >>>

[sqlalchemy] Poll of interest: add @properties to mapped objects or use functions instead?

Hi, Suppose I have the following ORM class: class User(Base): __tablename__= 'users' first_name = Column(String(64), nullable=False) last_name = Column(String(64), nullable=False) email = Column(String(128), nullable=False) In our project we now need the full name of a User,

Re: [sqlalchemy] QueuePool limit overflow and Pyramid sub-requests

Thank you, Mike! What you say makes sense, and I took this question into the Pylons group to get their views and recommendations. (Unfortunately I have no control over the number of subrequests, and thus connections, as

[sqlalchemy] QueuePool limit overflow and Pyramid sub-requests

Hello, I would like to understand the interplay between a SQLA session and a Pyramid’s subrequest . When a request is handled, a new session is created for that request as per the Pyramid/SQLA cookiecutter

Re: [sqlalchemy] MySQL “charset introducer” how-to?

Confirmed: adding `binary_prefix=true` to the URL gets rid of the warning. I’ll also keep an eye out for SA1.3, and will remove that string then. Thanks Mike! On Tuesday, March 13, 2018 at 11:57:52 PM UTC+10, Mike Bayer wrote: > > OK I have better news.pymysql already did the thing I

Re: [sqlalchemy] MySQL “charset introducer” how-to?

Well that’s good news then, thanks Mike 邏 On Monday, March 12, 2018 at 11:08:28 AM UTC+10, Mike Bayer wrote: > > the issue is Python 3 only and reproduces with PyMySQL 0.8.0 and not > with 0.7.1.Hopefully this is a PyMySQL bug that can be resolved > quickly (note I might recall seeing this

Re: [sqlalchemy] MySQL “charset introducer” how-to?

Thank you Mike! I hoped that we can do without all the details, but I guess not. This warning has been (quite literally) bugging me for days. I’m very tempted to ignore it because the warning seems to be a recent regression (see below) and also because we’re moving to PostgreSQL very soon…

[sqlalchemy] MySQL “charset introducer” how-to?

Hello, I ran into a warning (1300, "Invalid utf8mb4 character string: 'C3D545'") when inserting or updating a `blob` and apparently that’s expected behavior as per MySQL bug 79317 . However, according to one of the comments

Re: [sqlalchemy] sqlalchemy_utils.TimezoneType problems with pytz and UTC

Thank you Mike! I also filed an issue with sqlalchemy_utils: https://github.com/kvesteri/sqlalchemy-utils/issues/315 On Thursday, March 8, 2018 at 11:27:20 PM UTC+10, Mike Bayer wrote: > > just FYI I don't do sqlalchemy-utils though other people here may have > insight into your question. > >

[sqlalchemy] sqlalchemy_utils.TimezoneType problems with pytz and UTC

Hi, I just noticed an oddity using the TimezoneType as a column type. Suppose the following statement: conn.execute(t_user.update() \ .where(t_user.c.id == id_) \ .values({ 'timezone': pytz.timezone(tzname), }))

[sqlalchemy] How to properly handle bidirectional many-to-many and their double entries

Hi, I've been playing with the Many-to-Many relationship from the documentation. Suppose I have a student and teacher and would like to define a bidirectional “favorite” relationship between the two, i.e. if a

[sqlalchemy] Performance of ORDER BY vs. list.sort() vs. sorted()

Hello, I’m curious about your experience with sorting the results of all() queries which return a list. It seems to me that there are three ways of sorting such result lists: 1. Use an ORDER BY

Re: [sqlalchemy] Session's dialect's server_version_info may be None? (Execution order dependency)

Simon, thank you for the reference! That makes sense… Jens On Wednesday, February 21, 2018 at 7:15:20 PM UTC+10, Simon King wrote: > > SQLAlchemy doesn't connect to the database until it needs to. Creating > a Session by itself does not cause it to connect. This is mentioned in > the docs: >

Read the Alembic history

Hello, Is there a supported way to get programmatic access to the history of an Alembic migration chain? I mean, the command > alembic -c foo.ini history 3bf9af8da72c -> cb13f97d30c6 (head), Table funk 33b960335847 -> 3bf9af8da72c, Column funk … 36afb00c3a4 -> 7dd0eef31115, Funky funk ->

[sqlalchemy] Session's dialect's server_version_info may be None? (Execution order dependency)

Hello, Suppose I create a db session the normal way: >>> engine = engine_from_config({"sqlalchemy.url":"mysql+pymysql://…"}) >>> Session = sessionmaker(bind=engine) >>> session = Session() I noticed that there is now an order dependency between: >>> session.bind.dialect.server_version_info #

Re: [sqlalchemy] MySQL's sql_mode (ORM)

If I were to go into my MySQL DB and mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES'; would that have the same effect? I find the MySQL documentation somewhat lacking on that topic. What are the scope and lifetime of the above vs. using

Re: [sqlalchemy] Re: changing polymorphic identity on the fly

Thank you Mike! So suppose I have an a Manager object *manager* and I wanted to promote that to Engineer: # Modify the persisted data of the manager object in the db directly. session = object_session(manager) session.execute(manager.__table__.update() \

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

> is that session using "autocommit" mode? > print("--> autocommit", dbsession.autocommit) gives a False. > it looks like the error is raised on the UNLOCK ? When I comment out the UNLOCK, the exception still raises. Here is SQLA’s verbose logging: --> autocommit False 2017-12-07

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

Thank you! I found this elderly thread: https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/8WLhbsp2nls If injecting the SQL statements directly is still the way to go, then I’ll wrap the conditional insert with a table lock. Jens On Tuesday, December 5, 2017 at 6:00:16 AM

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

I am now thoroughly confused. My understanding of the above conditional insert statement was that it won’t persist a token if there is already a token with the same user_id and client_sig in the table. Alas, today once again I see an exception “MultipleResultsFound: Multiple rows were found

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

Hah 樂 Boy this is (not really) funny. Thank you for digging into this, Mike! I had to make two minor import adjustments from sqlalchemy.sql.elements import quoted_name from sqlalchemy.sql.expression import literal_column

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

No problem, here it is. To work with your initial code example... >>> e = create_engine("mysql+pymysql://jens@localhost/test?charset=utf8_socket=/opt/local/var/run/mysql56/mysqld.sock", echo=True) >>> Base.metadata.drop_all(e) 2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine SHOW

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

Thank you, Mike! I’ve tried to implement what we talked about above, but had a few problems. First, I was unable to use MySQLdb because of compilation errors; instead I’ve used pymysql for a while now.

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

Thanks Mike! that's the first red flag here, why can't you put a unique constraint here? > Ordinarily I’d agree. In this case, there’s an additional column called “deleted” which is NULL for active Tokens and contains utcnow() for deleted Tokens. That makes for deleted and active tokens which

[sqlalchemy] How to implement a conditional insert (MySQL)

Hi, My question is based on this answer on Stackoverflow to the question *MySQL Conditional Insert*. In my current SA implementation I have the following code: token = Token(

Re: [sqlalchemy] Confusion over session.dirty, query, and flush

Thank you, the event worked like a charm :-) Though I think that I don't need the commit events, because the application terminates anyway. I modified your approach to gather which objects were flushed so that in the end I can give the user more precise information:

Re: [sqlalchemy] Confusion over session.dirty, query, and flush

Sure. I'm working with two Pyramid/SQLAlchemy web servers, and in order to have a more convenient way of looking at the db data I wrote a small tool which essentially creates a db session, loads the server orm helper functions and sets up an environment much like a view handler functions has.

Re: [sqlalchemy] Confusion over session.dirty, query, and flush

That makes sense, thank you, Simon! Regarding the events: you suggest to use a before_flush() to examine session.dirty whenever a session.query() executes? Also, is there a way to get the list of objects that have been flushed, or should I track them myself whenever a before_flush() event

[sqlalchemy] Confusion over session.dirty, query, and flush

Hello, I've been exploring some of the session functionality that handles object states, and I'm quite confused. Here is what I see: >>> engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://…'}) >>> session_factory = sessionmaker(bind=engine) # No autoflush >>> session =

Re: [sqlalchemy] Handle anticipated data truncation before transaction commits

Hm. I enabled strict mode, and sure enough the warning turns into an exception. Alas, it seems that the flush() in the above example does not cause the exception, it happens later when the query executes. I was looking for a way to preempt that exception: the code runs within a view function

Re: [sqlalchemy] Handle anticipated data truncation before transaction commits

Thanks Mike! Considering we're planning to migrate to PostgreSQL in a month or two, how would I go about that (considering there's no strict mode there). Would the exception be raised on PostgreSQL? Jens On Friday, November 3, 2017 at 12:04:54 PM UTC+10, Mike Bayer wrote: > > > What is the

[sqlalchemy] Handle anticipated data truncation before transaction commits

Hi, I've got a db object with a string property that may grow larger than its column specifies (because it's being appended to). I tried: old_string = obj.string try: obj.string = "abc"

Re: [sqlalchemy] Working with versioned objects and inheritance hierarchies

I need to reread your code a few more times Mike. But I just realized that the versioning is an example (almost a recipe), but it's not something that ships with the SA package, yes? Meaning that I'll have to copy much of the history_meta.py

Re: [sqlalchemy] Working with versioned objects and inheritance hierarchies

Thank you Mike! Yes, indeed you are right and I referred to Single Table Inheritance ! I would like to version just one subclass, i.e. a subset of that single table, and it sounds like that will work (source

[sqlalchemy] Working with versioned objects and inheritance hierarchies

Hello, I'm looking for a way track changes to table rows, very much like described in this Stackoverflow question . SA supports versioning objects as described in the

Re: [sqlalchemy] Curious: SAWarning: DELETE statement on table '…' expected to delete 1 row(s); 0 were matched.

Thanks Mike! My code actually only uses session.delete(obj), at no place does it issue statements directly. Since this is running in the context of requests for a web server (as per this Pyramid cookiecutter ), could it be that two

[sqlalchemy] Curious: SAWarning: DELETE statement on table '…' expected to delete 1 row(s); 0 were matched.

Hi, I am curious about this warning: /…/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py:964: SAWarning: DELETE statement on table '…' expected to delete 1 row(s); 0 were matched. Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

I don't know, Mike, perhaps a good question to ask the mailing list? On Monday, October 16, 2017 at 10:52:25 AM UTC+10, Mike Bayer wrote: > > oh, a "pre fork" problem, does that mean TCP connections to mysql are > being copied from the main process to forked child processes? I > didn't know

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

After asking at the uWSGI mailing list (see this thread ) the suggested solution was to enable the lazy-apps flags. That seems to have worked... On Friday,

Re: Recommended logger for migration scripts

I've used in my generated migration script: log = logging.getLogger(__name__) log.setLevel(logging.INFO) for now and that seems to work; quick glance at

Recommended logger for migration scripts

Hi, I’d like to output some logging info from my migration script, which was generated by Alembic. Other than Python’s own logging module , what is the recommended way to hook into Alembic’s logger to use it’s formatting? Thanks! Jens -- You

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

Again, thank you Mike! I never see these issues happening locally, which is where I use gunicorn as the server. Your theory might explain that. The trace comes from our beta server online, which runs nginx/uwsgi

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

Thank you, Mike! I would have never extracted your interpretation from the trace! (I don’t know the code…) The two questions that arise now, though, are - Why would connections drop out of the pool, is this a configuration problem that I should worry about? - The request still failed with

[sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

Hello, I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the context of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and mysql 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation. On seemingly

Modeling One-to-One relationships and Unique constraints

Hello, I am a bit puzzled over modeling One-to-One relationships. The example in the documentation says to use uselist flag

Unique Constraint over binary blobs

Hello, One of my tables contains a number of LargeBinary columns (holding custom types of encrypted data). I'd like to ensure that one of these columns contains unique data, and thus added `unique=True` to its `Column()` definition. However, as per this thread

Re: Rename an existing constraint

Given an Alembic migration, would you recommend the following code to rename constraints? from alembic import op import sqlalchemy as sa

Re: Rename an existing constraint

Thank you, Mike! I’ll take a closer look at your proposed code this week. I am curious though: not even MySQL has a rename feature , is that because of consistency? PostgreSQL adds ALTER TABLE …

Rename an existing constraint

Hello, I started out migrating my db schema forward using Alembic, and without any constraint naming convention. That caused constraints to be named using MySQL’s default naming. Alas, now I added a naming convention (see doc here

Re: [sqlalchemy] Re: changing polymorphic identity on the fly

Thanks, Mike! My implementation uses the single table inheritance model and I would like to (i.e. *need to*) keep the id the same. Talking about the example in the documentation, do I understand you

Re: [sqlalchemy] Re: changing polymorphic identity on the fly

Hi, I came upon this thread because I've got almost the exact same question. Several years on, though, how would I go about promoting polymorphic objects across siblings today? Thank you! Jens On Tuesday, February 9, 2010 at 4:09:23 AM UTC+10, Michael Bayer wrote: > > Pavel Andreev wrote: >

Re: [sqlalchemy] Adding filters to association (secondary) relationships

Thank you, that worked. Alas, I might have to use an explicit association object after all if I want to set the *is_manager* value in the association table. Simply assigning a new User to the Team.managers

Re: [sqlalchemy] Adding filters to association (secondary) relationships

Thanks Simon. While this seems to have worked, I only run into the next error. Mind you, I’m somewhat new to Alchemy and my SQL is rather rusty at the moment. My current approach managed_teams = relationship("Team",

[sqlalchemy] Are mapped objects uniqued (in a session) ?

Hello, In the scope of a single session, are objects representing the same row of data uniqued? I ran multiple selects and received the same object back, so I suspect that they are uniqued. With that, is it safe to compare object references, e.g. when searching through a collection? For

Re: [sqlalchemy] Adding filters to association (secondary) relationships

Thank you Mike, the composite joins look like something close to what I need. However, it seems that they too require a mapped class for the association table? I tried managed_teams = relationship("Team",

Re: [sqlalchemy] Adding filters to association (secondary) relationships

Thank you Mike, the composite joins look like something close to what I need. However, it seems that they too require a mapped class for the association table? I tried managed_teams = relationship("Team",

[sqlalchemy] Adding filters to association (secondary) relationships

Hello, I’ve been thinking about modeling an *attributed* many-to-many relationship for a few days now, and can’t find a solution that I liked. Somehow association objects feel clumsy because I really would like to avoid creating a class for that association. See more details in this