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
|