ptkLL9Yw

1import pandas as pd
2from numpy import genfromtxt
3from sqlalchemy import Column, VARCHAR, INTEGER, BigInteger, ForeignKey
4from sqlalchemy.ext.declarative import declarative_base
5from sqlalchemy import create_engine
6from sqlalchemy.orm import sessionmaker
7from sqlalchemy import *
8from sqlalchemy.ext.declarative import declarative_base
9from sqlalchemy.orm import sessionmaker
10from sqlalchemy.sql import *
11from sqlalchemy.schema import Sequence, CreateSequence
12from sqlalchemy.schema import CreateColumn
13from sqlalchemy.ext.compiler import compiles
14
15
16def Load_Data(file_name):
17data = genfromtxt(file_name, delimiter=',', converters={0: lambda s: str(s)})
18return data.tolist()
19
20
21Base = declarative_base()
22
23engine = 
24create_engine('postgres://postgres:Farees143k@localhost/sqlalchemy')
25Base.metadata.create_all(engine)
26
27@compiles(CreateColumn, 'postgresql')
28def use_identity(element, compiler, **kw):
29text = compiler.visit_create_column(element, **kw)
30text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
31return text
32
33class csvimport(Base):
34__tablename__ = 'csvimport'
35id = Column(INTEGER,primary_key=True)
36
37#
38# class conform_layer(Base):
39#     __tablename__ = 'conform_layer'
40#     unique_no = Column(BigInteger, ForeignKey(csvimport.id), primary_key=True)
41#     key_name = Column(VARCHAR)
42#     value_name = Column(VARCHAR)
43
44#
45# csvimport.__tablename__.create(bind=engine, checkfirst=True)
46# conform_layer.__tablename__.create(bind=engine, checkfirst=True)
47
48if __name__ == "__main__":
49session = sessionmaker()
50session.configure(bind=engine)
51s = session()
52
53try: 
54 file_name = "data/sample.csv"
55 data = Load_Data(file_name)
56
57input_file = open('data/sample.csv')
58data = input_file.read()
59
60input_file.close()
61
62file_name = 'data/sample.csv'
63df = pd.read_csv(file_name)
64df.to_sql(con=engine, index_label='id', name=csvimport.__tablename__, if_exists='replace')
65
66#engine.execute(" CREATE SEQUENCE sequen START WITH 1000 INCREMENT BY 1 NO MAXVALUE CACHE 1;")
67
68#engine.execute("alter table csvimport add column primary_key integer DEFAULT NEXTVAL('sequen');")
69
70engine.execute("""
71            create table if not exists conform_layer (unique_no bigint, key_name text, value_name text);
72            """)
73
74engine.execute(
75    "insert into conform_layer SELECT id, key, value FROM (select row_to_json(t.*) AS line, id FROM csvimport AS t) AS r JOIN LATERAL json_each_text(r.line) ON (key <> 'id')")
76
77
78except:
79 s.rollback()
80 print('No !!!')
81finally:
82 s.close()
83
84print("Done")