def postgres(self, name, host="localhost", port=5432, model_path=None, is_secured=False, tables_scope=None):
"""
Description:
-----------
Get a PostgreSql Database query object using SQLAlchemy.
Related Pages:
https://www.postgresql.org/
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sqlalchemy.dialects.postgresql.dml.Insert.on_conflict_do_update.params.where
Attributes:
----------
:param name: Database name
:param host: Optional, Database hostname. Default localhost
:param port: Optional, Database port. Default 5432
:param model_path: Optional, Database model path with the python scripts of the tables
:param is_secured: If credentials required. Default False
:param tables_scope:
:rtype: epyk.core.py.PySql.SqlConn
"""
if 'postgresql' not in self.pkgs:
self.pkgs[name] = requires(
name, reason='Missing Package', install="postgresql", source_script=__file__, raise_except=True)
if 'psycopg2' not in self.pkgs:
self.pkgs[name] = requires(
name, reason='Missing Package', install="psycopg2", source_script=__file__, raise_except=True)
database = name
db_settings = {"loadModel": model_path is not None, 'model_path': model_path or False,
"username": "******", "password": "******", "host": host, "port": port}
if database not in self._db_bindings:
self._db_bindings[database] = PySql.SqlConn(
'postgresql+psycopg2', database=database, tables_scope=tables_scope, **db_settings)
return self._db_bindings[database]
def grpc(self, service_name, path, module, host="localhost", port=50051):
"""
Description:
-----------
Interface to a GRPC server.
Usage::
grpc = page.data.grpc(serviceName="GreeterStub", module="helloworld_pb2_grpc", path="")
data = grpc.imp("helloworld_pb2").HelloRequest(name="Test")
print(grpc.request("SayHello", data))
Related Pages:
https://grpc.io/docs/tutorials/basic/python/
https://grpc.io/docs/quickstart/python.html
Attributes:
----------
:param service_name: The Service name (the class name in the python module)
:param path: The path with the GRPC features
:param module: The python module name for the service
:param host: The service host name (e.g localhost)
:param port: The service port
:return: A GRPC wrapped object
:rtype: DataGrpc.DataGrpc
"""
requires("grpc",
reason='Missing Package',
install='grpcio',
source_script=__file__,
raise_except=True)
return DataGrpc.DataGrpc(service_name, path, module, host, port)
def mssql(self, name, host="localhost", driver_name="{ODBC Driver 17 for SQL Server}",
model_path=None, tables_scope=None):
"""
Description:
-----------
Attributes:
----------
:param name: The database name
:param host: The database host name
:param driver_name: Optional, The
:param model_path: Optinal, The database model path
:rtype: epyk.core.py.PySql.SqlConn
"""
if 'mssql' not in self.pkgs:
self.pkgs[name] = requires(
name, reason='Missing Package', install="mssql", source_script=__file__, raise_except=True)
if 'pyodbc' not in self.pkgs:
self.pkgs[name] = requires(
name, reason='Missing Package', install="pyodbc", source_script=__file__, raise_except=True)
database = name
data_settings = {"loadModel": model_path is not None, 'model_path': model_path or False,
"driver": 'mssql+pyodbc', "driverName": driver_name, 'host': host}
if database not in self._db_bindings:
self._db_bindings[database] = PySql.SqlConn(
'mssql+pyodbc', database=database, tables_scope=tables_scope, **data_settings)
return self._db_bindings[database]
def rpc(self, url, data=None, headers=None, is_secured=False):
"""
Description:
-----------
Interface to a RPC server.
This is using the external python package jsonrpcclient (https://jsonrpcclient.readthedocs.io/en/latest/)
Related Pages:
https://en.wikipedia.org/wiki/Rapid_control_prototyping
https://gurujsonrpc.appspot.com/
https://jsonrpcclient.readthedocs.io/en/latest/
Attributes:
----------
:param url: The RPC service url
:param data: The input data for the service
"""
http_client = requires("jsonrpcclient.clients.http_client",
reason='Missing Package',
install="jsonrpcclient[requests]",
source_script=__file__,
raise_except=True)
client = http_client.HTTPClient(url)
if headers is not None:
client.session.headers.update(headers)
if is_secured:
client.session.auth = ("user", "pass")
if data is None or "method" not in data:
raise ValueError("data must of a method defined")
return client.send(json.dumps(data))
def accdb(self, name, db_path, model_path=None):
"""
Get a Access (.accdb) Database query object using ODBC driver
Example
Documentation
https://www.599cd.com/access/studentdatabases/
:param name: The filename
:param db_path: The database full path
:param model_path: Optional, Database model path with the python scripts of the tables
:rtype: epyk.core.py.PySql.SqlConnOdbc
:return:
"""
if 'pyodbc' not in self.pkgs:
self.pkgs[name] = requires(name,
reason='Missing Package',
install="pyodbc",
source_script=__file__)
database = "%s/%s.accdb" % (db_path, name)
dataSettings = {
"loadModel": model_path is not None,
'model_path': model_path or False,
"driver": "{Microsoft Access Driver (*.mdb, *.accdb)}"
}
if database not in self._db_bindings:
self._db_bindings[database] = PySql.SqlConnOdbc(database=database,
**dataSettings)
return self._db_bindings[database]
def soap(self, wsdl):
"""
Description:
-----------
Interface to a SOAP server.
This function will require an external python package zeep to use SOAP
Usage::
soap = page.data.soap("http://www.soapclient.com/xml/soapresponder.wsdl")
soap.Method1('Zeep', 'is cool')
Related Pages:
https://en.wikipedia.org/wiki/SOAP
https://python-zeep.readthedocs.io/en/master/
Attributes:
----------
:param wsdl: The wsdl service url
:rtype: zeep.service
:return: The SOAP services
"""
soap = requires("zeep",
reason='Missing Package',
install="zeep",
source_script=__file__,
raise_except=True)
return soap.Client(wsdl).service
def pdf(self, filename, path=None):
"""
Description:
-----------
Read a pdf file
This will require an external module PyPDF2.
Usage::
data = page.data.pdf("document.pdf", r"")
data.getPage(0)
Related Pages:
https://www.geeksforgeeks.org/working-with-pdf-files-in-python/
Attributes:
----------
:param filename: The pdf file name
:param path: The file path
:return: A pdf object from PyPDF2
"""
pyPDF2 = requires("PyPDF2",
reason='Missing Package',
install='PyPDF2',
source_script=__file__,
raise_except=True)
pdf_data = pyPDF2.PdfFileReader(os.path.join(path, filename))
return pdf_data
def mariadb(self, name, host="localhost", port=3306, model_path=None, is_secured=False, tables_scope=None):
"""
Description:
-----------
Get a MariaDB Database query object using SQLAlchemy.
Usage::
page.data.db.mariadb("MySQL", port=3333)
Related Pages:
https://mariadb.org/
Attributes:
----------
:param name: Database name
:param host: Optional, Database hostname. Default localhost
:param port: Optional, Database port. Default 3306
:param model_path: Optional, Database model path with the python scripts of the tables
:param is_secured: If credentials required. Default False
:param tables_scope:
:rtype: epyk.core.py.PySql.SqlConn
"""
if 'pymysql' not in self.pkgs:
self.pkgs[name] = requires(
name, reason='Missing Package', install="pymysql", source_script=__file__, raise_except=True)
database = name
db_settings = {"loadModel": model_path is not None, 'model_path': model_path or False,
"username": "******", "password": "******", "host": host, "port": port}
if database not in self._db_bindings:
self._db_bindings[database] = PySql.SqlConn(
'mysql+pymysql', database=database, tables_scope=tables_scope, **db_settings)
return self._db_bindings[database]
def import_package(self, package: str, sub_module: str = None):
"""
Description:
------------
Install the external Python package.
This can automatically install it from the Python Index online repository is missing.
Usage::
>>> PyExt().import_package("sqlalchemy").__name__
'sqlalchemy'
Attributes:
----------
:param str package: The Python Package Name.
:param str sub_module: Optional. The sub module or class within the package.
:return: The installed Python module.
"""
package_alias = "%s_%s" % (
package, sub_module) if sub_module is not None else package
if package_alias not in self.page._props:
self.page._props[package_alias] = requires(
package,
reason='Missing Package',
package=sub_module,
install=package)
return self.page._props[package_alias]
def oracle(self, name, host, port, model_path=None, is_secured=False, tables_scope=None):
"""
Description:
-----------
Attributes:
----------
:param name: Database name
:param host: Optional, Database hostname. Default localhost
:param port: Optional, Database port. Default 5432
:param model_path: Optional, Database model path with the python scripts of the tables
:param is_secured: If credentials required. Default False
:param tables_scope:
:rtype: epyk.core.py.PySql.SqlConn
"""
if 'oracle' not in self.pkgs:
self.pkgs[name] = requires(name, reason='Missing Package', install="cx_Oracle", source_script=__file__)
database = name
db_settings = {"loadModel": model_path is not None, 'model_path': model_path or False,
"username": "******", "password": "******", "host": host, "port": port}
if database not in self._db_bindings:
self._db_bindings[database] = PySql.SqlConn('oracle', database=database, tables_scope=tables_scope, **db_settings)
return self._db_bindings[database]
def outlook(self):
"""
:return:
"""
win32com = requires("win32com.client", reason='Missing Package', install='pywin32', source_script=__file__, raise_except=True)
raise Exception("To be implemented")
def rss(self, url, proxy=None, method="GET"):
"""
Entry point to retrieve RSS feeds.
This module will require beautifulsoup4 as external package
Example
xml_soup = rptObj.data.rss("http://feeds.reuters.com/reuters/businessNews")
for title in xml_soup.findAll('title'):
print(title)
Documentation
https://pypi.org/project/beautifulsoup4/
:param url: The url of the html page
:param method: Optional, The request method. Default method GET
:return: A xml object
"""
bs4 = requires("bs4",
reason='Missing Package',
install='beautifulsoup4',
sourceScript=__file__,
raiseExcept=True)
headers = {
'User-Agent': 'Mozilla/5.0',
'accept': 'application/xml;q=0.9, */*;q=0.8'
}
response = self._report.py.requests.get(url,
headers=headers,
proxy=proxy)
xml_soup = bs4.BeautifulSoup(response, )
return xml_soup
def neo4j(self, host="localhost", port=5000, is_secured=False):
"""
Documentation
https://neo4j.com/developer/python/
https://community.neo4j.com/?_ga=2.69585106.394662973.1539480121-615400289.1539480121
:param host: Optional, Database hostname. Default localhost
:param port: Optional, Database port. Default 5000
:param is_secured:
:return: A Python SQL connectionr for Neo4J
"""
if 'neo4j' not in self.pkgs:
requires("neo4j",
reason='Missing Package',
install='neo4j-driver',
source_script=__file__,
raise_sxcept=True)
return PySql.SqlConnNeo4j(host, port)
def neo4j(self, host="localhost", port=5000, is_secured=False):
"""
Description:
-----------
Related Pages:
https://neo4j.com/developer/python/
https://community.neo4j.com/?_ga=2.69585106.394662973.1539480121-615400289.1539480121
Attributes:
----------
:param host: String. Optional. Database hostname. Default localhost.
:param port: Integer. Optional. Database port. Default 5000.
:param is_secured: Boolean. Optional.
:return: A Python SQL connection for Neo4J
"""
if 'neo4j' not in self.pkgs:
requires("neo4j", reason=MISSING_PACKAGE, install='neo4j-driver', source_script=__file__, raise_except=True)
return PySql.SqlConnNeo4j(host, port)
def webscrapping(self, url, parser="html.parser", proxy=None, method=None):
"""
Description:
-----------
Entry point to retrieve data from any website.
This module will require beautifulsoup4 as external package
Usage::
page.data.webscrapping("https://www.w3schools.com/colors/default.asp")
xml_soup.findAll('title')
Related Pages:
https://pypi.org/project/beautifulsoup4/
Attributes:
----------
:param url: The url of the html page
:param parser: The output data parser
:param proxy:
:param method:
:return: A xml object
"""
bs4 = requires("bs4",
reason='Missing Package',
install='beautifulsoup4',
source_script=__file__,
raise_except=True)
headers = {
'Accept':
'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
'Accept-Encoding':
'none',
'Accept-Language':
'en-US,en;q=0.8',
'Connection':
'keep-alive',
'Accept-Charset':
'ISO-8859-1,utf-8;q=0.7,*;q=0.3',
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2228.0 Safari/537.3'
}
response = self.page.py.request(url,
headers=headers,
method=method,
proxy=proxy)
xml_soup = bs4.BeautifulSoup(response, parser)
return xml_soup
def __init__(self, database=None, **kwargs):
self.query = {}
pyodbc = requires("pyodbc",
reason='Missing Package',
install='sqlalchemy',
source_script=__file__)
dbAttr = {'Driver': kwargs['driver'], 'DBQ': database}
for opt in [('password', 'PWD'), ('username', 'UID')]:
if kwargs.get(opt[0]) is not None:
dbAttr[opt[1]] = kwargs[opt[0]]
dbStr = []
for k in ['Driver', 'DBQ', 'PWD', 'UID']:
if k in dbAttr:
dbStr.append("%s=%s" % (k, dbAttr[k]))
self.conn = pyodbc.connect(";".join(dbStr))
def xls(self, filename, path=None):
"""
Open an excel file
Example
data = rptObj.data.office.xls("Classeur1.xlsx", "")
print(data.sheet_by_index(0) )
Documentation
https://www.geeksforgeeks.org/reading-excel-file-using-python/
:param filename: The filename with the extension
:param path: The path of the file
:return: A xlrd Python object
"""
xlrd = requires("xlrd", reason='Missing Package', install='xlrd', source_script=__file__, raise_except=True)
return xlrd.open_workbook(os.path.join(path, filename))
def mongo(self, host="localhost", port=5000, is_secured=False):
"""
Description:
-----------
Related Pages:
https://www.w3schools.com/python/python_mongodb_getstarted.asp
https://www.mongodb.com/dr/fastdl.mongodb.org/win32/mongodb-win32-x86_64-2008plus-ssl-4.0.3-signed.msi/download
Attributes:
----------
:param host: String. Optional. Database hostname. Default localhost
:param port: Integer. Optional. Database port. Default 5000
:param is_secured: Boolean. Optional.
"""
py_mongo = requires(
"pyMongo", reason=MISSING_PACKAGE, install="pyMongo", source_script=__file__, raise_except=True)
return py_mongo.MongoClient("mongodb://%s:%s/" % (host, port))
def ppt(self, filename, path=None):
"""
Open a power point file
Example
ppts = rptObj.data.office.ppt("diagramme.pptx", r"")
print(ppts.slides)
Documentation
https://python-pptx.readthedocs.io/en/latest/user/quickstart.html
:param filename: The filename with the extension
:param path: The path of the file
:return: A pptx Python object
"""
pptx = requires("pptx", reason='Missing Package', install='python-pptx', source_script=__file__, raise_except=True)
return pptx.Presentation(os.path.join(path, filename))
def mongo(self, host="localhost", port=5000, is_secured=False):
"""
Documentation
https://www.w3schools.com/python/python_mongodb_getstarted.asp
https://www.mongodb.com/dr/fastdl.mongodb.org/win32/mongodb-win32-x86_64-2008plus-ssl-4.0.3-signed.msi/download
:param host: Optional, Database hostname. Default localhost
:param port: Optional, Database port. Default 5000
:param is_secured:
:return:
"""
pyMongo = requires("pyMongo",
reason='Missing Package',
install="pyMongo",
source_script=__file__,
raise_sxcept=True)
return pyMongo.MongoClient("mongodb://%s:%s/" % (host, port))
def mdb(self, name, db_path, model_path=None):
"""
Description:
-----------
Get a Access (.mdb) Database query object using ODBC driver.
Attributes:
----------
:param name: The filename
:param db_path: The database full path
:param model_path: Optional, Database model path with the python scripts of the tables.
:rtype: epyk.core.py.PySql.SqlConnOdbc
"""
if 'pyodbc' not in self.pkgs:
self.pkgs[name] = requires(name, reason='Missing Package', install="pyodbc", source_script=__file__)
database = "%s/%s.mdb" % (db_path, name)
data_settings = {"loadModel": model_path is not None, 'model_path': model_path or False,
"driver": "{Microsoft Access Driver (*.mdb, *.accdb)}"}
if database not in self._db_bindings:
self._db_bindings[database] = PySql.SqlConnOdbc(database=database, **data_settings)
return self._db_bindings[database]
def pdf(self, filename, path=None):
"""
Read a pdf file
This will require an external module PyPDF2
Example
data = rptObj.data.pdf("document.pdf", r"")
data.getPage(0)
Documentation
https://www.geeksforgeeks.org/working-with-pdf-files-in-python/
:param filename: The pdf file name
:param path: The file path
:return: A pdf object from PyPDF2
"""
pyPDF2 = requires("PyPDF2",
reason='Missing Package',
install='PyPDF2',
sourceScript=__file__,
raiseExcept=True)
pdf_data = pyPDF2.PdfFileReader(os.path.join(path, filename))
return pdf_data
def word(self, filename, path=None):
"""
Open a word file
This module will use an external Python package: python-docx
Example
docx = rptObj.data.office.word("FX Sales Trainee.docx", path=r"")
print(docx.paragraphs)
Documentation
https://python-docx.readthedocs.io/en/latest/
:param filename: The filename with the extension
:param path: The path of the file
:return: A docx Python object
"""
docx = requires("docx", reason='Missing Package', install='python-docx', source_script=__file__, raise_except=True)
doc = open(os.path.join(path, filename), 'rb')
document = docx.Document(doc)
doc.close()
return document
def soap(self, wsdl):
"""
Interface to a SOAP server.
This function will require an external python package zeep to use SOAP
Example
soap = rptObj.data.soap("http://www.soapclient.com/xml/soapresponder.wsdl")
soap.Method1('Zeep', 'is cool')
Documentation
https://en.wikipedia.org/wiki/SOAP
https://python-zeep.readthedocs.io/en/master/
:param wsdl: The wsdl service url
:rtype: zeep.service
:return: The SOAP services
"""
soap = requires("zeep",
reason='Missing Package',
install="zeep",
sourceScript=__file__,
raiseExcept=True)
return soap.Client(wsdl).service
import traceback
# Check if pandas is available in the current environment
# if it is the case this module can handle Dataframe directly
try:
import pandas as pd
has_pandas = True
except:
has_pandas = False
from epyk.core.js.Imports import requires
# Will automatically add the external library to be able to use this module
sqlalchemy = requires("sqlalchemy",
reason='Missing Package',
install='sqlalchemy',
source_script=__file__)
DATABASE_AUTOSYNC = False
class SqlConn(object):
def __init__(self,
family,
database=None,
filename=None,
model_path=None,
reset=False,
migrate=True,
tables_scope=None,
**kwargs):
