def to_vertica_python_format(dsn: str):
"""
---------------------------------------------------------------------------
Converts the ODBC dictionary obtained with the read_dsn method to the
vertica_python format.
Parameters
----------
dsn: str
DSN name
Returns
-------
dict
dictionary with all the credentials
"""
check_types([("dsn", dsn, [str],)])
dsn = read_dsn(dsn)
conn_info = {}
for elem in dsn:
if elem.lower() == "servername":
conn_info["host"] = dsn[elem]
elif elem.lower() == "uid":
conn_info["user"] = dsn[elem]
elif elem.lower() == "pwd":
conn_info["password"] = dsn[elem]
elif elem.lower() == "kerberosservicename":
conn_info["kerberos_service_name"] = dsn[elem]
elif elem.lower() == "kerberoshostname":
conn_info["kerberos_host_name"] = dsn[elem]
else:
conn_info[elem.lower()] = dsn[elem]
if "port" not in [elem.lower() for elem in dsn]:
conn_info["password"] = 5433
return conn_info
def vertica_conn(
section: str,
dsn: str = "",
):
"""
---------------------------------------------------------------------------
Reads the input DSN and creates a Vertica Database connection.
Parameters
----------
section: str
Name of the section in the configuration file.
dsn: str, optional
Path to the file containing the credentials. If empty, the ODBCINI
environment variable will be used.
Returns
-------
conn
Database connection
See Also
--------
new_auto_connection : Saves a connection to automatically create database cursors.
read_auto_connect : Automatically creates a connection.
"""
check_types([(
"dsn",
dsn,
[str],
)])
conn = vertica_python.connect(**read_dsn(section, dsn))
return conn
def read_dsn(dsn: str):
"""
---------------------------------------------------------------------------
Reads the DSN information from the ODBCINI environment variable.
Parameters
----------
dsn: str
DSN name
Returns
-------
dict
dictionary with all the credentials
"""
check_types([("dsn", dsn, [str],)])
f = open(os.environ["ODBCINI"], "r")
odbc = f.read()
f.close()
if "[{}]".format(dsn) not in odbc:
raise NameError("The DSN '{}' doesn't exist.".format(dsn))
odbc = odbc.split("[{}]\n".format(dsn))[1].split("\n\n")[0].split("\n")
dsn = {}
for elem in odbc:
try:
info = elem.replace(" ", "").split("=")
dsn[info[0].lower()] = info[1]
except:
pass
return dsn
def read_dsn(
section: str,
dsn: str = "",
):
"""
---------------------------------------------------------------------------
Reads the DSN information from the ODBCINI environment variable or the input
file.
Parameters
----------
section: str
Name of the section in the configuration file.
dsn: str, optional
Path to the file containing the credentials. If empty, the ODBCINI
environment variable will be used.
Returns
-------
dict
dictionary with all the credentials
"""
check_types([(
"dsn",
dsn,
[str],
), (
"section",
section,
[str],
)])
confparser = ConfigParser()
confparser.optionxform = str
if not dsn:
dsn = os.environ["ODBCINI"]
confparser.read(dsn)
if confparser.has_section(section):
options = confparser.items(section)
conn_info = {"port": 5433, "user": "******"}
for elem in options:
if elem[0].lower() in ("servername", "server"):
conn_info["host"] = elem[1]
elif elem[0].lower() == "uid":
conn_info["user"] = elem[1]
elif elem[0].lower() == "port":
try:
conn_info["port"] = int(elem[1])
except:
conn_info["port"] = elem[1]
elif elem[0].lower() == "pwd":
conn_info["password"] = elem[1]
elif elem[0].lower() == "kerberosservicename":
conn_info["kerberos_service_name"] = elem[1]
elif elem[0].lower() == "kerberoshostname":
conn_info["kerberos_host_name"] = elem[1]
else:
conn_info[elem[0].lower()] = elem[1]
return conn_info
else:
raise NameError("The DSN Section '{}' doesn't exist.".format(section))
def new_auto_connection(dsn: dict, name: str = "DSN"):
"""
---------------------------------------------------------------------------
Saves a connection to automatically create DB cursors. This will create a
used-as-needed file to automatically set up a connection, avoiding redundant
cursors.
Parameters
----------
dsn: dict
Dictionnary containing the information to set up the connection.
database : Database Name
host : Server ID
password : User Password
port : Database Port (optional, default: 5433)
user : User ID (optional, default: dbadmin)
name: str, optional
Name of the auto connection.
See Also
--------
change_auto_connection : Changes the current auto creation.
read_auto_connect : Automatically creates a connection.
vertica_conn : Creates a Vertica Database connection.
"""
check_types([("dsn", dsn, [dict],)])
if "port" not in dsn:
print(
"\u26A0 Warning: No port found in the 'dsn' dictionary. The default port is 5433."
)
dsn["port"] = 5433
if "user" not in dsn:
print(
"\u26A0 Warning: No user found in the 'dsn' dictionary. The default user is 'dbadmin'."
)
dsn["user"] = "******"
if ("password" not in dsn) or ("database" not in dsn) or ("host" not in dsn):
raise ParameterError(
'The dictionary \'dsn\' is incomplete. It must include all the needed credentitals to set up the connection.\nExample: dsn = { "host": "10.211.55.14", "port": "5433", "database": "testdb", "password": "******", "user": "******"}"'
)
path = os.path.dirname(verticapy.__file__) + "/connections/all/{}.verticapy".format(
name
)
file = open(path, "w+")
file.write(
"Connection - {}\nhost: {}\nport: {}\ndatabase: {}\nuser: {}\npassword: {}".format(
name,
dsn["host"],
dsn["port"],
dsn["database"],
dsn["user"],
dsn["password"],
)
)
file.close()
def new_auto_connection(dsn: dict, name: str = "DSN"):
"""
---------------------------------------------------------------------------
Saves a connection to automatically create database cursors. This will create a
used-as-needed file to automatically set up a connection, avoiding redundant
cursors.
Parameters
----------
dsn: dict
Dictionnary containing the information to set up the connection.
database : Database Name
host : Server ID
password : User Password
port : Database Port (optional, default: 5433)
user : User ID (optional, default: dbadmin)
...
name: str, optional
Name of the auto connection.
See Also
--------
change_auto_connection : Changes the current auto creation.
read_auto_connect : Automatically creates a connection.
vertica_conn : Creates a Vertica Database connection.
"""
check_types([(
"dsn",
dsn,
[dict],
)])
path = os.path.dirname(verticapy.__file__) + "/connections.verticapy"
confparser = ConfigParser()
confparser.optionxform = str
try:
confparser.read(path)
except:
pass
if confparser.has_section(name):
confparser.remove_section(name)
confparser.add_section(name)
for elem in dsn:
confparser.set(name, elem, str(dsn[elem]))
f = open(path, "w+")
confparser.write(f)
f.close()
change_auto_connection(name)
def vertica_conn(dsn: str):
"""
---------------------------------------------------------------------------
Reads the input DSN from the ODBCINI environment and creates a Vertica
Database connection.
Parameters
----------
dsn: str
DSN name
Returns
-------
conn
Database connection
See Also
--------
new_auto_connection : Saves a connection to automatically create DB cursors.
read_auto_connect : Automatically creates a connection.
"""
check_types([("dsn", dsn, [str],)])
conn = vertica_python.connect(**to_vertica_python_format(dsn))
return conn