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
#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) |
|
|
|
''' |