Re: [sqlalchemy] Modeling single FK to multiple tables

That would certainly work. :) Would that offer any benefits over pyodbc, since I wouldn't have the mapping (which was taking all the time I was spending with SA)? On 3/25/16, Mike Bayer <clas...@zzzcomputing.com> wrote: > > > On 03/25/2016 05:20 PM, Alex Hall wrote: >>

Re: [sqlalchemy] Modeling single FK to multiple tables

qlite db), then use automap to map classes to > those tables. > > Simon > > On Mon, Mar 21, 2016 at 3:12 PM, Alex Hall <ah...@autodist.com> wrote: > >> Wow, thanks guys, especially for the sample code! I'm trying to use >> the example (and fully understan

Re: [sqlalchemy] Modeling single FK to multiple tables

. On 3/17/16, Mike Bayer <clas...@zzzcomputing.com> wrote: > > > On 03/17/2016 03:11 PM, Alex Hall wrote: >> Hello all, >> It seems like I can't go a day without running into some kind of wall. >> This one is a conceptual one regarding foreign keys. I have to

Re: [sqlalchemy] Re: Outer joins?

That would be the simplest. Having something so inefficient just bugs me. :) I'm using MSSQL, so limit() works. Would yield_per() help here, or is that for something different? Even if it didn't help local memory, but just kept the load on the DB server down, that would be good. On 3/16/16,

[sqlalchemy] joins instead of filters remove attributes of results

Hello all, I'm running a different query than yesterday. Before, I had something like: items = session.query(itemTable, attachmentTable, attachmentTextTable, assignmentTable, attributeTable, attributeValueTable, attributeValueAssignmentTable, vendorTable)\ .filter(attachmentTable.itm_id ==

[sqlalchemy] Defining relationships (was: joins instead of filters remove attributes of results)

wrote: > On Wed, Mar 16, 2016 at 1:07 PM, Alex Hall <ah...@autodist.com> wrote: > >> Hello all, >> I'm running a different query than yesterday. Before, I had something >> like: >> >> items = session.query(itemTable, attachmentTable, attachmentTextTable, >>

Re: [sqlalchemy] Re: Outer joins?

> On Mar 16, 2016, at 03:23, Jonathan Vanasco wrote: > > The database design you have is less than perfect. I didn't make it, I came in long after it had been set up and now have to work with it. I can't re-do anything. They did it this way so that, for instance, a

Re: [sqlalchemy] Re: Outer joins?

Thanks guys. I'm using automap, but I'm not completely sure how much that gives me for free. Yes, these tables are big, and the resulting set would be worrying large (potentially 5*20, and that's without the attributes and attachments, plus their assignment and values tables). I've switched to

[sqlalchemy] Outer joins?

Hi all, I need to pull data from a bunch of tables, and I *think* outer joins are the way to do it. However, I can't find much on SA's support for outer joins. What I'm trying to do is pull all items from the Items table, as well as associated attachments and attributes if an item is tied to

Re: [sqlalchemy] Re: Adding 'where' to query

Thanks for the clarification. I'm suddenly getting no results at all when I add this filter, but at least now I know I'm doing the syntax right. Never a dull moment. :) On 3/14/16, Jonathan Vanasco wrote: > >> >> .filter(t1.c1=='hello', and_(t3.c1=='world')) >> > > The

[sqlalchemy] Re: Adding 'where' to query

, but filter is the obvious solution to most of my question. On 3/14/16, Alex Hall <ah...@autodist.com> wrote: > Hi all, > I had a link that was a great intro to querying, but of course, I > can't find it now. I need to add a couple conditions to my query. In > SQL,

[sqlalchemy] Adding 'where' to query

Hi all, I had a link that was a great intro to querying, but of course, I can't find it now. I need to add a couple conditions to my query. In SQL, it might look like this: select * from t1 join t2 on t1.c1==t2.c1 join t3 on t3.c1==t1.c1 where t1.c1 = 'hello' and t3.c3 = 'world' The joins I

Re: [sqlalchemy] reflection fails on table with name in all caps

lename__ = "VENDR" > PVVNNO = Column(String, primary_key=True) > > metadata.reflect(e, only=desiredTables, extend_existing=True) > assert 'VENDR' in metadata.tables > > base.prepare() > > assert VENDR.DATA > > sess = Session(e) > print sess.query(V

Re: [sqlalchemy] reflection fails on table with name in all caps

ey=True) > > __table_args__ = {"extend_existing": True} > > that tells reflection to add new data to this Table object even though > it already exists. > > > On 03/14/2016 09:24 AM, Alex Hall wrote: >> Thanks for that. Somehow, I'm getting the same error as before--th

Re: [sqlalchemy] reflection fails on table with name in all caps

d" / "Integer" combination above is just an example. > > Then do the automap as you've done. At the end, if it worked, > Base.classes.VENDR should be the same class as the VENDR class above. > > > On 03/11/2016 05:09 PM, Alex Hall wrote: >> Sorry, do you me

Re: [sqlalchemy] reflection fails on table with name in all caps

t; > On 03/11/2016 04:14 PM, Alex Hall wrote: >> Ah, you're right. Every other table I've used in this database has had >> a key, and I didn't even notice that this VENDR table lacks one. That >> explains the mystery! Thanks. >> >> Now to map this table. I've read

Re: [sqlalchemy] reflection fails on table with name in all caps

owever, I don't know if this would still work with automapping. On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote: > ah. does VENDR have a primary key? it won't be mapped if not. > > what's in base.classes.keys() ? base.classes['VENDR'] ? > > > > > > > On

Re: [sqlalchemy] reflection fails on table with name in all caps

> On 03/11/2016 12:09 PM, Alex Hall wrote: >> That's weird: the name I see is exactly what I've been using, "VENDR". >> All caps and everything. I tried using lowercase, just to see what it >> would do, but it failed. >> >> On 3/11/16, Mike Bayer <cl

Re: [sqlalchemy] reflection fails on table with name in all caps

That's weird: the name I see is exactly what I've been using, "VENDR". All caps and everything. I tried using lowercase, just to see what it would do, but it failed. On 3/11/16, Mike Bayer <clas...@zzzcomputing.com> wrote: > > > On 03/11/2016 09:39 AM, Alex Hall wrote:

[sqlalchemy] reflection fails on table with name in all caps

Hello list, Finally, a pure SA question from me. I'm using Automap and the "only" keyword to automap a subset of the tables in our CMS database. This has worked perfectly thus far. Now, though, it's failing on a specific table, and the only difference I can see is that this table's name is in all

Re: [sqlalchemy] Re: Ways of processing multiple rows with same ID?

What I'm doing, and sorry for not explaining further, is making a CSV file of data. Each row is a row in my results, or would be if I were just selecting from products. Having to select from attributes as well is where I'm having problems. Each product can have multiple attributes, and each

[sqlalchemy] Ways of processing multiple rows with same ID?

Hi list, I'm not sure how to explain this, so let me know if I lose you. I have the same products database as yesterday, but I've just learned that product attributes are stored in their own tables. A product can have many attributes (size, color, weight, etc), and each attribute value is in a

[sqlalchemy] Re: properties of query results if names overlap?

. On 3/9/16, Alex Hall <ah...@autodist.com> wrote: > Hi all, > Just a quick question: what does SA do if names overlap? For example, > in assignmentTable, there's a column called itm_id. In > attachmentTable, there's also a column called itm_id, and there's one > in itemTable

Re: [sqlalchemy] Re: Select * but apply distinct to one column

learned something from all this. Thanks again for the help, guys. On 3/9/16, Jonathan Vanasco <jonat...@findmeon.com> wrote: > > On Wednesday, March 9, 2016 at 3:02:05 PM UTC-5, Alex Hall wrote: >> >> Fair enough, thanks. I didn't realize it was such a complex task;

[sqlalchemy] properties of query results if names overlap?

Hi all, Just a quick question: what does SA do if names overlap? For example, in assignmentTable, there's a column called itm_id. In attachmentTable, there's also a column called itm_id, and there's one in itemTable as well. If I combine these in a kind of join, as in: results =

Re: [sqlalchemy] Re: Select * but apply distinct to one column

Fair enough, thanks. I didn't realize it was such a complex task; I figured it was just a matter of passing an argument to distinct() or something equally easy. Speed isn't a huge concern, so I suppose I could get around this by storing the item numbers I find and then checking that the row I'm

[sqlalchemy] Select * but apply distinct to one column

Hi all, I want to select * from a table, getting all columns. However, the only rows I want are where the item number is distinct. I've got: items = session.query(itemTable)\ .distinct()\ .limit(10) But that doesn't apply "distinct" to just item_number. I'm not the best with SQL in general or I'd

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

;dbms_ver" attribute on the > pyodbc connection that you are creating in your custom creator function. > This is where it gets used: > > https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/base.py#L481 > > Simon > > On Fri, Feb 19, 2016 at 5:00 PM, Alex Hall

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

URL as > "db2+pyodbc://". Does that make any difference? > > On Fri, Feb 19, 2016 at 4:20 PM, Alex Hall <ah...@autodist.com> wrote: > >> Thanks. I tried both, and triedother variations including or excluding >> the module name as a prefix (ibm_db_sa.db2.

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py > > I would guess that you want to end up with the DB2Dialect_pyodbc class, > which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc:// > > Simon > > > On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall <ah...@autodist.co

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

ish flashbacks to my "has no attribute" error last week for the same object. But at least this is a different one; I'll count it as a good thing! On 2/19/16, Simon King <si...@simonking.org.uk> wrote: > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall <ah...@autodist.com> wrote: &g

[sqlalchemy] connected using pyodbc; how to hook that to SA?

As the subject says, I am connected to our iSeries through straight pyodbc. That seems to run perfectly. Now, is there a way to use SA with that connection? When I use "ibm_db_sa+pyodbc://..." I get the exact same error I was getting when using ibm_db directly. Using pyodbc, I can specify the

[sqlalchemy] OT: basic ibm_db script hangs while connecting (wasreflection taking a very long time?)

ru...@konk.org> wrote: > On Tue, Feb 16, 2016 at 04:02:08PM -0500, Alex Hall wrote: >> Great; I was hoping you wouldn't say that. :) I've been through them >> many, many times, trying to get the connection working. I've gone from >> error to error, and thought I had

Re: [sqlalchemy] Re: reflection taking a very long time?

; On 02/16/2016 03:37 PM, Alex Hall wrote: >> I tried that, hoping for a bit more insight into the problem. However, >> unless I'm doing something wrong, I don't even get any queries. I get >> my own print statements, then the script tries to connect and hangs. >> I'v

Re: [sqlalchemy] Re: reflection taking a very long time?

, and sure enough, it hangs on that line. On 2/16/16, Mike Bayer <clas...@zzzcomputing.com> wrote: > turning on echo=True inside create_engine() will show you what queries > are emitted as they occur so you can see which ones are taking long > and/or hanging. > > > On 02/16/2016 0

[sqlalchemy] Re: reflection taking a very long time?

, the connection starts up and the script hangs. I'm no closer to solving this, and would love to hear anyone's thoughts, but at least I know that my thought of blaming reflect/automap is likely incorrect. On 2/16/16, Alex Hall <ah...@autodist.com> wrote: > Hi list, > Sorry for all the

[sqlalchemy] reflection taking a very long time?

Hi list, Sorry for all the emails. I've determined that my script is actually connecting to the 400's test database. At least, a print statement placed just after the create_engine call is printing, so I guess we're good there. What I'm running into now is unresponsiveness when I try to reflect

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

l knocking on its door. On 2/16/16, Michal Petrucha <michal.petru...@konk.org> wrote: > On Tue, Feb 16, 2016 at 10:27:40AM -0500, Alex Hall wrote: >> I have pyodbc 3.0.10, ibm_db_sa 0.3.2, and ibm_db 2.0.6. I'm also >> talking to people on the ibm_db list, and they s

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

he object in “connection.connection” is a > pyodbc.Connection and doesn’t have a “dbms_ver” attribute. > > Note that there are at least 3 packages that could be involved here: > > pyodbc (https://pypi.python.org/pypi/pyodbc) > > ibm_db (https://pypi.python.org/pypi/ibm_db/) > > i

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

what I can do about it, it looks like this dbms_ver property is definitely in the latest ibm_db_sa version. Am I getting this from the wrong place, or confusing this with a different package somehow? I *must* be missing something obvious. On 2/15/16, Alex Hall <ah...@autodist.com>

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

off track with this? On 2/15/16, Alex Hall <ah...@autodist.com> wrote: > Thanks guys. I've checked the version I'm using, and it reports that > ibm_db_sa.__version__ is '0.3.2'. I have both ibm_db_sa and ibm_db > installed. Should I remove ibm_db and rely only on ibm_db_sa instead? > I

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

y.a...@gmail.com> wrote: > Try to use ibm_db_sa 0.3.2 instead, apparently you are using the previous > version. dbms_ver is a feature specific of native ibm_db version of which > not available in pyodbc. > > https://pypi.python.org/pypi/ibm_db_sa/0.3.2 > > > Salam, > > -Jaimy

[sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

Hello list, I've configured a DSN to a test version of my work's AS400 and I seem to be able to connect just fine (Yes!) I'm now running into a problem when I try to ask for a list of all tables. The line is: dbInspector = inspect(dbEngine) The traceback is very long, and I can paste it if you

Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails

<michal.petru...@konk.org> wrote: > On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote: >> I've done more research on this topic. There's a lot out there about >> using MSSQL with SA, but next to nothing about using ibm_db_sa or >> specifying drivers. >&

Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails

nnect installed on your > machine. > > Salam, > > -Jaimy > On Feb 11, 2016 01:50, "Alex Hall" <ah...@autodist.com> wrote: > >> Hello list, >> I sent this to the ibm_db list yesterday, but no one has responded >> yet. Since it's as much ibm_db

Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails

o other options at the moment and I need to get this working soon. On 2/11/16, Alex Hall <ah...@autodist.com> wrote: > I think I'm confused. Isn't Pyodbc an alternative to SQLAlchemy? If > not, how would the two work together? I just looked through the > 'Getting Started' and 'API' d

[sqlalchemy] Connecting to AS400 with SQLAlchemy fails

Hello list, I sent this to the ibm_db list yesterday, but no one has responded yet. Since it's as much ibm_db as SA, I thought I'd try here as well in case any of you have used an AS400 before. I have ibm_db, ibm_db_sa, the latest sqlalchemy, and Python 2.7 (latest) installed. I can talk to SQLite

Re: [sqlalchemy] Recommended way to delete record given instance?

" ? > > Best regards, > Thierry > > > 2016-02-06 5:16 GMT+01:00 Alex Hall <ah...@autodist.com>: > >> Hello all, >> Another basic question: given an instance of a record, can I somehow >> delete the record from a table? The longer story is this. >&g

[sqlalchemy] Using ibm_db to talk to AS400?

Hi list, Thus far, I've been using very basic database actions on a local SQLite database as I've written the application. Now, though, I have my GUI able to drive all CRUD operations, and I'm nearing the time when I'll switch over to the actual system for which I've written the app: the company's

[sqlalchemy] Best practice for restricting input to columns?

Hi all, What's the recommended way to restrict input? For instance, I store a phone number as an integer, and I need it to have 7, 10, or 11 digits. In its getter, I format it so it looks good as a string, and in its setter, I take the string the user inputs, strip only the integers, and store

[sqlalchemy] Recommended way to delete record given instance?

day. Thanks! -- Alex Hall Automatic Distributors, IT Department ah...@autodist.com -- 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 sqlalchem

Re: [sqlalchemy] Modifying records across multiple modules?

le, or as well as? Where are you getting the > object that you are putting in the first element of each of the > self.choices list? > > Simon > > On Thu, Feb 4, 2016 at 11:34 AM, Alex Hall <ah...@autodist.com> wrote: > >> It's all from a GUI, so it's something like th

Re: [sqlalchemy] Itterating over database row?

4 Feb 2016, at 18:19, Alex Hall <ah...@autodist.com> wrote: >> >> Hello all, >> I'm setting my application up the way Simon suggested. I still use the >> table object so I can get its name for displaying in one list, but the >> other list (which holds the ac

Re: [sqlalchemy] Modifying records across multiple modules?

sion.query(someTable) returns glorified tuples that can't be updated > session.query(someMappedClass) returns instances of someMappedClass, that > *can* be updated. > > Hope that makes sense, > > Simon > > On Thu, Feb 4, 2016 at 2:07 PM, Alex Hall <ah...@autodist.com>

Re: [sqlalchemy] Modifying records across multiple modules?

(myEngine) #should create the empty tables, right? #main.py from DBDefinitions import * import DBInterface c1 = Customer(...) c2 = Customer(...) mt1 = MyOtherTable(...) if DBInterface.session.query(DBDefinitions.Customer).count == 0: DBInterface.session.add_all([c1, c2]) On 2/4/16, Alex Hall <

Re: [sqlalchemy] Modifying records across multiple modules?

returns all mapped classes, something like this: > > def getAllClasses(): > return base.__subclasses__() > > (If your class hierarchy is more complicated you'd need a more > sophisticated function there) > > Simon > > On Thu, Feb 4, 2016 at 12:32 PM, Alex Hall

[sqlalchemy] Itterating over database row?

Hello all, I'm setting my application up the way Simon suggested. I still use the table object so I can get its name for displaying in one list, but the other list (which holds the actual rows of the selected table) is using the relevant subclass of base. I use wx.ListCtrl to display everything,