Re: [sqlalchemy] Checking approaches around parallel data import for records

> 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 --

Re: [sqlalchemy] Checking approaches around parallel data import for records

>> 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

Re: [sqlalchemy] Checking approaches around parallel data import for records

> 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.

Re: [sqlalchemy] Checking approaches around parallel data import for records

> 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

Re: [sqlalchemy] Checking approaches around database introspection

> 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

Re: [sqlalchemy] Checking the generation of query parameters for record counting

>> 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

Re: [sqlalchemy] Checking the generation of query parameters for record counting

> 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

Re: [sqlalchemy] Checking the generation of query parameters for record counting

> 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

Re: [sqlalchemy] Checking the generation of query parameters for record counting

> 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

Re: [sqlalchemy] Checking the generation of query parameters for record counting

> 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

Re: [sqlalchemy] Checking the generation of query parameters for record counting

> 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,

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

> 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

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

>> 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

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

> > 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

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

> > 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

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

> > 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

[sqlalchemy] Creating database tables from queries based on ORM API?

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

Re: [sqlalchemy] Determination of string lengths

Thanks for the link to information I was looking for: http://stackoverflow.com/questions/15743121/how-to-filter-in-sqlalchemy-by-string-length#answer-15743220 Why is the method "length" not mentioned in the documentation chapter "SQL and Generic Functions" so far?

Re: [sqlalchemy] Determination of string lengths

> As stated in the docs: > "Note that any name not known to func generates the function name as is > - there is no restriction on what SQL functions can be called, > known or unknown to SQLAlchemy, built-in or user defined." I get an error message like "Function len(text) does not exist." if I

Re: [sqlalchemy] Determination of string lengths

> I don't know which database you are using, > but in postgresql there certainly is a function called "length" How do you think about to avoid the direct reuse of functions which are specific for a few database software implementations? Is there any more portability possible around string

[sqlalchemy] How to refer to fields in nested queries?

Hello, I would like to compute a few fields in a query and then apply aggregate functions on corresponding results. I imagine that I would need a subquery (or a specific view) for this use case. How should such a query structure be expressed by interfaces of the software "SQLAlchemy"? Which

[sqlalchemy] Re: How to refer to fields in nested queries?

> If you are using a subquery for additional processing or joins, > you can use `label()` to specify the name for the computed column, > then access that column as an attribute on the `.c` columns attribute of the > subquery object. Thanks for your information. > inner_query =

[sqlalchemy] Support for SQL Merge (or Upsert?)

Hello, I would like to perform a specific data management task which will affect a combination of checks for the operations "INSERT" and "UPDATE". Such a functionality became part of the SQL standard as the operation "MERGE". https://en.wikipedia.org/wiki/Merge_%28SQL%29 It seems that the

[sqlalchemy] Re: Selection of sort directions

> Odd- this isn't the narrative docs Thanks for your clarification. > You can use column attributes... > (http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc) Would it help to add a few more descriptions (and links) to the section for the

[sqlalchemy] Selection of sort directions

Hello, The software "SQLAlchemy" supports also the handling of the SQL clause "ORDER BY" to some degree. http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.order_by Now I am looking for the corresponding description of details for the specification of the desired

[sqlalchemy] Determination of string lengths

Hello, I would like to reuse the standard function "len" for the determination of string lengths from specific database fields in a query. http://www.dailyfreecode.com/code/len-function-296.aspx Which interface does provide this functionality for the software "SQLAlchemy"? Regards, Markus --

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

If the table structure/name is known and expected to be used -- there's not really a good reason to defer creating it . Is the reason good enough to avoid the repeated specification of corresponding meta-data? Is it safer to maintain and manage column attributes for some tables only at a

Re: [sqlalchemy] SQL join between two tables from two databases

I am trying to do a join between two tables, each residing on a separate databases. Would you like to consider another software design option? * Do you know if any special connectors or data source adaptors are available for your database software implementations? * Can one of them be

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

How are you currently specifying the meta-data? Should the Python class be sufficient for the definition of a table structure? Will the mapping interface work also without tables that were created by other SQL scripts before? Regards, Markus -- You received this message because you are

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

Should the Python class be sufficient for the definition of a table structure? If you're using the declarative syntax, yes. Thanks for your acknowledgement. It's common to have a `models.py` file that simply defines the classes in one place; then that is imported and metadata

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

I wonder why you’re asking of the SQLAlchemy list about a specific developmental goal of the Postgresql project? I hoped that some more corresponding experiences could already be shared here. Wouldn’t you ask them about this? That might follow ... How should I add the parameter IF NOT

[sqlalchemy] Handling of differences between a table and its mapped class

Hello, What will (or should) happen if the column attributes which are specified by a class that is derived from declarative_base() differ (e. g. an other default value) from the settings of an existing database table? Is it occasionally appropriate to use different column properties by the

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

I would expect that database implementations will provide functionality for parallel updates including concurrent creation of each table. What would you expect a database to do if it receives 2 CREATE TABLE my_table(...) instructions simultaneously? This depends on the passed parameters.

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

The parameter IF NOT EXISTS could be passed to the SQL statement CREATE TABLE. * Is this setting already used by the class library SQLAlchemy 0.9.8-78.1? From a quick scan of the docs it appears not. How can parameter additions be achieved for this software? Are you asking if IF NOT

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

Using IF NOT EXISTS would not solve this problem in a high concurrency scenario. Thanks for your feedback. There would still be a race condition within the Postgres internal functions. Are there any chances that this database software implementation will become robust and safe against the

[sqlalchemy] Difficulties with parallel data insertion into the same table

Hello, I try to write some data from a source code analysis which is performed by a few processor cores simultaneously into a single table. Now I stumble on a message like the following again. … sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

So there is no valid use case for parallel generation of tables except in particular kinds of multi-tenancy situations. I find my use case simple enough (and therefore very valid). I am going to manage two database tables by corresponding Python classes with SQLAlchemy services. I would

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

I would appreciate if I can fill these data structures in parallel without a serial database preparation step (table creation with repeated meta-data specification). You’d need to implement checks for this concurrency. I would expect that database implementations will provide functionality

Re: [sqlalchemy] Handling unique key violations with bulk inserts of large number of rows

If I'm doing a bulk insert of a very large number of rows is it possible to add only the ones that don't violate unique constraints and log the rest? Yes. - But such functionality depends on some design details. Would you like to invest any software development efforts to get the involved

[sqlalchemy] ORM code example for a query of a single table

Hello, I would like to query a single table by the ORM interface for an export of the stored data into an other file format. The description of the query API refers also to the object relational tutorial. This tutorial explains also functionality which deals with table creation. I imagine that I

Re: [sqlalchemy] Checking the handling of unique keys/indexes

SQLAlchemy sends to the log the statement and parameters it is to send to the DBAPI cursor.execute() method, *before* it actually does so. This so that if the DBAPI throws an exception, as is the case here, one can see what instructions were sent to it which were the immediate cause of this

Re: [sqlalchemy] Checking the handling of unique keys/indexes

Should I get the exception sqlalchemy.exc.IntegrityError directly after I attempted to insert a second record set with unique attributes into a SQLite table? I don’t have a stack trace here to see what the nature of the issue is but it is likely that the INSERT is proceeding using DBAPI

Re: [sqlalchemy] Checking the handling of unique keys/indexes

There is no way to use the second form while being able to record the moment each parameter set is used, unless the DBAPI itself provides additional hooks for logging at this level. However, this logging would defeat some of the purpose of executemany(), which is that of processing many

[sqlalchemy] Checking the handling of unique keys/indexes

Hello, I am using source code like the following in one of my scripts where I am trying the software packages SQLAlchemy 0.9.7-77.1 and SQLite 3.8.7.2-1.1 out on my openSUSE system. ... engine = create_engine(sqlite:///:memory:, echo = False) base = declarative_base() class position(base):

[sqlalchemy] Setting column properties

Hello, I created another class after reading the object relational tutorial. Now I would like to apply more fine-tuning for corresponding attributes. How can I specify a default value for a column there? Is more documentation available for the class Column? Regards, Markus -- You received

Re: [sqlalchemy] Re: SQLAlchemy: Table creation before data insertion?

take your Base object and Base.metadata.create_all(engine) Thanks for your helpful advice. I have integrated it into a small script which I published a moment ago together with a result from static source code analysis. http://article.gmane.org/gmane.linux.kernel/1852033