IT & IoT Security | Cloud | It's all about the life itself

Hi all

In this short tutorial I’d like to cover how to connect to azure sql database , create table , insert sample data to respective columns, update data in columns and delete data with pyodbc. As an IDE I use VS code. To extract data from file I used regular experession re.findall()

import pyodbc
import re  #for regular expression
server = 'pythonadventures.database.windows.net'
database = 'pythonadventures'
username = 'db_user_name'
password = 'db_password'
driver= '{ODBC Driver 17 for SQL Server}'
#DB connection parameter is constructed below , according to the parameter above.
conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+password)

xfh = open("my_files/mbox.txt","r")  #This is a file handler, please use yur own file.

def createXTable(conn=conn):   #table creation
    print("Create Table process iniated")
    cursor=conn.cursor()
    table_name = input("Type table name to create :")
    print("Droping existing tables"+table_name)
    cursor.execute('''DROP TABLE IF EXISTS '''+table_name+'''''')
    print("Creating hold tight :)")
    cursor.execute('create table '+table_name+' (fromX varchar(max), domainX varchar(max))')
    cursor.commit()

def insertXDB(conn=conn):  #insert data
    print("Insert to table ")
    cursor = conn.cursor()
    for line in xfh:
        if not line.startswith("From:"):
            continue
        fromX=re.findall("^From: (\S+)@\S+",line)[0]
        domainX=re.findall("^From: \S+@(\S+)",line)[0]
        cursor.execute('insert or ignore into mbox (fromX,domainX) values(?,?);',(fromX,domainX))  #or ignore'u ekledim
    cursor.commit()
    #readXDB()

def readXDB(conn=conn):  #read data
    print("Read from table")
    cursor = conn.cursor()
    table_name = input("Enter table name to read data :")
    cursor.execute("select top 3 fromX,domainX from "+table_name+" ") #reading top 3 row
    for i in cursor:  #reading row per row
        print(f'row={i}')

def updateXDB(conn=conn):  #update of crud
    print("updating")
    cursor = conn.cursor()
    #ey_to_change = input("Type F Name : ")
    #value_to_change = input("Type L Name : ")
    cursor.execute(
        'update mbox set domainX = ? where fromX = ?;',
        ('aydog','dlhaines')
        ) #updating some data
    cursor.commit()
    readXDB()

def deleteXDB(conn=conn):  #delete some data
    print("deleting")
    cursor = conn.cursor()
    value_to_delete = input("Enter Domain value to delete :")
    cursor.execute('''delete from mbox where domainX=?;''',(value_to_delete))  #You need to enter the name of the data
    cursor.commit()
    readXDB()

#insertXDB() 
#readXDB()   
#updateXDB()
#deleteXDB()

The functions above can be called according to the action you want to perform.
For sure there are many different ways to perform these tasks. This is just another one.

Wish you a gr8 time.