Untitled

 avatar
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...