[sqlalchemy] Re: Filling up a field in a database from a text file, flask

['Jonathan Vanasco' via sqlalchemy]

You should ask this in a Flask discussion group or stackoverflow.  This is 
a sqlalchemy group and most users here have no experience with Flask.

On Friday, August 19, 2022 at 4:13:50 PM UTC-4 nand...@gmail.com wrote:

> I am trying to fill up a field in a table database with contents of a text 
> file, but get None as the response when I run the code. Any assistance will 
> be appreciated. Here is my code:
>
> # view function - routes.py
> ...
> @app.route('/add_vlans', methods = ['GET', 'POST'])
> @login_required
> def add_vlans():
> form = AddVlanForm(current_user.routername)
> if form.validate_on_submit():
> with open("show_vlans", "r") as vlans:
> vlan_output = vlans.read()
> rt = Router(raw_vlans=vlan_output) #raw_vlans - field variable 
> name
> db.session.add(rt)
> db.session.commit()
> return render_template('_show_vlans.html', title='Router Vlans')
>
> #forms.py
> class AddVlanForm(FlaskForm):
> raw_vlans = TextAreaField('Router vlan output:', 
> validators=[Length(min=0, max=140)])
> submit = SubmitField('Get Vlans on Router')
>
> #templates   - router.html
> {% extends "base.html" %}
>
> {% block content %}
>
> Router: {{ router.routername }}
> 
> {% if router.about_router %} About router: {{ router.about_router 
> }} {% endif %}
> 
> Vlans on {{ router.routername }}
> {% for vlan in vlans %}
> 
> {% include '_vlan.html' %}
> 
> {% endfor %}
> {% if router == current_user %}
>   Edit Router  
> {% endif %}
>  Vlan Configurations 
>  Show Router Vlans 
>
>  {% include '_show_vlans.html' %} 
> 
> {% endblock %}
>
> #sub-template -  _show_vlans.html
> 
> 
> Vlans on router {{ current_user.routername }}: 
> {{ current_user.raw_vlans }}
> 
> 
> 
>
> I get the response:
> Vlans on router router20:None
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f5b7ed8b-4c65-4e5d-a274-f8f8460e96fbn%40googlegroups.com.

[sqlalchemy] Re: SQL Alchemy TypeDecorator

['Jonathan Vanasco' via sqlalchemy]

This is usually done in the ORM with functions, and possibly hybrids.  

See https://docs.sqlalchemy.org/en/14/orm/mapped_attributes.html

On Tuesday, August 9, 2022 at 1:55:45 PM UTC-4 Justvuur wrote:

> Hi there,
>
> Is there a way to pass/access model data for a row within the " 
> process_result_value" method of a TypeDecorator?
>
> For example, I want to decrypt the value but only if another value in the 
> same model row is true/false.
>
> Regards,
> Justin
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/daa3-b548-4d33-a638-a7fae644f322n%40googlegroups.com.

[sqlalchemy] Re: SQLAlchemy exists() used with first() ?

['Jonathan Vanasco' via sqlalchemy]

I think you misunderstand `exists()` in SQLAlchemy and SQL.  `exists()` is 
a convenience function to create a SQL `EXISTS` clause, which is an 
operator used for filtering subqueries.

The 'from_exists' is just a subquery.  It is supposed to be used within a 
query which would then limit the query, not executed itself.

See
  
 
https://docs.sqlalchemy.org/en/14/core/selectable.html?#sqlalchemy.sql.expression.Exists
 
  
 
https://docs.sqlalchemy.org/en/14/core/selectable.html?#sqlalchemy.sql.expression.exists
  
 https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.exists
   https://www.w3schools.com/sql/sql_exists.asp



On Tuesday, August 9, 2022 at 2:05:56 PM UTC-4 Justvuur wrote:

> Hi there,
>
> When creating another column property in a model that makes use of the 
> exists(), I noticed that the exists does a "select *".
>
> *For example, the form exists below:*
> class Contact(ResourceMixin, db.Model):  
>  __tablename__ = 'contacts'
>
> form_contacts = db.relationship(FormContact, backref='contact', 
> passive_deletes=True)
>
> form_exists = column_property(
> exists().where(and_( FormContact .form_contact_id == id,
> FormContact.partnership_id == partnership_id
> )).label('form_contact_exist'), deferred=True
> )
> *prints out to be something like:*
> exists(select * from form_contacts where form_contacts.form_contact_id == 
> id and  form_contacts. partnership_id == partnership_id)
>
> Does the exists "stop" the query once one row is returned or does it 
> execute the entire select all query?
> If the latter, is there a way to limit the select all to one row?
>
> Regards,
> Justin
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0cb5d3e1-8e8d-4367-861d-f5e8328c4ffen%40googlegroups.com.

[sqlalchemy] Re: Shared ORM objects between threads

['Jonathan Vanasco' via sqlalchemy]

> I'm guessing we shouldn't be passing ORM objects to threads, but rather 
just passing IDs and then querying the full object in the thread function

Correct.

Database Connections and Sessions are not threadsafe, they are 
thread-local. 
See 
https://docs.sqlalchemy.org/en/14/orm/session_basics.html#is-the-session-thread-safe

Consequently, all objects are thread-local.

If you are simply dealing with read-only concepts, you can `.expunge` an 
object from one session/thread and `.merge` it into another 
session/thread.  This is often playing with fire though, as you must be 
prepared to handle situations where the data may have changed as that type 
of work is not transaction-safe.  See: 
https://docs.sqlalchemy.org/en/14/orm/session_state_management.html



On Thursday, June 30, 2022 at 4:02:23 PM UTC-4 ben.c...@level12.io wrote:

> Hi,
>
> I'm troubleshooting some code that uses thread_pool_executor to run a 
> function, passing an ORM entity as an argument. Within the executed 
> function, we are sometimes receiving a "Value Error: generator already 
> executing" when accessing a related entity via a relationship property.
>
> I'm guessing we shouldn't be passing ORM objects to threads, but rather 
> just passing IDs and then querying the full object in the thread function. 
> Does that hunch sound correct?
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b4fe09b4-bca9-43c6-9079-e601d88100e5n%40googlegroups.com.

Re: [sqlalchemy] simple query takes to long

['Jonathan Vanasco' via sqlalchemy]

When you select in the database ui tool, you are just displaying raw data.

When you select within your code snippets above, Python is creating pandas' 
DataFrame objects for the results. 

These two concepts are not comparable at all.  Converting the SQL data to 
Python data structures in Pandas (and SQLAlchemy's ORM) is a lot of 
overhead - and that grows with the result size.

You can use memory and code profiling tools to explore this and see where 
the issues are. The best approach is what Philip suggested above though, 
and not use pandas, so you can see how Python/SqlAlchemy handles the raw 
data.





On Wednesday, June 8, 2022 at 9:28:38 AM UTC-4 Trainer Go wrote:

> Hello Phil,
>
> i tested both and without printing the result.
>
> table_df = pd.read_sql_query(''SELECT, engine)
> #print(table_df)
> #query = "SELECT"
> #for row in conn.execute(query).fetchall():
> #pass
>
>
> both have nearly the same runtime. So this is not my problem. And yes, 
> they are the same queries cause i copy pasted the select from my DBUI where 
> is tested first the results and the runtime and i expected the same runtime 
> in my program but no ;)
>
> Greeting Manuel
>
> Philip Semanchuk schrieb am Mittwoch, 8. Juni 2022 um 15:04:08 UTC+2:
>
>>
>>
>> > On Jun 8, 2022, at 8:29 AM, Trainer Go  wrote: 
>> > 
>> > When im using pandas with pd.read_sql_query() 
>> > with chunksize to minimiza the memory usage there is no difference 
>> between both runtimes.. 
>>
>> Do you know that, or is that speculation? 
>>
>> > 
>> > table_df = pd.read_sql_query('''select , engine, chunksize = 3) 
>> > 
>> > for df in table_df: 
>> > print(df) 
>> > 
>> > the runtime is nearly the same like 5 minutes 
>>
>> Printing to the screen also takes time, and your terminal probably 
>> buffers the results, which requires memory allocation. I’m not saying this 
>> is your problem (it probably isn’t), but your test still involves pandas 
>> and your terminal, both of which cloud the issue. You would benefit from 
>> simplifying your tests. 
>>
>> Did you try this suggestion from my previous email? 
>>
>>
>> > for row in conn.execute(my_query).fetchall(): 
>> > pass 
>>
>> Also, are you 100% sure you’re executing the same query from SQLAlchemy 
>> that you’re pasting into your DB UI? 
>>
>> Cheers 
>> Philip 
>>
>>
>>
>> > 
>> > 
>> > 
>> > #print(table_df) result: #generator object SQLDatabase._query_iterator 
>> at 0x0DC69C30> 
>> > I dont know if the query will be triggered by using print(table_df) the 
>> result is generator object SQLDatabase._query_iterator at 0x0DC69C30> 
>> > 
>> > but the runtime is 6 seconds like in the DBUI im using. 
>> > 
>> > I have no clue what to do. 
>> > 
>> > Greetings Manuel 
>> > 
>> > Trainer Go schrieb am Mittwoch, 8. Juni 2022 um 09:27:04 UTC+2: 
>> > thank you Philip, 
>> > 
>> > I will test it today. 
>> > 
>> > 
>> > Greetings Manuel 
>> > 
>> > Philip Semanchuk schrieb am Dienstag, 7. Juni 2022 um 17:13:28 UTC+2: 
>> > 
>> > 
>> > > On Jun 7, 2022, at 5:46 AM, Trainer Go  wrote: 
>> > > 
>> > > Hello guys, 
>> > > 
>> > > Im executing 2 queries in my python program with sqlalchemy using the 
>> pyodbc driver. 
>> > > The database is a Adaptive SQL Anywhere Version 7 32 Bit. 
>> > > 
>> > > When im executing the queries in a DB UI it takes 5-6 seconds for 
>> both together and when im using the same queries in my python programm it 
>> takes 5-6 minutes instead of 6 seconds. What im doing wrong? Im new at 
>> this. 
>> > 
>> > To start, debug one query at a time, not two. 
>> > 
>> > Second, when you test a query in your DB UI, you’re probably already 
>> connected to the database. Your Python program has to make the connection — 
>> that’s an extra step, and it might be slow. If you step through the Python 
>> program in the debugger, you can execute one statement at a time (the 
>> connection and the query) to understand how long each step takes. That will 
>> help to isolate the problem. 
>> > 
>> > Third, keep in mind that receiving results takes time too. If your DB 
>> UI is written in C or some other language that allocates memory very 
>> efficiently, it might be a lot faster than building a Pandas dataframe. 
>> > 
>> > You might want to eliminate Pandas entirely so you don’t have to 
>> question whether or not that’s the source of your slowdown. You could do 
>> this instead - 
>> > 
>> > for row in conn.execute(my_query).fetchall(): 
>> > pass 
>> > 
>> > That will force your Python program to iterate over the result set 
>> without being forced to allocate memory for all the results. 
>> > 
>> > Hope this helps 
>> > Philip 
>>
>>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To 

[sqlalchemy] Re: SQLALCHEMY conncection to Sybase Adaptive Server Anywhere Version 7 via TCI/IP

['Jonathan Vanasco' via sqlalchemy]

thanks, gord!

On Thursday, April 14, 2022 at 12:30:44 PM UTC-4 Gord Thompson wrote:

> > Der Datenquellenname wurde nicht gefunden, und es wurde kein 
> Standardtreiber angegeben
>
> "The data source name was not found and no default driver was specified"
>
> Use
>
> import pyodbc
>
> print(pyodbc.drivers())
>
> to view the names of the ODBC drivers that are available to your 
> application.
>
> On Thursday, April 14, 2022 at 3:35:52 AM UTC-6 Trainer Go wrote:
>
>> i tried to connect my database but im getting an InterfaceError and i 
>> dont know how so solve it.
>>
>> connection_string = (
>> "DRIVER=Adaptive Server Anywhere 7.0;"
>> "SERVER=IP;"
>> "PORT=Port;"
>> "UID=ID;PWD=PASSWORD;"
>> "DATABASE=NameOfDatabase;"
>> "charset=utf8;"
>> )
>> connection_url = URL.create(
>> "sybase+pyodbc", 
>> query={"odbc_connect": connection_string}
>> )
>> engine = create_engine(connection_url)
>>
>> conn = engine.connect()
>>
>> InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der 
>> Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber 
>> angegeben (0) (SQLDriverConnect)')
>> InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] 
>> [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, 
>> und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')
>> (Background on this error at: http://sqlalche.me/e/14/rvf5)
>>
>> i have installed the driver on my computer and its called  Adaptive 
>> Server Anywhere 7.0 so i dont know where the problem is...
>>
>> Jonathan Vanasco schrieb am Donnerstag, 14. April 2022 um 00:07:06 UTC+2:
>>
>>> The Sybase dialect was deprecated from first-party support by SQLAlchemy 
>>> and is currently unsupported.
>>>
>>> Gord Thompson, who is a frequent contributor to the core SQLAlchemy 
>>> project, and has generously taken over responsibility for the original 
>>> dialect as a third-party dialect::
>>>
>>> https://github.com/gordthompson/sqlalchemy-sybase
>>>
>>> In addition to offering some excellent code, his repo offers a wiki and 
>>> some documentation - both of which should help.
>>>
>>>
>>> On Tuesday, April 12, 2022 at 11:10:40 AM UTC-4 Trainer Go wrote:
>>>
 im a bit lost and need some help.

 im trying to set up a database connection with sqlalchemy to a Sybase 
 Adaptive Server Anywhere Version 7 and i dont know how.

 I would be really happy if somebody could help me.

 Thanks in advace.


 Greetings Mae

>>>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/43542499-65df-4afd-b052-5a6517bd9b16n%40googlegroups.com.

[sqlalchemy] Re: SQLALCHEMY conncection to Sybase Adaptive Server Anywhere Version 7 via TCI/IP

['Jonathan Vanasco' via sqlalchemy]

The Sybase dialect was deprecated from first-party support by SQLAlchemy 
and is currently unsupported.

Gord Thompson, who is a frequent contributor to the core SQLAlchemy 
project, and has generously taken over responsibility for the original 
dialect as a third-party dialect::

https://github.com/gordthompson/sqlalchemy-sybase

In addition to offering some excellent code, his repo offers a wiki and 
some documentation - both of which should help.


On Tuesday, April 12, 2022 at 11:10:40 AM UTC-4 Trainer Go wrote:

> im a bit lost and need some help.
>
> im trying to set up a database connection with sqlalchemy to a Sybase 
> Adaptive Server Anywhere Version 7 and i dont know how.
>
> I would be really happy if somebody could help me.
>
> Thanks in advace.
>
>
> Greetings Mae
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/35a41aa2-83ca-4a22-af92-eca30662912dn%40googlegroups.com.

[sqlalchemy] Re: create database name lowcase ?

['Jonathan Vanasco' via sqlalchemy]

I'm not aware of any recent changes in the libraries that would cause that 
behavior.

It may be how you are using the libraries or raw sql.

PostgreSQL will convert database names to lowercase UNLESS the database 
name is in quotes.

These will all create `abc`:

CREATE DATABASE abc;
CREATE DATABASE Abc;
CREATE DATABASE ABc;
CREATE DATABASE ABC;
CREATE DATABASE aBc;
CREATE DATABASE aBC;
CREATE DATABASE abC;

These will create two different databases:

CREATE DATABASE "abc";
CREATE DATABASE "Abc";
CREATE DATABASE "ABc";
CREATE DATABASE "ABC";
.. etc.. 


On Thursday, March 31, 2022 at 2:39:32 PM UTC-4 ois...@gmail.com wrote:

> Hi everyone, I have a question
>
> I use Postgresql
> Before creating a database, the name is uppercase and lowercase, and there 
> is no problem.
>
> Later SQLAlchemy was updated to version 1.4
> Don't know when the version started,
> When creating a database again, use uppercase and lowercase names, which 
> will always be lowercase database names.
> As a result, using drop database will fail.
>
> I am currently using:
> Arch-linux
> postgresql  V13.6-1
> sqlalcgemy V1.4.33
> pyscopg2V2.93
> dictalchemy3 V1.0.0
>
> E.g :
> engine = sqlalchemy.create_engine(
> "postgresql://xxx:yyy@localhost/postgres"
> )
> conn = engine.connect()
> conn.execute( "commit" )
> stt = "CREATE DATABASE ABCDEF"
> conn.execute(stt)
> conn.close()
>
> ===
> The database name will become abcdef
>
> I'm not sure if this is the reason for sqlalchemy or pyscopg2 ?
>
> Thank you everyone.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/25fa8667-d4dd-43f8-a137-0c6a9125ccbbn%40googlegroups.com.

Re: [sqlalchemy] Re: many-to-many orm warnings

['Jonathan Vanasco' via sqlalchemy]

I'm sorry you're getting bit by this messaging - but also glad that I'm not 
the only one.  This got me a while ago too.

SqlAlchemy just uses a bare field name when emitting the warning and 
accepting the `overlaps` arguments. In more complex models with 3+ tables 
that have standardize relationship names, it's hard to tell what caused the 
issue and fixing one relationship can unknowingly affect others.

There is a related ticket/PR. I'm not sure if you can pull it against the 
current main branch, but you can do a manual patch of the warnings code 
locally to make the output better:

https://github.com/sqlalchemy/sqlalchemy/issues/7309  - Make the 
overlaps arguments use fully-qualified names

There's also a related ticket to improve the errors when not calling 
`configure_mappers` as 
above: https://github.com/sqlalchemy/sqlalchemy/issues/7305



On Thursday, March 10, 2022 at 12:27:33 PM UTC-5 Michael Merickel wrote:

> Thank you Mike. Really appreciate you unpacking my rambling. This works 
> for me. I found a few spots in our codebase where we were relying on 
> append() working because it really was a simple link table but I rewrote 
> them to just create the link manually and add it to the session which also 
> causes them to appear in the lists.
>
> On Thu, Mar 10, 2022 at 9:17 AM Mike Bayer  
> wrote:
>
>> hey there.
>>
>> The warnings go away entirely by making Parent.children viewonly=True, 
>> which for this type of mapping is recommended:
>>
>> class Parent(Base):
>> __tablename__ = "left"
>> id = Column(Integer, primary_key=True)
>> children = relationship(
>> "Child", secondary=Association.__table__, backref="parents",
>> viewonly=True
>>
>> )
>>
>>
>> you wouldn't want to append new records to Parent.children because that 
>> would create invalid Association rows (missing extra_data).
>>
>> The warning box at the end of 
>> https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object
>>  
>> discusses this situation and the desirability of making the relationship 
>> which includes "secondary" as viewonly=True.
>>
>> hope this helps
>>
>>
>> On Wed, Mar 9, 2022, at 8:09 PM, Michael Merickel wrote:
>>
>> Sorry for the rambling, it's been difficult for me to figure out what 
>> question to ask because I'm so confused. Below is the minimum viable 
>> example that produces no warnings with respect to the overlaps flags and I 
>> cannot explain hardly any of them. For example, why does Child.parents 
>> require "child_links,parent,child"? 3 values that seem to be somewhat 
>> unrelated and are at the very least definitely on different models?
>>
>> class Association(Base):
>> __tablename__ = 'association'
>> left_id = Column(ForeignKey('left.id'), primary_key=True)
>> right_id = Column(ForeignKey('right.id'), primary_key=True)
>> extra_data = Column(String(50))
>>
>> parent = relationship('Parent', back_populates='child_links')
>> child = relationship('Child', back_populates='parent_links')
>>
>> class Parent(Base):
>> __tablename__ = 'left'
>> id = Column(Integer, primary_key=True)
>>
>> children = relationship(
>> 'Child',
>> secondary=Association.__table__,
>> back_populates='parents',
>> overlaps='child,parent',
>> )
>> child_links = relationship(
>> 'Association',
>> back_populates='parent',
>> overlaps='children',
>> )
>>
>> class Child(Base):
>> __tablename__ = 'right'
>> id = Column(Integer, primary_key=True)
>>
>> parents = relationship(
>> 'Parent',
>> secondary=Association.__table__,
>> back_populates='children',
>> overlaps='child_links,parent,child',
>> )
>> parent_links = relationship(
>> 'Association',
>> back_populates='child',
>> overlaps='children,parents',
>> )
>>
>>
>> On Wed, Mar 9, 2022 at 4:50 PM Michael Merickel  
>> wrote:
>>
>> I think ultimately I want the overlaps config but reading through 
>> https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y
>>  
>> it doesn't make any sense to me what the values in the overlaps= argument 
>> are referring to. For example in last snippet that was simpler, what is 
>> overlaps='parent' referring to? Neither the Parent object, nor the Child 
>> object has something named "parent" so other than blinding trusting the 
>> warning I'm unclear how to see what the mapper is building that conflicts 
>> here.
>>
>> On Wed, Mar 9, 2022 at 4:33 PM Michael Merickel  
>> wrote:
>>
>> It's probably worth noting I can narrow it down to a single warning with 
>> the following snippet and it's still unclear to me how to resolve this:
>>
>> class Association(Base):
>> __tablename__ = 'association'
>> left_id = Column(ForeignKey('left.id'), primary_key=True)
>> right_id = Column(ForeignKey('right.id'), primary_key=True)
>> 

[sqlalchemy] Re: Calculate rank of single row using subquery

['Jonathan Vanasco' via sqlalchemy]

> Is this the most efficient way to do this, or am I over-complicating it?

That roughly looks like code that I've implemented in the past.

If it works and you don't have issues, I wouldn't worry about efficiency.  
Stuff like this will often vary based on the underlying table data - the 
structure, size, etc.  Adding indexes on columns can often improve 
performance a lot.

If you're really concerned on optimizing this, the typical approach is to 
focus on generating the target SQL query that works within the performance 
constraints you want, and then porting it to sqlalchemy by writing python 
code that will generate that same output.



On Thursday, September 9, 2021 at 7:57:57 AM UTC-4 ursc...@gmail.com wrote:

> I'm trying to calculate the rank of a particular (unique) row id by using 
> a subquery:
>
> I first calculate the total ranking for a table, Game (using 1.4.23):
>
> sq = (
> session.query(
> Game.id,
> Game.score,
> func.rank().over(order_by=Game.score.desc()).label("rank"),
> )
> .filter(Game.complete == True)
> .subquery()
> )
>
> Then filter by the row ID I want (gameid):
>
> gamerank = (
> session.query(
> sq.c.id, sq.c.score, sq.c.rank
> )
> .filter(sq.c.id == gameid)
> .limit(1)
> .one()
> )
>
> Game.score is a Float column. Is this the most efficient way to do this, 
> or am I over-complicating it?
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e6f92f26-4afb-44d5-a194-f04ace66be2cn%40googlegroups.com.

[sqlalchemy] Re: AsyncEngine always returns UTC time for datetime column

['Jonathan Vanasco' via sqlalchemy]

Can you share the database drivers / dialects you use?  The discrepancy 
could be there.
On Tuesday, September 14, 2021 at 7:03:27 AM UTC-4 
ivan.ran...@themeanalytics.com wrote:

> Hi all,
>
> I am trying to figure it out why AsyncEngine always returns UTC time for 
> datetime column, any help is appreciated?
>
> I am working with sqlalchemy core and async engine. Column definition:
> *Column('test', DateTime(timezone=True), nullable=False)*
>
> Also tried with this, but no luck:
>
> *_connect_args = {'server_settings': {'timezone': 
> ''America/New_York''}}async_db: AsyncEngine = 
> create_async_engine(async_url_from_config(), connect_args=_connect_args)*
>
> When I tried with regular create_engine, everything worked as expected 
> with the same database data.
> Data in the database (configured for New York) contains timezone info:
>
> *test | 2021-08-26 16:02:46.057288-04*
>
> BR,
>
> Ivan
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e7f24870-80d0-49f7-99a0-de4d141f33b9n%40googlegroups.com.

Re: [sqlalchemy] Change in before_flush (maybe from 1.3 to 1.4?)

['Jonathan Vanasco' via sqlalchemy]

What version of 1.4 are you using?  It is before 1.4.7? If so, please 
update to the latest (1.4.23 is current)

There was a regression in some early 1.4s that affected 
flush/commit/transaction in some situations. That was solved in 1.4.7.


On Thursday, September 9, 2021 at 8:52:59 AM UTC-4 Mike Bayer wrote:

>
>
> On Sun, Sep 5, 2021, at 6:41 PM, and...@acooke.org wrote:
>
>
> I'm having a hard time understanding some of my own code, which no longer 
> works.  The only way I can see it having worked in the past is if 
> auto-flushing did not call before_flush, but commit did?  Is that possible?
>
>
> autoflushing has always called before_flush.
>
>
> Somehow I was managing to create instances (in Python) and populate them 
> with auto-generated key values from the database, but then filter out some 
> objects (those with certain attributes null) and never commit them to the 
> database (in before_flush).
>
> I realise this is a somewhat confusing question, sorry, and I can probably 
> fix my code anyway.  I am just looking for some peace of mind in 
> understanding how on earth it ever worked.
>
> Thanks,
> Andrew
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/6a11bc86-54d3-4993-8746-ec865b3003a9n%40googlegroups.com
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0b13b266-9108-4c4b-87ea-18986fcbe140n%40googlegroups.com.

[sqlalchemy] Re: sqlite setting foreign_keys=off temporarily

['Jonathan Vanasco' via sqlalchemy]

The first two things I would look into:

1. Check the sqlite install/version that SqlAlchemy uses.  It is often NOT 
the same as the basic operating system install invoked in your terminal.  
Sometimes that version does not have the functionality you need.

2. Check the transactional isolation level in sqlalchemy and that you are 
committing if needed.  IIRC, the sqlite client defaults to 
non-transactional but the python library defaults to transactional.  I 
could be wrong on this.

Someone else may be able to look through your code and give more direct 
answers.
On Saturday, August 7, 2021 at 11:19:48 PM UTC-4 RexE wrote:

> On startup of my program, my in-memory sqlite DB needs to turn off foreign 
> key enforcement temporarily (in order to insert data from a different 
> sqlite DB). However, it seems my command to set foreign_keys back on has no 
> effect. See the attached MRE.
>
> I expect this output:
> after turning back on [(1,)]
>
> But I get this:
> after turning back on [(0,)]
>
> Interestingly, if I comment out the insert statement (or put it before the 
> toggle) the code works fine.
>
> Any ideas? I tried replicating this in the sqlite CLI but it works as I 
> expect:
>
> SQLite version 3.35.4 2021-04-02 15:20:15
> Enter ".help" for usage hints.
> sqlite> pragma foreign_keys;
> 0
> sqlite> pragma foreign_keys=on;
> sqlite> pragma foreign_keys;
> 1
> sqlite> create table groups (id primary key);
> sqlite> pragma foreign_keys=off;
> sqlite> pragma foreign_keys;
> 0
> sqlite> insert into groups default values;
> sqlite> pragma foreign_keys=on;
> sqlite> pragma foreign_keys;
> 1
>
> I'm using SQLAlchemy==1.3.22.
>
> Thanks!
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ce24e2db-b526-4f9b-bbcb-d0b2ead7b701n%40googlegroups.com.

[sqlalchemy] Re: Oracle connection problem

['Jonathan Vanasco' via sqlalchemy]

You should ensure the connection string does not have any reserved/escape 
characters in it.  People have had similar issues in the past.  If that is 
the case, there are some recent threads in this group and on the github 
issues that show how to overcome the issue by building a connection string 
from components.

Other than that, this could be  an Oracle configuration issue? (see 
http://dba-oracle.com/t_ora_12514_tns_listener_does_not_currently_know_service_requested.htm)
 
Is production configured correctly?
On Wednesday, August 4, 2021 at 7:52:42 PM UTC-4 jca...@gmail.com wrote:

> I am using sqlalchemy 1.4.22 and cx oracle 8.2.1 to connect to production 
> and development environments that each host a similar copy of the same 
> schema.
>
> The connection string that I use is the same for each excluding the 
> password:
>
> oracle+cx_oracle://user:pass@MyTNS
>
> Dev works without a problem, but prod throws an Oracle error:
>
> ORA-12514: TNS:listener does not currently know of service requested in 
> connect descriptor
>
> We can connect using sqlplus with the same credentials and tns id, any 
> idea why it may not work for one environment?
>
> Thanks,
> jlc
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/add2ce16-1470-4b0b-a561-f9549ef4ef48n%40googlegroups.com.

[sqlalchemy] Re: Testing and deprecation of nested transactions

['Jonathan Vanasco' via sqlalchemy]

I typically do local developer testing with sqlite3, and the switch the 
database to postgresql for build/deploy/ci testing in the cloud.

For complex tests, I typically use a fresh database "image". e.g. a sqlite 
file or pgdump output that is tracked in git. 

This is not the solution you're looking for, but i've found it very 
useful.  I spent a long time working on a testing setup like you are trying 
to accomplish, but abandoned it when we built out an integrated test suite 
and data had to persist across multiple database connections.
On Friday, July 30, 2021 at 4:19:35 AM UTC-4 dcab...@gmail.com wrote:

> Hello everyone,
>
> I am working on a new project using SqlAlchemy Core 1.4 with Postgresql 
> and wanted to implement the following pattern for my tests:
>
> - Before each test I would start a transaction (in a 
> @pytest.fixture(autorun=True))
> - Each test may create its own transactions
> - At the end of each test, I would rollback the transaction
>
> The purpose is to keep the database "clean" between tests and not have to 
> manually delete all inserted data. 
>
> However, it seems that SqlAlchemy 1.4 is deprecating nested transactions 
> and that they will be removed in 2.0.
>
> Is there an alternative approach or best practice that I can use for 
> isolating tests in transactions?
>
> I had an alternative idea, like:
>
> - Before each test create the first savepoint (let's call current 
> savepoint N)
> - Catch any commit in the code and instead create a savepoint N+1
> - Catch any rollback and rollback to N-1
>
> Obviously, that seems like a lot of work and I'm not even sure if I can 
> intercept begins, commits and rollbacks that easily.
>
> Alternatively, I could run upgrade and downgrade migrations on every test, 
> but that would slow the test suite down a lot.
>
> Any advice and thoughts would be appreciated.
>
> Thanks!
> Dejan
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e4e452ef-351d-4f92-a87c-1ab52ebc70ffn%40googlegroups.com.

Re: [sqlalchemy] prevent (raise exceptions) on bytestring values for non-byte types

['Jonathan Vanasco' via sqlalchemy]

The second option looks perfect. Will try it!

Thank you so much, Simon!
On Friday, July 30, 2021 at 1:32:42 PM UTC-4 Simon King wrote:

> I can think of a couple of options:
>
> 1. Create a TypeDecorator for String and Text columns that raises an
> error if it sees a bytestring. This will only flag the error when the
> session is flushed.
> 2. Listen for mapper_configured events, iterate over the mapper
> properties and add an "AttributeEvents.set" listener for each one.
> This should flag the error when a bytestring is assigned to a mapped
> attribute.
>
> Hope that helps,
>
> Simon
>
> On Fri, Jul 30, 2021 at 5:10 PM 'Jonathan Vanasco' via sqlalchemy
>  wrote:
> >
> > Mike, thanks for replying but go back to vacation.
> >
> > Anyone else: I am thinking more about an event that can be used to 
> catch, perhaps log, all bytes that go in. I only use a few column classes 
> that expect bytestrings, but many that do not. I've gotten every known bug 
> so far, but I'd like to make sure I'm not just lucky.
> >
> > On Thursday, July 29, 2021 at 6:05:03 PM UTC-4 Mike Bayer wrote:
> >>
> >> The Unicode datatype will emit a warning if you pass it a bytestring. 
> you can use that instead of String, or use a datatype with your own 
> assertions based on 
> https://docs.sqlalchemy.org/en/14/core/custom_types.html#coercing-encoded-strings-to-unicode
> >>
> >>
> >>
> >> On Thu, Jul 29, 2021, at 5:17 PM, 'Jonathan Vanasco' via sqlalchemy 
> wrote:
> >>
> >> I am finally at the tail end of migrating my largest (and hopefully 
> last) Python2 application to Python3.
> >>
> >> An issue that has popped up a lot during this transition, is when a py3 
> bytestring gets submitted into SqlAlchemy.
> >>
> >> When that happens, it looks like SqlAlchemy just passes the value into 
> psycopg2, which wraps it in an object, and I get a psycopg exception that 
> bubbles up to SqlAlchemy:
> >>
> >> > sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) 
> operator does not exist: character varying = bytea
> >> > LINE 3: WHERE foo = '\x626337323133...
> >> > HINT: No operator matches the given name and argument type(s). You 
> might need to add explicit type casts.
> >> > 
> >> > WHERE foo = %(foo)s
> >> > LIMIT %(param_1)s]
> >> > [parameters: {'foo':  0x10fe99060>, 'param_1': 1}]
> >> > (Background on this error at: http://sqlalche.me/e/13/f405)
> >>
> >> Is there an easy way to catch this in SQLAlchemy *before* sending this 
> to the driver and executing it on the server? I'd like to ensure I'm 
> catching everything I should, and nothing is working just by-chance.
> >>
> >>
> >>
> >>
> >> --
> >> SQLAlchemy -
> >> The Python SQL Toolkit and Object Relational Mapper
> >>
> >> http://www.sqlalchemy.org/
> >>
> >> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> >> ---
> >> You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group.
> >> To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy+...@googlegroups.com.
> >> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%40googlegroups.com
> .
> >>
> >>
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> > ---
> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy+...@googlegroups.com.
> > To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d6f8d50c-9465-41bc-a293-d8295c35ecc1n%40googlegroups.com
> .
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/51d32a79-136c-4ec3-9075-b3d1f098d629n%40googlegroups.com.

Re: [sqlalchemy] prevent (raise exceptions) on bytestring values for non-byte types

['Jonathan Vanasco' via sqlalchemy]

Mike, thanks for replying but go back to vacation.

Anyone else: I am thinking more about an event that can be used to catch, 
perhaps log, all bytes that go in.  I only use a few column classes that 
expect bytestrings, but many that do not.  I've gotten every known bug so 
far, but I'd like to make sure I'm not just lucky.

On Thursday, July 29, 2021 at 6:05:03 PM UTC-4 Mike Bayer wrote:

> The Unicode datatype will emit a warning if you pass it a bytestring.  you 
> can use that instead of String, or use a datatype with your own assertions 
> based on 
> https://docs.sqlalchemy.org/en/14/core/custom_types.html#coercing-encoded-strings-to-unicode
>
>
>
> On Thu, Jul 29, 2021, at 5:17 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>
> I am finally at the tail end of migrating my largest (and hopefully last) 
> Python2 application to Python3.
>
> An issue that has popped up a lot during this transition, is when a py3 
> bytestring gets submitted into SqlAlchemy.
>
> When that happens, it looks like SqlAlchemy just passes the value into 
> psycopg2, which wraps it in an object, and I get a psycopg exception that 
> bubbles up to SqlAlchemy:
>
> >sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) 
> operator does not exist: character varying = bytea
> >LINE 3: WHERE foo = '\x626337323133...
> >HINT: No operator matches the given name and argument type(s). You 
> might need to add explicit type casts.
> >
> >WHERE foo = %(foo)s 
> >LIMIT %(param_1)s]
> >[parameters: {'foo':  0x10fe99060>, 'param_1': 1}]
> >(Background on this error at: http://sqlalche.me/e/13/f405)
>
> Is there an easy way to catch this in SQLAlchemy *before* sending this to 
> the driver and executing it on the server?  I'd like to ensure I'm catching 
> everything I should, and nothing is working just by-chance.  
>
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d6f8d50c-9465-41bc-a293-d8295c35ecc1n%40googlegroups.com.

[sqlalchemy] prevent (raise exceptions) on bytestring values for non-byte types

['Jonathan Vanasco' via sqlalchemy]

I am finally at the tail end of migrating my largest (and hopefully last) 
Python2 application to Python3.

An issue that has popped up a lot during this transition, is when a py3 
bytestring gets submitted into SqlAlchemy.

When that happens, it looks like SqlAlchemy just passes the value into 
psycopg2, which wraps it in an object, and I get a psycopg exception that 
bubbles up to SqlAlchemy:

>sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) 
operator does not exist: character varying = bytea
>LINE 3: WHERE foo = '\x626337323133...
>HINT: No operator matches the given name and argument type(s). You 
might need to add explicit type casts.
>
>WHERE foo = %(foo)s 
>LIMIT %(param_1)s]
>[parameters: {'foo': , 'param_1': 1}]
>(Background on this error at: http://sqlalche.me/e/13/f405)

Is there an easy way to catch this in SQLAlchemy *before* sending this to 
the driver and executing it on the server?  I'd like to ensure I'm catching 
everything I should, and nothing is working just by-chance.  


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%40googlegroups.com.

[sqlalchemy] Re: checking in

['Jonathan Vanasco' via sqlalchemy]

> If not I wonder why messages aren't arriving in my INBOX.

Check your settings for this group.  If you do not see the option on the 
menu, try visiting https://groups.google.com/g/sqlalchemy/membership

Google sometimes has a product change de-selects the email delivery 
option.  Sometimes users de-select email delivery and forget about that too.


On Monday, June 14, 2021 at 5:25:47 PM UTC-4 rshe...@appl-ecosys.com wrote:

> I've not worked with SQLAlchemy for several years but now want to use it in
> a couple of applications. I've not seen messages on this maillist for a 
> very
> long time so I tried subscribing and learned that I'm still subscribed.
>
> Am I the only one on this list now?
>
> If not I wonder why messages aren't arriving in my INBOX.
>
> Rich
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b9f2ec03-8dff-4796-b1a3-2efc4409729en%40googlegroups.com.

[sqlalchemy] Re: (cx_Oracle.DatabaseError) ORA-00972

['Jonathan Vanasco' via sqlalchemy]

Try passing a small number to `label_length` in your `create_engine`.  
Something like `label_length=5` might work.  I typically use 4-6 on 
Production/Staging servers, and no argument on Development.


* 
https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.create_engine.params.label_length

I don't have Oracle, so I am not sure if this fixes your exact problem or 
just related ones.

`label_length` will limit the length of aliases that sqlalchemy generates.  
so you would see something like this:

- SELECT very_long_table_name_i_mean_it_is_long.id AS 
very_long_table_name_i_mean_it_is_long_id, 
very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo
 
AS 
very_long_table_name_i_mean_it_is_long_foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo
 
FROM very_long_table_name_i_mean_it_is_long
 LIMIT ? OFFSET ?

+ SELECT very_long_table_name_i_mean_it_is_long.id AS _1, 
very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo
 
AS _2 
FROM very_long_table_name_i_mean_it_is_long
 LIMIT ? OFFSET ?

If the exception is caused by the generated alias (notice the underscore 
separator) 
`very_long_table_name_i_mean_it_is_long_foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo`
 
being too long, that will solve your problem.

but if the exception is caused by (notice the dot separator in table/column 
addressing) 
"very_long_table_name_i_mean_it_is_long.foo_bar_biz_bang_bash_boom_bomb_bing_bong_foo"
 
, then I don't know the remedy.

 
On Monday, March 22, 2021 at 8:28:10 AM UTC-4 durand...@gmail.com wrote:

> Hello,
>
> SqlAchemy automatically specify the table name in front of columns and 
> thus my query parameters are too long and I get the 
> "(cx_Oracle.DatabaseError) ORA-00972" error on Oracle. For example if my 
> table name is "TABLE_NAME_TOO_LONG" and my columns are "id" and "name" a 
> request will look like this:
>
> SELECT "TABLE_NAME_TOO_LONG".id, "TABLE_NAME_TOO_LONG".name FROM 
> "TABLE_NAME_TOO_LONG" where ... 
>
> I could use alias for select request in order to bypass this issue if I 
> understand well (
> https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_using_aliases.htm
> ).
>
> However for an insert I cannot find any solution. 
>
> Is there a way to set an alias to a table name for an insert ? or remove 
> the table name ?
>
> Best regards ;)
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0435579f-8db8-4525-b3b3-54e5edeb243fn%40googlegroups.com.

Re: [sqlalchemy] Injecting User info into _history table to track who performed the change

['Jonathan Vanasco' via sqlalchemy]

Going beyond what Simon did..

I typically make make a table like `user_transaction`, which has all of the 
relevant information for the transaction:

* User ID
* Timestamp
* Remote IP

Using the sqlalchemy hooks, I'll then do something like:

* update the object table with the user_transaction id
or
* use an association table that tracks a user_transaction_id to an object 
id and version
 
FYI, Simon -- as of a few weeks ago, that pattern is now part of the 
pyramid sqlalchemy starter template!

On Monday, March 15, 2021 at 6:46:02 AM UTC-4 Simon King wrote:

> I use pyramid as a web framework, and when I create the DB session for
> each request, I add a reference to the current request object to the
> DB session. The session object has an "info" attribute which is
> intended for application-specific things like this:
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.info
>
> Then, in the before_flush event handler, I retrieve the request object
> from session.info, and then I can add whatever request-specific info I
> want to the DB.
>
> Simon
>
> On Sun, Mar 14, 2021 at 4:05 PM JPLaverdure  wrote:
> >
> > Hi Elmer,
> >
> > Thanks for your reply !
> > My issue is not with obtaining the info I want to inject (the logged in 
> users's email), I already have that all ready to go :)
> >
> > My whole database is versioned using the history_meta.py example from 
> SQLAlchemy
> > 
> https://docs.sqlalchemy.org/en/13/_modules/examples/versioned_history/history_meta.html
> >
> > I was hoping for a simple way to inject the user info into the _history 
> row creation steps.
> >
> > The SQLAlchemy example makes use of this event listener:
> >
> > def versioned_session(session):
> >
> > @event.listens_for(session, "before_flush")
> > def before_flush(session, flush_context, instances):
> > for obj in versioned_objects(session.dirty):
> > create_version(obj, session)
> > for obj in versioned_objects(session.deleted):
> > create_version(obj, session, deleted=True)
> >
> > So I'm tempted to follow the same strategy and just override this 
> listener to supplement it with the user info but I'm wondering how to pass 
> in non SQLAlchemy info into its execution context...
> >
> > So basically, I have the info I want to inject, I'm just not sure how to 
> pass it to SQLAlchemy
> >
> > Thanks,
> >
> > JP
> >
> > On Friday, March 12, 2021 at 6:55:19 p.m. UTC-5 elmer@gmail.com 
> wrote:
> >>
> >> Hi JP,
> >>
> >> Depending on how you've implemented your history tracking, that routine 
> is quite far removed from your web framework and getting a neat, clean way 
> of dealing with that might not be within reach.
> >>
> >> However, most web frameworks have some concept of a threadlocal request 
> (or function to retrieve it), which you could invoke and if such a request 
> exists, you could use that to load whatever user identity you have 
> available on there (again, the details differ, but this tends to be a 
> shared feature). From there you can store the user either as a foreign key, 
> or a unique identifier like email. Which one you pick would depend on how 
> you want the history to be affected when you delete a user record for 
> example.
> >>
> >>
> >>
> >> On Fri, Mar 12, 2021 at 11:58 PM JPLaverdure  
> wrote:
> >>>
> >>> Hello everyone,
> >>>
> >>> We already have the ability to timestamp the creation of the history 
> row, but it would also be interesting to be able to track the user 
> responsible for the content update.
> >>> I would like to get suggestions on the best way to achieve this.
> >>>
> >>> I realize this is somewhat outside the scope of sqlalchemy as the 
> notion of a "logged in user" is more closely related to the context of the 
> app/webapp using SQLAlchemy as its ORM but maybe other people would benefit 
> from having a way to inject arbitrary data in the history table.
> >>>
> >>> Ideally, I would like the insert in the _history table to be atomic, 
> so I feel like hooking an update statement to an event might not be the way 
> to go.
> >>> I'm tempted to modify the signature of before_flush but I'm not sure 
> where it gets called.
> >>>
> >>> Any help is welcome !
> >>> Thanks
> >>>
> >>> JP
> >>>
> >>> --
> >>> SQLAlchemy -
> >>> The Python SQL Toolkit and Object Relational Mapper
> >>>
> >>> http://www.sqlalchemy.org/
> >>>
> >>> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> >>> ---
> >>> You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group.
> >>> To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy+...@googlegroups.com.
> >>> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/82a24998-14e1-4ff4-a725-dd25c20a8bf2n%40googlegroups.com
> .
> >>
> >>
> >>
> >> --
> >>
> >> Elmer
> >
> > --
> > SQLAlchemy -
> > The 

Re: [sqlalchemy] Supporting Function Indexes on a Minimum Sqlite Version

['Jonathan Vanasco' via sqlalchemy]

Thank you so much, Mike!

I roughly had that same @compiles in my tests, but I didn't trust myself... 
and the .dbapi looked like what I wanted, but I really wasn't sure!
On Monday, March 8, 2021 at 4:36:03 PM UTC-5 Mike Bayer wrote:

>
>
> On Mon, Mar 8, 2021, at 12:06 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>
> I have a project that, in a few rare situations, may run on a version of 
> sqlite that does not support function indexes, and "need" to run a unique 
> index on `lower(name)`.  For simplicity, I'll just use a normal index on 
> correct systems,
>
> I'm trying to figure out the best way to implement this.
>
> 1. in terms of sqlite3, what is the best way to access the version 
> Sqlalchemy is using?  the import is in a classmethod, and could either be 
> pysqlite2 or sqlite3?  i seriously doubt anyone would deploy with 
> pysqlite2, but I feel like I should do things the right way.
>
>
> you'd get this from the dbapi:
>
> >>> from sqlalchemy import create_engine 
> >>> e = create_engine("sqlite://")
> >>> e.dialect.dbapi.sqlite_version
> '3.34.1'
>
>
>
>
>
> 2. What is the best way to implement this contextual switch?  I thought 
> about a `expression.FunctionElement` with custom `@compiles`.
>
>
> yeah that is probably the best approach
>
> from sqlalchemy.sql import expression
> from sqlalchemy.ext.compiler import compiles
>
> class maybe_lower(expression.FunctionElement):
> type = String()
>
> @compiles(maybe_lower, 'sqlite')
> def sl_maybe_lower(element, compiler, **kw):
> args = list(element.clauses)
> if compiler.dialect.dbapi_version < ...:
> return "LOWER(%s)" % (compiler.process(args[0], **kw))
> else:
> return compiler.process(args[0], **kw)
>
> @compiles(maybe_lower)
> def default_maybe_lower(element, compiler, **kw):
> args = list(element.clauses)
> return compiler.process(args[0], **kw)
>
>
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/106d60b5-8610-42a4-9738-bd27788b253bn%40googlegroups.com.

[sqlalchemy] Supporting Function Indexes on a Minimum Sqlite Version

['Jonathan Vanasco' via sqlalchemy]

I have a project that, in a few rare situations, may run on a version of 
sqlite that does not support function indexes, and "need" to run a unique 
index on `lower(name)`.  For simplicity, I'll just use a normal index on 
correct systems,

I'm trying to figure out the best way to implement this.

1. in terms of sqlite3, what is the best way to access the version 
Sqlalchemy is using?  the import is in a classmethod, and could either be 
pysqlite2 or sqlite3?  i seriously doubt anyone would deploy with 
pysqlite2, but I feel like I should do things the right way.

2. What is the best way to implement this contextual switch?  I thought 
about a `expression.FunctionElement` with custom `@compiles`.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/99598f81-3851-4f2c-988c-1560d2f5e906n%40googlegroups.com.

Re: [sqlalchemy] relationship query_class in SQLAlchemy 1.4.0b3

['Jonathan Vanasco' via sqlalchemy]

"is it better to think of rebuilding medium+ projects for 2.0 while 
maintaining existing codebases for 1.3? In other words, how much will 2.0 
be backward compatible with 1.3?"

I am saying the following as a general user, and not a past contributor to 
this project:

As per the Release Status system 
(https://www.sqlalchemy.org/download.html#relstatus) when 1.4 becomes the 
official "Current Release", 1.3 will drop to "Maintenance" status.  I 
believe we can expect that, when 2.0 becomes the "Current Release", 1.4 
will drop to "Maintenance" and 1.3 will drop to "EOL".

IMHO, while I might prioritize some migration work based on the size of a 
project, if any given project is expected to be undergoing active 
development or be deployed in 2022 and beyond, they should start planning 
for the "2.0" style migration in their sprints. I can't stress this enough, 
my metric would be active-use and active-development, not the size of the 
codebase.

Personally, I would prioritize adapting projects to deploy on 1.4 as the 
ASAP first step -- there are a few small backwards incompatibilities 
between 1.4 and 1.3.  I still run everything on 1.3, but we test and 
develop against 1.4 -- using comments. docstrings to note what changes will 
be required in 1.4 -- or "switch" blocks so CI can run against both 
versions.  

I strongly recommend doing all new work in the 2.0 style, and start 
scheduling the 2.0 migration into sprints. Building anything against 1.3 
right now is really doing nothing but assuming technical debt, and it's 
going to be much easier (and much less work!) planning for this change 
now.  I would not want to be in a situation where one or more projects 
require an EOL version, and there are critical features/bugfixes in the 
newer branch.

You're likely to get a good chunk of time out of 1.4, but I would not 
target 1.3 at this point.


On Monday, March 1, 2021 at 9:45:55 AM UTC-5 aa@gmail.com wrote:

> yes so, SQLAlchemy 2.0's approach is frankly at odds with the spirit of 
>> Flask-SQLAlchemy.The Query and "dynamic" loaders are staying around 
>> largely so that Flask can come on board, however the patterns in F-S are 
>> pretty much the ones I want to get away from. 
>
>
> 2.0's spirit is one where the act of creating a SELECT statement is a 
>> standalone thing that is separate from being attached to any specific class 
>> (really all of SQLAlchemy was like this, but F-S has everyone doing the 
>> Model.query thing that I've always found to be more misleading than 
>> helpful), but SELECT statements are now also disconnected from any kind of 
>> "engine" or "Session" when constructed.
>
>  
>
> as for with_parent(), with_parent is what the dynamic loader actually uses 
>> to create the query.  so this is a matter of code organization.
>> F-S would have you say:
>>
>  
>
> user = User.query.filter_by(name='name').first()
>> address = user.addresses.filter_by(email='email').first()
>>
>  
>
> noting above, there's no "Session" anywhere.  where is it?   Here's a 
>> Hacker News comment lamenting the real world implications of this: 
>> https://news.ycombinator.com/item?id=26183936  
>>
>  
>
> SQLAlchemy 2.0 would have you say instead:
>>
>  
>
> with Session(engine) as session:
>> user = session.execute(
>>   select(User).filter_by(name='name')
>> ).scalars().first()
>>
>>address = session.execute(
>>select(Address).where(with_parent(user, 
>> Address.user)).filter_by(email='email')
>>).scalars().first()
>>
>  
>
> Noting above, a web framework integration may still wish to provide the 
>> "session" to data-oriented methods and manage its scope, but IMO it should 
>> be an explicit object passed around.  The database connection / transaction 
>> shouldn't be made to appear to be inside the ORM model object, since that's 
>> not what's actually going on.
>
>
> The newer design indeed provides a clearer view of the session.
>
> If you look at any commentary anywhere about SQLAlchemy, the top 
>> complaints are:
>
>
>> 1. too magical, too implicit
>
>
>> 2. what's wrong with just writing SQL?
>
>
>> SQLAlchemy 2.0 seeks to streamline the act of ORMing such that the user 
>> *is* writing SQL, they're running it into an execute() method, and they are 
>> managing the scope of connectivity and transactions in an obvious way.   
>> People don't necessarily want bloat and verbosity but they do want to see 
>> explicitness when the computer is being told to do something, especially 
>> running a SQL query.  We're trying to hit that balance as closely as 
>> possible.
>
>
>> The above style also has in mind compatibility with asyncio, which we now 
>> support.  With asyncio, it's very important that the boundary where IO 
>> occurs is very obvious.  Hence the Session.execute() method now becomes the 
>> place where users have to "yield".  With the older Query interface, the 
>> "yields" would be all over the place and kind of arbirary, since some Query 
>> 

Re: [sqlalchemy] Batching INSERT statements

['Jonathan Vanasco' via sqlalchemy]

I'm not familiar with this exactly, but have a bit of experience in this 
area.

I just took a look at this module (nice work!).  It's VERY well documented 
in the docstrings (even nicer work!)

I think the core bit of this technique looks to be in 
`_get_next_sequence_values` -  
https://github.com/benchling/sqlalchemy_batch_inserts/blob/master/sqlalchemy_batch_inserts/__init__.py#L51-L83

Vineet is obtaining the ids by running the SQL generate_series function 
over the nextval function.

When I've done large batches and migrations like this, I've used a somewhat 
dirty cheat/trick.  Assuming a window of 1000 inserts, I would just 
increment the serial by 1000 and use "new number - 1000" as the range for 
IDs.  That is somewhat closer to the "max id" concept.  Vineet's approach 
is better.

In terms of dealing with multiple foreign key constraints, pre-assigning 
IDs may or may not work depending on how your database constraints exist.

As a habit, I always create (or re-create) Postgres foreign key checks as 
deferrable. When dealing with batches, I (i) defer all the involved 
constraints [which can be on other tables!], (ii) process the batch, (iii) 
set constraints to immediate.  If the migration is LARGE, i'll drop all the 
indexes the tables too, and possibly drop the constraints too and run 
multiple workers. This gets around the overheads from every insert 
populating rows+indexes, and the FKEY integrity checks on every row. 




On Friday, February 12, 2021 at 2:06:55 PM UTC-5 christia...@gmail.com 
wrote:

> Hi Vineet, Mike,
>
> @Vineet, thank you for the interesting blog post on bulk insert with 
> SQLAlchemy ORM: 
> https://benchling.engineering/sqlalchemy-batch-inserts-a-module-for-when-youre-inserting-thousands-of-rows-and-it-s-slow-16ece0ef5bf7
>
> A few questions:
>
> 1. Do we need to get the incremented IDs from Postgresql itself, or can we 
> just fetch the current max ID on a table and increment IDs in Python 
> without querying the DB for the incremented values?
>
> 2. I was intrigued by the following phrase:
>
> > * P.S. execute_values in psycopg2 v2.8 supports getting returned values 
> back, so it’s possible that SQLAlchemy may support batch inserting these 
> models (with an auto-incrementing primary key) in the future. 
>
> @Mike @Vineet, do you know if this is the case, ie if bulk insert now 
> works out of the box (without pre-assigning incremented IDs)?
>
> 3. Does this imply any change in case of bulk insert of multiple models 
> with foreign keys referring to each other? This answer 
>  seems to suggest 
> pre-assigning IDs for it to work.
> On Friday, February 21, 2020 at 3:49:54 PM UTC+1 Mike Bayer wrote:
>
>> Hi Vineet -
>>
>> glad that worked!   I'll have to find some time to recall what we worked 
>> out here and how it came out for you, I wonder where on the site this kind 
>> of thing could be mentioned.we have 3rd party dialects listed out in 
>> the docs but not yet a place for extensions.
>>
>> On Wed, Feb 19, 2020, at 9:28 PM, Vineet Gopal wrote:
>>
>> Hi Mike,
>>
>> Thanks for all of your help getting this working again. We've used this 
>> solution in production for two years now, and it's helped our performance 
>> significantly.
>>
>> We just open-sourced the solution that we built so others can use it, and 
>> are also writing a blog post to cover some of the details. I'm attaching a 
>> copy of the blog post here. Obviously not expected, but if you are 
>> interested in taking a look, we are happy to incorporate any comments that 
>> you may have before publishing.
>>
>> Here's a link to the repo as well: 
>> https://github.com/benchling/sqlalchemy_batch_inserts
>>
>> Best,
>> Vineet
>>
>> On Mon, Oct 9, 2017 at 10:27 PM  wrote:
>>
>> if you're using Postgresql, there's a vastly easier technique to use 
>> which is just to pre-fetch from the sequence: 
>> identities = [ 
>> val for val, in session.execute( 
>>  "select nextval('mytable_seq') from " 
>>  "generate_series(1,%s)" % len(my_objects)) 
>> ) 
>> ] 
>> for ident, obj in zip(identities, my_objects): 
>> obj.pk = ident 
>>
>> Wow, that's a great idea! I got it working for most of our models. I have 
>> some questions about how inserts for joined-table inheritance tables are 
>> batched together, but I'll ask them in a separate post since they're 
>> somewhat unrelated to this.
>>
>> So the complexity of adding multi-values insert with sequences would 
>> benefit an extremely narrow set of use cases, would be very labor 
>> intensive to implement and maintain, and is unnecessary for the single 
>> target platform in which this case would work. 
>>
>> That makes sense, thanks for the explanation!
>>
>>  
>>
>> On Monday, October 9, 2017 at 8:44:51 AM UTC-7, Mike Bayer wrote:
>>
>> On Mon, Oct 9, 2017 at 4:15 AM,   wrote: 
>> > Hello! I've spent some time looking at SQLAlchemy's ability to batch 
>> > inserts, and have 

[sqlalchemy] Re: Relationship with 2 intermediary tables

['Jonathan Vanasco' via sqlalchemy]

This is, IMHO, one of the most complex parts of SQLAlchemy.

In this public project, i have a handful of secondary/secondaryjoin 
examples that may help you

https://github.com/aptise/peter_sslers/blob/main/peter_sslers/model/objects.py#L3778-L4714

There is a section in the docs that should help a bit

  
  
https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#composite-secondary-joins

I think you want something like

Person.photos = relationship(
Photo,
primaryjoin="""Person.id==PersonInstance.person_id""",
secondary="""join(PersonInstance,
  PhotoInstance,
  
PersonInstance.id==PhotoInstance.person_instance_id).join(Photo, 
PhotoInstance.photo_id == Photo.id)""",
)

I don't think the secondaryjoin is needed in this case.  I could be wrong.

The way I like to structure these complex joins is something like this...

A.Zs = relationship(
Z,  # the destination
primaryjoin="""A.id == B.id""",  # only the first association table
secondary="""join(B.id == C.id).join(C.id == D.id)...(X.id==Y.id)""",  
# bring the rest of the tables in
secondaryjoin=="""and_(Y.id==Z.id,  Z.id.in(subselect))"""  # custom 
filtering/join conditions
)

Does that make sense?  Mike has another way of explaining it in the docs, 
but this is how I best remember and implement it.
On Sunday, February 7, 2021 at 3:25:35 PM UTC-5 daneb...@gmail.com wrote:

> I am trying to create a relationship from one table to another, which 
> involves two intermediary tables. I *think* I need to use the secondaryjoin 
> + secondary arguments to relationship(). But after studying the 
> documentation for a long time, I can't get my head around how these 
> arguments are supposed to work.
>
> Here is my schema:
>
> class Person(Base):
> __tablename__ = "person"
> id = Column(Integer, primary_key=True)
>
> class PersonInstance(Base):
> __tablename__ = "person_instance"
> id = Column(Integer, primary_key=True)
> person_id = Column(Integer, ForeignKey("person.id"))
>
> class Photo(Base):
> __tablename__ = "photo"
> id = Column(Integer, primary_key=True)
>
> class PhotoInstance(Base):
> __tablename__ = "photo_instance"
> id = Column(Integer, primary_key=True)
> photo_id = Column(Integer, ForeignKey("photo.id"))
> person_instance_id = Column(Integer, ForeignKey("person_instance.id"))
>
> I want to create a one-to-many relationship *Person.photos* which goes 
> from Person -> Photo. A Person is one-to-many with PersonInstance, and a 
> Photo is one-to-many with PhotoInstance objects. The connection from a 
> Person to a Photo exists between PersonInstance and PhotoInstance, via the 
> PhotoInstance.person_instance_id foreign key.
>
> First I tried using only primaryjoin:
>
> photos = relationship(
> "Photo",
> primaryjoin=(
> "and_(Person.id==PersonInstance.person_id, "
> "PersonInstance.id==PhotoInstance.person_instance_id, "
> "PhotoInstance.photo_id==Photo.id)"
> )
> )
>
> I got an error saying it couldn't find the necessary foreign keys to 
> compute the join.
>
> So now I'm messing with secondary + secondaryjoin, but it's really trial & 
> error as I don't know how these arguments are supposed to work in my case.
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ff490d21-4e96-492a-a8ca-f953d1dd3e2fn%40googlegroups.com.

Re: [sqlalchemy] SQLAlchemy transaction ID

['Jonathan Vanasco' via sqlalchemy]

FWIW, within the realm of pyramid_tm, the more common use-cases for 
two-phase transaction support are for sending mail and a dealing with task 
queues - not two separate databases.

On Wednesday, January 27, 2021 at 2:40:21 PM UTC-5 Mike Bayer wrote:

>
>
> On Wed, Jan 27, 2021, at 2:23 PM, Thierry Florac wrote:
>
> Hi,
>
> I'm actually using two databases connections: one is my "main" connection, 
> opened on a ZODB (with RelStorage), and **sometimes** I have to open 
> another connection on another database (and event more sometimes); the two 
> transactions have to be synchronized: if one of them is aborted for any 
> reason, the two transactions have to be aborted.
>
>
>
> OK, then two phase it is
>
> I have always thought that the two-phase transaction was created to handle 
> this kind of use case, but if there is another better solution, I would be 
> very happy to know about it!
>
>
> if you need the ORM to call prepare() then you need the XID and there you 
> are.
>
> This is all stuff that I think outside of the Zope community (but still in 
> Python) you don't really see much of.  If someone's Flask app is writing to 
> Postgresql and MongoDB they're just going to spew data out to mongo and not 
> really worry about it, but that's becasue mongo doesn't have any 2pc 
> support.It's just not that commonly used because we get basically 
> nobody asking about it.
>
>
>
> @jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github 
> and made a pull request. But I don't know how to provide a test case as a 
> two-phase commit is not supported by SQLite...
> I'll try anyway to provide a description of a "method" I use to reproduce 
> this!
>
>
> So interesting fact, it looks like you are using Oracle for 2pc, that's 
> what that tuple is, and we currently aren't including Oracle 2pc in our 
> test support as cx_Oracle no longer includes the "twophase" flag which I 
> think we needed for some of our more elaborate tests.  At the moment, 
> create_xid() emits a deprecation warning.  I've been in contact with Oracle 
> devs and it looks like we should be supporting 2pc as I can get help from 
> them now for things that aren't working.   I've opened 
> https://github.com/sqlalchemy/sqlalchemy/issues/5884 to look into this.   
> you should have been seeing a deprecation warning in your logs all this 
> time though.
>
>
>
>
>
> Best regards,
> Thierry
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> Le mer. 27 janv. 2021 à 19:19, Mike Bayer  a 
> écrit :
>
>
>
>
> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
>
>
> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I 
> create an SQLAlchemy session which is joined to main transaction using this 
> kind of code:
>
> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
>
> *  from *zope.sqlalchemy *import *register
> *  from *zope.sqlalchemy.datamanager *import* join_transaction
>
>   _engine = get_engine(*engine*, *use_pool*)
>   if *use_zope_extension*:
>   factory = scoped_session(sessionmaker(*bind*=_engine, *twophase*=
> *True*))
>   else:
>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>   session = factory()
>   if *use_zope_extension*:
>   register(session, *initial_state*=*STATUS_ACTIVE*)
>   if *join*:
>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which 
> is tracking many SQLAlchemy events, including two-phase commits 
> transactions, and which in this context receives transaction IDs as a three 
> values tuple instead of a simple string (like, for example: (4660, 
> '12345678901234567890123456789012', '0009'), 
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
>
> I would ask if you really really want to use the "twophase=True" flag, and 
> I would suggest turning it off if you aren't in fact coordinating against 
> multiple RDBMS backends (and even if you are, maybe).   I'm not really sure 
> what that tuple is, I'd have to look but it seems likely to be related to 
> the XID stuff, which is really not something anyone uses these days.
>
>
>
> Best regards,
> Thierry
>
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web 

Re: [sqlalchemy] FAQ or Feature Ideas for ORM Object and Session

['Jonathan Vanasco' via sqlalchemy]

Ok. I'll generate a docs PR for sqlalchemy and pyramid.   this comes up so 
much.

On Wednesday, January 27, 2021 at 2:25:29 PM UTC-5 Mike Bayer wrote:

>
>
> On Wed, Jan 27, 2021, at 1:12 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>
> I've been working with a handful of SQLAlchemy and Pyramid based projects 
> recently, and two situations have repeatedly come up:
>
> 1. Given a SQLAlchemy Object, access the SQLAlchemy Session
> 2. Given a SQLAlchemy Object or Session, access the Pyramid Request object
>
> The general solutions I've used to handle this is:
>
> 1. An Object can use the runtime inspection API to grab it's active 
> session:
>
> from sqlalchemy import inspect
>
> @property
> def _active_session(self):
> dbSession = inspect(self).session
> return dbSession
>
>
> There's a much older function sqlalchemy.orm.object_session() that also 
> does this.   I prefer giving people the inspect() interface because I'd 
> rather expose the first class API and not confuse things.   but 
> object_session() isn't going away.
>
>
>
> 2.  Attach the Pyramid request to the session_factory when a session is 
> created:
>
> def get_tm_session(request):
> dbSession = session_factory()
> zope.sqlalchemy.register(dbSession, 
> transaction_manager=transaction_manager, keep_session=True)
> if request is not None:
> def _cleanup(request):
> dbSession.close()
> request.add_finished_callback(_cleanup)
> # cache our request onto the dbsession
> dbSession.pyramid_request = request
> return dbSession
>
> I've needed to implement these patterns in a lot of projects. This makes 
> me wonder if there is/could be a better way.
>
>
> That request would be better placed in session.info which is the official 
> dictionary for third-party things to go.
>
>
>
>
> 1.  Would it be beneficial if ORM objects could surface the current 
> Session, if any, as a documented property ?  I do this in my base classes, 
> but with the overhead of the inspect system, and I repeat this in every 
> project.
>
>
> as a property?  no, we can't do that.we try to add zero "names" to the 
> class of any kind.there's "_sa_instance_state", 
> "_sa_instrumentation_manager" and that's as far as we go; doing absolute 
> zero to the namespace of the mapped class is a fundamental rule of the 
> ORM. 
>
>
>
> 2.  Would it be better for the sessionmaker had any of ?
>
> a. An official namespace were developers could attach information.  
> I'm using `pyramid_request` because I doubt SQLAlchemy will every step on 
> that - but it would be nice if there were a dedicated 
> attribute/object/namespace on the Session
>
>
> session.info:
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=session%20info#sqlalchemy.orm.session.Session.info
>
>
> b. `sqlalchemy.orm.session.Session()` could accept a 
> dict/payload/object/whatever on init, which would be attached to a single 
> session in the aforementioned dedicated namespace. 
>
>
> Session.info::)  
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=session%20info#sqlalchemy.orm.session.Session.params.info
>
>
>
>
> The usage would be something like:
>
> sess = Session(customized={"request": request})
>
> which might then me accessed as:
>
> sess.customized.request
>
>
> poof! it's done
>
>
>
>
>
>
>
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/b5031f10-c2c8-4065-b968-3a55f2bf6daen%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/b5031f10-c2c8-4065-b968-3a55f2bf6daen%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/a36086e9-499f-43af-969f-4f5cf3c0ff96n%40googlegroups.com.

[sqlalchemy] FAQ or Feature Ideas for ORM Object and Session

['Jonathan Vanasco' via sqlalchemy]

I've been working with a handful of SQLAlchemy and Pyramid based projects 
recently, and two situations have repeatedly come up:

1. Given a SQLAlchemy Object, access the SQLAlchemy Session
2. Given a SQLAlchemy Object or Session, access the Pyramid Request object

The general solutions I've used to handle this is:

1. An Object can use the runtime inspection API to grab it's active session:

from sqlalchemy import inspect

@property
def _active_session(self):
dbSession = inspect(self).session
return dbSession

2.  Attach the Pyramid request to the session_factory when a session is 
created:

def get_tm_session(request):
dbSession = session_factory()
zope.sqlalchemy.register(dbSession, 
transaction_manager=transaction_manager, keep_session=True)
if request is not None:
def _cleanup(request):
dbSession.close()
request.add_finished_callback(_cleanup)
# cache our request onto the dbsession
dbSession.pyramid_request = request
return dbSession

I've needed to implement these patterns in a lot of projects. This makes me 
wonder if there is/could be a better way.


1.  Would it be beneficial if ORM objects could surface the current 
Session, if any, as a documented property ?  I do this in my base classes, 
but with the overhead of the inspect system, and I repeat this in every 
project.

2.  Would it be better for the sessionmaker had any of ?

a. An official namespace were developers could attach information.  I'm 
using `pyramid_request` because I doubt SQLAlchemy will every step on that 
- but it would be nice if there were a dedicated attribute/object/namespace 
on the Session
b. `sqlalchemy.orm.session.Session()` could accept a 
dict/payload/object/whatever on init, which would be attached to a single 
session in the aforementioned dedicated namespace. 

The usage would be something like:

sess = Session(customized={"request": request})

which might then me accessed as:

sess.customized.request






-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b5031f10-c2c8-4065-b968-3a55f2bf6daen%40googlegroups.com.

[sqlalchemy] Re: SQLAlchemy transaction ID

['Jonathan Vanasco' via sqlalchemy]

Thierry,

Would you mind putting together a test-case on this?  I haven't experienced 
that before, and I authored that feature in the debugtoolbar.  If I can 
recreate it, I'll put together a fix and work with the pyramid team to get 
a new release out asap.

On Wednesday, January 27, 2021 at 8:32:34 AM UTC-5 tfl...@gmail.com wrote:

> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I 
> create an SQLAlchemy session which is joined to main transaction using this 
> kind of code:
>
>   from sqlalchemy.orm import scoped_session, sessionmaker
>
>   from zope.sqlalchemy import register
>   from zope.sqlalchemy.datamanager import join_transaction
>
>   _engine = get_engine(engine, use_pool)
>   if use_zope_extension:
>   factory = scoped_session(sessionmaker(bind=_engine, twophase=True))
>   else:
>   factory = sessionmaker(bind=_engine, twophase=True)
>   session = factory()
>   if use_zope_extension:
>   register(session, initial_state=STATUS_ACTIVE)
>   if join:
>   join_transaction(session, initial_state=STATUS_ACTIVE)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which 
> is tracking many SQLAlchemy events, including two-phase commits 
> transactions, and which in this context receives transaction IDs as a three 
> values tuple instead of a simple string (like, for example: (4660, 
> '12345678901234567890123456789012', '0009'), 
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
> Best regards,
> Thierry
>
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/8728dadd-102f-4751-a798-d1a5794145den%40googlegroups.com.

Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

['Jonathan Vanasco' via sqlalchemy]

This was not clear enough in Mike's post: `Foo.__table__` is the same type 
of object as `_foo = table(...)`.  SQLAlchemy ORM is built on top of 
SQLAlchemy's Core, so the  ORM's `.__table__` attribute is the Core's 
`table()` object.

Since they're the same, the two will have the same performance within 
`conn.execute(`.

On Wednesday, November 25, 2020 at 4:18:46 PM UTC-5 Kata Char wrote:

> I see, does that mean there is no difference in performance if one or the 
> other is used? In other words
> from sqlalchemy.sql import table
>
> _foo = table(...)
> conn.execute(_foo.insert(), [{...}, ...])
>
> Would have the same performance as `conn.execute(Foo.__table__.insert(), 
> [{...}, ...])`
>
> On Wednesday, November 25, 2020 at 8:27:53 AM UTC-8 Mike Bayer wrote:
>
>>
>>
>> On Wed, Nov 25, 2020, at 10:30 AM, Kata Char wrote:
>>
>> Hi, sorry if this post is a duplicate, my first one didn't seem to make 
>> it.
>>
>> I was reading the documentation:
>> - https://docs.sqlalchemy.org/en/13/core/tutorial.html#execute-multiple
>>
>> - 
>> https://docs.sqlalchemy.org/en/13/_modules/examples/performance/bulk_inserts.html
>>
>> Is there any difference between conn.execute(TableClause.insert(), [...]) 
>> vs conn.execute(Model.__table__.insert(), [...])?
>>
>> The first one is documented to use execumany(), but what about the second 
>> one? 
>>
>>
>> Any conn.execute() that passes a list of dictionaries as the second 
>> argument, where there is more than one entry in the list, will use the 
>> executemany() style with the DBAPI connection.
>>
>> With the ORM the Model.__table__ attribute is a Table object.  That 
>> tutorial seems to be referencing TableClause which is the base class for 
>> Table, but all the examples there are using Table objects.
>>
>>
>>
>> -- 
>> SQLAlchemy - 
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> --- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/1ffe48c6-4124-40ab-902f-ffa86885ea94n%40googlegroups.com
>>  
>> 
>> .
>>
>>
>>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/89002280-96e7-45e9-a11a-f104d8e2aa3fn%40googlegroups.com.

[sqlalchemy] Re: Dis/Reassociate objects with a db session.

['Jonathan Vanasco' via sqlalchemy]

Read the docs on State Management and pay attention to `merge`:

   https://docs.sqlalchemy.org/en/14/orm/session_state_management.html

Also, to simplify this stuff a popular related pattern is to use  a 
RevisionID or RevisionTimestamp on the objects.  In the first session, you 
note the version information. On the second session you fetch a new object 
and ensure it is the same - if so, your data is safe to update.  If not, 
the objects became out of-sync and may require more logic.



On Wednesday, November 25, 2020 at 12:57:23 PM UTC-5 jens.t...@gmail.com 
wrote:

> Hello,
>
> My question is regarding long-running tasks and db sessions. Currently I 
> have the very rare situation where a task takes longer than a db session is 
> valid and thus fails when it wants to write back results. Extending the TTL 
> of a db session is probably not a good idea.
>
> I think the proper approach would be to open a db session, fetch data, 
> close the db session, do work, open a new db session, write data, close the 
> db session. So, I must make sure that I fetch all data ahead of time while 
> the first session is active.
>
> Is there a way to re-associate objects that belonged to the first session 
> with a newly opened one? What’s the recommended approach here, does SQLA 
> have any magic in store to help me with very long-lived ORM objects across 
> db sessions? Or should I manage that data independently of their respective 
> ORM objects?
>
> Thanks!
> Jens
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/bf1d5c69-d500-4cac-bb29-026f1343a37bn%40googlegroups.com.

[sqlalchemy] Re: Zope.sqalchemy: AttributeError: '_thread._local' object has no attribute 'value'

['Jonathan Vanasco' via sqlalchemy]

Your new code is exactly what I have been running on several production 
systems, so it looks good to me!

Long story short, `zope.sqlalchemy` had been using the `sqlalchemy` 
"extensions", which were deprecated in 2012 and are set to be removed (if 
they haven't been already).  see 
https://github.com/zopefoundation/zope.sqlalchemy/issues/31

The change that caused your issues was due to `zope.sqlalchemy` migrating 
from the deprecated system to the next.  There wasn't a clean way of 
swapping this out, so their developers opted for a tiny breaking change.  
For most people, that means changing two lines of code; in some complex 
projects, 4 lines of code might need to be changed!

Unfortunately, someone posted that answer on StackOverflow that is 
incorrect and misdirected you – it's not a simple change in class names.  
I'm sorry that tripped you up.

On Sunday, October 25, 2020 at 9:23:36 AM UTC-4 dever...@gmail.com wrote:

> Thanks for the pointer to the source. My confusion came from the Zope docs 
> (and other sources e.g. this answer: 
> https://stackoverflow.com/a/58567212/123033 ) that seemed to suggest 
> *EITHER*
> from zope.sqlalchemy import ZopeTransactionExtension, register
> *OR*
> changing all instances of ZopeTransactionExtension to 
> ZopeTransactionEvents
> and using:
> from zope.sqlalchemy import register
> then
> DBSession = scoped_session(sessionmaker(**options))
> but the below - i.e. no parameters to sessionmaker() - got past the 
> errors in the end (so far so good, anyway):
>
> from zope.sqlalchemy import register
> # . . .
> DBSession = scoped_session(sessionmaker())
> register(DBSession)
>
> Feel free to point out anything glaringly obvious. I've not been in this 
> territory before, and it's a codebase in which I'm still finding my way 
> (written by a codev) and yes, I might not spot what's taken for granted by 
> anyone more familiar with SQLAlchemy etc. - I've often been in the reverse 
> situation!
>
> On Saturday, October 24, 2020 at 6:55:42 PM UTC+1 Jonathan Vanasco wrote:
>
>> The extract code you posted is incorrect.
>>
>> You were given a step towards the right answer - you MUST invoke 
>> `register`.
>>
>> I say a step, because there may be other factors going on.
>>
>> However as you can see from the source code (
>> https://github.com/zopefoundation/zope.sqlalchemy/blob/master/src/zope/sqlalchemy/datamanager.py#L293-L329),
>>  
>> the call to `register` is required because it invokes the 
>> ZopeTransactionExtenstion AND sets up the transaction events.
>>
>> On Saturday, October 24, 2020 at 10:47:27 AM UTC-4 dever...@gmail.com 
>> wrote:
>>
>>> I'm updating a working Pyramid app that uses sqlalchemy and have some 
>>> success by replacing ZopeTransactionExtension with ZopeTransactionEvents.
>>>
>>> On running initialise with my local .ini file, All goes well, the 
>>> database tables (MariaDB) are all written, but these errors occur:
>>>
>>> Traceback (most recent call last): 
>>> "[...]sqlalchemy/util/_collections.py", line 1055, in __call__ return 
>>> self.registry.value AttributeError: '_thread._local' object has no 
>>> attribute 'value' During handling of the above exception, another exception 
>>> occurred: 
>>>
>>> [cruft omitted]
>>> "[...]sqlalchemy/orm/deprecated_interfaces.py", line 367, in 
>>> _adapt_listener ls_meth = getattr(listener, meth) AttributeError: 
>>> 'ZopeTransactionEvents' object has no attribute 'after_commit'
>>>
>>> For more code details, I've posted extracts from the models and main 
>>> app code on StackOverflow , 
>>> but with no joy so far.
>>>
>>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/10c30fed-2898-4072-949d-a7011b454081n%40googlegroups.com.

[sqlalchemy] Re: Update multiple rows in SQLite Databse

['Jonathan Vanasco' via sqlalchemy]

I believe your error is tied to this section of code:
 

> for item in ingredDict:
> ingredient_item = Ingredients(ingredientKey=item['ingredientKey'], 
>  
> ingredientDescription=item['ingredientDescription'],
>  ingredientRecipeKey=recipeKey,
>  
> ingredientQuantity=item['ingredientQuantity'])
> Ingredients_item_object_list.append(ingredient_item)


It looks like you are iterating through this dict, creating new 
ingredients, and adding them to the recipe/database.

This is fine for CREATE, but is raising an integrity error on UPDATE 
because the ingredients already exist and you are creating a new entry on 
every iteration.

A lazy way to address this would be something like: remove all the existing 
ingredients, flush, then run this loop. 

A common way to handle this is the bit of Mike's suggestion which you 
missed: calculate the differences between the set of old and new items to 
determine which ingredients need to be added or removed (or updated, as 
that seems possible now).  Within the Unit of Work, as mike said, you need 
to delete and add (and also update it would seem).

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/36add23c-b1c5-4c6a-a494-d9d71addc1a8o%40googlegroups.com.

[sqlalchemy] Re: sqlalchemy messes up names with "_1" suffix

['Jonathan Vanasco' via sqlalchemy]

> i have this litte flask-admin game running, now out of nowwhere 
sqlalchemy has begun to add strange "_1" suffixes to the column names. i 
know sqlalchemy does this to keep names unique, but in my case the queries 
are failing

SQLAlchemy does do this, for those reasons, and to the columns... but note 
those exact error:


sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown 
column 'attribs_1.ts' in 'field list'")


It's not finding the `.ts` on the `attribs` table, which was mapped to 
`attribs_1` in the query.

I think the best thing do to is what mike said - create a complete 
executable example you can share. the model + the query.  My first guess is 
that you have a typo on the column/table name in the model or query.  There 
could also be an inheritance issue because of a typo too.


 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/c53dd18c-bc8a-42bd-819c-0b111e1a71a2o%40googlegroups.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

['Jonathan Vanasco' via sqlalchemy]

On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:
>
> I've done some more digging... It seems when I did the search for 
> "secrets", the text is encrypted and compared to the value in the columns, 
>

That is how client-side encryption works.  If you want to search for 
"secrets", you need to use server-side encryption (which depends on the 
database). In those systems, the server will decrypt the column in every 
row when searching - which can be a performance issue.

The thing is this type of comparison wont work, the algorithm generates a 
> different string each encryption for the same string.
>

 What are you using for your encryption key? The key should be persistent, 
and should always generate the same output for a given input.  In the 
example from Michael Bayer, a random uuid is used as a placeholder.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2506c7ef-7a66-4662-a40b-db6e70b93347o%40googlegroups.com.

Re: [sqlalchemy] convert subset to dictionary

['Jonathan Vanasco' via sqlalchemy]

Based on what you shared above:

* The "Subject" table is: `StudentId, SubjectCode, SubjectName`
* There are 181 subjects

It looks like you don't have a "Subject" table, but a "StudentAndSubject" 
table.

I think you'd have a bigger performance improvement by normalizing that 
data into two tables:

Subject:  SubjectId (primary key), SubjectCode, SubjectName
Student2Subject: StudentId, SubjectId, (primary key is both)

Assuming this can be done with your data... the database performance should 
improve because

1. The raw filestorage will decrease
2. The in-memory dataset size will decrease

You could then either

1. use the Subject table as part of a joined query to keep things simple, or
2. just select off a join of Student+Student2Subject , and query all the 
Subjects separately.  Even if there are 2000 subjects total, it should only 
take a few ms to get all that into a python datastructure that is used to 
generate your csv


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/37704b34-346d-4bf5-b0fa-c892b13d4e1eo%40googlegroups.com.

[sqlalchemy] Re: SQLAlchemy taking too much time to process the result

['Jonathan Vanasco' via sqlalchemy]

On Monday, July 6, 2020 at 2:14:33 PM UTC-4, Saylee M. wrote:
 

> So, when I passed the query to MySQL directly, it took very less time 
> (around 0.016 seconds) but when I passed the same 
> query through SQLAlchemy connector, it took around 600 seconds
>

"query ... MySQL directly"

Do you mean using the MySQL commandline client?  Assuming yes, the 0.016 
time only reflects the time MySQL spent processing the query and generating 
the result set; the SQLAlchemy time includes that + transferring all the 
data + generating Python data structures (which could be SQLAlchemy ORM 
models or generic python data structures)

There are also external factors that can account for time changes - like 
server load, index loading, cache utilization 

I am not sure what can be issue. It'll be great if I can get any pointers 
> to reduce the time, preferably under 10 seconds!
>

Showing a short, self contained, correct example (sscce) of your code would 
let others troubleshoot it more effectively.  The most likely situation 
though, is that you are loading all the rows.  There should be no 
difference in the query time.
 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/1b1d83d9-eb01-4541-962a-3d4a6551afb9o%40googlegroups.com.

[sqlalchemy] Re: Locking method used in SQLAlchemy (postgres)

['Jonathan Vanasco' via sqlalchemy]

On Monday, June 29, 2020 at 8:00:40 PM UTC-4, gbr wrote:
>
>
> I'm using SQLAlchemy's Core to interface a postgres database (via 
> psycopg2) component alongside Flask-SQLAlchemy in a Flask app. Everything 
> was working fine until I recently discovered what seems to be a deadlock 
> state which is caused by two queries locking each other (at least that's my 
> working hypothesis).
>

Beyond what Mike said... I don't use Flask but I use Pyramid and Twisted.

The only times I have experienced locking issues with SQLAlchemy:

* unit tests: the setup uses a first db connection, but it is erroneously 
implemented and not closed. when test runners begin, the db is locked so 
everything fails.

* application design issues: if you deploy a forking server and don't reset 
the pool on fork (`engine.dispose()`), all sorts of integrity and locking 
issues pop up (multiple processes try to use a single connection which 
never closes properly).  if you don't have a proper connection 
checkout/return that can happen too.

* very high concurrency: a pileup of connections want to lock for 
update/insert. inevitably, some timeout and deadlock.



-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/3981ffb7-611f-4a88-9058-a0e09ab60005o%40googlegroups.com.

[sqlalchemy] Re: Am I doing this query of lots of columns correctly?

['Jonathan Vanasco' via sqlalchemy]

that should be `loaded_columns_as_dict()` , unless you decorate the method 
with @property.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b912b1d6-8f17-4232-8b2b-1cebe8c90d9do%40googlegroups.com.

[sqlalchemy] Re: Am I doing this query of lots of columns correctly?

['Jonathan Vanasco' via sqlalchemy]

I use a mixin class to handle this stuff. Example below.

> So, my question: is it generally better practice to name every column 
that you want to pull, even if it's a long list? 
Not really.  There is a "bundle" api here that might be better for you- 
https://docs.sqlalchemy.org/en/13/orm/loading_columns.html

> Also, why does using just RecoveryLogEntries instead of naming each 
column yield a different result?
One is querying a "table", the other is querying a list of items. The 
queried items could be a "table", "table column", "subquery column" or 
several other things.

> It seems weird because in the SQL world, I could do a "SELECT *" or 
"SELECT id, ..." and the output is still in the same format regardless of 
whether I explicitly name name each column or use * to select all columns.
A lot of people approach SqlAclhemy like they are writing SQL. IMHO, a 
better approach is to remember that SqlAlchemy lets you write Python that 
generates Sql for multiple dialects.

> It just seems like it's a whole bunch of typing which could be 
error-prone.  I'll do it if I need to, but what I'm really asking is what 
is the most appropriate/accepted/standard way to do this.

The more standard ways are to expect/inspect the types of objects that are 
queried and act upon it.  The results are not text, but objects. If you 
inspect them, you can pull out the relevant information.

anyways, using a generic mixin (far below), I use the following code.  I 
also sometimes have methods on my objects to return json that only has 
specific fields (such as `as_json_v1`, `as_json_v2`, etc)


class RecoveryLogEntries(Base, UtilityObjectMixin):
# ... your class ...

# then...

results = session.query(RecoveryLogEntries).limit(record_count)
as_json = [r.loaded_columns_as_dict for r in results]



class UtilityObjectMixin(object):
 """see 
https://github.com/jvanasco/pyramid_sqlassist/blob/master/pyramid_sqlassist/objects.py#L127-L165"";


def columns_as_dict(self):
"""
Beware- this function will trigger a load of attributes if they 
have not been loaded yet.
"""
return dict((col.name, getattr(self, col.name))
for col
in sa_class_mapper(self.__class__).mapped_table.c
)


def loaded_columns_as_dict(self):
"""
This function will only return the loaded columns as a dict.
See Also: ``loaded_columns_as_list``
"""
_dict = self.__dict__
return {col.name: _dict[col.name]
for col in sa_class_mapper(self.__class__).mapped_table.c
if col.name in _dict
}


def loaded_columns_as_list(self, with_values=False):
"""
This function will only return the loaded columns as a list.
By default this returns a list of the keys(columns) only.
Passing in the argument `with_values=True` will return a list of 
key(column)/value tuples, which could be blessed into a dict.
See Also: ``loaded_columns_as_dict``
"""
_dict = self.__dict__
if with_values:
return [(col.name, _dict[col.name], )
for col in sa_class_mapper(self.__class__).mapped_table.
c
if col.name in _dict
]
return [col.name
for col in sa_class_mapper(self.__class__).mapped_table.c
if col.name in _dict
]

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/874a7299-9fa9-4be9-bb54-3aa186180269o%40googlegroups.com.

[sqlalchemy] Re: Can't find anything equivalent to bit_or in PostgreSQL

['Jonathan Vanasco' via sqlalchemy]

If this just needs this to be rendered for PostgreSQL, you can use the 
`func` generator:

https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.func

from sqlalchemy.sql.expression import func 

query = session.query(Foo).filter(func.bit_or(Foo.cola, Foo.colb)...


`func` is a special namespace and will render UNKNOWN functions as you 
invoke them.

This simple solution to render the correct sql works for most people.

If you have more specific needs, such as using this within python 
comparisons, you will need to read the docs on Custom types (
https://docs.sqlalchemy.org/en/13/core/custom_types.html); if you need this 
to work on multiple different databases you may have to write a custom 
compiler for each supported one (
https://docs.sqlalchemy.org/en/13/core/compiler.html 

)




-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/9263259a-8c8c-4fff-b915-86f6106665c4o%40googlegroups.com.