You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
78 lines
3.1 KiB
78 lines
3.1 KiB
2 years ago
|
#import mysql
|
||
|
|
import math
|
||
|
|
|
||
|
|
#from mysql.connector import Error
|
||
|
|
from runsql import runsql
|
||
|
|
import pandas as pd
|
||
|
|
from sqlalchemy import create_engine
|
||
|
|
import pymysql
|
||
|
|
|
||
|
|
# Credentials to database connection
|
||
|
|
hostname="localhost"
|
||
|
|
dbname="computeddata_testDB"
|
||
|
|
uname="root"
|
||
|
|
pwd="Hallo112!"
|
||
|
|
max_row_size_to_write = 200
|
||
|
|
# Create SQLAlchemy engine to connect to MySQL Database
|
||
|
|
engine = create_engine("mysql+pymysql://{user}:{pw}@{host}/{db}"
|
||
|
|
.format(host=hostname, db=dbname, user=uname, pw=pwd))
|
||
|
|
|
||
|
|
setup_ids = runsql('select distinct setup_id from computeddata order by setup_id asc')
|
||
|
|
setup_ids_list = list(setup_ids)
|
||
|
|
|
||
|
|
for ids in range(len(setup_ids_list)):
|
||
|
|
SETUP_ID = int(setup_ids_list[ids]['setup_id'])
|
||
|
|
if SETUP_ID == 868:
|
||
|
|
reader = runsql('select * from computeddata where setup_id = {} order by distance asc'.format(SETUP_ID))
|
||
|
|
# reader_data = list(reader)
|
||
|
|
df = pd.DataFrame.from_dict(reader)
|
||
|
|
rows = df.shape[0]
|
||
|
|
print('write setup_id = %d'%(SETUP_ID))
|
||
|
|
if rows > max_row_size_to_write:
|
||
|
|
iterations = math.ceil(rows / max_row_size_to_write)
|
||
|
|
for r in range(iterations):
|
||
|
|
start = r * max_row_size_to_write
|
||
|
|
end = (r+1) * max_row_size_to_write
|
||
|
|
if end > rows:
|
||
|
|
end = rows
|
||
|
|
print('-- table rows from %d to %d' % (start, end))
|
||
|
|
df.iloc[start:end].to_sql(dbname, engine, index=False)
|
||
|
|
else:
|
||
|
|
df.to_sql(dbname, engine, index=False)
|
||
|
|
|
||
|
|
# Convert dataframe to sql table
|
||
|
|
df.to_sql('users', engine, index=False)
|
||
|
|
|
||
|
|
###############################################################################
|
||
|
|
SETUP_ID = 868
|
||
|
|
reader = runsql('select * from computeddata where setup_id = {} order by distance asc'.format(SETUP_ID))
|
||
|
|
#reader_data = list(reader)
|
||
|
|
df = pd.DataFrame.from_dict(reader)
|
||
|
|
|
||
|
|
|
||
|
|
empdata = df
|
||
|
|
'''
|
||
|
|
try:
|
||
|
|
conn = mysql.connector.connect(host='localhost', database='mysql', user='root', password='Hallo112!')
|
||
|
|
if conn.is_connected():
|
||
|
|
cursor = conn.cursor()
|
||
|
|
cursor.execute("select database();")
|
||
|
|
record = cursor.fetchone()
|
||
|
|
print("You're connected to database: ", record)
|
||
|
|
cursor.execute('DROP TABLE IF EXISTS employee_data;')
|
||
|
|
print('Creating table....')
|
||
|
|
# in the below line please pass the create table statement which you want #to create
|
||
|
|
cursor.execute("CREATE TABLE employee_data(first_name varchar(255),last_name varchar(255),company_name varchar(255),address varchar(255),city varchar(255),county varchar(255),state varchar(255),zip int,phone1 varchar(255),phone2 varchar(255),email varchar(255),web varchar(255))")
|
||
|
|
print("Table is created....")
|
||
|
|
#loop through the data frame
|
||
|
|
for i,row in empdata.iterrows():
|
||
|
|
#here %S means string values
|
||
|
|
sql = "INSERT INTO employee.employee_data VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
|
||
|
|
cursor.execute(sql, tuple(row))
|
||
|
|
print("Record inserted")
|
||
|
|
# the connection is not auto committed by default, so we must commit to save our changes
|
||
|
|
conn.commit()
|
||
|
|
except Error as e:
|
||
|
|
print("Error while connecting to MySQL", e)
|
||
|
|
|
||
|
|
'''
|