How to connect ms-sql-server database with python's "pyreportjasper" to convert from "jrxml" to "pdf"?

Question

I have created a jrxml file using jaspersoft studio, within which i created a mssql server connection to populate report elements .. now i want to use this jrxml and convert it do pdf and other formats, but the problem i am facing is that I am unable to see any option to create a db_connection for mssql server .. I did look into python's pyreportjasper module and there's no db_type available for mssql server .. Even in their documentation the haven't shared any example for connection with mssql server. Here's the link of package's documentation: https://pyreportjasper.readthedocs.io/en/master/userguide.html

Answer 1

Don't forget to download the jdbc and place it in the folder where it is reported in the RESOURCES_DIR variable.

pip install git+https://github.com/acesseonline/pyreportjasper@master#egg=pyreportjasper

# -*- coding: utf-8 -*-
import os
from pyreportjasper import PyReportJasper


def mssql_to_pdf():
   RESOURCES_DIR = os.path.abspath(os.path.dirname(__file__))
   REPORTS_DIR = os.path.abspath(os.path.dirname(__file__))
   input_file = os.path.join(REPORTS_DIR, 'mssql.jrxml')
   output_file = os.path.join(REPORTS_DIR, 'report')
   pyreportjasper = PyReportJasper()
   pyreportjasper.config(
      input_file,
      output_file,
      output_formats=["pdf"], # "pdf", "rtf", "xml"
      db_connection={
        'driver': 'generic',
        'username': 'XXXXXXXXXXXX',
        'password': 'XXXXXXXXXXXXXXXXXXXXXXXX',
        'jdbc_url': 'jdbc:sqlserver://{host};databaseName={databasename};integratedSecurity=true;'.format(host='YOUR_HOST', databasename='YOUR_DATABASE_NAME'),
        'jdbc_driver': 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
        'port': '1433',
        'jdbc_driver': 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
        'jdbc_dir' : RESOURCES_DIR
      },
      resource=RESOURCES_DIR,
      locale='en_US'  # LOCALE Ex.:(en_US, de_GE, pt_BR)
   )
   pyreportjasper.process_report()
   print('Result is the file below.')
   print(output_file + '.pdf')


mssql_to_pdf()