[sqlalchemy] MySQL Connector/Python support
Hello, And best wishes for 2010! I am the maintainer, Geert Vanderkelen, of MySQL Connector/Python at Sun Microsystems. First, thanks for taking MySQL Connector/Python and putting it in as a dialect in SQL Alchemy, great stuff already! This will be great for other projects using SQLAlchemy. Very cool! Few things: * Could you change the dialect name from 'myconnpy' to 'mysqlconnector' or something like that. 'MySQL' should be first in the name, as this is most 'official' thing the MySQL Team has done for Python. Would be great. 'myconnpy' is just the short tag on LaunchPad. * Checking the current trunk of SQLAlchemy, it's using a quite old version of MySQL Connector/Python. We've done some development releases lately, and there will be more. The bug mentioned, was fixed though! Maybe, if I'll find time, I'll check the code myself and do some work there too. I'm looking forward for ideas, and improvements for MySQL Connector/ Python to make the integration easier. If you guys have ideas, please post them on LaunchPad. Cheers, Geert -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: MySQL Connector/Python support
On Jan 4, 5:29 pm, Michael Bayer mike...@zzzcomputing.com wrote:
..
* Could you change the dialect name from 'myconnpy' to
'mysqlconnector' or something like that. 'MySQL' should be first in
the name, as this is most 'official' thing the MySQL Team has done for
Python. Would be great. 'myconnpy' is just the short tag on LaunchPad.
renamed to mysqlconnector in r6613.
Thanks so much for this! I think that name will make it more clear
what it is. There was additional myconnpy import in __init__.py,
which I corrected in the patch below.
* Checking the current trunk of SQLAlchemy, it's using a quite old
version of MySQL Connector/Python. We've done some development
releases lately, and there will be more. The bug mentioned, was fixed
though!
There were two bugs that were being worked around, and for some reason the
hyperlink in the source code were all for just one bug; additionally I
can't seem to find any of those bugs on the launchpad site which doesn't
seem to have any way to show resolved bugs, or they were removed, not
really sure. Anyway I've removed both workarounds in r6613 - using
cursor.lastrowid now, using dbapi.paramstyle as is without forcing to
positional. Feel free to try out trunk and run the unit tests,
specifying uri=mysql+mysqlconnector://user:n...@host/db !
Bugs should be kept on Launchpad, but lets see how we can make it work
with the development release of MySQL Connector/Python.
One change I did was _get_server_version_info(), see in patch
below.
I ran the tests, not sure how normal the number of errors is :)
(Using MySQL Connector/Python 0.1.2-dev)
shell nosetests --dburi=mysql+mysqlconnector://root:@localhost/
sqlalchemy
..
--
Ran 784 tests in 49.766s
FAILED (errors=499, failures=2)
Looking into a few.
-Geert
Index: lib/sqlalchemy/dialects/mysql/__init__.py
===
--- lib/sqlalchemy/dialects/mysql/__init__.py (revision 6614)
+++ lib/sqlalchemy/dialects/mysql/__init__.py (working copy)
@@ -1,4 +1,4 @@
-from sqlalchemy.dialects.mysql import base, mysqldb, oursql, pyodbc,
zxjdbc, myconnpy
+from sqlalchemy.dialects.mysql import base, mysqldb, oursql, pyodbc,
zxjdbc, mysqlconnector
# default dialect
base.dialect = mysqldb.dialect
Index: lib/sqlalchemy/dialects/mysql/mysqlconnector.py
===
--- lib/sqlalchemy/dialects/mysql/mysqlconnector.py (revision 6614)
+++ lib/sqlalchemy/dialects/mysql/mysqlconnector.py (working copy)
@@ -57,13 +57,7 @@
def _get_server_version_info(self, connection):
dbapi_con = connection.connection
-version = []
-r = re.compile('[.\-]')
-for n in r.split(dbapi_con.get_server_version()):
-try:
-version.append(int(n))
-except ValueError:
-version.append(n)
+version = dbapi_con.get_server_version()
return tuple(version)
def _detect_charset(self, connection):
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: MySQL Connector/Python support
geertjanvdk wrote: Thanks so much for this! I think that name will make it more clear what it is. There was additional myconnpy import in __init__.py, which I corrected in the patch below. oops, yeah , that is in r6615 thx One change I did was _get_server_version_info(), see in patch below. that change is also in r6615. I ran the tests, not sure how normal the number of errors is :) (Using MySQL Connector/Python 0.1.2-dev) shell nosetests --dburi=mysql+mysqlconnector://root:@localhost/ sqlalchemy .. -- Ran 784 tests in 49.766s FAILED (errors=499, failures=2) For MySQL dialects there should be no errors or failures - at most there may be a handful of errors/failures which correspond to tests that should be skipped for that DBAPI, although if you're getting one of those which leads the database into an invalid state it may result in a cascade of many failures. It's more likely that the mysqlconnector dialect has some outdated elements in it which need to be updated, or perhaps that there's some DBAPI behavior not yet provided by mysqlconnector that SQLA is expecting. A log of tests running with MySQLdb as well as OurSQL is at http://paste.pocoo.org/show/161970/ -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: MySQL Connector/Python support
Thanks for the quick changes there! On Jan 4, 7:45 pm, Michael Bayer mike...@zzzcomputing.com wrote: .. For MySQL dialects there should be no errors or failures - at most there may be a handful of errors/failures which correspond to tests that should be skipped for that DBAPI, although if you're getting one of those which leads the database into an invalid state it may result in a cascade of many failures. OK, so I guess we got still a long way to go. :) I ran tests with MySQLdb but ran in lots of failures, but I'm concentrating on MySQL Connector/Python for now. One thing I can't figure out is why tests are not cleaning up or dropping tables. Works apparently good with MySQLdb, but not with our Connector. Something for tomorrow on the train. It's more likely that the mysqlconnector dialect has some outdated elements in it which need to be updated, or perhaps that there's some DBAPI behavior not yet provided by mysqlconnector that SQLA is expecting. A log of tests running with MySQLdb as well as OurSQL is athttp://paste.pocoo.org/show/161970/ Ah, attached another small patch, no need to parse, if you got the errno right from the exception :) -Geert === --- lib/sqlalchemy/dialects/mysql/mysqlconnector.py (revision 6615) +++ lib/sqlalchemy/dialects/mysql/mysqlconnector.py (working copy) @@ -66,11 +66,10 @@ return connection.connection.get_characterset_info() def _extract_error_code(self, exception): -m = re.compile(r\(.*\)\s+(\d+)).search(str(exception)) -c = m.group(1) -if c: -return int(c) -else: +try: +if exception.errno -1: +return int(exception.errno) +except: return None -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: new sqlalchemy user having problems to insert...
Hi Mike, Thank you very much for replying to my post. I really appreciate it. I agree that the init of the classes may have something to do with the problem. Maybe the way we are defining the attributes does not allow to SQLAlchemy table wrappers to view the attributes' values? What confuses me the most is that I am actually able to insert a row using sprint-python with the addxLPRVariable function below. Both approaches use the same variable classes. The row with id=33 below was inserted using spring-python. The row with id=32 below was inserted using SQLAlchemy. mysql select * from variables where id =32; +--+---+---+--+--- +--+ | id | value | units | name | dependent | independent_variable | +--+---+---+--+--- +--+ | 0032 | 1 | | | NULL | NULL | | 0033 | 1 | Hz| variable | 0 | NULL | +--+---+---+--+--- +--+ 2 rows in set (0.00 sec) #-- # This function writes to the database: #-- # def addxLPRVariable( self, xLPRVariable ): Add an xLPRVariable to the database. rowsAffected=self.database_template.execute( INSERT INTO variables (id, name, units, value, dependent) VALUES (null, ?, ?, ?, ?) , ( xLPRVariable.getName(), xLPRVariable.getUnits(), xLPRVariable.getValue(), xLPRVariable.isDependent() ) ) self.connection_factory.commit() logger.debug( Added %d rows%rowsAffected ) print Added %d%rowsAffected return rowsAffected #-- # Here are the sql scripts #-- # create table variables ( id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Primary key, sequence', value float NOT NULL COMMENT 'Initial value for variable', units varchar(45) NOT NULL COMMENT 'Common Si units of variable', name varchar(45) NOT NULL COMMENT 'Name of the variable', dependent boolean, independent_variable float COMMENT 'The independent variable x', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Maps to the xLPRVariable Object'; create table dependence_variables ( id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Primary key, sequence', variable_id bigint(20) unsigned zerofill COMMENT 'Foreign key to dependency_variable table', interpolate boolean, dependent_function varchar(4000) COMMENT 'The dependent function', evaluate boolean, xname_units varchar(45), xname varchar(45), PRIMARY KEY (id), FOREIGN KEY (variable_id) REFERENCES variables(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Maps to the dependency_variable object attribute'; create table data_set ( id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Primary key, sequence', dependence_variable_id bigint(20) unsigned zerofill NOT NULL COMMENT 'data_set will be grouped by data_set_ids', x float NOT NULL COMMENT 'Value of x', y float NOT NULL COMMENT 'Value of y', PRIMARY KEY (id), FOREIGN KEY (dependence_variable_id) REFERENCES dependence_variables (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Maps to the dependency_variable.data_set attribute'; #-- # Here are the classes definition #-- # class xLPRVariable( VariableQuantity ): ''' Defines a VariableUnitsAware object that may be a function of another independent variable x which is also a units-aware object classdocs args[0] = value:float args[1] = units:str kwargs['name'] = name:str kwargs['dependenceVariable'] = dependenceVariable:DependenceVariable ''' def __init__( self, *args, **kwargs ): ''' Constructor positional/required arguments: value:float = initial value for variable units:String = common SI units of variable Constructor keyed/optional arguments: name:String = name of variable dependent:Boolean is variable dependent upon another SIAM variable dependenceVariable:DependenceVariable ''' #=== # call the
[sqlalchemy] Re: new sqlalchemy user having problems to insert...
Here goes the variables' parent class
#===
# Variable quantities with units
# Written by Konrad Hinsen hin...@cnrs-orleans.fr
# with contributions from Greg Ward
# last revision: 2007-5-25
# Revised for SIAM-PFM by Paul Williams william...@ornl.gov
#
#===
class VariableQuantity( object ):
'''
Variable quantities with units.
This module provides a data type that represents a physical
quantity together with its units. It is possible to add and
subtract these quantities if the units are compatible, and
a quantity can be converted to another compatible unit.
Multiplication, subtraction, and raising to integer powers
is allowed without restriction, and the result will have
the correct unit. A quantity can be raised to a non-integer
power only if the result can be represented by integer powers
of the base units.
The values of physical constants are taken from the 1986
recommended values from CODATA. Other conversion factors
(e.g. for British units) come from various sources.
VariableQuantity instances allow addition, subtraction,
multiplication, and division with each other as well as
multiplication, division, and exponentiation with numbers.
Addition and subtraction check that the units of the two operands
are compatible and return the result in the units of the first
operand. A limited set of mathematical functions (from module
Numeric) is applicable as well:
- sqrt: equivalent to exponentiation with 0.5.
- sin, cos, tan: applicable only to objects whose unit is
compatible with 'rad'.
See the documentation of the VariableQuantities module for a list
of the available units.
Here is an example on usage:
from PhysicalVariables import VariableQuantity as p # short
hand
distance1 = p('10 m')
distance2 = p('10 km')
total = distance1 + distance2
total
VariableQuantity(10010.0,'m')
total.convertToUnit('km')
total.getValue()
10.01
total.getUnitName()
'km'
total = total.inBaseUnits()
total
VariableQuantity(10010.0,'m')
t = p(314159., 's')
# convert to days, hours, minutes, and second:
t2 = t.inUnitsOf('d','h','min','s')
t2_print = ' '.join([str(i) for i in t2])
t2_print
'3.0 d 15.0 h 15.0 min 59.0 s'
e = p('2.7 Hartree*Nav')
e.convertToUnit('kcal/mol')
e
VariableQuantity(1694.2757596034764,'kcal/mol')
e = e.inBaseUnits()
str(e)
'7088849.77818 kg*m**2/s**2/mol'
freeze = p('0 degC')
freeze = freeze.inUnitsOf ('degF')
str(freeze)
'32.0 degF'
'''
def __init__( self, *args ):
'''
There are two constructor calling patterns:
1. VariableQuantity(value, unit), where value is any
number
and unit is a string defining the unit
2. VariableQuantity(value_with_unit), where
value_with_unit
is a string that contains both the value and the unit,
i.e. '1.5 m/s'. This form is provided for more convenient
interactive use.
@param args: either (value, unit) or (value_with_unit,)
@type args: (number, C{str}) or (C{str},)
'''
if len( args )==2:
self.value=args[0]
self.unit=_findUnit( args[1] )
else:
s=string.strip( args[0] )
match=VariableQuantity._number.match( s )
if match is None:
raise TypeError( 'No number found' )
self.value=string.atof( match.group( 0 ) )
self.unit=_findUnit( s[len( match.group( 0 ) ):] )
_number=re.compile( '[+-]?[0-9]+(\\.[0-9]*)?([eE][+-]?[0-9]+)?' )
def __str__( self ):
return str( self.value )+' '+self.unit.name()
def __repr__( self ):
return ( self.__class__.__name__+'('+`self.value`+','+
`self.unit.name()`+')' )
def _sum( self, other, sign1, sign2 ):
if not isVariableQuantity( other ):
raise TypeError( 'Incompatible types' )
new_value=sign1*self.value+\
sign2*other.value*other.unit.conversionFactorTo
( self.unit )
return self.__class__( new_value, self.unit )
def __add__( self, other ):
return self._sum( other, 1, 1 )
__radd__=__add__
def __sub__( self, other ):
return self._sum( other, 1,-1 )
def __rsub__( self, other ):
return self._sum( other,-1, 1 )
def __cmp__( self, other ):
diff=self._sum( other, 1,-1 )
return cmp( diff.value, 0 )
def __mul__( self, other ):
if not isVariableQuantity( other ):
return self.__class__( self.value*other, self.unit )
value=self.value*other.value
unit=self.unit*other.unit
if unit.isDimensionless():
return value*unit.factor
Re: [sqlalchemy] Re: new sqlalchemy user having problems to insert...
seth wrote: Hi Mike, Thank you very much for replying to my post. I really appreciate it. I agree that the init of the classes may have something to do with the problem. Maybe the way we are defining the attributes does not allow to SQLAlchemy table wrappers to view the attributes' values? hi Seth - to be honest, I withheld saying anything earlier, but the very java-esque style of code you have here is really not helping the performance of your application or its readability.In Python, using explicit setters and getters for all instance variables is unnecessary and adds palpable overhead - since Python's method dispatch is nowheres near as efficient as that of Java's. The famous rant on this topic is here: http://dirtsimple.org/2004/12/python-is-not-java.html Also, the __name style of naming, while it is part of pep8 and all, is becoming less and less common these days as the implicit name mangling of the Python interpreter leads to confusing results. I don't see your mappings here but if in fact you're trying to map to __name style attributes, its unlikely to work properly since SQLA doesn't take into account the name mangling inherent in the usage of such names. It is a likely candidate for the issue you're having. What confuses me the most is that I am actually able to insert a row using sprint-python with the addxLPRVariable function below. Both approaches use the same variable classes. The row with id=33 below was inserted using spring-python. The row with id=32 below was inserted using SQLAlchemy. mysql select * from variables where id =32; +--+---+---+--+--- +--+ | id | value | units | name | dependent | independent_variable | +--+---+---+--+--- +--+ | 0032 | 1 | | | NULL | NULL | | 0033 | 1 | Hz| variable | 0 | NULL | +--+---+---+--+--- +--+ 2 rows in set (0.00 sec) #-- # This function writes to the database: #-- # def addxLPRVariable( self, xLPRVariable ): Add an xLPRVariable to the database. rowsAffected=self.database_template.execute( INSERT INTO variables (id, name, units, value, dependent) VALUES (null, ?, ?, ?, ?) , ( xLPRVariable.getName(), xLPRVariable.getUnits(), xLPRVariable.getValue(), xLPRVariable.isDependent() ) ) self.connection_factory.commit() logger.debug( Added %d rows%rowsAffected ) print Added %d%rowsAffected return rowsAffected #-- # Here are the sql scripts #-- # create table variables ( id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Primary key, sequence', value float NOT NULL COMMENT 'Initial value for variable', units varchar(45) NOT NULL COMMENT 'Common Si units of variable', name varchar(45) NOT NULL COMMENT 'Name of the variable', dependent boolean, independent_variable float COMMENT 'The independent variable x', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Maps to the xLPRVariable Object'; create table dependence_variables ( id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Primary key, sequence', variable_id bigint(20) unsigned zerofill COMMENT 'Foreign key to dependency_variable table', interpolate boolean, dependent_function varchar(4000) COMMENT 'The dependent function', evaluate boolean, xname_units varchar(45), xname varchar(45), PRIMARY KEY (id), FOREIGN KEY (variable_id) REFERENCES variables(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Maps to the dependency_variable object attribute'; create table data_set ( id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Primary key, sequence', dependence_variable_id bigint(20) unsigned zerofill NOT NULL COMMENT 'data_set will be grouped by data_set_ids', x float NOT NULL COMMENT 'Value of x', y float NOT NULL COMMENT 'Value of y', PRIMARY KEY (id), FOREIGN KEY (dependence_variable_id) REFERENCES dependence_variables (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Maps to the dependency_variable.data_set attribute';
