Re: [sqlalchemy] Issue DELETE statement with LIMIT

Hi.  Can you do a sub query with the ones you want to delete selected (with limit) and then delete with an IN clause on the sub query items?Warwick A. PrinceMushroom Systems International Pty. Ltd.On 22 Sep 2023, at 9:16 pm, 'Grennith' via sqlalchemy wrote:Hi everyone,I'd like to issue a LIMIT

Re: [sqlalchemy] Using SQLAlchemy Core as Query Builder, not ORM, in CRUD applications

Hi Matthew It’s not an unusual approach from my standpoint. I use a combination of both ORM and Core. For my core uses, I wrote a couple of generic getter and setter methods, wrapping up a lot of boilerplate operations. In my case, it looks like this; result = DBGetColumns(‘tableName’,

Re: [sqlalchemy] MySQL Connector

Hi Mike Thanks very much. It’s a shame they broke it so badly! I’ll change to your recommendation immediately. Cheers Warwick Warwick A. Prince Mushroom Systems International Pty. Ltd. > On 5 Mar 2019, at 3:07 am, Mike Bayer wrote: > >> On Sun, Mar 3, 2019 at 8:40 PM Wa

[sqlalchemy] MySQL Connector

and performance. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince  phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com <http://www.mushroomsys.com/> -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

Re: [sqlalchemy] Session / declarative_base scope

PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > > On 09/29/2016 01:38 AM, Warwick Prince wrote: >> Hi Mike >> >> I would like a little insight into the session object, and the >> declarative_base class. >> >> I have a process ru

[sqlalchemy] Session / declarative_base scope

Hi Mike I would like a little insight into the session object, and the declarative_base class. I have a process running many threads, where each thread may be connected to potentially a different engine/database. If the database connection between 2 or more threads is the same, then they

[sqlalchemy] Abort Query

Hi If I execute a query, is there a way to abort that query and release the server before the query completes? e.g. theTable = Table(‘some_large_table’, metadata, autoload=True) query = theTable.select() results = query.execute().fetchall() Is there a way that perhaps another thread, if

Re: [sqlalchemy] multiple databases?

Hi Richard There are no problems connecting to multiple database sources in the one application - we do it all the time :-) Cheers Warwick On 10 Dec 2013, at 4:08 am, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all, i don't know if anyone have to go through this, but here's

[sqlalchemy] Help on Counting Please

Hi All Please excuse this relatively noob question, but I can not for the life of me find the answer in docs. (Probably because I don't know what I'm looking for). I have a table with two columns A and B. A can have many duplicate values. e.g. A B 1 a 1 b 1 c 1

Re: [sqlalchemy] Help on Counting Please

, Have you tried: select A, count(*) from the_table group by A On Wed, Oct 23, 2013 at 4:17 PM, Warwick Prince warwi...@mushroomsys.com wrote: Hi All Please excuse this relatively noob question, but I can not for the life of me find the answer in docs. (Probably because I don't know what

Re: [sqlalchemy] How-to filter by the time part of a datetime field?

Hi, I've a model with a DateTime column and I need to select rows where the time part of this column is between two hours (something like: select all rows where the date is between 6:00 and 11:00). I need the date information, rows must be deleted after a couple of days. I don't know

Re: [sqlalchemy] How-to filter by the time part of a datetime field?

Hi Warwick, On 30/08/2013 14:38, Warwick Prince wrote: I'm sure there is a better way, but you could always filter using a date/time and supply the date part as well (i.e. today) so that you are comparing datetime to datetime. (Something like: select all rows where the datetime

Re: [sqlalchemy] Reliable way to read comments from database schema

for, but it's an open item for now. The four-year-old ticket is http://www.sqlalchemy.org/trac/ticket/1546. On Jun 4, 2013, at 8:33 PM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I'm toying with the idea of embedding some metadata into the comments on columns

Re: [sqlalchemy] Error during roll back

/ticket/2389/sqlalchemy_rollback_bug.py On Mar 17, 2013, at 1:38 AM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I have some fairly basic code which is moving data from one DB to another. I have trapped errors on inserts just in case there were unexpected duplicates

[sqlalchemy] Error during roll back

Hi Michael I have some fairly basic code which is moving data from one DB to another. I have trapped errors on inserts just in case there were unexpected duplicates. When I go to commit this transaction, MySQL correctly throws an IntegrityError exception: Duplicate Entry which I trap, and

Re: [sqlalchemy] Re: Inserting Entry Fastest way

Thanks Russ - took a look and found it very interesting indeed. Cheers Warwick On Monday, March 11, 2013 4:56:11 PM UTC-4, Arkilic, Arman wrote: Hi, I am working on a database design that I am required to use lots of tables with one-to-many relationship. As a consequence of the design, I

Re: [sqlalchemy] MySQL has gone away

On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read

Re: [sqlalchemy] NOT LIKE

was expecting to have to use lower level constructs - I'm pleasantly surprised. Thanks for your seemingly infinite patients! On Aug 22, 2012, at 11:38 PM, Warwick Prince wrote: Thanks Michael I struggle sometimes to find examples of the simple things, so eventually searched out the like_op

[sqlalchemy] NOT LIKE

Hi When creating a basic query, how does one code a NOT LIKE using SA? I can do this; query = table.select().where(like_op(table.c.name, 'fred%')) I can not find a NOT LIKE operator. The ones there notlike_op and notilike_op raise NotImplemented. I've placed it in as text('%s NOT LIKE %s')

Re: [sqlalchemy] NOT LIKE

why the like_op and nolike_op have come into your normal vocabulary here as they are usually just the ops used internally. LIKE is column.like(other) and NOT LIKE is ~column.like(other). On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote: Hi When creating a basic query, how does one

[sqlalchemy] PG error I don't understand.

Hi I have created a simple update like this on a PG database via PG8000; table=Table('invoice_line_items', meta, autoload=True) query = table.update() query = query.where(and_(eq(table.c.InvBook, 'SC'), eq(table.c.InvNum, 12862), eq(table.c.InvLine, 1))) query = query.values(**data)

Re: [sqlalchemy] Determining sqa type from dialect type

On Aug 10, 2012, at 5:19 AM, Warwick Prince wrote: Hi All If I have a Column() object, is there a way of determining the sqlalchemy type from the dialect specific type? e.g. I have a Postgres TIMESTAMP column, and I want to be able to map that back the a sqa DateTime type

Re: [sqlalchemy] Postgres migration issue

Hi David Thanks for that - much appreciated :-) Hi I usually use MySQL to develop on, however I need to work with Postgres for the first time today so I fired it up. I have a routine which converts a non-SQL database into the database of choice, converting its schema into a new

[sqlalchemy] Determining sqa type from dialect type

DATETIME not sqa DateTime as the basis of the column. Effectively, I want to reverse engineer the creation of the column. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com

Re: [sqlalchemy] Determining sqa type from dialect type

Hi Michel Thanks! I knew it was in there somewhere! :-) Cheers Warwick Hi All If I have a Column() object, is there a way of determining the sqlalchemy type from the dialect specific type? e.g. I have a Postgres TIMESTAMP column, and I want to be able to map that back the a sqa

[sqlalchemy] Postgres migration issue

Hi I usually use MySQL to develop on, however I need to work with Postgres for the first time today so I fired it up. I have a routine which converts a non-SQL database into the database of choice, converting its schema into a new table in the target database using SQA, and then copies all

[sqlalchemy] Re: Postgres migration issue

Hi I usually use MySQL to develop on, however I need to work with Postgres for the first time today so I fired it up. I have a routine which converts a non-SQL database into the database of choice, converting its schema into a new table in the target database using SQA, and then copies

[sqlalchemy] Performance Mystery

Hi All This is addressed to anyone who may be able to shed some light on this strange behaviour; I'm running MySQL on Windows 2003 Server (Sorry) and have a table that has ~2M rows of sales data in it. Knowing the type of BI queries I would want to be doing, I have added indexes where I

Re: [sqlalchemy] PostgreSQL 9.1 on the horizon, cool new stuff

Thanks for the 'heads-up' Eric :-) ! Nothing to see here, move right along ! Except... Couple of interesting additions coming up in PostgreSQL 9.1 (still in beta) for anyone who's interested. Release notes: http://developer.postgresql.org/pgdocs/postgres/release-9-1.html A couple of

[sqlalchemy] Removing a session (Really)

Hi Michael I'm having an issue with memory usage that I would appreciate some insight.. I have a fairly straight forward process, that works perfectly as far as it delivering the desired updates in the DB etc, however, it accumulates memory usage (just like a leak) and I can not find a way to

Re: [sqlalchemy] Removing a session (Really)

Excellent - thanks :-) Warwick On 27/06/2011, at 2:37 AM, Michael Bayer wrote: On Jun 26, 2011, at 4:31 AM, Warwick Prince wrote: Hi Michael I'm having an issue with memory usage that I would appreciate some insight.. I have a fairly straight forward process, that works perfectly

[sqlalchemy] ODBC general question

Hi All We need to connect to a Progress database, and we are in the very early days of this. I understand it supports an ODBC interface and therefore should be able to be connected to using SA - correct? Are there any limitations on the ODBC connector or gotcha's that we should look out for?

Re: [sqlalchemy] ODBC general question

to be used. On Jan 18, 2011, at 6:51 PM, Warwick Prince wrote: Hi All We need to connect to a Progress database, and we are in the very early days of this. I understand it supports an ODBC interface and therefore should be able to be connected to using SA - correct? Are there any

Re: [sqlalchemy] flush() issued, SQL seen, but database not updated?

Hi Jerry Looks to me like you will need to swap your .flush for a .commit.commit will flush for you, then actually commit the changes to the DB. :-) Cheers Warwick Hi, I have been pulling my hair the whole day today: I have a web application that runs fine, however, during unittest I

Re: [sqlalchemy] Char encoding..

, the issue is related to storing a unicode value into a BLOB. Surely I can store anything in a BLOB, or am I missing something? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web

[sqlalchemy] Char encoding..

Hi All I thought I had Character Encoding licked, but I've hit something I can't work through. Any help appreciated. I have a legacy non SQL database that I read legacy data from (using cool Python code that emulates the old ISDB binary comms) and it reads a str which has Foreign language

[sqlalchemy] Multiple request in the same controller causing problems with SQLAlchemy Session object

find, as it is a very annoying issue for us as well. BTW: What connector are you using? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 10/11/2010, at 7:56 AM

Re: [sqlalchemy] Connection / disconnect / Pool

PM, Warwick Prince wrote: Hi Michael I have an issue I can't fathom regarding Pools.I'm doing testing and hit the following error at exatly the same point each time; File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\pyDAP\DAPForm.py, line 3805, in _processQuery

[sqlalchemy] Connection / disconnect / Pool

fine (i.e. no errors or exceptions) until I reach the 10 overflow limit and then it dies. Each destroy of an engine and recreate of an engine is at least a second apart so it's not being thrashed at all. What am I missing? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026

[sqlalchemy] Secialists question: how to do implement stock-management

- see my Skype id below. Cheers Warwick Warwick Prince CEO mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 29/10/2010, at 8:12 PM, Dan @ Austria wrote: Hi, i have a question to database/design specialist

[sqlalchemy] Result of a table.update()

tried putting bad data in the whereClause and it simply did nothing to the database, but my resultproxy was the same. No Exceptions raised in either case? Please enlighten me.. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102

Re: [sqlalchemy] Result of a table.update()

RETURNING was used to return columns from those rows that were updated. When an UPDATE or DELETE is emitted, result.rowcount contains the number of rows that were matched by the statement's criterion. On Oct 21, 2010, at 2:06 AM, Warwick Prince wrote: Hi All I'm using 0.6.4 under Windoze

Re: [sqlalchemy] Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away')

HiTimmy What OS are you running under for each? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 15/10/2010, at 7:48 AM, Timmy Chan wrote: sorry

[sqlalchemy] Session problems

Dear All I'm having a very strange issue with Sessions that I'm hoping someone can guide me on; I have a situation where a large body of code spawns new processes (multiprocessing.process). The core design manages 3 (possible) database connections, and I've used a unique session for each. The

[sqlalchemy] Session problems

don't bind a session to any engine.. Does it then follow the bind on the mapped table class for a given query? Cheers Warwick On 6 October 2010 20:02, Warwick Prince warwi...@mushroomsys.com wrote: Hi Chris It's simply trapped as a except Exception as message: I'll see what I can do - Just

Re: [sqlalchemy] Session problems

Michael to your invaluable insights into the workings of SA, as it was only a few minutes after reading your response that the answer was obvious. :-) Cheers Warwick On 7 October 2010 00:20, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote: I can't

[sqlalchemy] Session problems

of commit/roll back, then dropping the session and exiting some time later -- Is my interpretation of session use correct? I'll play with other avenues of investigation before bringing the current session issues to the table. ;-) Cheers Warwick On Oct 6, 2010, at 6:46 AM, Warwick Prince

[sqlalchemy] Simple Join failing

Hi All I have what I hope is a very simple question; Just started experimenting with joins, so I tried a very basic test and got a fail that I don't understand. It appears that SA is creating bad SQL, but I'm sure it's something I'm missing.. Here's what I did; I have two tables. products

Re: [sqlalchemy] Batch Delete with ORM

Hi All Just my 2c; The original question was why is SA doing the select before it does the delete? and then the comment was added that he would have done a simple count instead.. It appears that he was not aware that the DELETE could return the count as well (indirectly) so in actual fact,

Re: [sqlalchemy] Simple Join failing

simon.k...@motorola.com wrote: Warwick Prince wrote: Hi All I have what I hope is a very simple question; Just started experimenting with joins, so I tried a very basic test and got a fail that I don't understand. It appears that SA is creating bad SQL, but I'm sure it's something

Re: [sqlalchemy] Simple Join failing

at the docs I can now clearly see TABLE.join T A B L E.join, not query.join...I'm SURE that wasn't there before ;-D Thanks again. Cheers Warwick On 5 October 2010 20:39, King Simon-NFHD78 simon.k...@motorola.com wrote: Warwick Prince wrote: Hi Simon Thanks for that - I knew

[sqlalchemy] Locking strategy

Hi All I would like some advice / best practice on the following basic problem please. I'm new to SQL so am groping with some things that used to be basic. :-( I've looked around in SA and have only found a few small notes on locking. There is a for_update but when I try this it appears to be

[sqlalchemy] SQL / SQLAlchemy'centric approach to problem

that would produce the results 1, 3, 5, 7 and then I could count that and that would be my position, but it's all sounding a little amateurish to me. Any advice would be most appreciated. :-) Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince

Re: [sqlalchemy] Sudden Connection issues

.. :-) Cheers Warwick On Aug 27, 2010, at 4:39 AM, Warwick Prince wrote: Hi Michael OK, I've invested (wasted?) my entire day on this connection issue and have the following to report. Hopefully, the hints I've managed to find may trigger something with you that will point me in the right

Re: [sqlalchemy] Sudden Connection issues

be, where is it set, how can it be NOT set etc.Your help would be most appreciated. Incidentally, all was not a waste of time as I traversed nearly ALL of the SA code today and picked up a few nice tips.. Thanks! :-) Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992

Re: [sqlalchemy] Sudden Connection issues

Hi Michael Sorry about that - it had been a long day and I didn't realise I was quoting code from the connector! I'll get onto them and see if they can help. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax

[sqlalchemy] Sudden Connection issues

Hi All This is my first post here, so I wish it were a little more spectacular.. :-) I have been working happily with SA 0.6.x on Windows 2003 server with MySQL and the Sun Python Connector. I have an identical config running (and working) on XP. I was testing my code which had been working

Re: [sqlalchemy] Sudden Connection issues

is..). :-) I'll let you know how I go. When I get it going again, I'll hit you with the REAL questions I have.. Cheers Warwick On 26/08/2010, at 12:43 PM, Michael Bayer wrote: On Aug 25, 2010, at 10:11 PM, Warwick Prince wrote: Hi All This is my first post here, so I wish it were a little