[sqlalchemy] Re: Separate version table
On 10/29/07, mmstud [EMAIL PROTECTED] wrote:
Thats handy. Where could i get the utils module you're using for Enum
datatype?
The Enum datatype is from the ASPN cookbook, with type bindings for
SA. Here's part of my utils module.
cheers,
Arnar
# -*- encoding: UTF-8 -*-
import sqlalchemy.types as types
import datetime, time
from itertools import groupby
def numericSort(alist, val=lambda x: x):
Returns a copy. See recipe 135435 on aspn
def genidx(str):
index = []
def _append(fragment, alist=index):
if fragment.isdigit(): fragment = int(fragment)
alist.append(fragment)
prev_isdigit = str[0].isdigit()
current_fragment = ''
for char in str:
curr_isdigit = char.isdigit()
if curr_isdigit == prev_isdigit:
current_fragment += char
else:
_append(current_fragment)
current_fragment = char
prev_isdigit = curr_isdigit
_append(current_fragment)
return tuple(index)
indicies = map(genidx, map(val, alist))
decorated = zip(indicies, alist)
decorated.sort()
return [item for idx, item in decorated]
# From ASPN cookbook, Zoran Isailovski
(http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/413486)
# Added JSON and SQLAlchemy handles
def Enum(*names):
##assert names, Empty enums are not supported # - Don't like
empty enums? Uncomment!
class EnumClass(types.TypeDecorator):
__slots__ = names
impl = types.Integer
def __iter__(self):return iter(constants)
def __len__(self): return len(constants)
def __getitem__(self, i): return constants[i]
def __repr__(self):return 'Enum' + str(names)
def __str__(self): return 'enum ' + str(constants)
def convert_bind_param(self, value, engine): return value.Value
def convert_result_value(self, value, engine): return self[value]
class EnumValue(object):
__slots__ = ('__value')
def __init__(self, value): self.__value = value
Value = property(lambda self: self.__value)
EnumType = property(lambda self: EnumType)
def __hash__(self):return hash(self.__value)
def __cmp__(self, other):
if type(other) is str:
return cmp(str(self), other)
elif type(other) is unicode:
return cmp(str(self), str(other))
else:
# C fans might want to remove the following assertion
# to make all enums comparable by ordinal value {;))
assert self.EnumType is other.EnumType, Only values
from the same enum are comparable
return cmp(self.__value, other.__value)
def __invert__(self): return constants[maximum - self.__value]
def __nonzero__(self): return bool(self.__value)
def __repr__(self):return str(names[self.__value])
def __json__(self):return str(names[self.__value])
maximum = len(names) - 1
constants = [None] * len(names)
for i, each in enumerate(names):
val = EnumValue(i)
setattr(EnumClass, each, val)
constants[i] = val
constants = tuple(constants)
EnumType = EnumClass()
return EnumType
class Region(object):
def __init__(self, left, top, width, height):
self.left = left
self.top = top
self.width = width
self.height = height
def __str__(self):
return Region(%d,%d,%d,%d) %
(self.left,self.top,self.width,self.height)
def __repr__(self):
return str(self)
def countdistinct(iterable, groups=None, key=None):
Count things.
items = ['red', 'green', 'blue', 'blue']
countdistinct(items)
{'blue': 2, 'green': 1, 'red': 1}
You can ensure that specific groups are always included in the result, even
if they don't occur in the input:
items = ['red', 'blue', 'blue']
countdistinct(items, groups=['red', 'green', 'blue'])
{'blue': 2, 'green': 0, 'red': 1}
The optional `key` argument can be used to provide a function that returns
the comparison key for every item:
from operator import itemgetter
items = [dict(name='foo', category='buzz'),
... dict(name='bar', category='buzz')]
print countdistinct(items, key=itemgetter('category'))
{'buzz': 2}
if groups is None: groups = []
d = dict([(g, 0) for g in groups])
for g, l in groupby(iterable, key=key):
d[g] = len(list(l)) + d.get(g, 0)
return d
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Separate version table
On 10/29/07, mmstud [EMAIL PROTECTED] wrote:
On 29 loka, 09:08, [EMAIL PROTECTED] wrote:
I dont need history tracking, just revert documents to older ones.
that is history, just not timed history.
Most optimal would it be, if i can make rows with updated fields only,
not to copy whole row... but im afraid setting unchanged field to None
would be problematic when retrieving versions. I should retrieve
several rows and collect the latest not None fields... just pondering
That reminded me, I have another project that does automatic
changelogs. There is something in the ML archives, but here's some
more code.
I'm afraid all class, variable and field names are in Icelandic - I
put in comments with translations where it matters. I also removed
alot of auxiliary tables since this is proprietary code. The base
entity that keeps a changelog of itself is Verkefni (means project).
A changelog entry is generated by calling changelog_entry on a dirty
object. This is not done automatically as sometimes one wants to
update an object without generating a log entry.
Arnar
# -*- encoding: UTF-8 -*-
import datetime
import cherrypy
import sqlalchemy
from sqlalchemy.ext.sessioncontext import SessionContext
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.orm import MapperExtension, EXT_PASS
_engine = None
metadata = sqlalchemy.DynamicMetaData()
def get_engine():
global _engine
dburi = cherrypy.config.get('verkstjorinn.db.uri')
encoding = cherrypy.config.get('verkstjorinn.db.encoding', 'utf-8')
echo = cherrypy.config.get('verkstjorinn.db.echo', False)
if not _engine:
_engine = sqlalchemy.create_engine(dburi, encoding=encoding,
echo=echo, convert_unicode=True)
metadata.connect(_engine)
elif not metadata.is_bound():
metadata.connect(_engine)
return _engine
ctx = SessionContext(lambda:sqlalchemy.create_session(bind_to=get_engine()))
def sa_uow_cleanup():
ctx.current.clear()
sa_uow_cleanup.failsafe = True
cherrypy.tools.sacleanup = cherrypy.Tool('on_end_request', sa_uow_cleanup)
class base_model(object):
def __repr__(self):
props = []
for key in self.c.keys():
props.append(%s=%r % (key, getattr(self, key)))
return %s %s % (self.__class__.__name__, ' '.join(props))
from sqlalchemy import *
## Verkefni = Projects
verkefni_flokkar = Table(verkefni_flokkar, metadata,
Column(verkefni, Integer, ForeignKey(verkefni.verkefni),
primary_key=True),
Column(flokkur, Integer, ForeignKey(flokkar.flokkur), primary_key=True)
)
# No mapper for this table, it's only a join table for the many-to-many relation
verkefni = Table(verkefni, metadata,
Column(verkefni, Integer, primary_key=True),
Column(skrad, DateTime, nullable=False, default=func.now()),
Column(sidast_breytt, DateTime, nullable=False,
default=func.current_timestamp(), onupdate=func.current_timestamp()),
Column(skrad_af, Unicode(20), ForeignKey(notendur.notandi),
nullable=False),
Column(deadline, Date),
Column(titill, Unicode, nullable=False),
Column(lysing, Unicode, nullable=False),
Column(mikilvaegi, Integer, ForeignKey(mikilvaegi.mikilvaegi),
nullable=False, default=40),
Column(forgangur, Integer, nullable=False, default=0),
Column(framvinda, Integer, nullable=False, default=0),
Column(fasi, Integer, ForeignKey(fasar.fasi), nullable=False,
default=5),
Column(abyrgdarmadur, Unicode(20),
ForeignKey(notendur.notandi), nullable=False),
Column(cc, Unicode),
Column(verknumer, Unicode(20)),
Column(tengilidur, Unicode(60)),
Column(bidur_eftir, Unicode)
)
class Verkefni(base_model):
class DEFAULTS:
def __init__(self):
self.verkefni = 0
self.skrad = datetime.datetime.now()
self.sidast_breytt = datetime.datetime.now()
self.titill =
self.lysing =
self.verknumer = None
self.tengilidur = None
self.deadline = None
self.bidur_eftir = None
self.flokkar = []
self.mikilvaegi = Mikilvaegi.get(40)
self.forgangur = 0
self.framvinda = 0
self.fasi = Fasi.get(5)
self.abyrgdarmadur = None # Must be set by controller
self.cc = None
self.depends_on = ()
self.depend_on_me = ()
def get_fyrirtaeki(self): return []
def get_framkvaemd(self): return []
def combined_athugasemdir(self): return []
def get_dependency_parents(self): return []
def get_dependency_children(self): return []
@property
def skyldverkefni(self):
related = SkyltVerkefni.select_by(
or_(SkyltVerkefni.c._verkefni_a==self.verkefni,SkyltVerkefni.c._verkefni_b==self.verkefni)
)
relations = []
for sv in related:
if sv.verkefni_a == self:
relations.append((sv.verkefni_b, sv.athugasemd))
[sqlalchemy] Re: Separate version table
Hi there,
On 10/28/07, mmstud [EMAIL PROTECTED] wrote:
Next design problem for me is version table. I have Document model
with DocumentVersion model, but i dont know how to:
- get the latest version of document
- set creator and updator, automatic behavior for this
- update version number
- fetch thru Document(s) and DocumentVersion(s)
I didn't read your code thoroughly, but I have a model with some
similarities. Perhaps it will provide some insight. Basically, I'm
dealing with Pages and PageVersions. PageVersions refers to it's
parent Page, but Page also keeps the version number of the latest
version.
Arnar
# encoding: utf-8
import os
from datetime import datetime
from sqlalchemy import *
from softproof import utils
from softproof.json import jsonify_saobject
__meta__ = metadata
def constructor(fun):
def decorated(self, *args, **kw):
assert hasattr(self, 'c')
for key,value in kw.items():
if hasattr(self.c, key):
setattr(self, key, value)
del kw[key]
fun(self, *args, **kw)
return decorated
jobs = Table(jobs, __meta__,
Column(jobno, Unicode(15), primary_key=True),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(deleted, Boolean, nullable=False, default=False))
class Job(object):
@constructor
def __init__(self, jobno=None):
if jobno:
self.jobno = jobno
def sortedpages(self):
listcopy = self.pages[:]
listcopy.sort(key=Page.sort_key)
return listcopy
def get_page_by_name(self, pagename):
Finnur síðu með nafnið pagename og skilar henni. Skilar
None ef engin síða hefur
viðkomandi nafn.
Ef pagename er _firstpage_ er skilað viðeigandi síðu (t.d.
kápu ef hún er til)
if len(self.pages) == 0:
return None
if '_firstpage_' == pagename:
for p in self.pages:
if 'KAP' in p.pagename:
return p
return self.pages[0]
for p in self.pages:
if p.pagename == pagename:
return p
return None
def create_page(self, pagename, *args, **kwargs):
p = Page(job=self, pagename=pagename, *args, **kwargs)
return p
def get_path(self):
if self.jobno.startswith('P'):
pg1, pg2, pg3 = self.jobno.split('.')
return os.path.join(pg1, pg1+'.'+pg2, self.jobno)
else:
return os.path.join(self.jobno[:-3]+'000',
self.jobno[:-2]+'00', self.jobno)
mapper(Job, jobs)
pageversions = Table(pageversions, __meta__,
Column(jobno, Unicode(15),
ForeignKey(pages.jobno), primary_key=True),
Column(pagename, Unicode(30),
ForeignKey(pages.pagename), primary_key=True),
Column(version, Integer, primary_key=True, default=1),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(md5sum, String(32)),
Column(width, Integer, nullable=False, default=0),
Column(height, Integer, nullable=False, default=0),
ForeignKeyConstraint([jobno,
pagename],[pages.jobno, pages.pagename]))
class PageVersion(object):
@constructor
def __init__(self, page=None, version=None):
if page:
self.page = page
if version:
self.version = version
@property
def filename(self):
if self.version == 1:
return self.page.pagename + '.jpg'
else:
return %s.v%02d.jpg % (self.page.pagename, self.version)
mapper(PageVersion, pageversions)
PageStates = utils.Enum('new', 'approved', 'rejected')
pages = Table(pages, __meta__,
Column(jobno, Unicode(15), ForeignKey(jobs.jobno),
primary_key=True),
Column(pagename, Unicode(30), primary_key=True),
Column(created, DateTime, nullable=False,
default=datetime.now),
Column(deleted, Boolean, nullable=False, default=False),
Column(current_version, Integer),
Column(status, PageStates, nullable=False,
default=PageStates.new))
class Page(object):
@constructor
def __init__(self, job=None, pagename=None):
if job:
self.job = job
if pagename:
self.pagename = pagename
self.currentversion = PageVersion(self, 1)
self.status = PageStates.new
def add_version(self):
self.currentversion = PageVersion(self, self.currentversion.version+1)
self.status = PageStates.new
comment = self.add_comment()
comment.closeable = False
comment.content = u'Ný útgáfa rippuð'
return self.currentversion
def get_version(self, versionno):
return self.versions[versionno-1]
def _get_status(self):
return self._status
def _set_status(self, newstatus):
if self._status is
[sqlalchemy] Re: Separate version table
On 10/28/07, mmstud [EMAIL PROTECTED] wrote: Thanks there were some good ideas to try. Btw. what does the first def constructor(fun)? It is a decorator I use on mapped classes constructors. It allows me to give keyword arguments to constructors with initial values for any field in class.c (i.e. any mapped columns). cheers, Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: family tree
On 9/27/07, jawarumnur [EMAIL PROTECTED] wrote: thanks, I'll try that, if the relation option foreign_keys didn't do it. I think you don't need foreign_keys. The docs for foreign_keys states that it should be used in conjuction with primaryjoin only if SA can not guess the FK from the join condition alone. In this case we're only joining on defined ForeignKey(..) fields so SA should have no problem figuring it out. I'd like to get normal properties with normal getter and setter or append methods. I use sqlalchemy because I don't want to write all these methods by myself ;) What do you mean normal? You have normal getters and setters for the properties father and mother. If you want a magic property for children that you can, say, append stuff to - I don't think SA will help you much since the relationship to the child depends strictly on if you are to be it's father or mother (which depends on the parent's gender). SA doesn't do that kind of logic. Besides, it's quite simple to do by hand. If you want a proper collection for children that you can append to, a small helper class will solve that issue. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL, pyodbc linux
On 8/13/07, Christophe de VIENNE [EMAIL PROTECTED] wrote:
I have checked out the trunk and am trying to run the unittests under
linux using pyodbc.
So far, the connection to the database is working, although I had to
name the freetds odbc driver {SQL Server}, including the {}, so the
driver manager nows which one to use.
My problem is the following, and I have to idea where it comes from :
Any test from engine/bind fail with the following error (only the
query changes) :
DBAPIError: (ProgrammingError) ('42000', [42000] [FreeTDS][SQL
Server]Could not find stored procedure 'S'. (2812); [42000]
[FreeTDS][SQL Server]Could not find stored procedure 'S'. (2812))
u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS
[COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND
[COLUMNS_1].[TABLE_SCHEMA] = ?' ['test_table', 'dbo']
Any hint ?
I don't know the mssql parts of SA, but it looks like somewhere the
statement is being treated as a collection (statment[0] or similar) so
only the 'S' from 'SELECT...' is being executed. 'Could not find
stored procedure 'S'.' is exactly the error ms-sql gives if one
executes a statment with just an 'S'.
Arnar
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Hierachical data
On 8/5/07, Alexandre CONRAD [EMAIL PROTECTED] wrote: As Michael pointed out, the ElementTree example stores the XML data en-masse, so if you don't need those kind of queries, you might see better performance and a simpler way of life if you just serialize the ElementTree instances to XML in a text field and vice versa. Yeah, I thought about that, but I feel it would be harder for me to do it this way. Ok, you should trust your instinct. Just wanted to point this out as an option. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Hierachical data
On 8/3/07, Alexandre CONRAD [EMAIL PROTECTED] wrote: The nested looks more efficient. But, things are still a little confused in my head. I need to well put down the pros and cons of each technic for my needs. I was using XML and I'm now switching to a flat database with technics I've never used yet. Basicly, my application is a web-based interface that displays a playlist. So at this point, I'll need to walk down the whole tree to retrieve each node's data (type, name, duration, etc.) and build up the HTML representation (probably with HTML lists ul and li tags). Then, the user (playlist manager) will have multiple tools available to manipulate the playlist: - create node somewhere specific in the tree (image, video, group, playlist, media_list) - move node N sibling up - move node N sibling down - move node top - move node bottom - edit node's data (change media, schedule info) - duplicate node - delete node All action would be recursive, so if a group node is moved up or deleted, children will follow (either up, or to /dev/null). You should definitely go with adjacency lists, no doubt. Nested sets will be very inefficient for all of those operations and poorly supported by SA (or any ORM for that matter). Also, that last one, the recursive bit, you get for free with adjacency lists. Trees constructed and manipulated by humans tend to not get very deep (20 levels is not deep for the computer) so there's another reason not to take on the overhead of maintaining nested sets. I.e. you won't get the benefits anyways. This reminds me of the XML XPath which I used pretty much for playlist manipulation. Although, you example feels less consistent because if a media is removed from the catalog, I would like the database to remove the playlist node that refers to that video. Also, how big should my path column be ? I guess I should make it big enough to store a long very long path, but it is not accurate. None of the strategies for storing hierarchical data will give you the property that nodes are removed from the tree if their referenced item is removed. You will have to do that yourself regardless. The path column should be at least (max depth)*(max length of key)*(length of delimiter). If you have a table of 10 million nodes, a numeric key is at most 7 characters long. A 256 character field would give you 32 levels of recursion minimum, but more in praxis since not all keys will be 7 characters long. Besides, given the operations you listed above, an adjacency list is still a better option. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Hierachical data
On 8/3/07, King Simon-NFHD78 [EMAIL PROTECTED] wrote: I think of adjacency lists and nested sets as more about hierarchies of a single type of object (imagine trying to represent a family tree with a row for each Person). I don't really think they're relevant in this case. If I understand correctly the OP has a need to store a set of hierarchical Nodes. Only that some node types (video, image) can't have children while others can (group, media_list). Alexandre, why do you find that the nested set model suits your needs better? The nested set model is particularly suited to the following type of queries: 1. find all children of a particular node, without needing to know their generation 2. test if node A is a descendant of node B These are queries that are expensive with adjacency lists but inexpensive with nested sets. Nested sets however may require you to update a lot of nodes if your tree changes. Traditionally you would do that with triggers or stored procedures - but those don't play well with ORMs which assumes that an update of a row won't invalidate cached instances of other rows. Do you have specific needs for those kind of queries? I don't know your application but I'd probably go with adjacency lists and a common Node superclass that provides access to child and parent nodes. Derive other classes from that and use joined table polymorphism as Simon suggested. There is another way to store hierarchical data than adjacency lists and nested sets though. If you have an invariant id on your objects (numerical or textual) you can store an objects path, governed by object.path = object.parent.path + delimiter + object.id This might not be efficient for deeply nested trees maybe, but it can help in answering the types of queries mentioned above efficiently without requiring you to touch multiple rows upon inserting or deleting. Also, it would be easy to implement in SA and you could have all sorts of convenience methods on a Node class, like get_direct_children get_all_descendants is_parent is_child is_descendant Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Storing JSON objects, maybe OT
On 6/25/07, voltron [EMAIL PROTECTED] wrote: Thank you very much for your feedback guys! I was very worried the whole day. I did some research and found this: http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html I have to say I've seen better writeups on this topic - so be warned :) Choosing a strategy for storing hierarchical data in a database depends largely on how you will be retrieving the data (what kind of queries do you need to make). Actually, I would be reordering or editing page links using this method, the JSON data is passed to a tree widget I'm not sure what is the relation between your JSON issue and the hierarchical stuff. JSON is really only useful to serialize simple data structures in a way that is easy for Javascript code to deserialize. It won't help you particularly in designing a hierarchical database model. If you still need help and if you want to, feel free to tell us a bit more what you want to do. If it's not an SA issue you can mail me directly if you're more comfortable with that. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping existing structure of a database
On 6/4/07, nathan harmston [EMAIL PROTECTED] wrote: What kind of overhead is associated with using the autoload flag? What kind of overhead would be associated with this over a network? (with a remote database). Is there a way to dump the structure of a database to a file and import this as a kind of module? I've had to do this a few times. What I do is I use the autoload once to create the table objects, and then just print'em on the python console. That gives a fairly good starting point that can be copied to a file and fixed up. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: InstrumentedList in a boolean context
Hi all, On 5/22/07, Julien Cigar [EMAIL PROTECTED] wrote: yes of course [1,2,3] is different than [3,2,1] (hopefully), I was talking for InstrumentedList, not tuple or list or ... IMO InstrumentedList should definately have the same semantics as a list - what you're talking about is to make it behave partially like a set. Anyway, I've used sorted(MyInstrumentList...) == sorted(MyInstrumentList2 or MyTuple or ...) ... which works :-) Given the use case a = Invasive.get(67).habitats b = Invasive.get(57).habitats you could add an order_by to the habitats relation - which would give you the list in the same order every time. Of course it will not maintain that order if you .append() or .remove() items before making the comparison. If you are pressing for speed, you could do two things: a) before checking if sorted(a) == sorted(b) - see if they're the same length. If they're not, there's no need to sort them, you know they'll be different from each other. b) see if using a set (or a dict if you don't have python 2.4, using only the keys) is faster than doing the two sorts and then comparing Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: set character set in sqlalchemy?
Hi there, You can include a command in the dburi that is executed upon connection start. I use it all the time like this: mysql://user:[EMAIL PROTECTED]:3306/dbname?init_command=set%20character%20set%20utf8 Arnar On 5/14/07, Noam [EMAIL PROTECTED] wrote: Hello, I'm trying to use sqlalchemy to access a database which contains unicode strings. When accessing the database through phpmyadmin the values look ok. When accessing them through sqlalchemy I get question marks instead of the characters which are not latin. When using the mysql command line tool, it can be fixed with the set character set command: mysql select display_name from tav.tg_user; +--+ | display_name | +--+ | ??? | | | +--+ 2 rows in set (0.02 sec) mysql set character set utf8; Query OK, 0 rows affected (0.00 sec) mysql select display_name from tav.tg_user; +-+ | display_name| +-+ | נעם רפאל | | מנהל| +-+ 2 rows in set (0.00 sec) phpmyadmin reports MySQL charset: UTF-8 Unicode (utf8) How can I fix it from sqlalchemy? Thanks, Noam Raphael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Orphaned parents..
Hi all, Say I have this simple model: DayTimesheet date: a Date entries: a list of TimeEntry TimeEntry: day: a DayTimesheet in: a time (HH:MM) out: a time (HH:MM) DayTimesheet is the parent of TimeEntry in a one-to-many relationship. What would be the best way of ensuring the following: 1. Creating DayTimesheet on demand, i.e. DayTimesheet.get_by(date=nonexistentdate) would create a new instance if needed (inserted on the next flush()) and 2. Automatically removing childless DayTimesheet instances - that is, when the last TimeEntry is deleted from a DayTimesheet, delete the DayTimesheet too? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: (solved) Automatic generation of changelog
Hi all,
I have this working to my liking and as promised, here's the code:
It's not complete since I'm omitting some of my model and just showing
you the relevant parts.
This is my model:
verkefni = Table(verkefni, metadata,
Column(verkefni, Integer, primary_key=True),
Column(skrad, DateTime, nullable=False, default=func.now()),
Column(sidast_breytt, DateTime, nullable=False,
default=func.current_timestamp(), onupdate=func.current_timestamp()),
Column(skrad_af, Unicode(20), ForeignKey(notendur.notandi),
nullable=False),
Column(deadline, Date),
Column(titill, Unicode, nullable=False),
Column(lysing, Unicode, nullable=False),
Column(mikilvaegi, Integer, ForeignKey(mikilvaegi.mikilvaegi),
nullable=False, default=40),
Column(forgangur, Integer, nullable=False, default=0),
Column(framvinda, Integer, nullable=False, default=0),
Column(fasi, Integer, ForeignKey(fasar.fasi), nullable=False,
default=0),
Column(abyrgdarmadur, Unicode(20),
ForeignKey(notendur.notandi), nullable=False),
Column(cc, Unicode),
Column(verknumer, Unicode(20)),
Column(tengilidur, Unicode(60)),
Column(bidur_eftir, Unicode)
)
class Verkefni(base_model):
def changelog_entry(self, notandi, skyring):
entry = Atburdur(self, notandi, skyring)
def changelog(fld, old, new):
entry.create_item(fld, old, new)
instance = self
# Scalar fields
for fld in ('deadline', 'titill', 'lysing', 'forgangur',
'framvinda', 'mikilvaegi',
'fasi', 'abyrgdarmadur', 'cc', 'verknumer',
'tengilidur', 'bidur_eftir'):
history = getattr(Verkefni, fld).get_history(instance, passive=True)
if history.is_modified():
old, new = history.deleted_items()[0], history.added_items()[0]
# Need to check this cause SA considers 10 (int) -
10L (long) a change, we don't
if old != new:
changelog(fld, old, new)
# Fyrirtaeki
history = Verkefni.fyrirtaeki_tengsl.get_history(instance, passive=True)
for i in history.added_items():
changelog('fyrirtaeki_add', None, i.fyrirtaeki.fyrirtaeki)
for i in history.deleted_items():
changelog('fyrirtaeki_remove', i.fyrirtaeki.fyrirtaeki, None)
# Flokkar
history = Verkefni.flokkar.get_history(instance, passive=True)
for i in history.added_items():
changelog('flokkur_add', None, i.flokkur)
for i in history.deleted_items():
changelog('flokkur_remove', i.flokkur, None)
# Framkvaemd
history = Verkefni.framkvaemd.get_history(instance, passive=True)
for i in history.added_items():
changelog('framkvaemd_add', None, i.notandi.notandi)
for i in history.deleted_items():
changelog('framkvaemd_remove', i.notandi.notandi, None)
# Dependencies
history = Verkefni.depends_on.get_history(instance, passive=True)
for i in history.added_items():
changelog('dep_first_add', None, i.verkefni)
for i in history.deleted_items():
changelog('dep_first_remove', i.verkefni, None)
history = Verkefni.depend_on_me.get_history(instance, passive=True)
for i in history.added_items():
changelog('dep_then_add', None, i.verkefni)
for i in history.deleted_items():
changelog('dep_then_remove', i.verkefni, None)
# Related
history = Verkefni._relatives_a.get_history(instance, passive=False)
for i in history.added_items():
changelog('rel_add', None, i.verkefni)
for i in history.deleted_items():
changelog('rel_remove', i.verkefni, None)
history = Verkefni._relatives_b.get_history(instance, passive=False)
for i in history.added_items():
changelog('rel_add', None, i.verkefni)
for i in history.deleted_items():
changelog('rel_remove', i.verkefni, None)
atburdaskra = Table(atburdaskra, metadata,
Column(atburdur, Integer, primary_key=True),
Column(verkefni, Integer, ForeignKey(verkefni.verkefni),
nullable=False),
Column(notandi, Unicode(20), ForeignKey(notendur.notandi),
nullable=False),
Column(dags, DateTime, nullable=False, default=func.now()),
Column(skyring, Unicode)
)
class Atburdur(base_model):
def __init__(self, verkefni, notandi, skyring=None):
self.verkefni = verkefni
self.notandi = notandi
self.skyring = skyring
def create_item(self, svid, gamalt=None, nytt=None, texti=None):
nextid = max([0] + [item.item for item in self.items])+1
return AtburdurItem(self, nextid, svid, gamalt, nytt, texti)
assign_mapper(ctx, Atburdur, atburdaskra, properties={
'_verkefni': atburdaskra.c.verkefni,
'verkefni': relation(Verkefni, backref=backref(atburdir,
cascade=all, delete-orphan, order_by=atburdaskra.c.dags)),
[sqlalchemy] Re: how to do many-to-many on the same table?
Hi there, I haven't read the reast of the thread, but I asked a question here of how to represent a graph. What you need is essentialli an undirected graph. On 4/3/07, tml [EMAIL PROTECTED] wrote: So, if article 1 is related to article 2.. then there should be two rows in the articles_related_articles table like: 1 2 0 2 1 0 I often use a different technique. Instead of keeping two lines in the association table, I only keep one and make a rule that relateditem1 = relateditem2. Here is the thread on graphs where Mike posted a working solution: http://groups.google.com/group/sqlalchemy/browse_frm/thread/4d81b50fbfd84195/8ea809a9532ad6ca The pastebin link in the thread doesn't work any more, but the code can be found here: http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/graphs/graph1.py Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to do many-to-many on the same table?
On 4/4/07, Arnar Birgisson [EMAIL PROTECTED] wrote: I often use a different technique. Instead of keeping two lines in the association table, I only keep one and make a rule that relateditem1 = relateditem2. Here is the thread on graphs where Mike posted a working solution: http://groups.google.com/group/sqlalchemy/browse_frm/thread/4d81b50fbfd84195/8ea809a9532ad6ca The pastebin link in the thread doesn't work any more, but the code can be found here: http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/graphs/graph1.py One thing I forgot: for convenience I add a method on Node defined like this: @property def neighbors(self): return self.higher_neighbors() + self.lower_neighbors() Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatic generation of changelog
Hi Mike, I've started looking into this and this is exactly what I need. I can pretty much eat the stuff directly from added_items() and deleted_items() and insert that into my history-log table. One question though: On 3/28/07, Michael Bayer [EMAIL PROTECTED] wrote: dont have an example handy, but yeah youd want to make a MapperExtension and work into the after_insert(), after_update() and after_delete() hooks (or maybe the before_ versions of each one, depending on how you detect changes). you can issue writes to the database immediately within those and theyll be within the current transaction. Can I use the ORM inside after_update? I.e. can I create new instances of say HistoryLoggedItem which is a mapped class and expect that to be inserted in the same transaction, or do I need to insert explicitly into that table? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatic generation of changelog
On 4/4/07, Michael Bayer [EMAIL PROTECTED] wrote: its not going to be able to modify the current flush() plan thats in progress, so youd either have to insert yourself, dispatch to class_mapper(HistoryLoggedItem).save_obj() yourself, or process the Session externally to the flush(), create the HLI items at that point and add them (by perusing session.dirty and processing). Ok, dispatching to class_mapper(...).save_obj() will get the sql executed right away? Will I need to .refresh() the HLI instances afterwards? The third option, creating the changelog-information independent of the flush - would that mean I can't use a mapper-extension to trigger it? Do I have any other ways for triggering things on object update. the third option is slightly less automatic but would be the more official OO way to do it, being that SA is a library, not a framework. when you try to add behavior by patching inside of SA's flush process, youre starting to use it as a framework. Ive noticed the Python community *really* wants to turn things into frameworks :). Well, dynamic and introspective languages tend to be framework friendly I guess :o) Does it hurt in anyway to use passive=False when I get_history? In some cases I had to in order to get what I want (get_history was returning None). thanks, Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatic generation of changelog
Gaetan and Ivo: Sure thing. I'll implement this one way or another monday or tuesday, will post what I end up with. Arnar On 4/1/07, imbunche [EMAIL PROTECTED] wrote: I'd also apreciate if you post your code later. thx in advance. IvO On Mar 28, 6:07 pm, Arnar Birgisson [EMAIL PROTECTED] wrote: Hi Dave, Thank you very much, seems I should be able to do what I want. I'll take a stab at it tomorrow and report. On 3/28/07, Michael Bayer [EMAIL PROTECTED] wrote: note that by history, we mean things that have occured since the instance was loaded from the database into the current session. That history is cleared on session.flush() - right? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] sqlalchemy.org down?
I can't reach www.sqlalchemy.org, anyone else having problems? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy.org down?
On 3/29/07, Michael Bayer [EMAIL PROTECTED] wrote: rebooting hopefully it comes up Works for me, thanks. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatic generation of changelog
Hi Dave, Thank you very much, seems I should be able to do what I want. I'll take a stab at it tomorrow and report. On 3/28/07, Michael Bayer [EMAIL PROTECTED] wrote: note that by history, we mean things that have occured since the instance was loaded from the database into the current session. That history is cleared on session.flush() - right? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
I like it all!
Arnar
On 3/10/07, Michael Bayer [EMAIL PROTECTED] wrote:
Well initial response to this post has been overwhelmingly low, but
thats fine with me. After trying to tackle this today, I realized
that I actually didnt want to add a brand new query object and go
through a painful deprecation procedure again and all that...and I
also observed that the notion of a query being against a single entity
is pretty important..things like get(), load() and all that dont make
much sense for multiple entities. Also, all the things Ive wanted to
do are not actually that hard as we already have code to do most of
it.
So I did not add any new classes or modules, no new methods on
session, I didnt go with my modified generative approach (its just
like it always was) and changed absolutely nothing about Query thats
already there (well one little thing i dont think anyone was using).
But I added a whole lot of new methods to Query, essentially
everything SelectResults was able to do. I was concerned about having
just too much crap on Query but i think it turned out fine. Plus I
added a little bit of hibernate-like capabilities to query multiple
entities and extra columns too.
So...with that, heres what Query can do now.
First of all, the same stuff that youve always used is just like it
always was:
query.select_by()
query.select()
query.get()
query.count()
query.select_by_XXX()
A couple of methods that Ive never liked because they are klunky, are
still there anyway:
query.join_to()
query.join_via()
As it stands, Query has two generative methods already (which also i
had to fix a little bit since generative-ness would wipe out what
was previously there). these return for you a new Query with
modifications to its state:
query.options()
query.with_lockmode()
So we add a lot of new generative methods taken from SelectResults,
all of which return a brand new Query. the things you add here will
also take effect on subsequent calls to the regular select(), count(),
select_by(), etc:
query.filter() - adds criterion
query.filter_by() - ditto
query.join() - joins to a property name, or a list
query.outerjoin()
query.order_by()
query.group_by()
query.distinct() - applies DISTINCT
query.offset()
query.limit()
query[3:5] - applies offset 3, limit 2
like SelectResults, we have some executors -
query.list()
list(query)
or just call query.select(), selectfirst(), selectone(), etc. to
execute whatever has been built up.
the aggregates, which take a Column (not sure how popular these are):
query.avg()
query.sum()
.. and others
So a generative example, including join which im really excited
about. join can act either like join_to():
q =
session.query(Person).filter_by(people.c.name.like('%2')).join('status').filter_by(name=active)
print q.list()
or like join_via (which is more deterministic), if you send a list:
l = q.filter(orderitems.c.item_name=='item 4').join(['orders',
'items']).list()
and then, some brand new stuff - better support for querying more
than one thing at a time. the instances() method, which was able to
take a list of *mappers, now returns the results the way the docs say
they do, as a list of tuples, each tuple having an entry for each
mapper. additionally, when that option is used, the uniquing of the
result is turned off - this so that the results you get back
correspond to the rows that went in. and, you can stick arbitrary
columns, not just mappers, in the list too...it will just pull them
from the row.
in addition to the changes on instances(), you can add extra entities/
columns to the compilation as well:
query.add_entity() - adds columns to the select criterion
query.add_column() - adds columns to the select criterion
so what can we do with this ? things like, query an entity and an
aggregate function at the same time:
q = sess.query(User)
q = q.group_by([c for c in
users.c]).outerjoin('addresses').add_column(func.count(addresses.c.address_id).label('count'))
l = q.list()
which will return for you a list of tuples:
[
(user1, 3),
(user2, 5),
(user3, 0),
..etc
]
note the label is needed right now for a function, it makes it
possible for Query to target the column correctly.
another one from the unit tests. the users table has users 7, 8, 9
in it. the addresses table has addresses 1, 2, 3, 4. user #7
refereces address #1, user #8 references addresses 2, 3 and 4.
(user7, user8, user9) = sess.query(User).select()
(address1, address2, address3, address4) =
sess.query(Address).select()
q = sess.query(User)
q = q.add_entity(Address).outerjoin('addresses')
l = q.list()
assert l == [
(user7, address1),
(user8, address2),
[sqlalchemy] Re: Parameters not handled correctly with adodbapi
Hi Paul, Yes, I had found this about the datatype size by way of breaking into the relevant code and setting the parameter length manually. This made the query work but just unveiled other problems. I too made no further efforts to get it work as pymssql works fine for my purposes - although server side cursors would have been nice to have. However, the adodbapi module is fairly simple, it's mostly just proxying to the underlying COM api. I'm pretty sure it's relatively easy to fix but since I don't have any real need for it myself I lack the motivation right now. Maybe someday if the DB-API becomes defunct due to microsoft dropping support for it.. It's good to hear that someone is working on pyodbc support in SA. Arnar On 2/17/07, Paul Johnston [EMAIL PROTECTED] wrote: Arnar, I agree that is an adodbapi problem; I have seen this as well. It is because adodbapi isn't setting the length of the parameter, so ADO is defaulting to 1 - hence you only getting O. I do have an idea how to fix this, however I'm choosing not to for now. adodbapi just isn't maintained any more - the mailing list is all but dead, and numerous attempts to contact the author have failed. Moreover, pyodbc is now a very viable alternative. I had been avoid ODBC because earlier versions had no Unicode support, but this is now fixed. So, from now I will be devoting my energies to making SQLAlchemy work better with pyodbc. Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Parameters not handled correctly with adodbapi
Thanks Rick, The SA table definitions were using Unicode so I changed those to String. I also make sure that the parameter in question is being sent as a str object and not unicode. Same result in both cases, SQL Server is only receiving the first character and the type is still detected as nvarchar(1). Any other ideas? Arnar On 2/14/07, Rick Morrison [EMAIL PROTECTED] wrote: The problem is most likely in the table definition which defines the dataset column as a Unicode type. A recent patch for adodb makes that an NVARCHAR field, vs. a simple VARCHAR for pymssql. Try defining the table with varchar or char datatype. On 2/14/07, Arnar Birgisson [EMAIL PROTECTED] wrote: Hi there, I have the following query in SA. It works using pymssql on Linux. criteria = and_( tables.salestable.c.dataset==self.xaldataset, tables.salestable.c.dataset==tables.projtable.c.dataset , tables.salestable.c.proj==tables.projtable.c.proj, tables.salestable.c.projclosed==0, #tables.salestable.c.salesoffersent!=2, #tables.salestable.c.salesofferinvoice!=2, tables.salestable.c.projeststatus!=4, tables.salestable.c.projeststatus!=6, #tables.salestable.c.projeststatus!=7, *critlist ) query = select([ tables.salestable.c.dataset , tables.salestable.c.salesnumber, tables.salestable.c.salesname, tables.salestable.c.ordertaker, tables.salestable.c.deliverydate, tables.salestable.c.projname , tables.salestable.c.createdate, tables.salestable.c.rownumber, tables.projtable.c.proj, tables.projtable.c.opmprojno, tables.projtable.c.framstjori ], criteria) Here, self.xaldataset is ODD and critlist is []. When run on windows with adodbapi - this is the echoed query: 2007-02-14 11:55:28,030 sqlalchemy.engine.base.Engine.0x..30 INFO SELECT TOP 25 subq.dataset, subq.salesnumber, subq.salesname, subq.ordertaker, subq.deliverydate, subq.projname, subq.createdate, subq.rownumber, subq.proj, subq.opmprojno, subq.framstjori FROM (SELECT salestable.dataset AS dataset, salestable.salesnumber AS salesnumber, salestable.salesname AS salesname, salestable.ordertaker AS ordertaker, salestable.deliverydate AS deliverydate, salestable.projname AS projname, salestable. createdate AS createdate, salestable.rownumber AS rownumber, projtable.proj AS proj, projtable.opmprojno AS opmprojno, projtable.framstjori AS framstjori FROM salestable, projtable WHERE salestable.dataset = ? AND salestable.dataset = projtable.dataset AND salestable.proj = projtable.proj AND salestable.projclosed = ? AND salestable.projeststatus != ? AND salestable.projeststatus != ?) AS subq ORDER BY deliverydate 2007-02-14 11:55:28,030 sqlalchemy.engine.base.Engine.0x..30 INFO ['ODD', 0, 4, 6] Note the first parameter being 'ODD'. Now, the query wasn't returning the expected results so I looked at the query that was being sent in sql profiler: exec sp_executesql N'SELECT TOP 25 subq.dataset, subq.salesnumber, subq.salesname, subq.ordertaker, subq.deliverydate, subq.projname, subq.createdate, subq.rownumber , subq.proj, subq.opmprojno, subq.framstjori FROM (SELECT salestable.dataset AS dataset, salestable.salesnumber AS salesnumber, salestable.salesname AS salesname, salestable.ordertaker AS ordertaker, salestable.deliverydate AS deliverydate, salestable.projname AS projname, salestable.createdate AS createdate, salestable.rownumber AS rownumber, projtable.proj AS proj, projtable.opmprojno AS opmprojno, projtable.framstjori AS framstjori FROM salestable, projtable WHERE salestable.dataset = @P1 AND salestable.dataset = projtable.dataset AND salestable.proj = projtable.proj AND salestable.projclosed = @P2 AND salestable.projeststatus != @P3 AND salestable.projeststatus != @P4) AS subq ORDER BY deliverydate',N'@P1 nvarchar(1),@P2 int,@P3 int,@P4 int',N'O',0,4,6 Here you can see that the first parameter is givent the type nvarchar(1) and the value N'O'. I assume this is b/c somewhere the parameter is handled as a list - and being a string messes things up. Maybe because I'm using unicode strings? Could this be a SA bug or should I rather look into adodbapi? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Hi Rick, On 2/8/07, Rick Morrison [EMAIL PROTECTED] wrote: Pymssql, or adodbapi? Client-side cursors, or Server-side? Pymssql and client side cursors (I guess, I'm just issuing plain select statements, no stored procs or such). We use pymssql here over FreeTDS with SQL Server 2005 here without issues. If your DBA suggests with (nolock), I'm assuming that you're seeing some sort of persistent table/page locks? Can you give a bit more info? I'm trying to analyze the problem right now, but I'm afraid I don't have alot of info. We were having locking issues in other systems running of the same db server which were solved (partially at least) by adding nolock hints to datawarehousing procedures. The symptom on the SA side is that this seems to happen after the server (apache/mod_python) has been running for a while. A user (and always the same two out of ~40) call me and says his page is empty - meaning that the main query is giving 0 result rows when it should be giving 300. I log in with my name and everything is fine - I log in with his and I see the empty screen. If I restart apache it fixes the problem for a while. I don't see any errors or tracebacks in the apache error_log, but I have yet to enable more logging since this only happens on the production server and quite sporadically. This has some pre-history. I was initially running this in cherrypy which was simply not working at all. DB connections would lock up until there were no left in the pool and this happend quite fast, in about 20 minutes. I moved the part of the application that speaks to mssql over to mod_python/apache and that solved the problem while we were running on sql server 2000. I'll try to analyze this some more and get back to you.. thanks for the help. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding with (nolock) to all queries (mssql)
Ok. Should I rather be using adodbapi then? Arnar On 2/8/07, Rick Morrison [EMAIL PROTECTED] wrote: Pymssql runs on top of the ancient library DB-lib, which has real issues with not fully reading all query results -- make sure all your query results are fully consumed, i.e. read them into a list and the iterate that instead of partially iterating the cursor. On 2/8/07, Arnar Birgisson [EMAIL PROTECTED] wrote: Hi Rick, On 2/8/07, Rick Morrison [EMAIL PROTECTED] wrote: Pymssql, or adodbapi? Client-side cursors, or Server-side? Pymssql and client side cursors (I guess, I'm just issuing plain select statements, no stored procs or such). We use pymssql here over FreeTDS with SQL Server 2005 here without issues. If your DBA suggests with (nolock), I'm assuming that you're seeing some sort of persistent table/page locks? Can you give a bit more info? I'm trying to analyze the problem right now, but I'm afraid I don't have alot of info. We were having locking issues in other systems running of the same db server which were solved (partially at least) by adding nolock hints to datawarehousing procedures. The symptom on the SA side is that this seems to happen after the server (apache/mod_python) has been running for a while. A user (and always the same two out of ~40) call me and says his page is empty - meaning that the main query is giving 0 result rows when it should be giving 300. I log in with my name and everything is fine - I log in with his and I see the empty screen. If I restart apache it fixes the problem for a while. I don't see any errors or tracebacks in the apache error_log, but I have yet to enable more logging since this only happens on the production server and quite sporadically. This has some pre-history. I was initially running this in cherrypy which was simply not working at all. DB connections would lock up until there were no left in the pool and this happend quite fast, in about 20 minutes. I moved the part of the application that speaks to mssql over to mod_python/apache and that solved the problem while we were running on sql server 2000. I'll try to analyze this some more and get back to you.. thanks for the help. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Foreign key not updated
On 1/5/07, Michael Bayer [EMAIL PROTECTED] wrote: yeah see, thats exactly the kind of thing i dont want SA's ORM to get into, because its really thorny..updating the relationship on all child objects. at the very least, it requires loading them all in, cascading the change, etc. it gets pretty nuts and is not particularly scalable (similar to cascading deletes not being terribly scalable in SA either). the best way to cascade a change of PK like that is to set it on your database using ON UPDATE CASCADE, and just do an UPDATE statement. the DB takes care of the rest. Ok, thanks :o) Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Foreign key not updated
On 1/4/07, Michael Bayer [EMAIL PROTECTED] wrote:
Arnar Birgisson wrote:
On 1/3/07, Michael Bayer [EMAIL PROTECTED] wrote:
yeah that would be why. SA's ORM has no ability to update primary key
columns from their original valueyoull have to update it yourself,
or copy the object instance to a new one, etc. (or not make that col a
primary key)
Ok thanks. Is there something fundamental in SA architecture that
prevents updating of primary keys - or is it simply not implemented
b/c it's complicated/difficult?
its something fundamental, i.e. the identity map:
http://www.sqlalchemy.org/trac/wiki/FAQ#asingleobjectsprimarykeycanchangecanSAsORMhandleit
adding code to update the column, and switch the identity of an entity,
is not impossible. im not sure at this moment how involved it is, i
have a feeling it will start out easy but then branch off into lots of
unforseen ill effects that I cant predict at the moment. mostly, im
just not sure if the ORM should really be taking on the task of
changing the identity of entities in-session.
Sounds reasonable that development is focused elsewhere then :o) - I
just wish I had more time so that I could be more involved.
In any case, if SA can't update primary keys I'd expect it to blow up
in my face if I tried doing that. Instead it just silently flushed the
uow session without making any updates - leaving the model in
inconsistent state with the database. Shouldn't there be some kind of
error?
initially i hadnt made my mind up about that, since i dont want to get
in the way of schemes that the user might be plotting, such as doing
something in a MapperExtension.after_update() step. however i have a
feeling that nobody is doing that sort of thing, so i can look into
adding this check (ticket 412).
Right, I hadn't thought of that.
Yesterday I had to write a one-time script that change the
identities of a bunch (~200k) records and their two kinds of children.
I did this by selecting up the objects via the ORM model to prepare
what I had to do, then I cleared the session and made the updates
directly with on the engine (bypassing the ORM). Now it seems (b/c
other people keep changing specs) that I have to do something similar
again.
Now, what I really wanted to do is to write a method on my class that
takes care of changing it's primary key (or part of it, at least) and
cascade the change down to the children of that entity. Would that be
possible while keeping the process transparent to the caller, as far
as removing the object from the identity map and re-adding it with the
new key. I'm sorry if it sounds stupid, I don't have a full
understanding of the identity map.
I.e. I would like to do something like this:
obj = session.query(TheClass).get('pk-before')
# obj has child relationships, some lazy-loaded and others eager-loaded
obj.rename('pk-after')
# session.flush() - I can live with having to do this here.
obj2 = session.query(TheClass).get('pk-after')
assert obj == obj2
I'm not asking for a solution, just an indicator if this is possible
and some pointers on how you would do it in general, then I'd be able
to figure out the details.
Arnar
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Foreign key not updated
Hi folks, I'm having a problem, illustrated here: http://paste.turbogears.org/paste/756 This is with Python 2.4 and SA 0.3.3. What happens is that when I have two parent objects (Jobs in my case) and one child object (a Page) - and move the child between parents, the session.flush() does not update the foreign key of the child. One contributing factor might be that the foreign key is also a part of the child's primary key - but I'm not sure. Any ideas? Should I submit this as a bug? Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.3.2 released
On 12/11/06, David Geller [EMAIL PROTECTED] wrote: 2. From what I know about reportlab, there *is* an opensource pdf toolkit available under a bsd license (although I have never used it) There is, and it works well, although I haven't used it extensively. It has advanced features for text layout and pagination. However it's still low level compared to markdown-html converters and one has to spend some time writing a program to generate a document. It allows you to create bookmarks/toc, hyperlinks and all that fancy PDF stuff.. :o) And yes, this is all in the BSD licensed version. Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping a graph data structure
On 11/1/06, Michael Bayer [EMAIL PROTECTED] wrote: im beginning to regret having viewonly and non_primary as options,since i cant think of anything they do that cant be better accomplishedjust by using Query.select(), or manual queries in conjunction with query.instances().I think im going to try to express this in thedocumentation...Nice, thanks. The version you pasted is exactly the elegant way I wanted to find* I hope this was a helpful excercise for others besides myself. :)Arnar* on a philosophical note: are programs, like mathematical findings, found or made? One could look at it in such a way that the method which a program uses to solve a problem already exists - the job of a programmer is only to discover it and express it in some form - not to create it :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Mapping a graph data structure
Hi there,There was a question on IRC yesterday on how to map a graph (as in nodes and edges) with SA. I didn't have much time to dwell on this but this was a start: http://paste.ufsoft.org/90 I was curious if someone has done this successfully? The problem I have with the above is that the viewonly property _lower_neighbours isn't updated until the session is clear()ed and the object is reloaded. Is there a way to refresh a specific relation? Now that I think about this, it would have been alot easier to create a directed graph and just create edges in both directions for an undirected one.Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
