[3] | 1 | """ |
---|
| 2 | Introduction |
---|
| 3 | ============ |
---|
| 4 | |
---|
| 5 | SqlSoup provides a convenient way to access database tables without |
---|
| 6 | having to declare table or mapper classes ahead of time. |
---|
| 7 | |
---|
| 8 | Suppose we have a database with users, books, and loans tables |
---|
| 9 | (corresponding to the PyWebOff dataset, if you're curious). For |
---|
| 10 | testing purposes, we'll create this db as follows:: |
---|
| 11 | |
---|
| 12 | >>> from sqlalchemy import create_engine |
---|
| 13 | >>> e = create_engine('sqlite:///:memory:') |
---|
| 14 | >>> for sql in _testsql: e.execute(sql) #doctest: +ELLIPSIS |
---|
| 15 | <... |
---|
| 16 | |
---|
| 17 | Creating a SqlSoup gateway is just like creating an SQLAlchemy |
---|
| 18 | engine:: |
---|
| 19 | |
---|
| 20 | >>> from sqlalchemy.ext.sqlsoup import SqlSoup |
---|
| 21 | >>> db = SqlSoup('sqlite:///:memory:') |
---|
| 22 | |
---|
| 23 | or, you can re-use an existing metadata or engine:: |
---|
| 24 | |
---|
| 25 | >>> db = SqlSoup(MetaData(e)) |
---|
| 26 | |
---|
| 27 | You can optionally specify a schema within the database for your |
---|
| 28 | SqlSoup:: |
---|
| 29 | |
---|
| 30 | # >>> db.schema = myschemaname |
---|
| 31 | |
---|
| 32 | |
---|
| 33 | Loading objects |
---|
| 34 | =============== |
---|
| 35 | |
---|
| 36 | Loading objects is as easy as this:: |
---|
| 37 | |
---|
| 38 | >>> users = db.users.all() |
---|
| 39 | >>> users.sort() |
---|
| 40 | >>> users |
---|
| 41 | [MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0), MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)] |
---|
| 42 | |
---|
| 43 | Of course, letting the database do the sort is better:: |
---|
| 44 | |
---|
| 45 | >>> db.users.order_by(db.users.name).all() |
---|
| 46 | [MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1), MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0)] |
---|
| 47 | |
---|
| 48 | Field access is intuitive:: |
---|
| 49 | |
---|
| 50 | >>> users[0].email |
---|
| 51 | u'student@example.edu' |
---|
| 52 | |
---|
| 53 | Of course, you don't want to load all users very often. Let's add a |
---|
| 54 | WHERE clause. Let's also switch the order_by to DESC while we're at |
---|
| 55 | it:: |
---|
| 56 | |
---|
| 57 | >>> from sqlalchemy import or_, and_, desc |
---|
| 58 | >>> where = or_(db.users.name=='Bhargan Basepair', db.users.email=='student@example.edu') |
---|
| 59 | >>> db.users.filter(where).order_by(desc(db.users.name)).all() |
---|
| 60 | [MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0), MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)] |
---|
| 61 | |
---|
| 62 | You can also use .first() (to retrieve only the first object from a query) or |
---|
| 63 | .one() (like .first when you expect exactly one user -- it will raise an |
---|
| 64 | exception if more were returned):: |
---|
| 65 | |
---|
| 66 | >>> db.users.filter(db.users.name=='Bhargan Basepair').one() |
---|
| 67 | MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1) |
---|
| 68 | |
---|
| 69 | Since name is the primary key, this is equivalent to |
---|
| 70 | |
---|
| 71 | >>> db.users.get('Bhargan Basepair') |
---|
| 72 | MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1) |
---|
| 73 | |
---|
| 74 | This is also equivalent to |
---|
| 75 | |
---|
| 76 | >>> db.users.filter_by(name='Bhargan Basepair').one() |
---|
| 77 | MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1) |
---|
| 78 | |
---|
| 79 | filter_by is like filter, but takes kwargs instead of full clause expressions. |
---|
| 80 | This makes it more concise for simple queries like this, but you can't do |
---|
| 81 | complex queries like the or\_ above or non-equality based comparisons this way. |
---|
| 82 | |
---|
| 83 | Full query documentation |
---|
| 84 | ------------------------ |
---|
| 85 | |
---|
| 86 | Get, filter, filter_by, order_by, limit, and the rest of the |
---|
| 87 | query methods are explained in detail in the `SQLAlchemy documentation`__. |
---|
| 88 | |
---|
| 89 | __ http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying |
---|
| 90 | |
---|
| 91 | |
---|
| 92 | Modifying objects |
---|
| 93 | ================= |
---|
| 94 | |
---|
| 95 | Modifying objects is intuitive:: |
---|
| 96 | |
---|
| 97 | >>> user = _ |
---|
| 98 | >>> user.email = 'basepair+nospam@example.edu' |
---|
| 99 | >>> db.flush() |
---|
| 100 | |
---|
| 101 | (SqlSoup leverages the sophisticated SQLAlchemy unit-of-work code, so |
---|
| 102 | multiple updates to a single object will be turned into a single |
---|
| 103 | ``UPDATE`` statement when you flush.) |
---|
| 104 | |
---|
| 105 | To finish covering the basics, let's insert a new loan, then delete |
---|
| 106 | it:: |
---|
| 107 | |
---|
| 108 | >>> book_id = db.books.filter_by(title='Regional Variation in Moss').first().id |
---|
| 109 | >>> db.loans.insert(book_id=book_id, user_name=user.name) |
---|
| 110 | MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None) |
---|
| 111 | >>> db.flush() |
---|
| 112 | |
---|
| 113 | >>> loan = db.loans.filter_by(book_id=2, user_name='Bhargan Basepair').one() |
---|
| 114 | >>> db.delete(loan) |
---|
| 115 | >>> db.flush() |
---|
| 116 | |
---|
| 117 | You can also delete rows that have not been loaded as objects. Let's |
---|
| 118 | do our insert/delete cycle once more, this time using the loans |
---|
| 119 | table's delete method. (For SQLAlchemy experts: note that no flush() |
---|
| 120 | call is required since this delete acts at the SQL level, not at the |
---|
| 121 | Mapper level.) The same where-clause construction rules apply here as |
---|
| 122 | to the select methods. |
---|
| 123 | |
---|
| 124 | :: |
---|
| 125 | |
---|
| 126 | >>> db.loans.insert(book_id=book_id, user_name=user.name) |
---|
| 127 | MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None) |
---|
| 128 | >>> db.flush() |
---|
| 129 | >>> db.loans.delete(db.loans.book_id==2) |
---|
| 130 | |
---|
| 131 | You can similarly update multiple rows at once. This will change the |
---|
| 132 | book_id to 1 in all loans whose book_id is 2:: |
---|
| 133 | |
---|
| 134 | >>> db.loans.update(db.loans.book_id==2, book_id=1) |
---|
| 135 | >>> db.loans.filter_by(book_id=1).all() |
---|
| 136 | [MappedLoans(book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))] |
---|
| 137 | |
---|
| 138 | |
---|
| 139 | Joins |
---|
| 140 | ===== |
---|
| 141 | |
---|
| 142 | Occasionally, you will want to pull out a lot of data from related |
---|
| 143 | tables all at once. In this situation, it is far more efficient to |
---|
| 144 | have the database perform the necessary join. (Here we do not have *a |
---|
| 145 | lot of data* but hopefully the concept is still clear.) SQLAlchemy is |
---|
| 146 | smart enough to recognize that loans has a foreign key to users, and |
---|
| 147 | uses that as the join condition automatically. |
---|
| 148 | |
---|
| 149 | :: |
---|
| 150 | |
---|
| 151 | >>> join1 = db.join(db.users, db.loans, isouter=True) |
---|
| 152 | >>> join1.filter_by(name='Joe Student').all() |
---|
| 153 | [MappedJoin(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0,book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))] |
---|
| 154 | |
---|
| 155 | If you're unfortunate enough to be using MySQL with the default MyISAM |
---|
| 156 | storage engine, you'll have to specify the join condition manually, |
---|
| 157 | since MyISAM does not store foreign keys. Here's the same join again, |
---|
| 158 | with the join condition explicitly specified:: |
---|
| 159 | |
---|
| 160 | >>> db.join(db.users, db.loans, db.users.name==db.loans.user_name, isouter=True) |
---|
| 161 | <class 'sqlalchemy.ext.sqlsoup.MappedJoin'> |
---|
| 162 | |
---|
| 163 | You can compose arbitrarily complex joins by combining Join objects |
---|
| 164 | with tables or other joins. Here we combine our first join with the |
---|
| 165 | books table:: |
---|
| 166 | |
---|
| 167 | >>> join2 = db.join(join1, db.books) |
---|
| 168 | >>> join2.all() |
---|
| 169 | [MappedJoin(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0,book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0),id=1,title=u'Mustards I Have Known',published_year=u'1989',authors=u'Jones')] |
---|
| 170 | |
---|
| 171 | If you join tables that have an identical column name, wrap your join |
---|
| 172 | with `with_labels`, to disambiguate columns with their table name |
---|
| 173 | (.c is short for .columns):: |
---|
| 174 | |
---|
| 175 | >>> db.with_labels(join1).c.keys() |
---|
| 176 | [u'users_name', u'users_email', u'users_password', u'users_classname', u'users_admin', u'loans_book_id', u'loans_user_name', u'loans_loan_date'] |
---|
| 177 | |
---|
| 178 | You can also join directly to a labeled object:: |
---|
| 179 | |
---|
| 180 | >>> labeled_loans = db.with_labels(db.loans) |
---|
| 181 | >>> db.join(db.users, labeled_loans, isouter=True).c.keys() |
---|
| 182 | [u'name', u'email', u'password', u'classname', u'admin', u'loans_book_id', u'loans_user_name', u'loans_loan_date'] |
---|
| 183 | |
---|
| 184 | |
---|
| 185 | Relations |
---|
| 186 | ========= |
---|
| 187 | |
---|
| 188 | You can define relations on SqlSoup classes: |
---|
| 189 | |
---|
| 190 | >>> db.users.relate('loans', db.loans) |
---|
| 191 | |
---|
| 192 | These can then be used like a normal SA property: |
---|
| 193 | |
---|
| 194 | >>> db.users.get('Joe Student').loans |
---|
| 195 | [MappedLoans(book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))] |
---|
| 196 | |
---|
| 197 | >>> db.users.filter(~db.users.loans.any()).all() |
---|
| 198 | [MappedUsers(name=u'Bhargan Basepair',email='basepair+nospam@example.edu',password=u'basepair',classname=None,admin=1)] |
---|
| 199 | |
---|
| 200 | |
---|
| 201 | relate can take any options that the relation function accepts in normal mapper definition: |
---|
| 202 | |
---|
| 203 | >>> del db._cache['users'] |
---|
| 204 | >>> db.users.relate('loans', db.loans, order_by=db.loans.loan_date, cascade='all, delete-orphan') |
---|
| 205 | |
---|
| 206 | |
---|
| 207 | Advanced Use |
---|
| 208 | ============ |
---|
| 209 | |
---|
| 210 | Accessing the Session |
---|
| 211 | --------------------- |
---|
| 212 | |
---|
| 213 | SqlSoup uses a ScopedSession to provide thread-local sessions. You |
---|
| 214 | can get a reference to the current one like this:: |
---|
| 215 | |
---|
| 216 | >>> from sqlalchemy.ext.sqlsoup import Session |
---|
| 217 | >>> session = Session() |
---|
| 218 | |
---|
| 219 | Now you have access to all the standard session-based SA features, |
---|
| 220 | such as transactions. (SqlSoup's ``flush()`` is normally |
---|
| 221 | transactionalized, but you can perform manual transaction management |
---|
| 222 | if you need a transaction to span multiple flushes.) |
---|
| 223 | |
---|
| 224 | |
---|
| 225 | Mapping arbitrary Selectables |
---|
| 226 | ----------------------------- |
---|
| 227 | |
---|
| 228 | SqlSoup can map any SQLAlchemy ``Selectable`` with the map |
---|
| 229 | method. Let's map a ``Select`` object that uses an aggregate function; |
---|
| 230 | we'll use the SQLAlchemy ``Table`` that SqlSoup introspected as the |
---|
| 231 | basis. (Since we're not mapping to a simple table or join, we need to |
---|
| 232 | tell SQLAlchemy how to find the *primary key* which just needs to be |
---|
| 233 | unique within the select, and not necessarily correspond to a *real* |
---|
| 234 | PK in the database.) |
---|
| 235 | |
---|
| 236 | :: |
---|
| 237 | |
---|
| 238 | >>> from sqlalchemy import select, func |
---|
| 239 | >>> b = db.books._table |
---|
| 240 | >>> s = select([b.c.published_year, func.count('*').label('n')], from_obj=[b], group_by=[b.c.published_year]) |
---|
| 241 | >>> s = s.alias('years_with_count') |
---|
| 242 | >>> years_with_count = db.map(s, primary_key=[s.c.published_year]) |
---|
| 243 | >>> years_with_count.filter_by(published_year='1989').all() |
---|
| 244 | [MappedBooks(published_year=u'1989',n=1)] |
---|
| 245 | |
---|
| 246 | Obviously if we just wanted to get a list of counts associated with |
---|
| 247 | book years once, raw SQL is going to be less work. The advantage of |
---|
| 248 | mapping a Select is reusability, both standalone and in Joins. (And if |
---|
| 249 | you go to full SQLAlchemy, you can perform mappings like this directly |
---|
| 250 | to your object models.) |
---|
| 251 | |
---|
| 252 | An easy way to save mapped selectables like this is to just hang them on |
---|
| 253 | your db object:: |
---|
| 254 | |
---|
| 255 | >>> db.years_with_count = years_with_count |
---|
| 256 | |
---|
| 257 | Python is flexible like that! |
---|
| 258 | |
---|
| 259 | |
---|
| 260 | Raw SQL |
---|
| 261 | ------- |
---|
| 262 | |
---|
| 263 | SqlSoup works fine with SQLAlchemy's `text block support`__. |
---|
| 264 | |
---|
| 265 | __ http://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_text |
---|
| 266 | |
---|
| 267 | You can also access the SqlSoup's `engine` attribute to compose SQL |
---|
| 268 | directly. The engine's ``execute`` method corresponds to the one of a |
---|
| 269 | DBAPI cursor, and returns a ``ResultProxy`` that has ``fetch`` methods |
---|
| 270 | you would also see on a cursor:: |
---|
| 271 | |
---|
| 272 | >>> rp = db.bind.execute('select name, email from users order by name') |
---|
| 273 | >>> for name, email in rp.fetchall(): print name, email |
---|
| 274 | Bhargan Basepair basepair+nospam@example.edu |
---|
| 275 | Joe Student student@example.edu |
---|
| 276 | |
---|
| 277 | You can also pass this engine object to other SQLAlchemy constructs. |
---|
| 278 | |
---|
| 279 | |
---|
| 280 | Dynamic table names |
---|
| 281 | ------------------- |
---|
| 282 | |
---|
| 283 | You can load a table whose name is specified at runtime with the entity() method: |
---|
| 284 | |
---|
| 285 | >>> tablename = 'loans' |
---|
| 286 | >>> db.entity(tablename) == db.loans |
---|
| 287 | True |
---|
| 288 | |
---|
| 289 | entity() also takes an optional schema argument. If none is specified, the |
---|
| 290 | default schema is used. |
---|
| 291 | |
---|
| 292 | |
---|
| 293 | Extra tests |
---|
| 294 | =========== |
---|
| 295 | |
---|
| 296 | Boring tests here. Nothing of real expository value. |
---|
| 297 | |
---|
| 298 | :: |
---|
| 299 | |
---|
| 300 | >>> db.users.filter_by(classname=None).order_by(db.users.name).all() |
---|
| 301 | [MappedUsers(name=u'Bhargan Basepair',email=u'basepair+nospam@example.edu',password=u'basepair',classname=None,admin=1), MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0)] |
---|
| 302 | |
---|
| 303 | >>> db.nopk |
---|
| 304 | Traceback (most recent call last): |
---|
| 305 | ... |
---|
| 306 | PKNotFoundError: table 'nopk' does not have a primary key defined [columns: i] |
---|
| 307 | |
---|
| 308 | >>> db.nosuchtable |
---|
| 309 | Traceback (most recent call last): |
---|
| 310 | ... |
---|
| 311 | NoSuchTableError: nosuchtable |
---|
| 312 | |
---|
| 313 | >>> years_with_count.insert(published_year='2007', n=1) |
---|
| 314 | Traceback (most recent call last): |
---|
| 315 | ... |
---|
| 316 | InvalidRequestError: SQLSoup can only modify mapped Tables (found: Alias) |
---|
| 317 | |
---|
| 318 | [tests clear()] |
---|
| 319 | >>> db.loans.count() |
---|
| 320 | 1 |
---|
| 321 | >>> _ = db.loans.insert(book_id=1, user_name='Bhargan Basepair') |
---|
| 322 | >>> db.expunge_all() |
---|
| 323 | >>> db.flush() |
---|
| 324 | >>> db.loans.count() |
---|
| 325 | 1 |
---|
| 326 | """ |
---|
| 327 | |
---|
| 328 | from sqlalchemy import Table, MetaData, join |
---|
| 329 | from sqlalchemy import schema, sql |
---|
| 330 | from sqlalchemy.orm import scoped_session, sessionmaker, mapper, class_mapper, relation |
---|
| 331 | from sqlalchemy.exceptions import SQLAlchemyError, InvalidRequestError |
---|
| 332 | from sqlalchemy.sql import expression |
---|
| 333 | |
---|
| 334 | _testsql = """ |
---|
| 335 | CREATE TABLE books ( |
---|
| 336 | id integer PRIMARY KEY, -- auto-increments in sqlite |
---|
| 337 | title text NOT NULL, |
---|
| 338 | published_year char(4) NOT NULL, |
---|
| 339 | authors text NOT NULL |
---|
| 340 | ); |
---|
| 341 | |
---|
| 342 | CREATE TABLE users ( |
---|
| 343 | name varchar(32) PRIMARY KEY, |
---|
| 344 | email varchar(128) NOT NULL, |
---|
| 345 | password varchar(128) NOT NULL, |
---|
| 346 | classname text, |
---|
| 347 | admin int NOT NULL -- 0 = false |
---|
| 348 | ); |
---|
| 349 | |
---|
| 350 | CREATE TABLE loans ( |
---|
| 351 | book_id int PRIMARY KEY REFERENCES books(id), |
---|
| 352 | user_name varchar(32) references users(name) |
---|
| 353 | ON DELETE SET NULL ON UPDATE CASCADE, |
---|
| 354 | loan_date datetime DEFAULT current_timestamp |
---|
| 355 | ); |
---|
| 356 | |
---|
| 357 | insert into users(name, email, password, admin) |
---|
| 358 | values('Bhargan Basepair', 'basepair@example.edu', 'basepair', 1); |
---|
| 359 | insert into users(name, email, password, admin) |
---|
| 360 | values('Joe Student', 'student@example.edu', 'student', 0); |
---|
| 361 | |
---|
| 362 | insert into books(title, published_year, authors) |
---|
| 363 | values('Mustards I Have Known', '1989', 'Jones'); |
---|
| 364 | insert into books(title, published_year, authors) |
---|
| 365 | values('Regional Variation in Moss', '1971', 'Flim and Flam'); |
---|
| 366 | |
---|
| 367 | insert into loans(book_id, user_name, loan_date) |
---|
| 368 | values ( |
---|
| 369 | (select min(id) from books), |
---|
| 370 | (select name from users where name like 'Joe%'), |
---|
| 371 | '2006-07-12 0:0:0') |
---|
| 372 | ; |
---|
| 373 | |
---|
| 374 | CREATE TABLE nopk ( |
---|
| 375 | i int |
---|
| 376 | ); |
---|
| 377 | """.split(';') |
---|
| 378 | |
---|
| 379 | __all__ = ['PKNotFoundError', 'SqlSoup'] |
---|
| 380 | |
---|
| 381 | Session = scoped_session(sessionmaker(autoflush=True)) |
---|
| 382 | |
---|
| 383 | class PKNotFoundError(SQLAlchemyError): |
---|
| 384 | pass |
---|
| 385 | |
---|
| 386 | def _ddl_error(cls): |
---|
| 387 | msg = 'SQLSoup can only modify mapped Tables (found: %s)' \ |
---|
| 388 | % cls._table.__class__.__name__ |
---|
| 389 | raise InvalidRequestError(msg) |
---|
| 390 | |
---|
| 391 | # metaclass is necessary to expose class methods with getattr, e.g. |
---|
| 392 | # we want to pass db.users.select through to users._mapper.select |
---|
| 393 | class SelectableClassType(type): |
---|
| 394 | def insert(cls, **kwargs): |
---|
| 395 | _ddl_error(cls) |
---|
| 396 | |
---|
| 397 | def delete(cls, *args, **kwargs): |
---|
| 398 | _ddl_error(cls) |
---|
| 399 | |
---|
| 400 | def update(cls, whereclause=None, values=None, **kwargs): |
---|
| 401 | _ddl_error(cls) |
---|
| 402 | |
---|
| 403 | def __clause_element__(cls): |
---|
| 404 | return cls._table |
---|
| 405 | |
---|
| 406 | def __getattr__(cls, attr): |
---|
| 407 | if attr == '_query': |
---|
| 408 | # called during mapper init |
---|
| 409 | raise AttributeError() |
---|
| 410 | return getattr(cls._query, attr) |
---|
| 411 | |
---|
| 412 | class TableClassType(SelectableClassType): |
---|
| 413 | def insert(cls, **kwargs): |
---|
| 414 | o = cls() |
---|
| 415 | o.__dict__.update(kwargs) |
---|
| 416 | return o |
---|
| 417 | |
---|
| 418 | def delete(cls, *args, **kwargs): |
---|
| 419 | cls._table.delete(*args, **kwargs).execute() |
---|
| 420 | |
---|
| 421 | def update(cls, whereclause=None, values=None, **kwargs): |
---|
| 422 | cls._table.update(whereclause, values).execute(**kwargs) |
---|
| 423 | |
---|
| 424 | def relate(cls, propname, *args, **kwargs): |
---|
| 425 | class_mapper(cls)._configure_property(propname, relation(*args, **kwargs)) |
---|
| 426 | |
---|
| 427 | def _is_outer_join(selectable): |
---|
| 428 | if not isinstance(selectable, sql.Join): |
---|
| 429 | return False |
---|
| 430 | if selectable.isouter: |
---|
| 431 | return True |
---|
| 432 | return _is_outer_join(selectable.left) or _is_outer_join(selectable.right) |
---|
| 433 | |
---|
| 434 | def _selectable_name(selectable): |
---|
| 435 | if isinstance(selectable, sql.Alias): |
---|
| 436 | return _selectable_name(selectable.element) |
---|
| 437 | elif isinstance(selectable, sql.Select): |
---|
| 438 | return ''.join(_selectable_name(s) for s in selectable.froms) |
---|
| 439 | elif isinstance(selectable, schema.Table): |
---|
| 440 | return selectable.name.capitalize() |
---|
| 441 | else: |
---|
| 442 | x = selectable.__class__.__name__ |
---|
| 443 | if x[0] == '_': |
---|
| 444 | x = x[1:] |
---|
| 445 | return x |
---|
| 446 | |
---|
| 447 | def class_for_table(selectable, **mapper_kwargs): |
---|
| 448 | selectable = expression._clause_element_as_expr(selectable) |
---|
| 449 | mapname = 'Mapped' + _selectable_name(selectable) |
---|
| 450 | if isinstance(mapname, unicode): |
---|
| 451 | engine_encoding = selectable.metadata.bind.dialect.encoding |
---|
| 452 | mapname = mapname.encode(engine_encoding) |
---|
| 453 | if isinstance(selectable, Table): |
---|
| 454 | klass = TableClassType(mapname, (object,), {}) |
---|
| 455 | else: |
---|
| 456 | klass = SelectableClassType(mapname, (object,), {}) |
---|
| 457 | |
---|
| 458 | def __cmp__(self, o): |
---|
| 459 | L = self.__class__.c.keys() |
---|
| 460 | L.sort() |
---|
| 461 | t1 = [getattr(self, k) for k in L] |
---|
| 462 | try: |
---|
| 463 | t2 = [getattr(o, k) for k in L] |
---|
| 464 | except AttributeError: |
---|
| 465 | raise TypeError('unable to compare with %s' % o.__class__) |
---|
| 466 | return cmp(t1, t2) |
---|
| 467 | |
---|
| 468 | def __repr__(self): |
---|
| 469 | L = ["%s=%r" % (key, getattr(self, key, '')) |
---|
| 470 | for key in self.__class__.c.keys()] |
---|
| 471 | return '%s(%s)' % (self.__class__.__name__, ','.join(L)) |
---|
| 472 | |
---|
| 473 | for m in ['__cmp__', '__repr__']: |
---|
| 474 | setattr(klass, m, eval(m)) |
---|
| 475 | klass._table = selectable |
---|
| 476 | klass.c = expression.ColumnCollection() |
---|
| 477 | mappr = mapper(klass, |
---|
| 478 | selectable, |
---|
| 479 | extension=Session.extension, |
---|
| 480 | allow_null_pks=_is_outer_join(selectable), |
---|
| 481 | **mapper_kwargs) |
---|
| 482 | |
---|
| 483 | for k in mappr.iterate_properties: |
---|
| 484 | klass.c[k.key] = k.columns[0] |
---|
| 485 | |
---|
| 486 | klass._query = Session.query_property() |
---|
| 487 | return klass |
---|
| 488 | |
---|
| 489 | class SqlSoup: |
---|
| 490 | def __init__(self, *args, **kwargs): |
---|
| 491 | """Initialize a new ``SqlSoup``. |
---|
| 492 | |
---|
| 493 | `args` may either be an ``SQLEngine`` or a set of arguments |
---|
| 494 | suitable for passing to ``create_engine``. |
---|
| 495 | """ |
---|
| 496 | |
---|
| 497 | # meh, sometimes having method overloading instead of kwargs would be easier |
---|
| 498 | if isinstance(args[0], MetaData): |
---|
| 499 | args = list(args) |
---|
| 500 | metadata = args.pop(0) |
---|
| 501 | if args or kwargs: |
---|
| 502 | raise ArgumentError('Extra arguments not allowed when metadata is given') |
---|
| 503 | else: |
---|
| 504 | metadata = MetaData(*args, **kwargs) |
---|
| 505 | self._metadata = metadata |
---|
| 506 | self._cache = {} |
---|
| 507 | self.schema = None |
---|
| 508 | |
---|
| 509 | def engine(self): |
---|
| 510 | return self._metadata.bind |
---|
| 511 | |
---|
| 512 | engine = property(engine) |
---|
| 513 | bind = engine |
---|
| 514 | |
---|
| 515 | def delete(self, *args, **kwargs): |
---|
| 516 | Session.delete(*args, **kwargs) |
---|
| 517 | |
---|
| 518 | def flush(self): |
---|
| 519 | Session.flush() |
---|
| 520 | |
---|
| 521 | def clear(self): |
---|
| 522 | Session.expunge_all() |
---|
| 523 | |
---|
| 524 | def expunge_all(self): |
---|
| 525 | Session.expunge_all() |
---|
| 526 | |
---|
| 527 | def map(self, selectable, **kwargs): |
---|
| 528 | try: |
---|
| 529 | t = self._cache[selectable] |
---|
| 530 | except KeyError: |
---|
| 531 | t = class_for_table(selectable, **kwargs) |
---|
| 532 | self._cache[selectable] = t |
---|
| 533 | return t |
---|
| 534 | |
---|
| 535 | def with_labels(self, item): |
---|
| 536 | # TODO give meaningful aliases |
---|
| 537 | return self.map(expression._clause_element_as_expr(item).select(use_labels=True).alias('foo')) |
---|
| 538 | |
---|
| 539 | def join(self, *args, **kwargs): |
---|
| 540 | j = join(*args, **kwargs) |
---|
| 541 | return self.map(j) |
---|
| 542 | |
---|
| 543 | def entity(self, attr, schema=None): |
---|
| 544 | try: |
---|
| 545 | t = self._cache[attr] |
---|
| 546 | except KeyError: |
---|
| 547 | table = Table(attr, self._metadata, autoload=True, schema=schema or self.schema) |
---|
| 548 | if not table.primary_key.columns: |
---|
| 549 | raise PKNotFoundError('table %r does not have a primary key defined [columns: %s]' % (attr, ','.join(table.c.keys()))) |
---|
| 550 | if table.columns: |
---|
| 551 | t = class_for_table(table) |
---|
| 552 | else: |
---|
| 553 | t = None |
---|
| 554 | self._cache[attr] = t |
---|
| 555 | return t |
---|
| 556 | |
---|
| 557 | def __getattr__(self, attr): |
---|
| 558 | return self.entity(attr) |
---|
| 559 | |
---|
| 560 | def __repr__(self): |
---|
| 561 | return 'SqlSoup(%r)' % self._metadata |
---|
| 562 | |
---|
| 563 | if __name__ == '__main__': |
---|
| 564 | import logging |
---|
| 565 | logging.basicConfig() |
---|
| 566 | import doctest |
---|
| 567 | doctest.testmod() |
---|