Creating database tables from queries based on ORM API?

[Markus Elfring]

Hello,

I would like to count value combinations in database records together with the software “SQLAlchemy 1.3.2”.
The computation result should be stored into a dynamically generated table.

I have found a few answers for this data processing task.

• How to create a new table from select statement in sqlalchemy?
• creating a temporary table from a query using sqlalchemy orm

I am curious also how such table creation can be achieved by the programming interface “Object Relational Mapper” currently.

Regards,
Markus

[Mike Bayer]

both of the above stackoverflow recipes refer to usage of the
SQLAlchemy select() object. When you have an ORM Query object, you
can get the underlying select() from it by calling upon the .statement
accessor:

my_query = session.query(Entity).filter_by(foo='bar')

ins = InsertFromSelect(temp, my_query.statement)
session.execute(ins)

https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query%20statement#sqlalchemy.orm.query.Query.statement





>
> Regards,
> Markus
>
> --
> 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 post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Markus Elfring]

both of the above stackoverflow recipes refer to usage of the
SQLAlchemy select() object.    When you have an ORM Query object, you
can get the underlying select() from it by calling upon the .statement
accessor:

Thanks for this information.

I imagine that there is no need to use the attribute “statement” if I can stick to the high-level ORM programming interface.

 

ins = InsertFromSelect(temp, my_query.statement)
session.execute(ins)

The call of the method “Insert.from_select” can occasionally be also interesting.

But I am looking for direct support of the SQL command “CREATE TABLE AS SELECT” (CTAS) without switching to the “core” API.

Will any software extensions be helpful for this purpose?

Regards,
Markus

[Mike Bayer]

insert.from_select() will accept the Query object directly. if using
the CreateTableAs recipe, it can be modified to coerce a Query passed
to it into a Core statement internally. the code to do this coercion
would resemble:

def __init__(self, name, query):

if isinstance(query, orm.Query):
query = query.statement
self.name = name
self.query = query

[Markus Elfring]

insert.from_select() will accept the Query object directly.

Such data addition depends on the detail that a table object exists before, doesn't it?

 

if using the CreateTableAs recipe,

Do you refer to any additional information source here?

 

it can be modified to coerce a Query passed to it into
a Core statement internally.

Do you suggest to work with an extra conversion approach for a while?

I would prefer the mapping of a dynamically generated database table to an additional Python object instead.

Regards,
Markus

[Mike Bayer]

On Mon, Apr 15, 2019 at 9:53 AM Markus Elfring <Markus....@web.de> wrote:
>>
>> insert.from_select() will accept the Query object directly.
>
>
> Such data addition depends on the detail that a table object exists before, doesn't it?

INSERT FROM SELECT refers to an existing table, yes, I mention it
because you referred to this construct in one of the stack overflow
answers you mentioned.

>
>
>>
>> if using the CreateTableAs recipe,
>
>
> Do you refer to any additional information source here?

this is from the StackOverflow answer you referred towards of which I
am also the author:

https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608

>
>
>>
>> it can be modified to coerce a Query passed to it into
>> a Core statement internally.
>
>
> Do you suggest to work with an extra conversion approach for a while?

If you're asking, if you should pass query.statement to the
hypotheical CreateTableAs construct, or if the CreateTableAs construct
should do it, it's your choice. It is a goal of Core objects that an
ORM query passed can be interpreted as its underlying SELECT statement
which suggests the latter.

>
> I would prefer the mapping of a dynamically generated database table to an additional Python object instead.

I'm beginning to understand what it is you might want to do. The
most expedient approach is to use the CreateTableAs construct, then
reflect the new database table using autoload, or to build the Table
object directly:

q = session.query(<things>)

session.execute(CreateTableAs('t2', q))

class T2(Base):
__table__ = Table('t2', Base.metadata,
autoload_with=session.connection())

Note the above requires that the Table has a primary key, however, I'm
not sure if CREATE TABLE .. AS also generates primary key constraints
on the new table. If not, these need to be added as well:

class T2(Base):
__table__ = Table('t2', Base.metadata,
autoload_with=session.connection())
__mapper_args__ = {"primary_key": ["id", "a"]} # names of
columns to be considered primary key

To create the table directly, the basic idea would be like the
following, where I'm also trying to copy out which columns would be
considered a primary key:

def mapping_for_query(Base, q, name):
select = q.statement

# dynamically create a Table object, try to copy primary key
flag also from queried tables
table = Table(name, Base.metadata, *[Column(col.name, col.type,
primary_key=col.primary_key) for col in select.inner_columns])

# dynamically create a mapped class against this table
return type(name, (Base, ), {"__table__": table})

With the above, you also need to run the CreateTableAs() construct to
actually generate the table in the database.

These various units could likely be composed into a polished function
that is fairly usable for simple queries. Determining the "primary
key" from a SELECT statement however is not easily generalizable, so
your functions may need to be passed additional hints from the
programmer as to which columns would be part of the primary key. The
ORM cannot map a class without a primary key configured.

[Markus Elfring]

INSERT FROM SELECT refers to an existing table, yes,

This SQL command is already supported by your class library.

 

I mention it because you referred to this construct in one of the stack overflow
answers you mentioned.

It was shown that is possible (in principle) to copy some table data by additional programming with Python code.

 

https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608

Can a variant of the method “CreateTableAs” (which you published on 2015-06-01) become a standard component of the application programming interface?

 

I'm beginning to understand what it is you might want to do.

Was any information from my clarification request unclear (at the beginning)?

The most expedient approach is to use the CreateTableAs construct,
then reflect the new database table using autoload,

I was unsure on how the provided data structures would be determined from a dynamically generated database table.

or to build the Table object directly:

I would prefer to reuse such functionality from the selected database system (instead of duplicating it by extra Python code).

Regards,
Markus

[Mike Bayer]

On Mon, Apr 15, 2019 at 12:22 PM Markus Elfring <Markus....@web.de> wrote:
>>
>> INSERT FROM SELECT refers to an existing table, yes,
>
>
> This SQL command is already supported by your class library.
>
>
>>
>> I mention it because you referred to this construct in one of the stack overflow
>> answers you mentioned.
>
>
> It was shown that is possible (in principle) to copy some table data by additional programming with Python code.
>
>
>> https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608
>
>
> Can a variant of the method “CreateTableAs” (which you published on 2015-06-01) become a standard component of the application programming interface?

it can, but as you are seeing, it's insufficient for what people
actually want to do which is map ORM classes. We like to keep these
kinds of things as external recipes until it is crystal clear what
built in API should be added that meets at least the following
criteria, noting that more criteria may be considered: 1. does
everything that people need, that is, the full universe of use cases
is discovered and addressed 2. is definitely the best and only way to
do the thing that people need 3. works very consistently on at least
most backends 4. has very comprehensive test coverage 5. is not such
a rare use case that a simple recipe (as given) is not enough

for this feature, we are lacking #1 , #2, #3, #4 and #5 right now.
in particular, should the construct attempt to create a new Table
object in memory also so that it need not be reflected? What kinds of
complications might arise, can we definitely determine what decisions
the database makes when it creates this table ? Will people want to
ORM map these classes? how do we determine the primary keys then, or
how do we otherwise provide an API for users to define that? How do
we document this, including what do we state as why someone might want
to do this? Database support - it is unclear what databases
support this or how they do so; for example in SQL Server they seem to
have a different syntax entirely (SELECT INTO), how do the
capabilities of SELECT INTO differ from "CREATE TABLE AS" ? Would
we build separate constructs for different databases so that they
remain unaffected by each other and if so how does the ORM use case
map to that?

There is a lot of effort to be considered when we add things to the
library including the very arduous and tedious process of dealing with
mistakes in the API design, which occur when we build features where
we don't have an absolutely solid understanding of what this use case
needs to accomplish. By contrast, by providing recipes, all users
can immediately do exactly the thing they need without any issue as
far as API compatibility, cross-database compatibility, dealing with
shortcomings in the API, etc.

>
>
>> I'm beginning to understand what it is you might want to do.
>
>
> Was any information from my clarification request unclear (at the beginning)?

When you stated "I would like to count value combinations in database
records", that sounds like you are looking to emit a SQL query of some
kind so there is no need to involve the ORM here, so it was not clear
what feature of the ORM you were seeking.

>
>
>> The most expedient approach is to use the CreateTableAs construct,
>> then reflect the new database table using autoload,
>
>
> I was unsure on how the provided data structures would be determined from a dynamically generated database table.

can you perhaps illustrate a code example of what you would like to do ?

>
>
>> or to build the Table object directly:
>
>
> I would prefer to reuse such functionality from the selected database system (instead of duplicating it by extra Python code).

The examples I've illustrated so far aren't duplications. SQLAlchemy
doesn't have this feature right now.

[Markus Elfring]

>> Can a variant of the method “CreateTableAs” (which you published on 2015-06-01) become a standard component of the application programming interface?
>
> it can,

Thanks for such positive feedback.

> but as you are seeing, it's insufficient for what people
> actually want to do which is map ORM classes.

I presented a description for another use case.

> We like to keep these kinds of things as external recipes

How do you think about to clarify these approaches a bit more?

> until it is crystal clear what built in API should be added
> that meets at least the following criteria,
> noting that more criteria may be considered:

I hope that these checks can be reconsidered as usual.

> for this feature, we are lacking #1 , #2, #3, #4 and #5 right now.

I imagine that similar clarification requests can trigger further
software evolution.

> in particular, should the construct attempt to create a new Table
> object in memory also so that it need not be reflected?

Can this aspect result in another software extension?

> What kinds of complications might arise,

Some “surprises” will occur because of the usual challenges
of software development.

> can we definitely determine what decisions the database makes
> when it creates this table?

I am curious on how good the determination of table properties
can become.

> Will people want to ORM map these classes?

I would like to achieve it somehow.

> how do we determine the primary keys then,

Would any more database developers like to add their concerns
and advices for this aspect?

> or how do we otherwise provide an API for users to define that?

I imagine that adjustments can become interesting also in this area.

> Database support - it is unclear what databases support this
> or how they do so; for example in SQL Server they seem to
> have a different syntax entirely (SELECT INTO), how do the
> capabilities of SELECT INTO differ from "CREATE TABLE AS" ?

Does one of these commands belong to the official SQL standard?

> Would we build separate constructs for different databases

This might be necessary if you would like to support non-standard functionality.
https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization

> so that they remain unaffected by each other

Will a bit more than the least common denominator become applicable?

> and if so how does the ORM use case map to that?

I find that temporary tables and corresponding objects are useful
in several cases.

> By contrast, by providing recipes, all users can immediately do exactly
> the thing they need without any issue as far as API compatibility,
> cross-database compatibility, dealing with shortcomings in the API, etc.

How much will these approaches influence the change acceptance
for a better API design?

> When you stated "I would like to count value combinations in
> database records", that sounds like you are looking to emit a SQL query
> of some kind

Yes. - I would like to perform data analysis for my needs.

> so there is no need to involve the ORM here,

I published a few scripts which demonstrate the application
of your class library for specific data processing tasks.

> so it was not clear what feature of the ORM you were seeking.

I hope that this programming interface can shield also me a bit more
from challenges because of SQL variations.

Selections an be constructed in convenient ways by Python query classes.
The data export of computed results into additional tables can become nicer,
can't it?

> can you perhaps illustrate a code example of what you would like to do ?

I could mention a bit more from a concrete example. But I imagine
that this does not really matter here at the moment.

It find it more important to take run time consequences occasionally better
into account.
Some computations require then that their results will be stored in new tables.

>>> or to build the Table object directly:
>>
>> I would prefer to reuse such functionality from the selected database system (instead of duplicating it by extra Python code).
>
> The examples I've illustrated so far aren't duplications.

I got the impression (from the descriptions a moment ago) in such a direction.

> SQLAlchemy doesn't have this feature right now.

Thanks for such an information.

I am still curious then how the situation can be improved further.
How will database reflection support evolve?
https://docs.sqlalchemy.org/en/13/core/reflection.html#limitations-of-reflection

Regards,
Markus

[Mike Bayer]

Hi there -

Answering line by line is not really a good use of either of our time
and I apologize that I cannot really work with the lack of specifics
presented here, if I can restate the situation, the "CREATE TABLE AS"
use case is not very common, and as far as an ORM use case, I have no
idea what such a feature would look like. The SQL component of
CREATE TABLE AS is very easy to create as a recipe and since it is
very idiosyncratic to specific databases there is not a compelling
reason to prioritize designing and maintaining a built in construct,
but as always, contributors are welcome to propose and assist in
implementing specific Core and ORM-level features. Specific
proposals and test cases are the most helpful approach.

[Markus Elfring]

> Answering line by line is not really a good use of either of our time

I increased my feedback according to the provided information.

> and I apologize that I cannot really work with the lack of specifics
> presented here,

You might prefer an other communication style.

> if I can restate the situation, the "CREATE TABLE AS" use case
> is not very common,

Other usage patterns can be more popular.

> and as far as an ORM use case, I have no idea what such a feature
> would look like.

The design of additional classes is an usual challenge for corresponding
software development, isn't it?

> The SQL component of CREATE TABLE AS is very easy to create as a recipe

The construction of such a SQL command might be reasonable.

> and since it is very idiosyncratic to specific databases

The variations in SQL support are interesting somehow, aren't they?

> there is not a compelling reason to prioritize designing and maintaining a built in construct,

I can follow such a view to some degree.

> but as always, contributors are welcome to propose

I suggested a clarification for software aspects in this direction.

> and assist in implementing specific Core and ORM-level features.

I am curious under which circumstances further software extensions
will become more feasible.

> Specific proposals and test cases are the most helpful approach.

Can you imagine a need for table creations because of data processing
requirements and known consequences from query run time characteristics?

Regards,
Markus

[Markus Elfring]

> can you perhaps illustrate a code example of what you would like to do ?

I have tried the following approach out for the software “SQLAlchemy 1.3.2”
together with the engine “sqlite:///:memory:”.

…
q = session.query(action.statement1, action.statement2, action.name, action.source_file,
func.count("*").label("C")
).group_by(action.statement1,
action.statement2,
action.name,
action.source_file) \
.having(func.count("*") > 1)
ct = 'create table t2 as ' + str(q.statement)
sys.stderr.write("Command: " + ct + "\n")
session.execute(ct)
…


Now I wonder about parameter specifications in the generated SQL command
(and a traceback with the corresponding error code “http://sqlalche.me/e/cd3x”).

Command: create table t2 as SELECT …, statements.source_file, count(:count_1) AS "C"
FROM … GROUP BY …
HAVING count(:count_2) > :count_3


Which details should be adjusted a bit more here?

Regards,
Markus

[Mike Bayer]

Here is a complete proof of concept using your query, I hope this helps!

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr


from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement, Executable


class CreateTableAs(Executable, ClauseElement):
def __init__(self, name, query):
self.name = name
if hasattr(query, "statement"):
query = query.statement
self.query = query


@compiles(CreateTableAs, "postgresql")
def _create_table_as(element, compiler, **kw):
return "CREATE TABLE %s AS %s" % (
element.name,
compiler.process(element.query),
)


Base = declarative_base()


class Action(Base):
__tablename__ = "action"
id = Column(Integer, primary_key=True)
some_other_data = Column(String)
statement1 = Column(String)
statement2 = Column(String)
name = Column(String)
source_file = Column(String)


e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = session = Session(e)
s.add_all(
[
Action(
some_other_data="some other data %d" % i,
statement1="s1 %d" % (i % 4),
statement2="s2 %d" % (i % 2),
name="name %d" % (i % 4),
source_file="some file %d" % (i % 4),
)
for i in range(10)
]
)
session.flush()

q = (
session.query(
Action.statement1,
Action.statement2,
Action.name,
Action.source_file,
func.count("*").label("C"),
)
.group_by(
Action.statement1, Action.statement2, Action.name, Action.source_file
)
.having(func.count("*") > 1)

)

session.execute(CreateTableAs("t2", q))


class T2(Base):
__table__ = Table("t2", Base.metadata, autoload_with=session.connection())

__mapper_args__ = {
"primary_key": [__table__.c.source_file] # a primary key must
be determined
}

print(
session.query(T2).all()
)

[Markus Elfring]

> Here is a complete proof of concept using your query,

Thanks for this constructive response.


> I hope this helps!

The provided implementation details look very promising.


> @compiles(CreateTableAs, "postgresql")

There are additional constraints to consider.

* I am experimenting with data analysis in an execution environment
where I can not use Python decorators so far.

* I would like to perform queries together with the engine “sqlite:///:memory:”
(at the moment).

> q = (
> session.query(
> Action.statement1,
> Action.statement2,
> Action.name,
> Action.source_file,
> func.count("*").label("C"),
> )
> .group_by(
> Action.statement1, Action.statement2, Action.name, Action.source_file
> )

I wonder about the extra parentheses for this expression.

> .having(func.count("*") > 1)
> )
>
> session.execute(CreateTableAs("t2", q))

I wonder also about the aspect that this suggestion should probably work
while I stumbled on the code “HAVING count(:count_2) > :count_3”
in a generated SQL command.
I would expect the filter “HAVING count(*) > 1” there instead.

I would appreciate further advices for this questionable situation.

Regards,
Markus

[Mike Bayer]

On Thu, Apr 18, 2019 at 11:50 AM Markus Elfring <Markus....@web.de> wrote:
>

> * I am experimenting with data analysis in an execution environment
> where I can not use Python decorators so far.
>
> * I would like to perform queries together with the engine “sqlite:///:memory:”
> (at the moment).
>
>
> > q = (
> > session.query(
> > Action.statement1,
> > Action.statement2,
> > Action.name,
> > Action.source_file,
> > func.count("*").label("C"),
> > )
> > .group_by(
> > Action.statement1, Action.statement2, Action.name, Action.source_file
> > )
>
> I wonder about the extra parentheses for this expression.
>
>
> > .having(func.count("*") > 1)
> > )
> >
> > session.execute(CreateTableAs("t2", q))
>
> I wonder also about the aspect that this suggestion should probably work
> while I stumbled on the code “HAVING count(:count_2) > :count_3”
> in a generated SQL command.
> I would expect the filter “HAVING count(*) > 1” there instead.

there's no technical reason the "1" needs to render inline and
SQLAlchemy defaults literal values to being bound parameters. If
you'd like it to say "1", then use having(func.count("*") >
literal_column("1")).

[Markus Elfring]

> there's no technical reason the "1" needs to render inline

How does this information fit to the code “having(func.count(Address.id) > 2)”
from an example?
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.having

> and SQLAlchemy defaults literal values to being bound parameters.

Did I overlook a detail from the software documentation?

> If you'd like it to say "1", then use having(func.count("*") >
> literal_column("1")).

I have got the generated codes “count(:count_1) AS "C"” and “HAVING count(:count_2) > 1”
then for my query approach.
Which details should I adjust further?

Regards,
Markus

[Markus Elfring]

> there's no technical reason the "1" needs to render inline

How does this information fit to the code “having(func.count(Address.id) > 2)”
from an example?
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.having

> and SQLAlchemy defaults literal values to being bound parameters.

Did I overlook a detail from the software documentation?

> If you'd like it to say "1", then use having(func.count("*") >
> literal_column("1")).

[Mike Bayer]

On Thu, Apr 18, 2019 at 4:00 PM Markus Elfring <Markus....@web.de> wrote:
>
> > there's no technical reason the "1" needs to render inline
>
> How does this information fit to the code “having(func.count(Address.id) > 2)”
> from an example?
> https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.having

it will generate the same SQL and provide "2" for the bound value
instead of "1".

>
>
> > and SQLAlchemy defaults literal values to being bound parameters.
>
> Did I overlook a detail from the software documentation?

see here:

https://docs.sqlalchemy.org/en/13/core/tutorial.html#operators

>
> > If you'd like it to say "1", then use having(func.count("*") >
> > literal_column("1")).
>
> I have got the generated codes “count(:count_1) AS "C"” and “HAVING count(:count_2) > 1”
> then for my query approach.
> Which details should I adjust further?

I don't know what SQL you are attempting to render.


>
> Regards,
> Markus

[Markus Elfring]

> it will generate the same SQL and provide "2" for the bound value
> instead of "1".

Why is the expression “func.count(Address.id) > literal_column("2")”
not referenced there?

> I don't know what SQL you are attempting to render.

I would like to get an asterisk instead of the parameters “count_1” and “count_2”.

Regards,
Markus

[Mike Bayer]

On Thu, Apr 18, 2019 at 4:30 PM Markus Elfring <Markus....@web.de> wrote:
>
> > it will generate the same SQL and provide "2" for the bound value
> > instead of "1".
>
> Why is the expression “func.count(Address.id) > literal_column("2")”
> not referenced there?

there's no reason to refer to this, literal values should be passed as
bound parameters unless there is some reason they shouldn't.

>
>
> > I don't know what SQL you are attempting to render.
>
> I would like to get an asterisk instead of the parameters “count_1” and “count_2”.

again there is no reason for the asterisk to render in the text unless
you are using the text in some other way besides executing it. use
literal_column('*') if you prefer.

if you are trying to print your SQL to a file or use it for display
purposes, there are separate instructions for this at
https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined





>
> Regards,
> Markus

[Markus Elfring]

>> Why is the expression “func.count(Address.id) > literal_column("2")”
>> not referenced there?
>
> there's no reason to refer to this, literal values should be passed as
> bound parameters unless there is some reason they shouldn't.

Can this information be confusing?

> again there is no reason for the asterisk to render in the text unless
> you are using the text in some other way besides executing it.

Do we stumble on different expectations for the usage of bind parameters?
https://docs.sqlalchemy.org/en/13/core/tutorial.html#operators

Is there a need for an adjusted SQL coding style?

> use literal_column('*') if you prefer.

I would prefer to get my query to work also without this function call.
But it seems that your programming interface contains a requirement
for such a data conversion.

Did the Python decorator “compiles” take care of this detail?

Regards,
Markus

[Markus Elfring]

> class T2(Base):
> __table__ = Table("t2", Base.metadata, autoload_with=session.connection())
> __mapper_args__ = {
> "primary_key": [__table__.c.source_file] # a primary key must
> be determined
> }

I find this software design approach also interesting.

I have tried the following code variant out.

…
my_inspector = Inspector.from_engine(engine)
results = Table('t2', MetaData())
my_inspector.reflecttable(results, None)
entries = session.query(func.count("*")).select_from(results).scalar()

if entries > 0:
delimiter = "|"
sys.stdout.write(delimiter.join( ("statement1",
"statement2",
'"function name"',
'"source file"',
"incidence") ))
sys.stdout.write("\r\n")
for statement1, statement2, name, source_file, incidence \
in session.query(results.statement1,
results.statement2,
results.name,
results.source_file,
results.C).order_by(results.source_file,
results.name,
results.statement1,
results.statement2):
sys.stdout.write(delimiter.join( (statement1,
statement2,
name,
source_file,
str(incidence)) ))
sys.stdout.write("\r\n")
else:
…


Unfortunately, I stumble on another error message after a text line
is displayed in the expected way.

AttributeError: 'Table' object has no attribute 'statement1'


Which adjustments would be needed here?

Regards,
Markus

[Markus Elfring]

> class T2(Base):
> __table__ = Table("t2", Base.metadata, autoload_with=session.connection())
> __mapper_args__ = {
> "primary_key": [__table__.c.source_file] # a primary key must
> be determined
> }

A variant of this software design approach can work also for my data processing
needs to some degree.

Command example:
elfring@Sonne:~/Projekte/Linux/next-patched> time spatch --timeout 9 -D database_URL=postgresql+psycopg2:///serial_DVB_duplicates --dir drivers/media/dvb-frontends --sp-file ~/Projekte/Coccinelle/janitor/list_duplicate_statement_pairs_from_if_branches8.cocci > ~/Projekte/Bau/Linux/scripts/Coccinelle/duplicates1/next/20190418/pair-PG-serial-DVB-results.txt 2> ~/Projekte/Bau/Linux/scripts/Coccinelle/duplicates1/next/20190418/pair-PG-serial-DVB-errors.txt

real 1m54,399s
user 1m52,667s
sys 0m0,382s


Such a data analysis produces an usable result if it is performed
by a single process.
The applied software combination “Coccinelle 1.0.7-00186-g99e081e9 (OCaml 4.07.1)”
supports also parallel data processing by using background processes.
I observed that no data were imported then into a specified database table.

See also:
Checking import of code search results into a table by parallel SmPL data processing
https://systeme.lip6.fr/pipermail/cocci/2019-April/005774.html
https://lore.kernel.org/cocci/bed744fe-5c5c-cf28...@web.de/

The main developer for the semantic patch language does not like
to debug anything that involves external tools (including my application
of your class library) at the moment.
https://systeme.lip6.fr/pipermail/cocci/2019-April/005778.html
https://lore.kernel.org/cocci/alpine.DEB.2.21.1904220953540.3142@hadrien/

So I am looking for additional solution ideas from other information sources.
Where would the desired data get lost on the transmission to the database table
during usage of background process?

Regards,
Markus

[Markus Elfring]

> Where would the desired data get lost on the transmission to the database table
> during usage of background process?
>

> When using a background process you must first call engine.dispose()
> when first starting the process, then you start a new transaction
> and work from there. You cannot transfer the work of an ongoing transaction
> to another process , a new transaction must be used.

I admit that I did not take such a data processing requirement into account
so far for my recent developments of SmPL scripts.
It seems that I can not add customised process preparation code for the selected
execution environment which is supported by the current Coccinelle software.

See also:
Complete support for fork-join work flows
https://github.com/coccinelle/coccinelle/issues/50

Other software architectures can support parallelisation better, can't they?

Regards,
Markus

[Mike Bayer]

Hi 

Can you clarify what you hope to achieve when you continue to make statements of this form?  I don't find them to be very constructive.   There are many software architectures available for your use and you should choose the ones which work best for you.   

    

Regards,
Markus

[Markus Elfring]

>> Other software architectures can support parallelisation better, can't they?
>

> Can you clarify what you hope to achieve when you continue to make statements
> of this form? I don't find them to be very constructive.

I dared to point out that I stumbled on another software limitation.
Now I am also looking again for possible adjustments and extensions.

> There are many software architectures available for your use

I check a few of them once more.

> and you should choose the ones which work best for you.

I hope to achieve more helpful evolution for affected software areas.

Regards,
Markus

[Mike Bayer]

On Wed, Apr 24, 2019, 1:50 AM Markus Elfring <Markus....@web.de> wrote:

>>     Other software architectures can support parallelisation better, can't they?
>
> Can you clarify what you hope to achieve when you continue to make statements
> of this form?  I don't find them to be very constructive.

I dared to point out that I stumbled on another software limitation.
Now I am also looking again for possible adjustments and extensions.

Why not report these problems to the cochinelle tool which you are trying to integrate ?   

[Markus Elfring]

> Why not report these problems to the cochinelle tool
> which you are trying to integrate ?

I suggest to take another look at corresponding information sources.
https://systeme.lip6.fr/pipermail/cocci/2019-April/thread.html
https://github.com/coccinelle/coccinelle/issues

Regards,
Markus

[Mike Bayer]

wow, so I got off light then! good luck


>
> Regards,
> Markus