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

[William Phillips]

Sorry I was not back sooner.  Thank-you Simon King, you have solved my 
problem.  As anyone can guess I am still in the learning stages of 
SqlAlchemy.  My python/Sqlite solution has shown that I have neglected 
SqlAlchemy core.  When I first downloaded and studied SqlAlchemy, I only 
glanced at the CORE.  I went directly to ORM which intrigued me.  I could 
have tried your CORE suggestion which works.  Didn't even think to try.

I am glad that you identified the opposite of my option "append" comand, 
"remove".  After your post, I even found the documentation for the code.  I 
have tried the command and it works the way I want it.

The code:
def removeOption(machineKey,  OptionKey):

session = connectToDatabase()

machineData = session.query(Machine).filter(Machine.machine_ID == 
machineKey).one()
optionData = session.query(Options).filter(Options. options_ID == 
OptionKey).one()
machineData.children.remove(optionData)
session.add(machineData)
session.commit()
session.close()

Problem solved.  The option has been removed from the association table, 
thus disconnected from the machine and both the machine and option tables 
remain intact.

For completion purposes here is the code to connect an option to a machine 
which is the same except for append/remove: (there is probably a way of 
converting these two functions into a single function)

def connectOption(machineKey, optionKey):

session = connectToDatabase()

machineData = session.query(Machine).filter(Machine.machine_ID == 
machineKey).one()
optionData = session.query(Options).filter(Options. options_ID == 
optionKey).one()
machineData.children.append(optionData)
session.add(machineData)
session.commit()
session.close()

On Saturday, August 15, 2020 at 3:58:37 PM UTC-4 Simon King wrote:

> SQLAlchemy normally presents a many-to-many relationship as a list on
> both sides. You've got "Machine.children", which is a list of Options,
> and "Option.parents", which is a list of Machines.
>
> If you remove one of the options from a machine.children list, you'll
> find that SQLAlchemy removes the entry from the association table.
> Something like this:
>
> machine.children.remove(option_to_remove)
>
> However, this does have the downside that when you access
> "machine.children", SQLAlchemy will load all the Options for that
> Machine from the database. This is a waste of effort if you are only
> trying to delete one of them. (But if you've got them loaded anyway,
> it doesn't matter)
>
> The other option is to delete the row explicitly, something like this:
>
> statement = Machine_Options.delete().where(
> Machine_Options.c.machine_FK == machine.machine_ID,
> Machine_Options.c.options_FK == option.option_ID
> )
> session.execute(statement)
>
> But beware that if you do this, any machines or options already loaded
> in your session won't be aware that the delete happened. If they had
> already loaded their "parents" or "children" relationships, that
> cached data will not match what is in the database.
>
> Hope that helps,
>
> Simon
>
> On Wed, Aug 12, 2020 at 3:05 AM William Phillips  
> wrote:
> >
> > For the sake of completeness I am including the code to disconnect an 
> option from a machine using only python/SQLite code.
> >
> > def removeOption(bladeKey, OptionKey):
> >
> > """
> > DELETE from blade_options
> > WHERE blade_FK == ?
> > AND options_FK == ?
> > """
> > import sqlite3
> > dbPath = config.database_path
> > sqliteConnection = sqlite3.connect(dbPath)
> > cursor = sqliteConnection.cursor()
> > sql = 'DELETE from blade_options WHERE blades_ID == ? AND options_ID == 
> ?; '
> > cursor.execute(sql, (bladeKey, OptionKey, ))
> > sqliteConnection.commit()
> > sqliteConnection.close()
> > return
> >
> > --
> > 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/45da4231-3550-4f5b-882e-9e61bef86bd5o%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 

Re: [sqlalchemy] Table to track applied migrations?

[Jasen Jacobsen]

Audit-Alembic  meets the 
requirement. It creates an alembic_version_history table, and populates it 
with Alembic events. Unfortunately, it was last updated three years ago and 
I believe is out of date (it's check for whether 'on_version_apply' exists 
fails). And I can't figure out how to get access to the message/slug from 
within env.py. But it does work in the trivial case.

On Wednesday, August 19, 2020 at 1:55:45 PM UTC-4 Mike Bayer wrote:

> this is issue 309 https://github.com/sqlalchemy/alembic/issues/309  
> waiting for someone with the time and motivation to work on it.
>
>
>
> On Wed, Aug 19, 2020, at 11:25 AM, Jasen Jacobsen wrote:
>
> I've used Liquibase in the past and as part of its migration tracking it 
> creates a table which lists each migration applied. See Liquibase 
> Changelog Table 
> .
> Alembic has the alembic_version table, but it only holds a single row and 
> column (version_num).
> Is there a way to have Alembic populate a table similarly to Liquibase? 
> (And not manually adding statements to each migration script.) Something 
> that would grab the 'slug', Create Date, Revision ID, and applied date for 
> each migration script and add a row to a table. Maybe something to be used 
> in configure(on_version_apply)?
>
> Thanks for any pointers.
>
>
> --
> 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/c7fa8044-a5c9-4941-8d93-71d9663f4e60n%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/2e7d674c-3388-424a-a49a-f7a79850fdacn%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.

Re: [sqlalchemy] Table to track applied migrations?

[Mike Bayer]

this is issue 309 https://github.com/sqlalchemy/alembic/issues/309  waiting for 
someone with the time and motivation to work on it.



On Wed, Aug 19, 2020, at 11:25 AM, Jasen Jacobsen wrote:
> I've used Liquibase in the past and as part of its migration tracking it 
> creates a table which lists each migration applied. See Liquibase Changelog 
> Table 
> .
> Alembic has the alembic_version table, but it only holds a single row and 
> column (version_num).
> Is there a way to have Alembic populate a table similarly to Liquibase? (And 
> not manually adding statements to each migration script.) Something that 
> would grab the 'slug', Create Date, Revision ID, and applied date for each 
> migration script and add a row to a table. Maybe something to be used in 
> configure(on_version_apply)?
> 
> Thanks for any pointers.
> 

> --
> 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/c7fa8044-a5c9-4941-8d93-71d9663f4e60n%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/7ae8b44f-4e28-42c5-8a72-48ef1903e477%40www.fastmail.com.

[sqlalchemy] Table to track applied migrations?

[Jasen Jacobsen]

I've used Liquibase in the past and as part of its migration tracking it 
creates a table which lists each migration applied. See Liquibase Changelog 
Table 
.
Alembic has the alembic_version table, but it only holds a single row and 
column (version_num).
Is there a way to have Alembic populate a table similarly to Liquibase? 
(And not manually adding statements to each migration script.) Something 
that would grab the 'slug', Create Date, Revision ID, and applied date for 
each migration script and add a row to a table. Maybe something to be used 
in configure(on_version_apply)?

Thanks for any pointers.

-- 
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/c7fa8044-a5c9-4941-8d93-71d9663f4e60n%40googlegroups.com.

[sqlalchemy] can't connect to Teradata - "The UserId, Password or Account is invalid"

[Chuck Pedro]

Hi. I'm using Anaconda Python 3.8.3, trying to use SQLAlchemy to connect to 
Teradata but it's not working. I've tried several ways but no luck. 

In "pip list" I have
SQLAlchemy
sqlalchemy-teradata
teradata
teradatasql
teradatasqlalchemy

Currently config is like this:

import sqlalchemy
conn_string = 'teradata://' + user + ':' + passw + '@' + host + 
'/?logmech=LDAP'
eng = sqlalchemy.create_engine(conn_string)
sql = 'select top 10 * from some_table'
result = eng.execute(sql) 

The error I get is:
DatabaseError: (teradata.api.DatabaseError) (210, '[28000] [Teradata][ODBC 
Teradata Driver][Teradata Database] (210) The UserId, Password or Account 
is invalid. FailCode = -8017')

But by now it's certain that there's no problem with my account 
credentials. I can connect to the same DB with other Python connection 
methods (pyodbc) and also DBeaver. So I think this is a driver issue? 

I posted here but didn't get anything helpful: 

https://stackoverflow.com/questions/63476810/userid-password-or-account-is-invalid-for-sqlalchemy-teradata?noredirect=1#comment112249505_63476810
 

Please help if you can. The reason I want to use sqlalchemy is to use 
df.to_sql to insert to a table in Teradata. It seems easier than with 
pyodbc.  

Thanks in advance. 

-- 
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/13e78d8d-6e24-4380-a29a-b051b4510d9an%40googlegroups.com.

Re: [sqlalchemy] Getting column data from result set with column name as a string.

[Mike Bayer]

__table__ is public (private would be a single or double underscore prefix 
only), but also you could use inspect(cls).column_attrs:

https://docs.sqlalchemy.org/en/13/orm/mapping_api.html?highlight=column_attrs#sqlalchemy.orm.Mapper.column_attrs



On Wed, Aug 19, 2020, at 1:22 AM, Dale Preston wrote:
> Thanks.  The label is an interesting option; I'll look into that.
> 
> On a StackOverflow thread, I got *row.__table__.columns* which I can iterate 
> over and test the key, allowing me to get the column I need but I have to 
> loop through all the columns until I find the one I want for each row because 
> columns doesn't have an index either.  I also don't like using a private 
> property but I guess (hope) __table__ would always be there.
> On Tuesday, August 18, 2020 at 6:05:49 PM UTC-5 Mike Bayer wrote:
>> 
>> 
>> On Tue, Aug 18, 2020, at 5:20 PM, Dale Preston wrote:
>>> I'm using sqlalchemy 1.3.18.  I'm trying to write an app that looks at data 
>>> from an ORM declarative table without necessarily knowing the table 
>>> definition.
>>> 
>>> What I am looking for is a way to get a single object (row in resultSet), 
>>> having the name of column[1] is "lastname", and having "lastname" as a 
>>> string in memory, how can I get the value of the "lastname" field from the 
>>> row in resultSet?
>>> 
>>> It's easy if I know in code that the row has a lastname property and I can 
>>> use row.lastname but I want to do something like row["lastname"] or 
>>> row.columns["lastname"] if there's a way.
>> 
>> to get individual columns in the row you query for those columns directly:
>> 
>> 
>> row = sess.query(User.lastname).first()
>> 
>> print(row.lastname)
>> 
>> 
>> otherwise you can always label a column if you need:
>> 
>> row = sess.query(User.anything.label("lastname")).first()
>> 
>> print(row.lastname)
>> 
>> 
>> 
>> 
>> 
>> 
>>> 
>>> 
>>> I'm using reflection to get the columns for the table.  Here's some code I 
>>> tried:
>>> 
>>> class Users(Base):
>>> __tablename__ = 'users'
>>> userid = Column(String(80), primary_key=True)
>>> lastname = Column(String(40), nullable=False)
>>> firstname = Column(String(40), nullable=False)
>>> emailaddress = Column(String(80), nullable=False)
>>> 
>>> def ReflectTableColumns(DbEngine, meta, targetTable):
>>> tableschema = Table(targetTable, meta, autoload=True, 
>>> autoload_with=DbEngine)
>>> cols = dict()
>>> for c in tableschema.columns:
>>> print("{0}\t|\t{1}".format(c.name, c.type))
>>> cols[c.name] = c.type
>>> 
>>> return cols
>>> 
>>> def GetUsers():
>>> DBSession = sessionmaker(bind=Engine)
>>> session = DBSession()
>>> ShowTableData(session.query(Users).all(), 'users')
>>> 
>>> 
>>> def ShowTableData(resultSet, tablename):
>>> columns = ReflectTableColumns(Engine, Base.metadata, tablename)
>>> columnNames = list(columns.keys())
>>> print (type(resultSet))
>>> for row in resultSet:
>>> print (row.items[columnNames[1]])
>>> print (row.columns[columnNames[1]])
>>> print (row[columnNames[1]])
>>> 
>>> GetUsers()
>>> 

>>> --
>>> 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/f11bf60e-e872-489e-9a9b-03998440bbb1n%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/975d8626-39cf-439f-b5ed-df1e9680f7bfn%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