def sql(data):
# create database cursor
db = connect(env.database, env.server, env.driver, env.username,
env.password)
# database cursor
cursor = db.connection.cursor()
# column name = dataframe column name, data type = dataframe column name without prefixed underscore
create = {x: x[1::].upper() for x in data.columns if x != "id"}
# use string lengths to determine string column size
create = {
k:
(v + "(" + str(data[k].str.len().max()) + ")" if v == "VARCHAR" else v)
for k, v in create.items()
}
create = {
k: (v + "(" + str(data[k].str.len().max()) +
")" if v == "NVARCHAR" else v)
for k, v in create.items()
}
# create SQL table, including a primary key for SQL read sorting
create = ",\n".join([k + " " + v for k, v in create.items()])
create = f"""
CREATE TABLE ##test_conversion (
id BIGINT PRIMARY KEY,
{create}
)"""
cursor.execute(create)
yield db.connection
db.connection.close()
def cursor():
# create database cursor
db = connect(env.database, env.server, env.driver, env.username,
env.password)
# database cursor
cursor = db.connection.cursor()
yield cursor
def test_connect():
# master database, local host, trusted Windows connection
db = connect(env.database, env.server, env.driver, env.username, env.password)
assert isinstance(db.connection, pyodbc.Connection)
# username/password without having to hardcode for testing
with pytest.raises(pyodbc.InterfaceError):
connect(
env.database,
env.server,
username="******",
password="",
)
# invalid driver name
with pytest.raises(custom_errors.EnvironmentODBCDriverNotFound):
connect(env.database, env.server, driver="")
def sql():
db = connect(env.database, env.server, env.driver, env.username,
env.password)
yield package(db)
db.connection.close()