[sqlalchemy] copy database from MS SQL Server database to PostgreSQL database
So what I want to do is to reflect all the tables in an existing MS SQL Server database, and use for loop on sorted_tables list and tometadata() function to create a new PostgreSQL database. However, there are some datatype incompatibility. Got an exception about collation on MS SQL server tables. Wondering if there any way to work around this, may be write decorators for some SA API or something? Thank you. ---Tony -- 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: mapper for ms sql server
I think what desmaj said makes a good point. Actually the Source table/ mapper indeed was created by someone else, and I just used Attach database to attach the mdf file and log file to the server. But how am I going to deal with this though? should I go ahead and use SA's account and create a copy of this database? Thanks... -- 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] mapper for ms sql server
Hello. Recently I have been working on this environment: linux + pyodbc + sqlalchemy 5.8 + ms sql server. In the program, I try to build a mapper from a db table to my python class, then I instatiate the class, and try to use session.add() to insert record to database. However, I need to have alter any schema permission in order to make it through, If I do not have this permission, I will get File C:\Python25\lib\site-packages\sqlalchemy\databases\mssql.py, line 954, in pre_exec self.dialect.identifier_preparer.format_table(self.compiled.statement.table)) ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object Sources because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)') exception. wondering is If there is any other way to work around this issue, assuming no alter any schema permission given. Thanks! ---Tony -- 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: sqlalchemy reflection
Wondering if I need the creating table, or creating views priviledge to reflect tables. -- 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: sqlalchemy reflection
Don't want to reask, but help me please. Can somebody tell me what kind of permission (in terms of ms sql server) do I need in order to do table reflection? Thank you! -- 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: sqlalchemy reflection
After 6 hours of searching and debugging,finally figured out the permission needed for table reflection, those are: select, and connect replication for ORM to work, you need one more: Alter any schema to do add() or delete() you obviously need more permission, respectively. Those are specific permissions under MS Sql server 2005. When doing reflection and orm mapping, did not see any exception regarding permissions. Anyway, thank you Michael. You info helps. -- 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] sqlalchemy reflection
quick question: for sqlalchemy+ms sql server, what kind of accessibility does it need to do table reflection? What I am trying to do is that I try to reflect a table from an existing database, and I successfully created an engine. But I got following exception: raise exc.DBAPIError.instance(None, None, e) sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000] [Microsof t][ODBC SQL Server Driver][SQL Server]Cannot open database HisTpwdCoastal requ ested by the login. The login failed. (4060) (SQLDriverConnectW); [42000] [Micro soft][ODBC SQL Server Driver][SQL Server]Cannot open database HisTpwdCoastal r equested by the login. The login failed. (4060)') None None The connection string to mssql server only has delete, insert, and update capabilities . Wondering if any more capabilities needed. Thank you! -- 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: The correct usage of use_alt to avoid circular dependency
Thanks for clarification. Sorry that I do not think I understand circular dependency and compsite key very well. Also can you help me to read the circular dependency error message? I mean How to find the circle from the error message of Circular Dependency. Here is the error message: sqlalchemy.exc.CircularDependencyError: Circular dependency detected [(DataValues, Groups), (DataValues, DerivedFrom), (Sites, TexasStream), (Sites, DataValues), (Sites, ReservoirRatingCurve), (Sites, Reservoir), (TexasStream, HydrologicUnit), (TexasStream, MajorWaterRight), (TexasStream, Reservoir), (Reservoir, MajorWaterRight), (Reservoir, ReservoirRatingCurve), (Reservoir, StorageInformation), (MajorWaterRight, Reservoir), (HydrologicUnit, Sites)][] Thanks a lot... -- 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] The correct usage of use_alt to avoid circular dependency
I want to create a table, say: employee_table = Table( 'employee',metadata, Column('id',Integer,primary_key=True), Column('name',String(255)) ) staffGroup_Table = Table( 'role',metadata, Column('manager',None,ForeignKey('employee.id')), Column('worker',None,ForeignKey('employee.id')), Column('janitorr',None,ForeignKey('employee.id')) ) metadata.create_all() however this will generate circular dependency, I tried to use use_alt = True with ForeignKey constraint , but no luck. What is the correct way of creating table for this kind of situation : a table refers to another table with a composite foreign keys on the same column? Thank you! -- 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: ms sql server schema.sequence
Thanks a lot, this is the information I am looking for! -- 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] ms sql server schema.sequence
Whenever I try to use schema.sequence() function on ms sql server, the return serial number (after flush()) is not started from 0, instead, it is started from a random number or something. wondering how to work aroud this issue. try to specify start = 0 in sequence function, but no luck -- 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: ms sql server schema.sequence
I mean, I tried to debug a program, using session add() to insert new records to database after I deleted all the old records from a table. I used the schema.sequence() to build up a mapper Can anyone help? On 3月16日, 下午2时05分, Tan Yi tonytan198...@gmail.com wrote: Whenever I try to use schema.sequence() function on ms sql server, the return serial number (after flush()) is not started from 0, instead, it is started from a random number or something. wondering how to work aroud this issue. try to specify start = 0 in sequence function, but no luck -- 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] pymssql issues with nvarchar ntext
pymssql does not suport reading in nvarchar and ntext since it is encoded as unicode in sqlserver. is any API could convert the query result into Unicode? Tried 'convert_unicode' argument in create_engine, and it seems not working. Thank you! --Tony -- 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.