# preferred format:
import sqlalchemy
# import pymssql
import pandas as pd
import getpass

username=r'PROD\' + getpass.getuser()  # Raw string for backslash + current user # r'PROD\ '[:-1] + getpass.getuser()
password=getpass.getpass(prompt="Please Enter Your Password: ")
host='B041473-PD.prodlb.travp.net'
port='41473'
database='Activity_SB'

engine3 = sqlalchemy.create_engine(f"mssql+pymssql://{username}:{password}@{host}:{port}/{database}")


sql_to_run = """
select top 10 *
from rptdb_batch.v_rptmis_loss_incurred
where mrl_cd in ('607340','607341')"""

# read
a9 = pd.read_sql(sql_to_run, engine3)
print(a9.head(2))

# write
df = pd.DataFrame({"A": [1, None, 202]})
df.to_sql(name='testpy2', con=engine3, schema='cmn', if_exists='replace')

a9.head(2)

# other format1
# engine
engine = sqlalchemy.create_engine("mssql+pymssql://PRODALJIAMGJ:Spring2025itherc@BD41473_80.prodlib.trawp.okts_41473/4s_twgrr_99")

# read
sql_to_run = ""
select top 10 *
from rptdb_batch..v_rotnis_loss_incurred
where mrl_cd in ('607340','607341')"""

a3 = pd.read_sql(sql_to_run, engine)
a3.head(2)

# write the DataFrame to a table in the sql database......
df = pd.DataFrame(["A": [1, None, 3102]])
df.to_sql(name='testpy2', con=engine, schema='cmn', if_exists='replace')

# other format2
username='PRODALIJAMG3'
password='Spring2024'
host='BD41473-PD_prodlb.travp.net'
port='41473'
database='Actuary_SB'

engine1 = sqlalchemy.create_engine(f"mssql+pymssql://(username):{password}@{host}:{port}/{database}")
df = pd.DataFrame(["A": [1, None, 202]])
df.to_sql(name='testpy2', con=engine1, schema='cmn', if_exists='replace')

# other format3
engine2 = sqlalchemy.create_engine(f"mssql+pymssql://{?:{}$()/{}\",format(
    'PRODALIJAMG3',
    'Spring2024',
    'BD41473-PD_prodlb.travp.net: 41473',
    'Actuary_SB',
))

# write the DataFrame to a table in the sql database......
df = pd.DataFrame(["A": [1, None, 2]])
df.to_sql(name='testpy', con=engine2, schema='cmn', if_exists='replace')