[sqlalchemy] fractional second percision- mysql
Support,
I recently updated our MySQL database to version 5.6.5 with hopes of
using the newly added fractional second support for the Time datatype.
Using SQLalchemy version 0.6.5 to create our table definitions, I add
the fractional second percision paramater to the time type as shown in
the MySQL documentation:
meta_timings = Table('meta_timings', metadata,
...
Column('elapsed', Time(2), nullable=False),
...)
However, when I build the database with these new table definitions,
sqlalchemy seems to ignore the new time parameters and creates the
table using the default time command without fractional second
precision. The resulting table definition is:
CREATE TABLE `meta_timings` (
...
`elapsed` time NOT NULL,
...
) ;
When I attempt to bypass this problem by manually creating this table
definition by using `session.execute(''' table definition '''), the
MySQL database renders the time in the correct format with two
fractional seconds places after the seconds place (00:00:00.00 instead
of 00:00:00). This is a step closer, however, when I use timedelta to
insert the data, everything except the fractional seconds is parsed
and put into the time datatype correctly. The result is that every
time has .00 fractional seconds (example xx:xx:xx.00).
Here is how I am parsing our data using timedelta:
# Match time values
time_re = re.match('(\d{1,2}):(\d{1,2})\.(\d{1,2})', v)
if time_re:
minutes, seconds, milliseconds = [int(x) for x in
time_re.groups()]
td = timedelta(minutes=minutes, seconds=seconds,
milliseconds=milliseconds)
return td
Does sqlalchemy support this type of operation?
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] fractional second percision- mysql
On Jul 3, 2012, at 3:47 PM, James wrote:
Support,
I recently updated our MySQL database to version 5.6.5 with hopes of
using the newly added fractional second support for the Time datatype.
Using SQLalchemy version 0.6.5 to create our table definitions, I add
the fractional second percision paramater to the time type as shown in
the MySQL documentation:
meta_timings = Table('meta_timings', metadata,
...
Column('elapsed', Time(2), nullable=False),
...)
The Time() object there is a SQLAlchemy construct, and you can't assume every
argument accepted by every MySQL type is automatically accepted by SQLAlchemy's
types - especially brand new ones.Above, the 2 you're passing there is
only being interpreted as a value True for the timezone parameter, which is
the only parameter that the Time type accepts (docs:
http://docs.sqlalchemy.org/en/rel_0_6/core/types.html?highlight=time#sqlalchemy.types.Time
).
The first thing to note is that sqlalchemy.types.Time is a database-agnostic
construct, and most databases don't support the wide variety of custom fields
that MySQL does. At the very least, such an argument would be accepted only by
the sqlalchemy.dialects.mysql.TIME type (docs:
http://docs.sqlalchemy.org/en/rel_0_6/dialects/mysql.html?highlight=time#sqlalchemy.dialects.mysql.TIME).
At the moment, the mysql.TIME type does not accept any additional
MySQL-specific arguments, so to support this attribute directly would be a
small enhancement.
So to work around this limitation for now, SQLAlchemy documents the @compiles
extension which allows one to redefine how DDL is emitted for a type:
http://docs.sqlalchemy.org/en/rel_0_6/core/compiler.html#changing-compilation-of-types
Here's a recipe for TIME:
from sqlalchemy import create_engine, Table, Column, MetaData, Integer
from sqlalchemy.dialects.mysql import TIME
from sqlalchemy.ext.compiler import compiles
class FracTime(TIME):
def __init__(self, fractional_seconds=None):
super(FracTime, self).__init__()
self.fractional_seconds = fractional_seconds
@compiles(FracTime, mysql)
def _frac_time(element, compiler, **kw):
if element.fractional_seconds:
return TIME(%d) % element.fractional_seconds
else:
return TIME
m = MetaData()
t = Table('t', m,
Column('id', Integer, primary_key=True),
Column('elapsed', FracTime(2))
)
e = create_engine(mysql://scott:tiger@localhost/test, echo=True)
m.create_all(e)
When I attempt to bypass this problem by manually creating this table
definition by using `session.execute(''' table definition '''), the
MySQL database renders the time in the correct format with two
fractional seconds places after the seconds place (00:00:00.00 instead
of 00:00:00). This is a step closer, however, when I use timedelta to
insert the data, everything except the fractional seconds is parsed
and put into the time datatype correctly. The result is that every
time has .00 fractional seconds (example xx:xx:xx.00).
For most DBAPIs including all of those for MySQL, SQLalchemy passes Python date
and time data directly to the DBAPI.In the case of TIME values, there seems
to be some logic that converts on the result side only, and this is lacking
microseconds. So if you are only seeing an issue on the result side, this could
be a DBAPI or SQLAlchemy issue or both. If you are seeing the issue on the
insert side, that's the DBAPI.
If we need to patch mysql/base.py, here's what that looks like:
diff -r f9e50d71e67c lib/sqlalchemy/dialects/mysql/base.py
--- a/lib/sqlalchemy/dialects/mysql/base.py Tue Jul 03 14:10:39 2012 -0400
+++ b/lib/sqlalchemy/dialects/mysql/base.py Tue Jul 03 17:19:06 2012 -0400
@@ -682,9 +682,10 @@
def process(value):
# convert from a timedelta value
if value is not None:
+microseconds = value.microseconds
seconds = value.seconds
minutes = seconds / 60
-return time(minutes / 60, minutes % 60, seconds - minutes * 60)
+return time(minutes / 60, minutes % 60, seconds - minutes *
60, microseconds)
else:
return None
return process
however, 0.6 is no longer being maintained, so if this patch is needed you'd at
least have to use version 0.7. We can modify the FracTime type above to also
process data directly:
class FracTime(TIME):
def __init__(self, fractional_seconds=None):
super(FracTime, self).__init__()
self.fractional_seconds = fractional_seconds
def result_processor(self, dialect, coltype):
time = datetime.time
def process(value):
# convert from a timedelta value
if value is not None:
microseconds = value.microseconds
seconds = value.seconds
minutes = seconds / 60
return time(minutes / 60,
Re: [sqlalchemy] How do I do this SQL query in SQLAlchemy?
this is a very basic series of joins which can be approached using the
techniques described in the ORM tutorial:
http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#querying-with-joins .
the parenthesization of the joined tables within subqueries is also not needed
and these tables can be joined directly to produce the same result.
On Jul 2, 2012, at 3:06 AM, Jason Phillips wrote:
I have a MySQL database with the following structure and was hoping someone
could help me convert the SQL query below to SQLAlchemy.
Database structure:
bottom:
id
name
middle_id
middle:
id
name
top_id
top:
id
name
Here are my models:
class Bottom(db.Model):
id= db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
middle_id = db.Column(db.Integer, db.ForeignKey('middle.id'))
middle= db.relationship('Middle',
backref=db.backref('bottoms', lazy='dynamic'))
class Middle(db.Model):
id= db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
top_id= db.Column(db.Integer, db.ForeignKey('top.id'))
top = db.relationship('Top',
backref=db.backref('middles', lazy='dynamic'))
class Top(db.Model):
id= db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
Here's the SQL I want to convert to SQLAlchemy:
SELECT
b.*,
m.*,
t.*
FROM bottom AS b
LEFT JOIN (SELECT id, name, top_id from middle) AS m on m.id =
b.middle_id
LEFT JOIN (SELECT id, name FROM top) AS t on t.id = m.top_id
Thank you in advance :).
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/JpDLJzBXBzUJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Query relationship in order
this is approached using joins as described in the ORM tutorial at http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#querying-with-joins . If you'd like to present a specific A to Query without filtering for A rows, you can use with_parent(), see the last example in http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#common-relationship-operators . On Jul 1, 2012, at 3:42 PM, Diego wrote: Hi, Say I have two entities A and B, that have a many to many relationship between them. Now I'd like to get all Bs from a given A, ordered by some attribute of B. Something like select B from A order by B.attr desc How do I express that query using the ORM? Thanks in advance, Diego -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/MUvsZ0Mgu78J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Declaring tables without a metadata
I have a bunch of Mixins for SQL alchemy objects for doing standard stuff that I use in multiple projects and this all works well by using @declared_attr like this: class CategoryMixin(object): @declared_attr def title(self): Unique title for the category return sa.Column(sa.Unicode(30), nullable=False, unique=True) The use of this class would be like this: Base = declarative_base() class Category(CategoryMixin, Base): pass but I now want to make some many to many relationships so I need Table() which requires me to pass it a meta data. Is there a way to do this in the same way as the declared attributes? -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
