[sqlalchemy] Re: sqlacodegen --noclaases depracated?

[Jonathan Vanasco]

sqlacodegen is a third party tool. Your best option for support is on their 
Github page: 
https://github.com/agronholm/sqlacodegen/discussions/categories/q-a



On Monday, October 23, 2023 at 2:38:15 PM UTC-4 peter.dani...@gmail.com 
wrote:

> SQLAlchemy and sqlacodegen noob here. I'd like to just get some simple 
> SQLAlchemy tables generated for my database.  I tried using  --noclasses, 
> but it doesn't seem to recognize the option.
>
> sqlacodegen --noclasses mysql+pymysql://root:@localhost:3306/mydb
>
> sqlacodegen: error: unrecognized arguments: --noclasses
>
> How can I get it to just gen some tables?
>
> This is what I read in the project docs:
> "Unless the --noclasses option is used, sqlacodegen tries to generate 
> declarative model classes from each table."
> From: https://pypi.org/project/sqlacodegen/
>
> Thanks!
>
> -Peter
>

-- 
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/1410ad86-ab12-468b-86f4-e156ddeaeb0bn%40googlegroups.com.

[sqlalchemy] Re: session.close() is not wokring sqlalchemy i have tried diffrent diffrent way but it's not working i am trying sqlalchemy session in python but it's not working still getting session a

[Jonathan Vanasco]

That is working as intended.  `close()` just resets the session and 
connection, returning it to a connection pool to be used again.

https://docs.sqlalchemy.org/en/20/orm/session_basics.html#closing

The Session.close() 

 method issues a Session.expunge_all() 

 which removes all ORM-mapped objects from the session, and releases 
 any 
transactional/connection resources from the Engine 

 object(s) to which it is bound. When connections are returned to the 
connection pool, transactional state is rolled back as well.


When the Session 
 
is 
closed, it is essentially in the original state as when it was first 
constructed, and may be used again. In this sense, the Session.close() 

 method 
is more like a “reset” back to the clean state and not as much like a 
“database close” method.

It’s recommended that the scope of a Session 
 
be 
limited by a call to Session.close() 

 at 
the end, especially if the Session.commit() 

 or Session.rollback() 

 methods 
are not used. The Session 
 
may 
be used as a context manager to ensure that Session.close() 

 is 
called:

On Friday, July 7, 2023 at 3:22:47 AM UTC-4 react...@gmail.com wrote:

>
> from sqlalchemy import create_engine,text
> from sqlalchemy.orm import sessionmaker, declarative_base
> from datetime import datetime
> from sqlalchemy.orm import relationship
> from sqlalchemy import Column, ForeignKey, Integer, String, DateTime, Text, 
> Boolean
> from sqlalchemy.orm import scoped_session
> from sqlalchemy.pool import QueuePool
> import psycopg2
> import sqlalchemy
>
> # conn = psycopg2.connect(database="masterdb", 
> # user="kft_user", 
> # password="6dbvghdjhh78bs", 
> # host="
> db-primary.crszysz9bdut.ap-south-1.rds.amazonaws.com", 
> # port="5432")
> conn = psycopg2.connect("db_url")
> cursor = conn.cursor()
> SQLALCHEMY_DATABASE_URL = """db_url"""
>
> dbschema = 'kft_dev_db'
> engine = create_engine(SQLALCHEMY_DATABASE_URL, poolclass=QueuePool, 
> pool_size=5, max_overflow=10)
> connectionpool = engine.connect()
> con = engine.connect()
> Base = declarative_base()
>
> session_factory = sessionmaker(autocommit=False, autoflush=False,bind=
> engine)
> Session = scoped_session(session_factory)
> # print('Global Session', Session)
> #
> #models
> class User(Base):
> __tablename__ = "org_users"
> __table_args__ = {'schema': dbschema}
>
> org_user_id= Column(Integer, primary_key=True, index=True)
> org_role_id_ref=Column(Integer,ForeignKey(f'{dbschema}
> .org_roles.org_role_id', ondelete='CASCADE'))
> first_name = Column (String(128), nullable=False)
> last_name = Column(String(128), nullable=False)
> email = Column(String(128), nullable=False)
> auth_token = Column(Text, default=None)
> encrypted_password = Column(Text, nullable=False)
> mobile_number = Column(String(128))
> designation  = Column(String(128))
> is_verified = Column(Boolean, default=False)
> is_active = Column(Boolean, default=True)
> created_at = Column(DateTime, default=datetime.utcnow(), nullable=
> False)
> updated_at = Column(DateTime, default=None)
>
> roles = relationship('Roles', back_populates='user_reg')
> 
>
> class Roles(Base):
> __tablename__ = "org_roles"
> __table_args__={'schema': dbschema}
>
> org_role_id = Column(Integer, primary_key = True, index=True)
> role_name = Column(String(128), nullable=False)
> is_active = Column(Boolean, default=True)
> created_at = Column(DateTime, default=datetime.utcnow(), nullable=
> False)
> updated_at = Column(DateTime, default=None)
> description = Column(Text,default= None)
>
> user_reg = relationship('User',back_populates='roles')
>
> def get_user(user_id):
> print("get_user")
> user = Session.query(User).filter_by(org_user_id=user_id).first()
> print("Active Session", {'id': user.org_user_id, 'email': 

Re: [sqlalchemy] Re: Dealing with readonly column

[Jonathan Vanasco]

FWIW, I often use the events to ensure an object or column is "read only".  

Sometimes I also will use two different attributes, where `Object.foo` is a 
getter for `Object._foo` in the database.  The database will raise an error 
(hopefully) if I try to write to a 'protected column', and SQLAlchemy code 
is somewhat easy to audit to ensure I am only reading and never accessing 
the protected column.  I generally like to integrate that approach because 
I know something special is going on with the columns that have a leading _ 
and will immediately check the table's schema to remind myself.   


On Friday, April 14, 2023 at 4:18:05 PM UTC-4 Mike Bayer wrote:

>
>
> On Fri, Apr 14, 2023, at 3:02 PM, Lele Gaifax wrote:
> > "Mike Bayer"  writes:
> >
> >> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
> >>> I now have
> >>>
> >>> CREATE TABLE something (id SERIAL, name TEXT)
> >>>
> >>> CREATE FUNCTION counter(something)
> >>> RETURNS INTEGER AS $$
> >>> SELECT count(*) FROM something_else se
> >>> WHERE se.something_id = $1.id
> >>> $$ STABLE SQL
> >>>
> >>> and thus existing queries such as
> >>>
> >>> SELECT s.name, s.counter
> >>> FROM something AS s
> >>>
> >>> work as before.
> >>
> >> how does "SELECT s.counter" work if the table does not have an actual 
> "counter" column? 
> >
> > As explained here [1], "the field notation and functional notation are
> > equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM
> > t AS a" means the same, when "foo" is a function accepting the "implicit
> > composite type corresponding to a row in the table t".
>
> oh geez it's that silly PostgreSQL syntax. not a fan. you can use that but 
> you'd be on your own
>
> >
> >>
> >> Using Computed in this way is equivalent to using FetchedValue. Why is 
> that a problem? 
> >
> > No, it's not a problem, but the following
> >
> > stmt = something.update().values(counter=42).where(something.c.id=1)
> > connection.execute(stmt)
> > 
> > will raise an error at execution time, as will, assuming
> > "instance_of_something" is an instance of the ORM class mapped to the
> > table "something"
>
> So what then is a "read only column" if not one that raises an error if 
> someone tries to write to it explicitly?
>
> > instance_of_something.counter = 42
> > session.flush()
> >
> > I was just imagining something that could raise an error earlier.
>
> well you have the ORM validates and the before_cursor_execute approaches, 
> but I would think if this is simple developer level programming guards, the 
> PG exception is perfect
>
>
> >
> > Unfortunately in this case PG does not help: the closest thing is a
> > "GENERATED" column [2], but (up to version 15 at least) it "is a special
> > column that is always computed from other columns", it cannot be an
> > arbitrary subquery.
>
> trigger, then. im sure this is a thing PG can do
>
> >
> >> Without using server-side constructs, in a practical sense, simply
> >> omitting the column from the Table or using mapper.exclude_properties
> >> is in my experience sufficient.
> >
> > Uhm, how could I then select that value, to be returned by an API that
> > queries the "something" table?
>
> OK so the "readonly" you are looking for includes one where your 
> application actually needs to load it, then fine, dont exclude it from your 
> mappings.
>
>

-- 
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/4cf77276-32bf-4ba7-8510-83197384feden%40googlegroups.com.

[sqlalchemy] Re: trying to create a custome dialect did not find proper step guide

[Jonathan Vanasco]

There are no guides, because this is a very advanced topic.

If you are unable to code this based on the examples I shared above, you 
will have to find someone who can.




On Friday, April 7, 2023 at 11:41:30 PM UTC-4 sayakchak...@gmail.com wrote:

> Thank you sir for your response ,can you please guide me about how to 
> create a customs dialect because in sqlalchemy i find a topic on 3rd party 
> dialect but i dont fine and proper guide for it if you can please can you 
> provide me guide.
>
>
> On Friday, April 7, 2023 at 11:12:14 PM UTC+5:30 Jonathan Vanasco wrote:
>
>> Most custom dialects are written by forking an existing dialect.
>>
>> Unfortunately, a dialect can not accomplish what you want to do.
>>
>> SqlAlchemy Dialects are used to generate SQL, which is then passed to the 
>> database via a DBAPI driver.
>>
>> For example, when someone uses PostgreSQL with SQLAlchemy, they use the 
>> SQLAlchemy postgresql dialect AND their choice of a DBAPI driver (which 
>> could be any one of psycopg2, psycopg 3, pg8000, asyncpg, psycopg2cffi, etc)
>>
>> There are a few independent projects that developed REST solutions by 
>> writing both a dialect AND a DBAPI driver into a single package.
>>
>> Examples include:
>>  https://github.com/laughingman7743/PyAthena/
>>  https://github.com/betodealmeida/gsheets-db-api
>>  https://github.com/aadel/sqlalchemy-solr
>>
>> You will need to implement a solution similar to one of the above 
>> projects.
>>
>> If you look at the table of known dialects - 
>> https://docs.sqlalchemy.org/en/20/dialects/index.html - you may find 
>> other projects that implemented dialect+dbapi drivers in a single package.
>>
>> On Friday, April 7, 2023 at 1:03:19 AM UTC-4 sayakchak...@gmail.com 
>> wrote:
>>
>>> create a custom SQLAlchemy  dialect, called PCRRESTapiDialect for SQL 
>>> Alchemy that can call a rest api and return result as a table.
>>> i am trying to achive this but i dont find  any solution
>>
>>

-- 
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/07f14651-4a69-44d3-a50a-3ed4185fc170n%40googlegroups.com.

[sqlalchemy] Re: trying to create a custome dialect did not find proper step guide

[Jonathan Vanasco]

Most custom dialects are written by forking an existing dialect.

Unfortunately, a dialect can not accomplish what you want to do.

SqlAlchemy Dialects are used to generate SQL, which is then passed to the 
database via a DBAPI driver.

For example, when someone uses PostgreSQL with SQLAlchemy, they use the 
SQLAlchemy postgresql dialect AND their choice of a DBAPI driver (which 
could be any one of psycopg2, psycopg 3, pg8000, asyncpg, psycopg2cffi, etc)

There are a few independent projects that developed REST solutions by 
writing both a dialect AND a DBAPI driver into a single package.

Examples include:
 https://github.com/laughingman7743/PyAthena/
 https://github.com/betodealmeida/gsheets-db-api
 https://github.com/aadel/sqlalchemy-solr

You will need to implement a solution similar to one of the above projects.

If you look at the table of known dialects 
- https://docs.sqlalchemy.org/en/20/dialects/index.html - you may find 
other projects that implemented dialect+dbapi drivers in a single package.

On Friday, April 7, 2023 at 1:03:19 AM UTC-4 sayakchak...@gmail.com wrote:

> create a custom SQLAlchemy  dialect, called PCRRESTapiDialect for SQL 
> Alchemy that can call a rest api and return result as a table.
> i am trying to achive this but i dont find  any solution

-- 
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/c9390a59-d63f-4069-b9f9-989ac2c65399n%40googlegroups.com.

Re: [sqlalchemy] Re: Working with func in dates, No response, no error message

[Jonathan Vanasco]

> The only difference between my code and yours is that I am not using 
sessions. 

The Flask-Sqlalchemy package handles all the session stuff automatically in 
the background. The code you write is not interacting with the session 
explicitly, but it is utilizing the session implicitly.  

IMHO, the best way to troubleshoot these things is to create a standalone 
script like Lele Gaifax supplied for you above, and iterate fixes on that.

On Wednesday, April 5, 2023 at 8:12:52 AM UTC-4 nand...@gmail.com wrote:

> The only difference between my code and yours is that I am not using 
> sessions. Might it be the reason why my query is not working as expected? 
> Here is my query one more time:
> monthly_events = current_user.followed_events().filter(Event.event_date < 
> datetime.today().date()).filter(func.strftime('%m', Event.event_date) == 
> datetime.today().strftime('%m')).order_by(Event.timestamp.desc()) 
> Again, this is the problem the query is trying to solve. I want to know 
> the subscribed events (events that I am following) that took place in the 
> current month. This is irrespective of whether they took place 5 years ago, 
> or a year ago, so long as they took place in the current month.
>
> However, the query gives no events that took place in the current month, 
> yet I have events that indeed took place in April (the current month). The 
> current month is to be automated, which I have successfully accomplished 
> through *datetime.today().strftime('%m')*. 
>
> By breaking the query into sections, I was able to know where the problem 
> is. The section of the query: 
> *current_user.followed_events().filter(Event.event_date 
> < datetime.today().date())*gives all events that have passed (yesterday 
> and beyond). This part works correctly.
>
> The section: *current_user.followed_events().filter(Event.event_date < 
> datetime.today().date()).order_by(Event.timestamp.desc())*arranges these 
> past events in descending order and this section works correctly, as well.
>
> However, the part with problem is: .filter(func.strftime('%m', 
> Event.event_date) == datetime.today().strftime('%m'))where the aim is to 
> filter out events that took place in the current month, irrespective of the 
> year they took place.
>
> Note that I have imported the following modules from sqlalchemy import 
> funcand from datetime import datetimeat the top of the routes.py.
>
> The *event_date* field in the *models.py* is stored as a *db.DateTime* with 
> a *default = datetime.utcnow*. I am using Flask, with *SQLite* db, but 
> will change it to *Postgresql* later.
>
> I hope the information is enough, otherwise let me know if additional 
> information is needed.
> On Wed, Apr 5, 2023 at 10:21 AM James Paul Chibole  
> wrote:
>
>> Sorry, it is a type, it should actually be  .filter(func.strftime('%m', 
>> Event.event_date == datetime.today().strftime('%m')))
>>
>> Let me go through your latest response and will get back to you. Thank 
>> you for the prompt response.
>>
>> On Wed, Apr 5, 2023 at 10:16 AM Lele Gaifax  
>> wrote:
>>
>>> Nancy Andeyo  writes:
>>>
>>> > However, the part with problems is this one: 
>>> .filter(func.strftime('%m',
>>> > Event.event_date = datetime.today().strftime('%m'))) where the aim to
>>> > filter out events that will take place in the current month. This is 
>>> the
>>> > section that I posted, yet I needed to post the entire query for what 
>>> I am
>>> > intending to achieve is understood.
>>>
>>> I can't say if the typos are due to you rewriting the cide in these
>>> messages, or if instead they are effectively present in the real code,
>>> but also the above is not correct:
>>>
>>>   .filter(func.strftime('%m', Event.event_date = 
>>> datetime.today().strftime('%m')))
>>>
>>> This should raise a syntax error when evaluated by Python...
>>>
>>> For comparison, the following complete script works for me:
>>>
>>>   from datetime import date
>>>   from pprint import pprint
>>>
>>>   from sqlalchemy import create_engine
>>>   from sqlalchemy import func
>>>   from sqlalchemy.orm import Session
>>>
>>>   from sol.models import Tourney
>>>
>>>
>>>   engine = create_engine('sqlite:///development.db')
>>>   session = Session(engine)
>>>
>>>   q = session.query(Tourney)
>>>   q = q.filter(func.strftime('%m', Tourney.date) == 
>>> date.today().strftime('%m'))
>>>
>>>   pprint([(t.description, t.date) for t in q.limit(3).all()])
>>>
>>> and emits
>>>
>>>   [('2° Torneo', datetime.date(2001, 4, 1)),
>>>('7° Torneo', datetime.date(2004, 4, 24)),
>>>('7° Torneo', datetime.date(2005, 4, 30))]
>>>
>>> Hope this helps,
>>> ciao, lele.
>>> -- 
>>> nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
>>> real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
>>> le...@metapensiero.it  | -- Fortunato Depero, 1929.
>>>
>>> -- 
>>> SQLAlchemy - 
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post 

[sqlalchemy] Re: TLS 1.2

[Jonathan Vanasco]

Many users with similar experiences ultimately traced the issue to an 
outdated database driver.

I would try updating your driver.  If that does not work, please share the 
driver + version, and your connection string / code.

On Monday, March 27, 2023 at 2:39:21 PM UTC-4 pdb...@g.clemson.edu wrote:

> I am connecting to a MySQL db using sqlalchemy and TLS 1.2 is enforced 
> server side. I connect by setting my URI to the correct connection string 
> and adding the ?ssl=true flag at the end. However I am getting an error 
> that the TLS version does not meet the minimum requirements of the server. 
> My question is how to force sqlalchemy to connect using TLS 1.2?
>
> App env: Python 3.6 using Flask 
>

-- 
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/3478f483-e238-4725-9b01-0b0256f59504n%40googlegroups.com.

Re: [sqlalchemy] Re: testing patterns with sqlalchemy 2.0

[Jonathan Vanasco]

> When taking this approach, how do you ensure the accumulated schema
migrations end up with a database that matches the one that create_all
gives you?

Sadly, I don't.  I've actually been working on a test to ensure every table 
and column in the model exists in the database!

> Creating a database for every unit test feels like something that would
be slow enough to be annoying. How are you creating databases such that
it's fast enough for this not to be the case?

It depends on the project, context and test.  That's why we use env vars to 
control it.

Using a sql dump into postgres is pretty fast, but sqlite is not.
I have some tests that note which tables are required, and just build those 
tables.
Tests are designed to work on a populated existing database as well, so 
they don't need to be empty.
It's definitely not a one-size fits all concept for me.  I have some 
projects with under 20 tables, but some legacy applications with hundreds 
of tables.  

On Friday, September 2, 2022 at 2:47:05 AM UTC-4 Chris Withers wrote:

> On 01/09/2022 20:00, Jonathan Vanasco wrote:
> > 
> > > Create an empty schema from the models using create_all?
> > 
> > This is what I usually do with smaller projects. 
>
> When taking this approach, how do you ensure the accumulated schema 
> migrations end up with a database that matches the one that create_all 
> gives you?
>
> > When using unittest, some tests will use a fresh DB per test-run, others 
> > per-class, and others per-test. 
>
> Yeah, pytest fixture scoping gives a great way to set these up.
>
> > Sometimes the tests dictate that, other 
> > times I control that with env vars.  That gives us the flexibility to 
> > work on a small section and do efficient test runs during development.
>
> Creating a database for every unit test feels like something that would 
> be slow enough to be annoying. How are you creating databases such that 
> it's fast enough for this not to be the case?
>
> Chris
>

-- 
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/6fca8254-421d-4ad2-a7e3-24df42c6a7b6n%40googlegroups.com.

[sqlalchemy] Re: testing patterns with sqlalchemy 2.0

[Jonathan Vanasco]

> Create an empty schema from the models using create_all? 

This is what I usually do with smaller projects.  On some large legacy 
projects, I use a database dump that is loaded into Postgres - as they 
often rely on a lot of records that need to be in the database and 
generating them via SqlAlchemy is so much slower and would be a pain to 
develop.

When using unittest, some tests will use a fresh DB per test-run, others 
per-class, and others per-test.  Sometimes the tests dictate that, other 
times I control that with env vars.  That gives us the flexibility to work 
on a small section and do efficient test runs during development.

On Wednesday, August 31, 2022 at 6:02:29 PM UTC-4 Chris Withers wrote:

> Hi All,
>
> Are there any libraries (or anything in sqlalchemy itself!) that cover 
> the pattern of running unit tests in against a database such that each 
> test gets its own sterile environment in which to run? Postgres, if it 
> helps. I've done some stuff with running in a subtransaction and rolling 
> back at the end of the test before, but wanted to see if anything had 
> become common enough to end up in a popular library yet...
>
> What's the recommended way of getting a blank db to test against? Run 
> alembic migrations in a session-level fixture? Create an empty schema 
> from the models using create_all? Something else?
>
> cheers,
>
> Chris
>

-- 
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/dc72a5f3-794f-43e7-b3aa-f0a2ec2a9028n%40googlegroups.com.

[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: [Proposal] Some simple high-level feature to set connection timeouts

[Jonathan Vanasco]

SQLAlchemy supports connection timeouts to establish a connection already.

SQLAlchemy does not, and can not, support query timeouts.  This is possible 
with some python database drivers, but very rare.  In every Python database 
program/library query timeouts are typically handled on the database 
server, and almost never on Python.  You can use SQLAlchemy's engine events 
to emit sql that will set/clear a timeout on the database server.

This has come up many times in the past, and there are several threads in 
the group history that explain the details and offer solutions.

On Monday, January 10, 2022 at 4:52:49 AM UTC-5 donnill...@gmail.com wrote:

> Hi!
> Sorry for bothering, I have not enough skills to contribute yet (
> But...
> I think it would be great to have some feature to easily set connection 
> timeouts in SQLAlchemy 2.0.
> The reason is I faced a problem where I was unable to cancel 
> some erroneous time-consuming queries with SQLAlchemy Core. And I guess, I 
> am not the only one.
> My straightforward nooby solution so far is this:
>
> from threading import Timer
>
> with engine.connect() as connection:
>   timeout = Timer(MAX_EXECUTION_TIME, lambda: 
> connection.connection.connection.cancel())
>   timeout.start()
>   r = connection.execute(stmt).freeze() # I just love FrozenResult)
>   timeout.cancel()
>
> The bad thing this is dialect-specific and works only due to cancel() 
> method in psycopg2
> I was also trying to benefit from handling sqlalchemy.events but failed...
> One of my intentions was to modify before_execute() method to catch 
> unintended cartesian product queries and raise error instead of throwing 
> warning.
> Unfortunately, at the moment this feels like too low-level for me.
>
> What I wish to have is something like this:
>
> with engine.connect(timeout=MAX_EXECUTION_TIME) as connection:
>   r = connection.execute(stmt)
>
> I hope somebody smart enough could seize time to think about it.
> This would make me happy. 
>
> Thanks in advance!
>
>
>
> [image: thanks-in-advance.png]
>

-- 
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/82b228ef-338f-4876-9cc4-4eb998dc04fan%40googlegroups.com.

Re: [sqlalchemy] [Question] Why not passing Connection URL query parameters to the dialect?

[Jonathan Vanasco]

> Ok. So if I understand you correctly, you want to keep query parameters 
solely for DBAPI drivers connection parameters and would hence not accept a 
PR that would implement something that changes that.

Just adding: the standard across programming languages and database 
products/projects is to utilize a database connection url as SQLAlchemy 
currently implements.  departing from this concept could create too many 
compatibility issues in the future, and has the potential for breaking 
existing integrations today.
On Friday, January 7, 2022 at 11:11:49 AM UTC-5 Mike Bayer wrote:

> also if you really want your app to have just one URL with all kinds of 
> config in it, then just use that.  get the URL object using the make_url() 
> API, pull out the configuration you need from URL.query, make a new URL 
> from that one that is for your database, then connect.it's all public 
> API that's there:
>
>
> https://docs.sqlalchemy.org/en/14/core/engines.html?highlight=url#sqlalchemy.engine.URL
>
> as far as specific dialects, these tend not to have too much in the way of 
> dialect-specific options that are not part of what's needed to connect to 
> the DBAPI.  SQLAlchemy's dialects have a few which usually refers to some 
> SQL generation behaviors, but most of these are themselves derived from 
> inspecting the database itself, after the connection has been established.
>
>
>
> On Fri, Jan 7, 2022, at 11:00 AM, Mike Bayer wrote:
>
> the idea of Table objects being linked to a database is something I 
> thought was a good idea in 2006, which is why for the last 15 years there's 
> been this notion of "bound metadata" that associates a specific engine with 
> Table objects.   however, probably by 2009 if not earlier, the limited and 
> misleading nature of this idea was pretty apparent not the least of which 
> because modern applications quite often need a certain Table object to 
> apply to lots of different databases, different kinds of databases, etc, 
> and then people were trying to hack around "bound metadata" not doing any 
> of these things, even though by then "bound metadata" was fully optional.  
> but since the pattern was there, people were confused, "bound metadata" was 
> present, why aren't we using it then, why is it there, etc.
>
> Here we are and when SQLAlchemy 2.0 betas are released hopefully in a few 
> months you'll see the notion of linking a Table directly to anything 
> regarding anything to do with locating a specific database is gone.
>
> If your Table has things to do with it that you need to know when you 
> execute queries, which are invariant no matter what the database URL is, 
> you can put those things in the table.info dictionary.
>
> Overall, URLs are meant to refer to "where is a particular database, get 
> me in" and that's it.  things that are *in* the database, tuning 
> parameters, etc. that all goes in config.   An app will usually have config 
> that is more than just a single URL argument.
>
> On Fri, Jan 7, 2022, at 5:26 AM, nicolas...@jobteaser.com wrote:
>
> Hi !
>
> Ok. So if I understand you correctly, you want to keep query parameters 
> solely for DBAPI drivers connection parameters and would hence not accept a 
> PR that would implement something that changes that.
>
> There are other reasons though for which I was looking into this. In 
> particular, what I am mentioning is already sort of done by PyAthena. They 
> use at least two query parameters that help tell where the data is stored.
> One (`s3_staging_prefix`) tells where query results are stored and fits 
> nicely amongst the connection parameters.
> The second (`s3_prefix`) is used to tall where data should be stored when 
> a table is created and does not fit so well.
>
> It does not fit because you end-up relying on SchemaItem to be bound to a 
> connection to get back those parameters 
> ,
>  
> but in many case this binding is not done.
> In particular DDL statements compilation just blows in your face. A 
> statement like:
>
>   Table('name', MetaData(), Column('c', Integer)).create(bind=engine)
>
> Fails with:
>
>   File "~/pyathena/sqlalchemy_athena.py", line 313, in 
> post_create_table
> raw_connection = table.bind.raw_connection()
>  AttributeError: 'NoneType' object has no attribute 'raw_connection'
>  Table('name', MetaData(), Column('c', 
> Integer)).create(bind=engine)
>
> I guess the storage location of a table does fit in the table dialect 
> kwargs:
>
> Table('', MetaData(), ..., awsathena_location='s3://...')
>
> Initially I thought it could be useful, e.g. when building ETL pipelines 
> that moves data around, to be able to bind a table with the actual storage 
> location as late as possible (to reuse a Table object).
>
> But generally other bits in the table definition needs to change too, like 
> the name of the 

[sqlalchemy] Re: SQLAlchemy with postgres: crash sqlalchemy connection with thread parallelism,

[Jonathan Vanasco]

Please submit a "Short, Self Contained, Correct (Compilable), Example" 
along with any potential bug reports.

http://sscce.org/
On Wednesday, December 15, 2021 at 11:29:30 AM UTC-5 Ramin Farajpour Cami 
wrote:

> Hi, 
>
> I'm was testing the project by fastapi + sqlalchemy, i write golang code 
> for sending thread parallelism to the my API endpoint, I found a problem, 
> in the sqlalchemy many requests fail, 
>
> this is my config : 
>
> SQLALCHEMY_DATABASE_URL = config("DATABASE_URL")
> engine = create_engine(SQLALCHEMY_DATABASE_URL)
> SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
>
> I took a video of this problem,
>
>
>
> https://drive.google.com/file/d/1rgd_GBwRQHmiv-C-26luFfESKIByQP4U/view?usp=sharing
>
>
> We do not know exactly how to solve the problem. sqlalchemy must cover 
> many requests connection.
>
> Thanks,
> Ramin
>
>

-- 
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/9c629ccc-66fc-481a-989f-f45689c4056dn%40googlegroups.com.

Re: [sqlalchemy] Dialect-specific dispatch in user code

[Jonathan Vanasco]

Adding that on top of Mike's approach, you may also want to create some 
custom functions via the @compiles decorator:

https://docs.sqlalchemy.org/en/14/core/compiler.html

This would allow you to further customize the SQL emitted against Postgres 
vs SQLite as needed.For example, I have some examples dealing with the 
differences in date operations between those two backends in this file 
- https://github.com/aptise/peter_sslers/blob/main/peter_sslers/model/utils.py

On Tuesday, October 19, 2021 at 5:20:13 PM UTC-4 Mike Bayer wrote:

>
>
> On Tue, Oct 19, 2021, at 1:41 PM, Jonathan Brandmeyer wrote:
>
> We're supporting both Postgres and SQLite in our application.  For the 
> most part, sticking close to ANSI has made this pretty seamless.  However, 
> there have been occasions where we want to write either DDL or DML in a 
> dialect-specific way.
>
> For column data, we want to enable the use of JSON.  However, when on 
> Postgres we'd like to use JSONB, but when on SQLite we'd use their JSON1 
> extension.  The generic JSON type provided by sqlalchemy defaults to the 
> postgres JSON type when on postgres.  How can we get it to default to JSONB 
> instead?  Using dialect-specific column types in the mapper is a 
> non-starter, because a mapped class (or Core table) may have to work with 
> both a Postgres connection and an SQLite connection in the same program.  
> We almost want to follow[1], except that I'm concerned that some of the 
> query syntax renderer might also be affected by switching to JSONB.
>
>
> use with_variant:
>
> Column("data", JSON().with_variant(postgresql.JSONB(), "postgresql"))
>
>
> https://docs.sqlalchemy.org/en/14/core/type_api.html?highlight=with_variant#sqlalchemy.types.TypeEngine.with_variant
>
>
>
> For DML there are a few cases where we'd like to use the 
> on_conflict_do_nothing syntax.  However, it isn't available as generic 
> syntax, only dialect-specific syntax.  It's not clear how query-generating 
> code can figure out which syntax to use given only a connection. 
>
>
> this was just asked yesterday and basically you can dispatch on 
> connection.engine.name for different database backends.   Simple + more 
> generalized decorator approach illustrated at 
> https://github.com/sqlalchemy/sqlalchemy/discussions/7199#discussioncomment-1495790
>
>
>
> SQA has some support for adding text() to larger queries, but this is a 
> modifier that doesn't clearly fit with the other generative methods that 
> accept text arguments.  Is there a way to hack on some extra text into the 
> _post_values_clause that will be supported into the future?
>
>
> not sure what you're looking to do here.
>
>
>
>
>
> Sincerely,
> -- 
> Jonathan Brandmeyer
> PlanetiQ
>
>
> -- 
> 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/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.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/c4edb89d-6e33-4bae-98f5-1d0abf5e4217n%40googlegroups.com.

[sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

[Jonathan Vanasco]

I'm not sure, but AFAIK, this type of search isn't *easily* doable in 
PostgreSQL. The json and jsonb operators and functions are really targeting 
"object literals" style data, not lists. 

https://www.postgresql.org/docs/current/functions-json.html

In the past, I think one could search against the column like text and 
match/regex out a list value like `"user1"` - but that didn't work right.

This type of search is possible with advanced PostgreSQL queries, by using 
the functions like json_array_elements on a field and joining against that. 
That's really not within the scope of SQLAlchemy or this list though, and 
you'll have better luck search (or asking) on Stack Overflow.  There are a 
handful of questions and solutions there on this topic.

Once you can figure out the PostgreSQL queries to accomplish what you want, 
this list can help you convert it to SQLAlchemy if you have trouble.

On Wednesday, October 13, 2021 at 9:50:16 AM UTC-4 chat...@gmail.com wrote:

> Imagine a Postgres JSON column with values like below:
> "["user1", "user2"]" 
>
> Is there any way to query a postgres JSON (not JSONB) column with 
> SqlAlchemy,like above that checks if the value "user1" is contained in this 
> column?
>

-- 
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/8f1986f6-4a39-4cad-93f2-a8d1c392b4b2n%40googlegroups.com.

[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 <https://stackoverflow.com/q/64486574/123033>, 
>>> 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: Zope.sqalchemy: AttributeError: '_thread._local' object has no attribute 'value'

[Jonathan Vanasco]

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/1de6b5a3-4ca0-489a-abf6-4f4c582d6cc9n%40googlegroups.com.

[sqlalchemy] Re: migrating a child object from one parent to another

[Jonathan Vanasco]

I totally missed the `AcmeAccountKey.is_active.is_(True)` on the 
relationship. I set it as viewonly and crisis solved.

-- 
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/d803347f-062e-41e9-b5ec-443aa195c2ben%40googlegroups.com.

[sqlalchemy] migrating a child object from one parent to another

[Jonathan Vanasco]

I am having an issue with migrating an instance child relationship from one 
object to another.

I have the following two classes:

class AcmeAccount(Base):
__tablename__ = "acme_account"
id = sa.Column(sa.Integer, primary_key=True)
account_url = sa.Column(sa.Unicode(255), nullable=True, unique=True)
acme_account_key = sa_orm_relationship(
"AcmeAccountKey",

primaryjoin="and_(AcmeAccount.id==AcmeAccountKey.acme_account_id, 
AcmeAccountKey.is_active.is_(True))",
uselist=False,
)
acme_account_keys_all = sa_orm_relationship(
"AcmeAccountKey",
primaryjoin="AcmeAccount.id==AcmeAccountKey.acme_account_id",
uselist=True,
)
class AcmeAccountKey(Base):
__tablename__ = "acme_account_key"
id = sa.Column(sa.Integer, primary_key=True)
acme_account_id = sa.Column(
sa.Integer, sa.ForeignKey("acme_account.id"), nullable=False
)
is_active = sa.Column(sa.Boolean, nullable=False, default=True)
acme_account = sa_orm_relationship(
"AcmeAccount",
primaryjoin="AcmeAccountKey.acme_account_id==AcmeAccount.id",
uselist=False,
)

Occasionally a "Duplicate" AcmeAccount is detected when syncing against the 
upsteam ACME server.

I need to merge the two accounts into the earlier account...

accountOriginal = dbSession.query(AcmeAccount)...
accountDuplicate = dbSession.query(AcmeAccount)...

with dbSession.no_autoflush:
# update unique url; no problem   
accountOriginal.account_url = accountDuplicate.account_url
accountDuplicate.account_url = None
dbSession.flush()

# unset active key on original; no problem
if accountOriginal.acme_account_key:
accountOriginal.acme_account_key.is_active = None
dbSession.flush()

# migrate duplicate key to original; many problems
accountkeyMigrate = accountDuplicate.acme_account_key

# PROBLEMS HERE, one (of many) attempts was something like
accountkeyMigrate.acme_account_id == accountOriginal.id
accountOriginal.acme_account_key == accountkeyMigrate

# eventually
dbSession.delete(accountDuplicate)
dbSession.flush()

I have tried addressing the relationship, the columns, both, explicit calls 
to flush() and a few settings on the relationship itself

I keep running into problems where the ORM will set the `acme_account_id` 
of one of the AcmeAccountKeys to None, which violates the not-null 
constraint and raises an exception.

I know that I am overthinking this situation, i've just never had to 
migrate an object in the ORM like this before.

Can anyone point me in the right direction?





-- 
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/27642b73-27e4-4e7d-addd-6dee4d155fean%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: can't connect to Teradata - "The UserId, Password or Account is invalid"

[Jonathan Vanasco]

You will have better luck asking for help from the people who 
write/maintain the Teradata dialect.  They list a gitter room here: 
https://github.com/Teradata/sqlalchemy-teradata

According to a posting on gitter (
https://gitter.im/sandan/sqlalchemy-teradata), it looks like that package 
is being deprecated in favor of an official client 
https://pypi.org/project/teradatasqlalchemy/


-- 
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/febda084-029c-460a-83ce-68243bc6953ao%40googlegroups.com.

[sqlalchemy] Re: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) ([Errno 13] Permission denied)")

[Jonathan Vanasco]

> using the same user and am able to connect from python terminal from the 
same linux box but it doesnt work using the python code.

When this works in the terminal, how are you connecting?  In Python via the 
database driver, or using the mysql client?

If it's the mysql client, check the server's mysql and system/auth logs.  
The mysql instance might not be configured to allow this type of 
connection/authentication from the user.

-- 
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/d91a42f0-80e8-4431-8882-eaeb29d7ffc9o%40googlegroups.com.

[sqlalchemy] Re: compatibility between SQLite and PostgreSQL

[Jonathan Vanasco]

On Friday, August 14, 2020 at 10:00:46 AM UTC-4, Alceu Rodrigues de Freitas 
Junior wrote:
 

> I have some projects that are using SQLAlchemy and PostgreSQL and they are 
> running fine until we have to run automated tests that uses PostgreSQL and 
> they took ~5 minutes to complete, even after trying to improve PostgreSQL 
> configuration for better performance.
>
> Those tests are meant to be executed locally, so SQLite (in memory) would 
> be a much faster option.
>

I usually target PostgreSQL production and continuous-integration, and 
SQLite for developer testing and unit-tests..  There are some tradeoffs on 
leveraging SQLlite in-memory vs file-based regarding: locking, concurrency, 
and per-test setups.  In my experience, a file-backed sqlite is better on 
higher-level tests and memory-backed is otherwise fine..


The problem is, creating a model for the SQLite dialect results in 
> something incompatible with PostgreSQL (I'm quite aware that their inner 
> mechanisms are very different).
>
> Is it possible to generate models that could be compatible between the two 
> databases systems, so I could switch from to another depending on the 
> environment? Retaining data is not something required since all data 
> created from the tests will be disposable anyway.
>

Exactly what issues are you having, and why do you need to create a 
different model?  You should be able to use the same model for both.

When it comes to managing core differences between the databases - like the 
very different database functions --  I use the @compiles decorator to 
write custom functions that will compile differently on each backend.

What you're talking about is a very common usage of SqlAlchemy. Please 
share more details so we can help you get up and running.

-- 
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/e0e326d1-fcf9-47c0-a71f-d23fd8401b55o%40googlegroups.com.

[sqlalchemy] Re: Deletion of a row from an association table

[Jonathan Vanasco]

Thanks. IIRC, I think you just need to set a custom cascade on these 
relationships (see https://docs.sqlalchemy.org/en/13/orm/cascades.html)

I am not sure which option that would be, because it sounds like your 
application is behaving with a "delete-orphan", but that's not set.

-- 
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/ba415856-5c1b-4d8e-b72a-eac9c5d90f98o%40googlegroups.com.

[sqlalchemy] Re: Deletion of a row from an association table

[Jonathan Vanasco]

Can you share the full model for these 3 classes, which includes the 
relationship declarations?

-- 
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/f26d4470-6483-4d97-89d1-d11b0670b560o%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.

Re: [sqlalchemy] forcing (composite) primary key order?

[Jonathan Vanasco]

thanks mike!

On Monday, June 1, 2020 at 7:15:23 PM UTC-4, Mike Bayer wrote:
>
> yes use the PrimaryKeyConstraint() construct
>
>
>
> https://docs.sqlalchemy.org/en/13/core/constraints.html?highlight=primarykeyconstraint#sqlalchemy.schema.PrimaryKeyConstraint
>
> here you'd want to put it in your __table_args__ and remove 
> primary_key=True from each column
>
>

-- 
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/c2bf33e3-a43f-4829-8bb0-4135fd961a61%40googlegroups.com.

[sqlalchemy] forcing (composite) primary key order?

[Jonathan Vanasco]

is it possible to force the order of primary keys?

for example in this setup...

class AcmeDnsServer2Domain(Base):
__tablename__ = "acme_dns_server_2_domain"
acme_dns_server_id = sa.Column(
sa.Integer, sa.ForeignKey("acme_dns_server.id"), primary_key=True
)
domain_id = sa.Column(
sa.Integer, sa.ForeignKey("domain.id"), primary_key=True
)



I want to influence the creation of the primary key to be 
["acme_dns_server_id", 
"domain_id"]

In several projects I've worked on, the order has been important.  (it can 
affect query planning and caching)

I thought I had seen something in the docs several years ago, but I just 
spend a few fruitless minutes trying to find 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/6728fb39-f5d9-4c97-a8b3-7df7fd918efa%40googlegroups.com.

[sqlalchemy] Re: Presenting a higher-level view of a physical table to the end-user

[Jonathan Vanasco]

How will the end-users be querying?  Are they going to be consumers who are 
submitting params to a form, or are they going to be developers using 
Python/SqlAlchemy?

-- 
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/357ae2f9-4a82-4214-8986-2c0e9ab64bea%40googlegroups.com.

Re: [sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

If I had time to respond yesterday, I would have said the same thing as 
Simon.

Your database model leverages two separate parts of SQLAlchemy:

* SqlAlchemy ORM (Left side of the docs https://docs.sqlalchemy.org/en/13/ )
* SqlAlchemy Core (Right side of the docs https://docs.sqlalchemy.org/en/13/
 )

There is nothing wrong with that setup; the two are often used together and 
the docs recommend that in many situations!

However... the way you use and want to query the relationship table is more 
suited to redefining the permissions tables from SQLAlchemy Core objects 
(which are created by invoking `db.Table()`) into SQLAlchemy ORM classes 
that inherit from `db.model`.  

It is possible to keep these objects in "Core" and query them as-is, but 
that will have some tradeoffs:

* your application code will mix ORM and Core, which can cause some 
maintenance headaches
* changes to ORM and Core are independent of each other and may cause 
issues like race conditions if they overlap. For example, changes made to 
the database via Core would not necessarily appear to ORM 
objects/relationships if they are already loaded. 

There is no right way or wrong way here. Given your familiarity with this 
library though, I personally suggest keeping everything in the ORM.

Going to the ORM docs, your setup right now is roughly in line with a "Many 
to Many" setup that leverages an `association_table` (
https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#many-to-many)

However, your association_table is more than just primary keys joining the 
two sides of the relationship - it has other fields - and you want to be 
querying it directly.  That is more in line with the "Association Object" 
pattern (
https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object
)

Altering your model to implementing the Association Object pattern is 
pretty straightforward and should be easy to do based on the examples in 
the docs. That should give you the flexibility you need.


-- 
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/9121424d-8298-4018-9e95-1d4e312604e2%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

What is the code for PermissionEntity, ContractEntity, and the joining 
table?

it will look like this 
https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-many

-- 
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/ff5bec4d-7dec-4c49-a802-8ac3643f9af4%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

Sorry, I meant the SqlAlchemy schema.  I can't attempt to troubleshoot code 
that I can't see.

-- 
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/3b37b465-4245-4522-8191-f7a6a899ad7c%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

can you share your schema for these 3 tables?

-- 
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/f12ee526-1da2-4431-8453-1cd28a6dae03%40googlegroups.com.

[sqlalchemy] Re: how can i remove an entry from relational database using sqlalchemy in python

[Jonathan Vanasco]

`.get()` returns the corresponding row/object based on the primary key 
https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=get#sqlalchemy.orm.query.Query.get

assuming `PermissionEntity` has a primary key of (permission_id, 
contact_id), the syntax from the examples would be:

some_object = session.query(VersionedFoo).get((5, 10))

or

my_object = query.get((5, 10))



If you have another primary key, you'd have to filter:


some_object = 
session.query(PermissionEntity).filter(PermissionEntity.permission_id==2, 
PermissionEntity.contract_id==2).first()


or

some_object = session.query(PermissionEntity).filter_by(permission_id==2, 
contract_id==2).first()




On Thursday, May 28, 2020 at 11:15:38 AM UTC-4, Tanjil Hussain wrote:
>
> [image: Capture.PNG]
>
> permission_id and contract_id have a relationship in the database
>
> How can i using remove a entry for example.. if permission_id = 2 and 
> contract_id = 2 exists in the same entry as shown on line one in database, 
> i want to be able to remove it from my database. (This entry is unique so 
> can only appear once)
>
> I have tried PermissionEntity.query.get(contract_id) and 
> PermissionEntity.query.get(permission_id) but doesnt seem to be working 
> as Its not stored in a permission entity.. My relationship does not have an 
> entity. the table i have provided a picture for has a relationship with 
> permissions table and contracts table..
>
>

-- 
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/f095a379-1e2e-42c3-89b1-d739de014086%40googlegroups.com.

[sqlalchemy] Re: custom encryption for db columns/fields

[Jonathan Vanasco]

That would be purely PostgreSQL. You can look on StackOverflow for answers. 
Newer versions of postgresql also have a crypto library that can be 
compiled into the server, which may help. 
https://www.postgresql.org/docs/current/pgcrypto.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/3d2b09be-0e7c-4e11-bbe9-baee61f354ea%40googlegroups.com.

[sqlalchemy] Re: ImportError: No module named 'cx_Oracle' - Python3.6

[Jonathan Vanasco]

On Thursday, May 21, 2020 at 5:53:57 PM UTC-4, Thirsty ForKnowledge wrote:
>
> Hi,
>
> I am having an issue where a flask application is crashing with a 500 
> Error. I upgraded from python 3.5 to 3.6 on linux:
>

 
When you upgrade Python, you need to (re)install all of the packages.   

Most likely, cx_Oracle was probably not listed as a dependency in your 
application, so it didn't install.

You can test this by opening a new Python interpreter for this virtual env 
and typing "import cx_Oracle". if it errors out, it is not installed.

Installing cx_Oracle into your 3.6 virtual environment should fix this.

-- 
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/f24cc61a-80fc-4386-807a-2412ce25997b%40googlegroups.com.

[sqlalchemy] Re: I have to create custom type in sqlAlchemy

[Jonathan Vanasco]

There is a section in the documentation titled "CustomTypes"

The following example shows how to encode and decode JSON:

https://docs.sqlalchemy.org/en/13/core/custom_types.html#marshal-json-strings

Please be advised: If you store data in your own custom format, as you 
described, it will be difficult to query.

-- 
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/305cbea9-ab72-45bd-9412-3e42feb68315%40googlegroups.com.

[sqlalchemy] Re: custom encryption for db columns/fields

[Jonathan Vanasco]

> On Tuesday, 19 May 2020 19:24:48 UTC+2, Justvuur wrote:
>>
>> Is it possible to create a custom encryption/decryption algorithm that 
>> can be used with sqlalchemy querying/filtering?
>> When querying and filtering, I would like the field to automatically 
>> decrypt using the custom algorithm.
>>
>
You can create a SqlAlchemy CustomType that will encrypt/decrypt values to 
the database.(https://docs.sqlalchemy.org/en/13/core/custom_types.html)

There is an example of this when dealing with JSON:

https://docs.sqlalchemy.org/en/13/core/custom_types.html#marshal-json-strings


In order to query/filter the encrypted values, you will have to extend 
PostgreSQL to perform the encryption, decryption, and querying of that data.

-- 
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/0dfba5fd-bdfd-4eee-9877-7650c0d8ee22%40googlegroups.com.

[sqlalchemy] is it possible to implement this anti-pattern in SQLAlchemy?

[Jonathan Vanasco]

Ok never mind! 

I realized I could scrap this entire functionality and replace it with 
something else.

The use-case was trying to detect the backup renewal options for SSL 
Certificates if the private key Or account key is revoked. (foo is an ACME 
order if available, bar is the certificate).  Instead of automatically fixing 
these, I’m just going to log them as “needs triage” and let each one be managed 
individually.

-- 
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/c6353ed2-cd8f-43ab-8b1e-eec137265995%40googlegroups.com.

Re: [sqlalchemy] Passing reference to model to model column_property function

[Jonathan Vanasco]

It’s been a while since I’ve worked on stuff like this, but IIRC the simplest 
way was to use a function that accepts an ID and to flush in SqlAlchemy before 
executing it. Then you select the necessary row fields within the sql function, 
instead of passing args in or trying to pass a row in. 

In my experience, when you pass multiple args in, the function becomes very 
fragile and prone to break as your model changes.

-- 
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/d9a59130-e7f8-4c24-96ad-c0e8bb255bc3%40googlegroups.com.

[sqlalchemy] is it possible to implement this anti-pattern in SQLAlchemy?

[Jonathan Vanasco]

I have two classes where one f-keys onto another.

Things work perfectly:

 class Foo(Base):
id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)
bar = relationship(
"bar", 
primaryjoin="Foo.bar_id==Bar.id",
uselist=False,
back_populates="foo",
)

 class Bar(Base):
id = Column(Integer, primary_key=True)
foo = relationship(
"Foo", 
primaryjoin="Bar.id==Foo.bar_id",
uselist=False,
back_populates="bar",
)
Thanks to SQLAlchemy, I can do this:

myFoo.bar = myBar
As expected `myFoo.bar_id` is updated.  Wonderful.

I am working on some new functionality, and hit a potential performance 
issue.  While my `Foo` objects inherently know if there is an associated 
`Bar`, I have to query (direct or via lazy-load) the database to find out 
if a `Bar` has an associate `Foo`.  In most situations, this is not an 
issue. In a few contexts, the lazyloading or joins are a bit burdonsome.

Is there a way to set up the `relationship` so I could do cache the 
`foo_id` on Bar? Something like this:

 class Foo(Base):
id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)
bar = relationship("Bar", ???)
 
  class Bar(Base):
id = Column(Integer, primary_key=True)
foo_id = Column(Integer, ForeignKey("foo.id"), nullable=True)
foo = relationship("Foo", ???)

This is obviously an anti-pattern in database design, as I only need to 
duplicate this data to improve performance in a few places.

-- 
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/171b01dc-787e-43b6-bd8c-afdceb211543%40googlegroups.com.

Re: [sqlalchemy] hiding/encrypting the Oracle database connection information?

[Jonathan Vanasco]

There are two related concerns on this concept:

* protecting your credentials in source code
* protecting your credentials on the server

For the first concern, I like to use encryption management tools like 
Blackbox (https://github.com/StackExchange/blackbox)

With an encryption management system, you "enroll" certain files to be 
managed by the system.  Instead of saving the plaintext files to version 
control, the encrypted files are saved.  Approved users (via GPG keys in 
blackbox) are able to decrypt or edit (decrypt+edit+encrypt) the files.  
**When a project is deployed to a server, the files are decrypted and the 
plaintext version is left on the server**

For the second concern, I've never seen a foolproof way to safeguard the 
plaintext "secrets".  You can force stuff into environment variables, but 
hackers can still get to those.  You can trash files after starting an 
application... but then you can't restart the application unless you have 
an external service that logs into the machine and 
decrypts/reloads/deletes.  

I would focus on safeguarding your secrets from versioncontrol, and 
constructing them in ways that are prepared for leaks (for example, 
rotating credentials periodically, using ip whitelisting to limit where 
they can be used, using ACLs on the various services to limit what each 
credential can do)

-- 
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/5c0f526f-299b-44bc-827e-1e0b41813586%40googlegroups.com.

Re: [sqlalchemy] Best way to implement PostgreSQL materialized views?

[Jonathan Vanasco]

There also exists a 3rd party library that has been somewhat maintained: 
https://github.com/jklukas/sqlalchemy-views

IIRC, it is largely based on the Wiki recipe that Mike linked to.


-- 
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/8dc8616e-2362-45f9-8c34-aafc171875d6%40googlegroups.com.

Re: [sqlalchemy] composite secondary join variant

[Jonathan Vanasco]

Thanks! That works exactly as I needed.  I knew there was a problem in the 
secondaryjoin, so i commented it out.

This works more intuitively than my other composite relationships, which 
are all more complex. The joining you used is:

primaryjoin: A->B
secondaryjoin: B->B2C
secondary: B2C->C

-- 
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/71b757c9-6665-4cb2-8b57-d8f7fddd9fe5%40googlegroups.com.

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

[Jonathan Vanasco]

On Friday, April 24, 2020 at 1:16:10 PM UTC-4, Jens Troeger wrote:
>
>
> If I understand you correctly, then the solution above is as good as it 
> gets and SQLA doesn’t provide a builtin solution for what I’m trying to do?
>

There are so many hooks in SqlAlchemy, there may still be a more elegant 
manner... however, you are accomplishing exactly what you want with 
documented features, so it's not going to break in an update.

>

-- 
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/6d4ef82a-6c81-4790-8f0c-1dc7e21efc20%40googlegroups.com.

[sqlalchemy] composite secondary join variant

[Jonathan Vanasco]

i'm stuck on a variant of the Composite Secondary Join (
https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#composite-secondary-joins
)

I hope someone can see what my tired eyes are missing. I'm fairly certain 
the issue is in `secondary' and 'secondaryjoin'.  I've tried a handful of 
variants I thought would work, but trigger this error:

sqlalchemy.exc.InvalidRequestError: Class  does not 
have a mapped column named 'get_children'

I've used this pattern with much luck in the past: `primaryjoin` goes from 
the base class to whatever I build the secondary/secondary join from. 

I've distilled the relationship pattern as below:

* `A` does not fkey onto anything.
* `B` fkeys onto `A`
* The intended relationship on `A` is a list of `C` items, which are 
related to `B` through an association table

I can build out a the relationship from B, and I could probably mount it 
onto A with an association_proxy, but I'd like to understand what I'm doing 
wrong with the `relationship` off A.  This is puzzling me.

Thanks in advace.


import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
Base = declarative_base()
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)


cs = relationship(
"C",
primaryjoin="A.id == B.a_id",
secondary="join(B, B2C, B.id == B2C.b_id)."
  "join(B2C, C, B2C.c_id == C.id)",
# secondaryjoin="and_(C.id == B2C.c_id)",
viewonly=True
)


class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey('a.id'))


class B2C(Base):
__tablename__ = 'b2c'

id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey('b.id'))
c_id = Column(ForeignKey('c.id'))


class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)


engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
sessionFactory = sessionmaker(bind=engine)
sess = sessionFactory()

sess.query(A).join(a.cs).all()


-- 
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/474ccb9b-6839-47b7-9d38-fd1a7065f7a4%40googlegroups.com.

Re: [sqlalchemy] debugging DetachedInstanceError

[Jonathan Vanasco]

On Thursday, April 23, 2020 at 10:17:12 AM UTC-4, Mike Bayer wrote:
>
> Using the event hook is likely the most direct way to see where it's 
> happening, either via logging or pdb:
>
>
> https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=persistent_to_detached#sqlalchemy.orm.events.SessionEvents.persistent_to_detached
>  
> 
>

 
Awesome! Thanks mike. That's exactly what I needed.

After several hours of debugging, I finally tracked down the issue - which 
solved at least 4 outstanding bugs, and let me clear out a bunch of code.  
And now I can have `expire_on_commit` back too!

I never realized that `zope.sqlalchemy.mark_changed(` has it's own 
`keep_session` kwarg... which defaults to `False` and does not inherit from 
the existing configuration. 

I can't tell why the errors on this was inconsistent.  The app has a 
simplified "commit" hook that lets me handle multiple `pyramid_tm` commits 
in a single request, and I invoke mark_changed() in there to pick up a few 
edge cases.  In a small number of these instances, I lost the 
`keep_session` setting - in others I didn't.  








-- 
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/52dbb2c4-8eb7-4de6-b09b-fb5e7f4320ef%40googlegroups.com.

[sqlalchemy] debugging DetachedInstanceError

[Jonathan Vanasco]

I'm trying to figure out how I got a DetatchedInstanceError


DetachedInstanceError: Parent instance  is 
not bound to a Session; lazy load operation of attribute 'domain' cannot 
proceed (Background on this error at: http://sqlalche.me/e/bhk3)


This is happening in a web application where I never call 
`dbSession.close()`. It does use zope.sqlalchemy for pyramid_tm, but 
`keep_session` is True.  I set `expire_on_commit=False` on the sessionmaker.

Any tips on the best ways to troubleshoot how/where this is getting removed 
from a session?



-- 
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/3e2d1a58-3f95-4021-9e3a-8b20fb9a2559%40googlegroups.com.

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

[Jonathan Vanasco]

Assuming you are using declarative, this is okay (
https://docs.sqlalchemy.org/en/14/orm/mapping_api.html?#sqlalchemy.orm.Mapper
)

Note this line:
  A class which was mapped by the sqlalchemy.ext.declarative 

 extension will also have its mapper available via the __mapper__ attribute.

Mike generally doesn't change things once they're stated in the docs.

If you're not using declarative, it will probably be okay. Those things 
don't typically change.  If they do, you can easily grab the mapper in your 
property with something like this:

sqlalchemy.orm.class_mapper(self.__class__).relationships 

-- 
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/6a39b653-0139-4968-b6aa-c671769262cf%40googlegroups.com.

Re: [sqlalchemy] SQLAlchemy Access to Native Bulk Loaders

[Jonathan Vanasco]

On Sunday, April 19, 2020 at 4:44:56 PM UTC-4, Ben wrote:
>
>
> These seem to be embedded in the related SQL implementations but are 
> clearly not ANSI standard. I'm not sure if that makes it disqualifying for 
> a SQLAlchemy feature request, or if anyone else could even use it, but 
> functionality like this is something that, at least for me, would make my 
> implementation more DB independent.
>

The major database servers and clients do have similar extensions to ANSI 
and the Python DBAPI here, but they're fairly different from one another in 
how they work and their input/output formats (and capabilities).  They also 
are not necessarily supported by the underlying database drivers. Pyscopg2 
has "copy_" methods on the cursor (
https://www.psycopg.org/docs/usage.html#copy); there are several mysql 
drivers, i looked at two and neither have explicit support for this. I'm 
not sure how this would really work though - it's basically designed for 
being implemented in the C clients with local files. 

The "bulk loading" functionality in SqlAlchemy is largely in the realm of 
being a low-level DBAPI operations for insert statements, and SqlAlchemy's 
performance is about as close to using the driver itself - while still 
having the syntactic sugar and not needing to write to a database 
specifically.  I don't believe there is any code to invoke a database 
client to read a source file itself; I am often mistaken though as Mike has 
put a ton of work into SqlAlchemy over the years.

>From experience with large imports, I can tell you the following:

* The best performance for large updates involves:
1) take the database offline to clients
2) drop (and copy) all the foreign key and check constraints
3) import your data from a file with the native client that ships with 
your database *
4) add the foreign key and check constraints back in
if you feel the need to script this, you could use `subprocess` to 
invoke the database's native client on the machine

* The second best performance is:
use SqlAlchemy's bulk strategies, 
segment out the ranges of your source data file
run multiple processes in parallel, each processing a subset of the 
source data file

* The third best performance is:
   write a script that uses the SqlAlchemy ORM with the 'windowed query' 
option to iterate over a range of lines in your source file
   have the script use a 3rd party tool like Redis to mark that it has 
"checked out" sections of the source data, and has completed/not-completed 
that range
   run multiple processes in parallel, each processing a subset of the 
source data file

 
In the second and third approach, you can periodically run a query or 
script to calculate the right number of processes to run.  It could be 7, 
or 20, or another number.




 





 

-- 
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/88842622-82e1-4679-8bd4-bd61a7020604%40googlegroups.com.

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

[Jonathan Vanasco]

On Friday, April 17, 2020 at 8:02:50 PM UTC-4, Jens Troeger wrote:
>
>
>  Indeed, Child does have multiple parents…
>

Ok, so this isn't a one-to-one relationship, but a many-to-many 
relationship.

I would opt for a 3 table structure:

Parent
Parent_2_Child
Child

The AssociationProxy extension (part of SqlAlchemy itself) can 
transparently map the Parent and Child relationships through the 
Parent_2_Child table as attributes on Parent & Child tables itself.  (
https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html)

The section "Simplifying Association Objects" (
https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#simplifying-association-objects)
 
gives a good example of this on User/UserKeyword/Keyword

You could even remove the `oldest_child_id` and `youngest_child_id` 
columns, and turn them into readonly attributes that can compute the child 
as needed.

SqlAlchemy is very robust and you can do almost anything with it.  I am 
certain it is possible to build something that only uses the columns on the 
Parent table to accomplish exactly what you desire... but it really sounds 
like you should be using an association table in this model.


-- 
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/746f8e4d-c61a-4a74-9812-faecce7b0efa%40googlegroups.com.

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

[Jonathan Vanasco]

This departs a bit from the example, because you are caching the youngest 
and oldest ids onto the Parent object.  is that necessary for your usage?

> Now my question is: how can I introduce a set/list of all children on the 
parent?

The line you commented out from the example was either:

children = relationship("Child")

children = relationship("Child", back_populates="parent")


both of those lines create an iterable list of all the Child objects on the 
`children`

There are a handful of ways you could structure this.  It really depends on 
your data model and usage patterns.

Off the top of my head, the simplest way to accomplish this would be to add 
a "parent_id" column on the child table, and then create a relationship for 
"children" that correlates the `Parent.id` to `Child.parent_id`.  

That change might not work with your data model if a Child can have 
multiple parents. 



-- 
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/ecfe0528-e141-44f4-a39e-eff4e1a3fe6d%40googlegroups.com.

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Jonathan Vanasco]

On Thursday, March 26, 2020 at 2:53:08 PM UTC-4, Mike Bayer wrote:
>
> is the issue that your follower database is only updating 
> asynchronously?   I would likely organize the application to simply use two 
> different Session objects, one for master one for follower.   Trying to do 
> it on a per-query basis is needlessly implicit and complicated.
>

 In my applications, I have explicit Primary/Writer and Secondary/Reader 
sessions.  As a convenience, I will create a shortcut to proxy one of them 
onto a request(web)/context object as `.dbSession`.  Certain logic flows 
will only utilize the Primary/Writer, other logic flows will only utilize 
the Secondary/Reader.

-- 
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/5a92dc19-0d7a-41e0-bc6f-47b96ac1d32b%40googlegroups.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Jonathan Vanasco]

By default, SqlAlchemy has `expire_on_commit=True`. 

I'm going to poorly restate most of what Mike Bayer has told me in the 
past:  the rationale behind this- an active Session is supposed to mirror 
the current state in the database; within a transaction we know the object 
values are equal to the table values.  Once the transaction ends via 
`commit`, the object is no longer in sync - another transaction may have 
modified that database row.  

In your example, note these 2 lines from the stacktrace:

File "sqlalchemy/orm/attributes.py", line 669, in get
  value = state._load_expired(state, passive)

when you invoked a `commit`, SqlAlchemy expired the object (after the 
commit).

when you invoked `print()`, SqlAclhemy recognized the object as expired and 
is now trying to load the expired attributes so it can print them for you.

-- 
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/bdb5dde6-ff15-472d-a3da-4aa5b980fdfc%40googlegroups.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

[Jonathan Vanasco]

My first guess is two things are going on:

1. This is a behavior of `expire_on_commit` on the session.  Once you 
commit on the Primary database, the object is stale.
   https://docs.sqlalchemy.org/en/13/orm/session_api.html

2. The session is then trying to read off a Secondary database, but the row 
has not yet synced.

You can adjust `expire_on_commit` if you're only doing short-term read-only 
actions. However,  I would explore to ensure this is trying to read off the 
other database and why.

-- 
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/7cdd3a62-63d3-4b3c-8366-4748fc2256cf%40googlegroups.com.

Re: [sqlalchemy] Re: AM/PM question

[Jonathan Vanasco]

It doesn't matter when the time is entered or what it is supposed to 
reflect.  The best option -- by a wide margin -- for storing any time 
values is in a TIMESTAMP column, and for storing date+time values is in a 
DATETIME column.  These types of columns/fields/formats exist to streamline 
data storage and leverage numerous operations on the database and in your 
application.

Even though you will only show the user an option of 12hours and am/pm, on 
the backend you should be storing the time in a 24hour native time object; 
if there is a date associated, you should be storing it in a timestamp.  
That will allow you to easily query records with these fields in the future.

Timestamp/Datetime:
   * Python https://docs.python.org/3/library/datetime.html#datetime-objects
 
   * SqlAlchemy 
https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=timestamp#sqlalchemy.types.DateTime

Time:
   * Python - https://docs.python.org/3/library/datetime.html#time-objects
   * SqlAlchemy Column - 
https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=timestamp#sqlalchemy.types.Time

If you feel a strong need to handle this otherwise, you can use an Enum (
https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=timestamp#sqlalchemy.types.Enum)
 
column to constrain the am/pm options.  You can also just use a string 
column and enforce the selection with constraints.

Your best path forward, however, is convert the user-input into a python 
Time or DateTime object and store that object in the database; then you can 
construct the widgets for "display/edit" by setting their defaults to be 
the day/month/year/hour/minute of the datetime field in your sqlalchemy 
object.



-- 
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/50f736a5-4dd3-4537-9530-7b990eb64473%40googlegroups.com.

[sqlalchemy] Re: AM/PM question

[Jonathan Vanasco]

The common approach to this situation is storing the data as a 24hour 
timestamp in the database/sqlalchemy and converting it to a 12hour am/pm 
for display/editing.

You could use a "12 hour" option and an enum column for am/pm or a string. 
You're going to have a much easier time in the longterm by using a standard 
timestamp though.

-- 
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/1fcf1565-a1d1-40b9-b094-4f8671838905%40googlegroups.com.

[sqlalchemy] Re: Problem with inserting row containing a value with type PATH of postgres with geoalchemy2

[Jonathan Vanasco]

I'm glad it's up and running for you!

-- 
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/1ba8c778-4264-4f22-ae0e-f15470a3cb7d%40googlegroups.com.

[sqlalchemy] Re: Warning about using backref with viewonly - how to make a two-way read-only relation?

[Jonathan Vanasco]

Tony,

Mike helped me with a similar problem a few weeks ago:

https://groups.google.com/d/msg/sqlalchemy/k4a-v2ebeJM/bj73xd4CFwAJ

In his suggestion, I stash some mapper configuration data into `info`, then 
use the `mapper_configured` event to audit my configuration requirements. 

-- 
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/e2f485df-460a-4215-bfdd-5d9ffa7aba53%40googlegroups.com.

[sqlalchemy] Re: Problem with inserting row containing a value with type PATH of postgres with geoalchemy2

[Jonathan Vanasco]

On Tuesday, March 17, 2020 at 12:06:36 PM UTC-4, Christos Ch wrote:
>
> I am running a service from a docker container
>

The error is from psycopg2 and stating the function is not available on the 
PostgreSQL server.  This is most likely because PostGIS is not installed on 
the server (which could be in the docker container, or elsewhere) or needs 
to be enabled for that database.

-- 
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/73ccf694-b521-482a-87c6-a112b5e2d311%40googlegroups.com.

[sqlalchemy] Re: Problem with inserting row containing a value with type PATH of postgres with geoalchemy2

[Jonathan Vanasco]

`st_geomfromewkt` is a function in the PostGIS extension to PostgreSQL.  

Are you certain postgis is installed in the server and enabled for the 
database?

-- 
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/3cb2d821-e03a-4a96-a2c9-565eb539edf5%40googlegroups.com.

Re: [sqlalchemy] association_proxy

[Jonathan Vanasco]

On Monday, March 16, 2020 at 12:31:09 PM UTC-4, Mike Bayer wrote:
>
> I sometimes get a "moderators spam report" for SQLAlchemy and then I know 
> I have to go to the admin interface on the website.   I likely approve them 
> really quick before you see them.   as far as originals missing i dont know 
> where to go for that.
>
>
Maybe you are replying before approving? This is just so odd - i've looked 
through the admin interface many times and can't find them.  I'll ask a 
google friend if they know how this happens.

-- 
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/6f34d835-0804-4b4a-9a1c-ec94da25f796%40googlegroups.com.