about-me

Python


Portfolio




click the iamge below!
python python delphi excel mysql



PYTHON CODEs: the transaction table as on the excerpt above
             can be expoted to MySQL table using these codes below:
            
            import os
            import pandas as pd
            import openpyxl
            import mysql.connector
            from sqlalchemy import create_engine
            from sqlalchemy import Text
            #from sqlwriter import SQLWriter
            #import psycopg2

            db_ku = mysql.connector.connect(
                host = "localhost",
                user = "root",
                password = "******",
                database = "posdb",
            )
            print("insert tabel name : ")
            nama_file =input()
            print("You Inserted Table Name : ", nama_file)
            mycursor = db_ku.cursor()
            tabelku="CREATE TABLE " + nama_file
            tabelku2="(no_transak VARCHAR(30),tanggal DATETIME,nama_barang VARCHAR(150),harga NUMERIC(15,2),qty NUMERIC(15,2),"
            tabelku3="sat VARCHAR(15), jumlah NUMERIC(15,2), ket VARCHAR(100), kasir VARCHAR(30))"

            mycursor.execute(tabelku+tabelku2+tabelku3)
            #xlFile = r'C:\\Users\\User\\Dropbox\\DATA SCIENCE\\jupiter\\Apotek-V.M4.xlsm'
            xlFile = "C:\\Users\\User\\Dropbox\\Dintsan\\Apotek-V.M4.xlsm"
            sqlFile = "C:\\Users\\User\\Dropbox\\Dintsan\\DataTransaksi"

            df = pd.read_excel(
                io=xlFile,
                engine='openpyxl',
                sheet_name='DATA TRANSAKSI',
                usecols='A:I',
                header=1
            )
            val = df

            #print(val)

            #conn = psycopg2.connect(**db_creds)

            #writer = SQLWriter(conn=conn,
            #                   database='shop',
            #                   table_name='DataTransaksi',
            #                   cols = 9,
            #                   truncate=True)
            #writer.write(keSQL.values)
            #writer.close()

            pos_con= create_engine("mysql+mysqlconnector://root:apotek@localhost/posdb")
            val.to_sql(nama_file, con=pos_con, if_exists='append', index=False)

            sql = "INSERT INTO " + nama_file + "('no_transak','tanggal','nama_barang','harga','qty','sat','jumlah','kasir') VALUES(%s, %s,%s, %s,%s, %s,%s, %s,%s)"
            mycursor.execute(sql,val)
            db_ku.commit    
            
            
#934
18.97.14.85
19-04-2025 03:02:45am