Re: [sqlalchemy] fractional second percision- mysql
I have created a feature request ticket for MySQLdb to add fractional second support: http://sourceforge.net/tracker/?func=detailaid=3545195group_id=22307atid=374935 Currently, I am still using my patched version of MySQLdb/times.py however I did notice a slight formatting issue with my original patch. To fix the issue, the return statement of 'def format_TIMEDELTA(v)' now reads: return '%d %d:%d:%d.%06d' % (v.days, hours, minutes, seconds, microseconds) Hopefully, MySQLdb will add support on their end, so you can proceed with committing the hypothetical changes that you suggested, which that work for me. Thank you for your help. -- 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/-/x_-Bd0lbg2gJ. 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
Yeah, I kind of suspected MySQLdb might have problems here. You need to file a bug report with the DBAPI's bug tracker: http://sourceforge.net/tracker/?group_id=22307atid=374932 I didn't see anything on the subject of fractional second support but I ended up getting the insert working by the format_TIME function in /MySQLdb/times.py to process microseconds: def format_TIMEDELTA(v): microseconds = v.microseconds seconds = float(v.seconds) % 60 minutes = int(v.seconds / 60) % 60 hours = int(v.seconds / 3600) % 24 return '%d %d:%d:%d.%d' % (v.days, hours, minutes, seconds, microseconds) Thank you for your help with the SQLalchemy side of things, redefining how the DDL is emitted for the type and whatnot. Hopefully we can see these changes in future releases of the 0.7 series. --James -- 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/-/9u5Yiq9v6LMJ. 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 13, 2012, at 6:36 PM, James wrote: Yeah, I kind of suspected MySQLdb might have problems here. You need to file a bug report with the DBAPI's bug tracker: http://sourceforge.net/tracker/?group_id=22307atid=374932 I didn't see anything on the subject of fractional second support but I ended up getting the insert working by the format_TIME function in /MySQLdb/times.py to process microseconds: def format_TIMEDELTA(v): microseconds = v.microseconds seconds = float(v.seconds) % 60 minutes = int(v.seconds / 60) % 60 hours = int(v.seconds / 3600) % 24 return '%d %d:%d:%d.%d' % (v.days, hours, minutes, seconds, microseconds) Thank you for your help with the SQLalchemy side of things, redefining how the DDL is emitted for the type and whatnot. Hopefully we can see these changes in future releases of the 0.7 series. are you saying you patched mysqldb/times.py directly? yeah, MySQLdb would need to implement this change properly for the SQLAlchemy side of things to be of any use. We can't just tell our users to patch their MySQLdb clients. harmless enough for us to look for the microseconds portion on our end, anyway. -- 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
I have not made any changes, have only proposed some hypothetical changes for the 0.7 series. I don't have this newest version of MySQL installed, so I was asking you to test the workaround I gave and/or the patch, to ensure it solves all the problems fully. This testing would also establish that the MySQL DBAPI is properly receiving/returning the microseconds field. If the workarounds I gave solve your problem fully, then I can commit the patch to SQLAlchemy and resolve that we are doing all that's needed for fractional time support. I have tried implementing your suggestions. Unfortunately, your workaround did not solve my problem fully. I successfully created the FracTime type which extends Time and that shows a field for fractional seconds. However, when I try to insert values into the FracTime column, I am still unable to to specify the fractional seconds part. Even after modifying Time's process method in lib/sqlalchemy/dialects/mysql/base.py to accept microseconds, I was still unable to get it to work. To ensure that it was not a problem with sqlalchemy, I turned on logging to look at was being passed to the DBAPI. What I see is: ... INFO:sqlalchemy.engine.base.Engine.0x...a3d0:(4L, 7.48, 0.27, datetime.timedelta (0, 8, 26), 93, 0, 0, 62032, 0, 0, 19, 15573, 0) ... It looks like datetime.timedelta is holding the microseconds correctly (that is 0 minutes, 8 seconds and 26 microseconds). It looks to me that sqlalchemy is correctly passing the microsecond value onto the DBAPI. After your workaround, this seems to have confirmed that I am having a problem with my DBAPI's (which I think is MySQLdb) communication with the db. Please let me know if you have anymore ideas. Thank you for your suggestions. -James -- 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/-/OLtWHwRJzKMJ. 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 10, 2012, at 2:08 PM, James wrote: I have not made any changes, have only proposed some hypothetical changes for the 0.7 series. I don't have this newest version of MySQL installed, so I was asking you to test the workaround I gave and/or the patch, to ensure it solves all the problems fully. This testing would also establish that the MySQL DBAPI is properly receiving/returning the microseconds field. If the workarounds I gave solve your problem fully, then I can commit the patch to SQLAlchemy and resolve that we are doing all that's needed for fractional time support. I have tried implementing your suggestions. Unfortunately, your workaround did not solve my problem fully. I successfully created the FracTime type which extends Time and that shows a field for fractional seconds. However, when I try to insert values into the FracTime column, I am still unable to to specify the fractional seconds part. Even after modifying Time's process method in lib/sqlalchemy/dialects/mysql/base.py to accept microseconds, I was still unable to get it to work. To ensure that it was not a problem with sqlalchemy, I turned on logging to look at was being passed to the DBAPI. What I see is: ... INFO:sqlalchemy.engine.base.Engine.0x...a3d0:(4L, 7.48, 0.27, datetime.timedelta (0, 8, 26), 93, 0, 0, 62032, 0, 0, 19, 15573, 0) ... It looks like datetime.timedelta is holding the microseconds correctly (that is 0 minutes, 8 seconds and 26 microseconds). It looks to me that sqlalchemy is correctly passing the microsecond value onto the DBAPI. After your workaround, this seems to have confirmed that I am having a problem with my DBAPI's (which I think is MySQLdb) communication with the db. Please let me know if you have anymore ideas. Thank you for your suggestions. Yeah, I kind of suspected MySQLdb might have problems here. You need to file a bug report with the DBAPI's bug tracker: http://sourceforge.net/tracker/?group_id=22307atid=374932 poke around there and see if someone's already requested this feature. -- 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
Michael,
Thank you for your response. Your help is greatly appreciated. Just to be
clear, are these changes that you have made and that I can access if I
update to 0.7 or later? And would I simply need to specify the new FracTime
type instead of Time?
Example:
meta_timings = Table('meta_timings', metadata,
...
Column('elapsed', FracTime, nullable=False),
...)
--James
--
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/-/Xj8EoZp3NBkJ.
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 8, 2012, at 3:21 PM, James wrote:
Michael,
Thank you for your response. Your help is greatly appreciated. Just to be
clear, are these changes that you have made and that I can access if I update
to 0.7 or later? And would I simply need to specify the new FracTime type
instead of Time?
I have not made any changes, have only proposed some hypothetical changes for
the 0.7 series. I don't have this newest version of MySQL installed, so I was
asking you to test the workaround I gave and/or the patch, to ensure it solves
all the problems fully. This testing would also establish that the MySQL DBAPI
is properly receiving/returning the microseconds field.
If the workarounds I gave solve your problem fully, then I can commit the patch
to SQLAlchemy and resolve that we are doing all that's needed for fractional
time support.
Example:
meta_timings = Table('meta_timings', metadata,
...
Column('elapsed', FracTime, nullable=False),
...)
--James
--
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/-/Xj8EoZp3NBkJ.
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] 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,
