The original MSSQL table reflection code was, way back in the day (like, a few months ago) based on the reflection code for Postgres, which still seems to do roughly the same thing.
But yep, when you query the MS-SQL INFORMATION_SCHEMA.COLUMNS table for a datetime column, it does indeed return NUMERIC_PRECISION = 23, NUMERIC_SCALE = 3. Whether that makes sense for a datetime column is debatable, the but the resulting effect is not, and the workaround makes sense. I'll put it in, and thanks for the patch!
Regards,
Rick
On 9/26/06, Terrence Brannon <
[EMAIL PROTECTED]> wrote:
On 9/26/06, Rick Morrison <[EMAIL PROTECTED] > wrote:Hey Terrence,
Howdy ho!
regarding the Datetime type -- not sure I understand where the [23,3] came from -- can you give a testcase?
Well, I can give the you the CREATE TABLE for the table that caused it (below) and I can tell you why [23, 3] is there (I just traced the code).
The reason it is happening is that args is a list whose items are set as follows in mssql.py:
(name, type, nullable, charlen, numericprec, numericscale, default) = (
row[columns.c.column_name],
row[columns.c.data_type],
row[columns.c.is_nullable ] == 'YES',
row[columns.c.character_maximum_length],
row[columns.c.numeric_precision],
row[columns.c.numeric_scale],
row[columns.c.column_default ]
)
args = []
for a in (charlen, numericprec, numericscale):
if a is not None:
args.append(a)
Which basically means that args will be a list of length 0 to 3 based on how many of character length, numeric precision and numeric scale were not None. Because two of them (I'm guessing precision and scale) were not None, args became [23, 3] # numericprecision = 23, scale = 3
These values were then passed to MSDateTime() which then passed them to it's parent's __init__() method (sqlalchemy.types.datetime()) which only took one argument. Thereby leading to the barf-fest that I was witness to this morning :)
--- the last column in this table is what caused the problem ---
CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NULL ,
[Password] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [int] NULL ,
[SALT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecurityLevel] [int] NOT NULL ,
[DateCreated] [datetime] NULL
) ON [PRIMARY]
GOThanks,
RickOn 9/26/06, Terrence Brannon <[EMAIL PROTECTED] > wrote:Well, the patch below to v1898 for me. I can get things out of my database as a dictionary. So I'm happy. Someone spelled "uppercase" as "uupercase" in the code, so I fixed that too. I'm not sure what the best way to submit patches is, so please advise.-------------------------------------------------------------------------
<!-- ~/Downloads/sqlalchemy tbrannon --> svn diff
Index: lib/sqlalchemy/databases/mssql.py
===================================================================
--- lib/sqlalchemy/databases/mssql.py (revision 1898)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -105,6 +105,12 @@
return "SMALLINT"
class MSDateTime(sqltypes.DateTime):
+ def __init__(self, *args):
+ # sqltype.DateTime () only takes self and optionally timezone as args
+ # but it was being called with self and *args = [23, 3] leading to a
+ # an error. I therefore created a constructor and dropped all the
+ # arguments to that constructor and then called the parent constructor.
+ self = sqltypes.DateTime()
def get_col_spec(self):
return "DATETIME"
@@ -423,6 +429,9 @@
if a is not None:
args.append(a)
coltype = ischema_names[type]
+ import pprint
+ print ("Calling %s with :" % coltype)
+ pprint.pprint(args)
coltype = coltype(*args)
colargs= []
if default is not None:
@@ -450,10 +459,10 @@
ic.sequence = schema.Sequence(ic.name + '_identity')
# Add constraints
- RR = self.uupercase_table(ischema.ref_constraints ) #information_schema.referential_constraints
- TC = self.uupercase_table(ischema.constraints) #information_schema.table_constraints
- C = self.uupercase_table(ischema.column_constraints).alias('C') #information_schema.constraint_column_usage: the constrained column
- R = self.uupercase_table(ischema.column_constraints).alias('R') #information_schema.constraint_column_usage: the referenced column
+ RR = self.uppercase_table(ischema.ref_constraints) #information_schema.referential_constraints
+ TC = self.uppercase_table(ischema.constraints) #information_schema.table_constraints
+ C = self.uppercase_table(ischema.column_constraints).alias('C') #information_schema.constraint_column_usage: the constrained column
+ R = self.uppercase_table(ischema.column_constraints).alias('R') #information_schema.constraint_column_usage: the referenced column
fromjoin = TC.join(RR, RR.c.constraint_name == TC.c.constraint_name ).join(C, C.c.constraint_name == RR.c.constraint_name)
fromjoin = fromjoin.join(R, R.c.constraint_name == RR.c.unique_constraint_name)
<!-- ~/Downloads/sqlalchemy tbrannon -->
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
