Untitled
unknown
python
2 years ago
2.6 kB
15
Indexable
import psycopg2 import pandas as pd import numpy as np conn = psycopg2.connect("host='localhost' port='5432' dbname='de' user='jovyan' password='jovyan'") cur = conn.cursor() user_order_log = pd.read_csv("/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/user_order_log.csv", sep=';', usecols=[2,3,4,5,6,7,8,9,10,11]) insert_uol = """insert into stage.user_order_log ( date_time, city_id, city_name, customer_id, first_name, last_name, item_id, item_name, quantity, payment_amount ) VALUES {uol_val};""" step = int(user_order_log.shape[0] / 100) i = 0 while i <= user_order_log.shape[0]: print(i, end='\r') uol_val = str([tuple(x) for x in user_order_log.loc[i:i + step].to_numpy()])[1:-1] cur.execute(insert_uol.replace('{uol_val}',uol_val)) conn.commit() i += step+1 user_activity_log = pd.read_csv("/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/user_activity_log.csv", usecols=[3,4,5,6]) insert_ual = """insert into stage.user_activity_log ( date_time, action_id, customer_id, quantity ) VALUES {ual_val};""" step = int(user_activity_log.shape[0] / 100) i = 0 while i <= user_activity_log.shape[0]: print(i, end='\r') ual_val = str([tuple(x) for x in user_activity_log.loc[i:i + step].to_numpy()])[1:-1] cur.execute(insert_ual.replace('{ual_val}',ual_val)) conn.commit() i += step+1 customer_research = pd.read_csv("/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/customer_research.csv", usecols=[1,2,3,4,5]) insert_cr = """insert into stage.customer_research ( date_id, category_id, geo_id, sales_qty, sales_amt) VALUES {cr_val};""" step = int(customer_research.shape[0] / 100) i=0 while i <= customer_research.shape[0]: print(i, end='\r') cr_val = str([tuple(x) for x in customer_research.loc[i:i + step].to_numpy()])[1:-1] cur.execute(insert_cr.replace('{cr_val}',cr_val)) conn.commit() print(cr_val) i += step+1
Editor is loading...