import pyodbc
import csv

try:
    connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.;DATABASE=SampleDB;Trusted_Connection=yes')

    cursor = connection.cursor()

    print("[creating table tbl_customers_from_csv...]")
    create_table="""IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_customers_from_csv]') AND type in (N'U'))
    begin
    create table [dbo].[tbl_customers_from_csv] (
    id int not null,
    code varchar(50) null,
    first_name varchar(50) null,
    last_name varchar(50) null,
    )
    end;
    """

    cursor.execute(create_table)
    connection.commit()

    print("[selecting from table before csv insert...]")
    cursor.execute("select * from tbl_customers_from_csv")
    while 1:
        row = cursor.fetchone()
        if not row:
            break
        print(row.id, row.code, row.first_name, row.last_name)

    print()

    # read csv file and insert each row into table in sql server
    print("[reading csv file and insert new rows into sql server table...]")
    with open("./csv/sample-csv-file-for-demo.csv", newline='') as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=';', quotechar='|')
        for row in csv_reader:
            prm_id=row[0]
            prm_code=row[1]
            prm_first_name=row[2]
            prm_last_name=row[3]

            print("inserting record:",', '.join(row))
            cursor.execute("insert into dbo.tbl_customers_from_csv values (?,?,?,?)", prm_id, prm_code, prm_first_name, prm_last_name)
            connection.commit()
            
    print()

    print("[select table after inserts...]")
    cursor.execute("select * from tbl_customers_from_csv")
    while 1:
        row = cursor.fetchone()
        if not row:
            break
        print(row.id, row.code, row.first_name, row.last_name)

    print()

    cursor.close()
    connection.close()

except pyodbc.Error as ex:
    print()
    print("exception: ",ex)
    cursor.close()
    connection.close()
    print()
    exit()

print()