def test_nan(self):
df3 = pd.DataFrame({"A": [1, np.NaN], "B": [np.nan, 4.3]})
fts.fast_to_sql(df3, "test_table5", self.conn, "replace")
cur = self.conn.cursor()
res = cur.execute("SELECT * FROM test_table5").fetchall()
self.assertIsNone(res[0][1])
self.assertIsNone(res[1][0])
def test_null_datetime(self):
df = pd.DataFrame({
"A": [1, 2, 3],
"B": [pd.Timestamp("20200101"),
pd.Timestamp("20200202"), None]
})
fts.fast_to_sql(df, "test_table5", self.conn, "replace")
cur = self.conn.cursor()
res = cur.execute("SELECT * FROM test_table5").fetchall()
self.assertEqual(datetime.datetime(1, 1, 1, 0, 0), res[2][1])
def test_null_values(self):
cur = self.conn.cursor()
data = pd.read_csv("tests/test_data2.csv")
fts.fast_to_sql(data,
"testy2",
self.conn,
if_exists="replace",
temp=False)
self.conn.commit()
output = cur.execute("select * from testy2").fetchall()
self.assertIsNone(output[0][1])
self.assertIsNone(output[1][2])
def test_big_numbers(self):
cur = self.conn.cursor()
with open("tests/test_data.dat", "r") as f:
data = f.read()
data = data.split("\n")
data = {i.split("|")[0]: [i.split("|")[1]] for i in data}
data = pd.DataFrame(data)
fts.fast_to_sql(data,
"testy1",
self.conn,
if_exists="replace",
temp=False)
self.conn.commit()
test_val = int(cur.execute("select M from testy1").fetchall()[0][0])
self.assertEqual(352415214754234, test_val)
def fast_server_nimerya(database_name, df, tablename, if_exists):
"""
Parameters
----------
database_name : TYPE
Add the name of the database you want to upload the df to.
df : TYPE
dataframe you want to upload to sql server
tablename : TYPE
Name of the table to upload to sql server.
if_exists : TYPE
If the table already exists "append".
If the table has to be deleted "replace".
Important information
---------------------
.to_sql will create the table in the SQL server, therefore the column names of
the dataframe will be the same column names you will have in the database.
"""
conn = pyodbc.connect(
'DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1}; + SERVER='
+ "'" + st.secrets["DB_SERVER"] + "'" + ';DATABASE=' + 'bimbo' +
';UID=' + "'" + st.secrets["DB_USERNAME"] + "'" + ';PWD=' + "'" +
st.secrets["DB_PASSWORD"] + "'")
# =============================================================================
# Upload de dataframe
# =============================================================================
create_statement = fts.fast_to_sql(df,
tablename,
conn,
if_exists=if_exists)
# Commit upload actions and close connection
conn.commit()
conn.close()
def should_fail():
fts.fast_to_sql(df, "test_table3", conn, "fail",
{"A": "INT PRIMARY KEY"}, False)
def test_fast_to_sql(self):
"""Test main fast_to_sql function
"""
cur = conn.cursor()
# Simple test table
df = pd.DataFrame({
"A": [1, 2, 3],
"B": ["a", "b", "c"],
"C": [True, False, True]
})
fts.fast_to_sql(df, "dbo.test_table2", conn, "replace", None, False)
self.assertEqual(
(1, 'a', True),
tuple(cur.execute("select * from dbo.test_table2").fetchall()[0]))
# Series
s = pd.Series([1, 2, 3])
fts.fast_to_sql(s, "seriesTest", conn, "replace", None, False)
self.assertEqual(
1,
cur.execute("select * from seriesTest").fetchall()[0][0])
# Temp table
df = pd.DataFrame({
"A": [1, 2, 3],
"B": ["a", "b", "c"],
"C": [True, False, True]
})
fts.fast_to_sql(s, "seriesTest", conn, "replace", None, True)
self.assertEqual(
1,
cur.execute("select * from #seriesTest").fetchall()[0][0])
# Custom Column Type
df = pd.DataFrame({
"A": ["1", "2", "3"],
"B": ["a", "b", "c"],
"C": [True, False, True]
})
fts.fast_to_sql(df, "test_table3", conn, "replace",
{"A": "INT PRIMARY KEY"}, False)
with open("tests/get_col_def.sql", "r") as f:
sql = f.read()
res = cur.execute(sql).fetchall()
self.assertEqual("int", res[0][1])
self.assertTrue(res[0][6])
# Fail if_exists
def should_fail():
fts.fast_to_sql(df, "test_table3", conn, "fail",
{"A": "INT PRIMARY KEY"}, False)
self.assertRaises(errors.FailError, should_fail)
# SQL output
df = pd.DataFrame({
"A": [4, 5, 6],
"B": ["a", "b", "c"],
"C": [True, False, True]
})
output = fts.fast_to_sql(df, "test_table3", conn, "append", None,
False)
self.assertEqual("", output)
output = fts.fast_to_sql(df, "test_table4", conn, "append", None,
False)
with open("tests/test_create_2.sql", "r") as f:
compare = f.read()
self.assertEqual(compare, output)
def test_copy(self):
df2 = pd.DataFrame({"A Minus": [1, 2], "B Plus": [3, 4]})
fts.fast_to_sql(df2, "test_table4", self.conn, "replace", copy=True)
self.assertEqual(df2.columns[0], "A Minus")
fts.fast_to_sql(df2, "test_table4", self.conn, "replace")
self.assertEqual(df2.columns[0], "[A_Minus]")