MS SQL Server: Change Default Schema

[jeffny2015]

Hi,

To make a query in SQL Server to specific table i have and intermediate scheme to stableish the access (dbname.schema_name.table_name), how do i set the schema to query that table????

I know to connect to db you use the connection string to stablish the connection to databe, only left to set the schema name to get the table, how i do that???

[pythononwheels]

Hi jeffny2015,

Can you post the PythonOnWheels version you are using?

And can you post some example code (the model forexample) and gibe a little more input about the DB Scheme and what you try to achieve ?

Thx and regards

[jeffny2015]

Version:pythononwheels 0.924 I cannot give you the exact source but for example in MS SQL Server dabase I'm working on, to connect to that db I use an string connection something like this:

mssql+pyodbc:///Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd?driver={SQL Server}

The connection is ok, but this database has multiple schemas, for example the default schema in SQL Server is the "dbo" schema, so if i want to access a table in "dbo" schema i have to make a query like this: Select from [dbo].[table_name], if i want to query a table that is not in "dbo" I should query something like: Select from [schema_name].[table_name], so my question is how i can set this schema_name in the pythononwheels framework.

Note: Using the complete query(using also the dbname), the query should be like this: Select * from [database_name].[schema_name].[table_name]

[pythononwheels]

Since we use sqlalchemy under the hood, and sqlalchemy supports multi tenancy since 1.1 the following should work:

Multi-Tenancy Schema Translation

For example, if the User class were assigned the schema “per_user”:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

    __table_args__ = { 'schema': 'per_user'}

On each request, the Session can be set up to refer to a different schema each time:

session = Session()
session.connection(execution_options={
    "schema_translate_map": {"per_user": "account_one"}})

will query from the account_one.user table

So if you for example create a model User:

1. set the __table_args__ = {, 'schema': 'whatever_your_case_is'} in your model (as shown above)
2. before you execute an operation on the model you can point the session that is bound to each model to the right schema

Your pow model:

from models.sql.user import User
u=User()
u.session.connection(execution_options={"schema_translate_map": {"per_user": "account_one"}})
....

Make sure to set and reset the session before your operations.

[jeffny2015]

Hi

I will try that and i let you know what results i got

[jeffny2015]

Hi tryng to recreate what you said,i got an error , this is My model class

from sqlclchemy import Column, Integer, String Boolean, Sequence
from sqlalchemy import BigInteger, Date, DateTime, Float,Numeric, Unicode, Text
from pythononwheelsapp.lib.powlib import relation
from pythononwheelsapp.database.sqlib import Base

@relation.setup_sql_schema()
class User(Base):#,metaclass=PowBaseMeta
    schema = {}
    __tablename__ = 'user'
    __use_pow_schema_attrs = False
    __incluede_attributes = []
    __table_args__ = {"extend_existing":True, "autoload":True,  'schema': 'per_user'}

    def __init__(self, **kargs):
          self.setup_instance_values()
          self.init_on_load(**kargs)

Executing this on the python shell , i'm getting error while creating the User Instance

python3.8

>>>> from pythononwheelsapp.models.sql.User import User
__setup_sql_schema:user
>>>> u = User()
venv\lib\site-packages\sqlalchemy\orm\state.py, line 433, in _initialize_instance
manager.dispatch.init_failure(self, args, kwargs)
venv\lib\site-packages\sqlalchemy\util\langhelpers.py, line 68, in __exit__
compat.raise()
venv\lib\site-packages\sqlalchemy\util\compat.py, line 178, in raise_
raise exception
venv\lib\site-packages\sqlalchemy\orm\state.py, line 430, in _initialize_instance
return manager.original_init(*mixed[1:], **kwargs)
pythononwheelsapp\models\sql\User.py, line 37, in __init__
self.init_on_load(**kwargs)
pythononwheelsapp\models\sql\basemodel.py, line 65, in init_on_load
jschema_class = type(self.class_name+'Schema',(modelSchema,),
venv\lib\site-packages\marshmallow\schema.py, line 121, in__new__
klass.declared_fields = mcs.get_declared_fields(
venv\lib\site-packages\marshmallow_sqlalchemy\schema\schema_meta.py, line 19, in get_declared_fields
fields = mcs.get_fields(converter, opts, declared_fields, dict_cls)
venv\lib\site-packages\marshmallow-sqlalchemy\schema\model_schema.py, line 42, in get_fields
return converter.fields_for_model(
venv\lib\site-packages\marshmallow-sqlalchemy\convert.py, line 111, in fields_for_model
field = base_fields.get(key) or self.property2field(prop)
venv\lib\site-packages\marshmallow-sqlalchemy\convert.py, line 146, in property2field
field_class = field_class or self.get_field_class_for_property(prop)
venv\lib\site-packages\marshmallow-sqlalchemy\convert.py, line 211, in _get_field_class_for_property
field_cls = self._get_field_class_for_column(column)
venv\lib\site-packages\marshmallow-sqlalchemy\convert.py, line 177, in _get_field_class_for_column
return self._get_field_class_for data_type(colemn.type)
venv\lib\site-packages\marshmallow-sqlalchemy\convert.py, line 201, in _get_field_class_for_data_type
raise ModelConversionError(
marshmallow_sqlalchemy.exceptions.ModelConversionError: could not find field column of type <class 'sqlalchemy.dialects.mssql.base.UNIQUEIDENTIFIER'>.

[jeffny2015]

Hi khz,

I think i founf the problem of this,

just changing the code in:

pythononwheelsapp\models\sql\basemodel.py, line 65, in init_on_load jschema_class = type(self.class_name+'Schema',(modelSchema,),

by

from sqlalchemy
jschema_class = type(self.class_name+'Schema',(ModelConverter,),#modelSchema,),

this solved the issue, but still with problems

then i got other problem in the: "basemodel.py" file

# I got and error here with keyerror
self.table = self.metadata.tables[self.__class__.__tablename__]
# i fixed by this way, schema.table
self.table = self.metadata.tables[self._class_.__table_args__["schema"] + "." + self.__class__.__tablename__]

then i got issue type in the: def __setup_schema_from_sql(self) method looping the tables.columns.items,

for idx, col in enumerate(self.table.columns.tiems()):
          col_type = col[1].type.python_type     # this line 

As i saw the UNIQUEIDENTIFIER() type is not in type.python_type

Any idea to fix this???????

[pythononwheels]

As i saw the UNIQUEIDENTIFIER() type is not in type.python_type

Yeas, right Native GUID, UNIQUEIDENTFIER, UUIDs DB-types are sort of a problem since they are not directly supported by serialisation libs, sqlalchemy mapping to python types, cerberus and so on.

Normally you can fall back to binary(16) or string (varchar(32)) Like in this case/example:

def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value).int
            else:
                # hexstring
                return "%.32x" % value.int

backend-agnostic-guid-type

Still need to handle that in the schema generation ... So adding a "converter" like above for the GUID/UUID kind of types... so work for me in the long run and finding a workaround for you in the short run.

There is also the sqlalchemy_utils package which should be taken into consideration: sqlalchemy_utils uuid type

UUIDType

class sqlalchemy_utils.types.uuid.UUIDType(binary=True, native=True, **kwargs)[source]

    Stores a UUID in the database natively when it can and falls back to a BINARY(16) or a CHAR(32) when it can’t.

    from sqlalchemy_utils import UUIDType
    import uuid

    class User(Base):
        __tablename__ = 'user'

        # Pass `binary=False` to fallback to CHAR instead of BINARY
        id = sa.Column(UUIDType(binary=False), primary_key=True)

I think your usecase is reflecting the whole DB,right?

If you dont need the cerberus schemas in the first place ... just skip them for now. Adding a try ... except .. as a workaround.

[jeffny2015]

Yes your rigth

[jeffny2015]

I think your usecase is reflecting the whole DB,right? yep

[pythononwheels]

I will to setup a small MSSQL docker environment and setup a simple DB with UNIQUEIDENTIFIER to check how the sqlalchemy Colum behaves. Type etc. If it is simple and clearly identifiable I'll add a custom cerberus validator and add it as a default option to the schemas ... Especially when using reflection.

[jeffny2015]

I solved this uuid by changing the line who parse the column type by checking first if the column type is uuid

# dont remember the variable name, so i use colum as variable but i hope is understandable, so i change this line by the below validation
column_type = column.type.python_type

if str(colum.type) == "UNIQUEIDENTIFIER":
     column_type = uuid.UUID
else:
   column_type = column.type.python_type

Givin this validation now I'm able to create the user instance and get the values:

>>>> from pythononwheelsapp.models.sql.user import User
>>>> u = User()
>>>> r = u.find_all()
>>>> for elem in r:
>>>> ....... print(elem)

For now everything is ok, but now I'm having and error, when I request the http handler (also im working to create a Restful API to connect to ms sql server using this frameework) curl -X GET -H "Accept: json/application" http://myip/User/, something like this, the response I got is the pythononwheels server error view page, and not the json whith all the database values, but it seems that the server is nott returning the error, but as i see in the handler there is a "def list():" method that is supposed to be callable when i do the get request to create the User instance and make the find_all query but the get request process seems to not pass through this method so i don't know why, do you have any idea????

[jeffny2015]

To be clear the error return this:

Status: 500 Message: ( 'NameError'), NameError("name 'before_handler'is not defined"), (<traceback object at 0x062B4E68>) Data: URI: /user Request: HTTPServerRequest(protocol='http',host='localhost', method='GET',uri="/user", version='HTTP/1.1', remote_ip='::1')

[pythononwheels]

You can look at the log/pow.log file to get a detailed error description and traceback.

Maybe something seralizing the uuid type to json.

You can also try that with u.to_json() in the cli.

You can also debug that using VSCode. I wrote a short blogpost about how to do that. Just some 10 Minute read. Set a breakpoint right in the handler function.

See post here:

But normally the pow.log is enough to get a good idea whats going wrong.

[jeffny2015]

Jejeje that was not the problem, the problem stills in the uniqueidenfier, when the server calls the User Handler list method and calls a new instances of the user

user handler classs

model = Model()

def list():
      m = Model()

tryng to see the error in python console:

from pythononwheelsapp.models.sql.User import User

>>>> u = User() # first time its ok
>>>>u = User() 
User.py,line 35, in __init__
self.setup_instance_values()
myapp/models/sql/basemodel.py, setup_instances_values
settattr(self.key,cgf.database["default_values"][self.schema[key]["type"]])

[jeffny2015]

keyError: "uniqueidentifier"

[jeffny2015]

Ok It seems i fixed by adding uniqueidentifier to the default values, but the response retruned to me the query but not the result query thats not good jejee I need to debbuged

[pythononwheels]

Ok It seems i fixed by adding uniqueidentifier to the default values !

Absolutely right, that was missing.

@jeffny2015: Very good that you push to add the support for UNIQUEIDENTIFIER sort of types ... Think this is a very good improvement!

[pythononwheels]

but the response retruned to me the query but not the result query

Can you paste what you get as a response. Just using the cli and the model. For example a

model.find_all()

Do you get a Query object in return insteas of a result set (counter) ?

Maybe pasting the relevat pow.log would help as well since it also logs the sqlalchemy executions.

You can set the loglevel for sqlalchemy in

conf/config.py section: database["sql"]["loglevel"]

[jeffny2015]

Hey,

Can you help me jejeje, I want to add a route in the User handler, for example:

#My handler
from myapp.lib.application import app, route
@app.make_routes()
class User(PowHandler):
      @route(r'/User/query/', dispatch=["get"], params=["q"])
      def query(self, q=None):
            self.write("I got q:" + str(q))

Testing this using curl i got as a result: "I got q: None"

$ curl -H "Content-Type: application/json" -X GET -d "q=Jose Pablo" http://localhost/User/query/
$ curl -H "Content-Type: application/json" -X GET -d "q=Jose Pablo" http://localhost/User/query/?q=Jose+Pablo
$ curl -H "Content-Type: application/json" -X GET -d "q=11"  http://localhost/User/query/

I got the same answer

My idea is to recieve an String from the q argument

[jeffny2015]

For this question i got what i expected, i forgot to initialize the model instance in the model class XD m= Model() so with this i got the response of the query jejeje

El dom., 16 de ago. de 2020 a la(s) 13:25, khz (notifications@github.com) escribió:

but the response retruned to me the query but not the result query

Can you paste what you get as a response. Just using the cli and the model. For example a

model.find_all()

Do you get a Query object in return insteas of a result set (counter) ?

Maybe pasting the relevat pow.log would help as well since it also logs the sqlalchemy executions.

You can set the loglevel for sqlalchemy in

conf/config.py section: database["sql"]["loglevel"]

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/pythononwheels/pow_devel/issues/42#issuecomment-674567043, or unsubscribe https://github.com/notifications/unsubscribe-auth/AFOQM5NAKTDG2PWJ3EGPEPLSBAXA3ANCNFSM4PYBHXVA .

[jeffny2015]

What I'm tryng to do is have a mathod in the user handler that recieves a query string("Select name from schema.user", something like this localhost/User/?q="Select name from schme.user" to execute a query and return a response. But i have been tryng and I still get anything

[pythononwheels]

Hi @jeffny2015,

two things on this:

1. regarding the parameter and the route:

I recommend using the new, flask styled routes which are fully implemented in PythonOnWheels So receiving a string parameter can be done like this:

@route(r'/testquery/<string:q>', dispatch=["get"])
    def testuuid2(self, q=None):
        """
            Testcase: dont delete (see tests/run_tests.py)
        """
        self.write("I got: " + str(q))

Result:

If you (aditionally) want to use query parameters

in the /URL?param=something style

you can use something like this:

@route(r'/testquery/<string:q>', dispatch=["get"])
    def testuuid2(self, q=None):
        """
            Testcase: dont delete (see tests/run_tests.py)
        """
        query = None
        try:
            query = self.get_argument("query", None)
        except:
            pass
        finally:
            self.write("I got: " + str(q) + " query: " + str(query) )

2. Really important: regarding security: I would not recommend styling your app in a way that you receive and execute whole SQL statements this is particularry dangerous since you might encounter SQL-injection attacks. So someone can add a ?q="drop database" or any other harmful statement. So the advice would be to just receive the name as a string and execute the pre defined query

res=model.find(Model.name==q)

[jeffny2015]

ok thanks