Created Pymssql driver support in pydal

[Kunal Gangaramani]

Hi All,

We were trying to get the  MS-SQL connection working for py4web from a linux server without having to install odbc on the server .  

Linux Server Details :- Linux <Server> 4.18.0-553.77.1.el8_10.x86_64 #1 SMP Fri Sep 19 03:36:32 EDT 2025 x86_64 x86_64 x86_64 GNU/Linux


We saw that it was possible to use pytds , and we tried with pytds but it gives an error below :-

[FAILED] loading fadebook (('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)'))

With below details in settings.py

DB_URI = "pytds://<user_id>:<password>@<server>/<database>"

When debugging, we found that in the file 'pydal/adapters/mssql.py' , even though it is using  PyTDS , but it's using pyodbc as the driver

self.driver = <module 'pyodbc' from '/py4web/lib/python3.13/site-packages/pyodbc.cpython-313-x86_64-linux-gnu.so'>

 

We decided  to adapt the pydal to allow connections using pymssql (similar to PyTDS) which uses FreeTDS and it doesn’t require an odbc installation . We added the code below in the following python scripts to get it working :-

pydal/drivers.py

try:

    import pymssql

    DRIVERS["pymssql"] = pymssql

except:

    pass


pydal/adapters/mssql.py

# Drivers List 

class MSSQL(SQLAdapter):

    dbengine = "mssql"

    drivers = ("pyodbc", "pytds","pymssql") ## Added pymssql

@adapters.register_for("pymssql")

class PyMssql(MSSQL):

    def _initialize_(self):

        self.driver = pymssql

        super(MSSQL, self)._initialize_()

        ruri = self.uri.split("://", 1)[1]

        if "@" not in ruri:

            m = re.match(self.REGEX_DSN, ruri)

            if not m:

                raise SyntaxError("Invalid URI string in DAL")

            self.dsn = m.group()

        else:

            m = re.match(self.REGEX_URI, ruri)

            if not m:

                raise SyntaxError("Invalid URI string in DAL: %s" % self.uri)

            self.dsn = m.group("host")

            self.driver_args.update(

                user=self.credential_decoder(m.group("user")),

                password=self.credential_decoder(m.group("password")) or "",

                database=m.group("db"),

                port=m.group("port") or "1433",

            )

 

    def connector(self):

        return self.driver.connect(self.dsn, **self.driver_args)

 

 

I will create a pull request, to add the pymssql driver solution to the pydal. If you any feedback, please let me know. 

[Massimo DiPierro]

If this works it looks good to me. Thanks!

[Davidiam]

Nice work Kunal, this will definitely be very useful in our framework.

[Kunal Gangaramani]

Hi Massimo, 

I created the pull request to merge the changes. But it looks like it is failing the build. 

pydal 1.0.1306 - AppVeyor

Here is the link to my Pull request -  Added support for pymssql in pydal by Kunal09001 · Pull Request #747 · web2py/pydal
I am not sure if the error is related to my change or something else. Could you please help us to check this. 

Thanks in Advance
Kunal