Generate Multiple sql Files With csv inputs

[vkomarag]

Hi Team,

I m new to python and started working on basics but cant get through when the problem goes to next level.

I have data looks like below and attached but I need to generate create statements out of CSV rows.

DB SCH TAB COL
DEV SCOTT EMP EMPNO
DEV SCOTT EMP ENAME
DEV SCOTT EMP DEPTNO
DEV SCOTT DEPT DEPTNO
DEV SCOTT DEPT DNAME
DEV SCOTT DEPT LOC

I want to generate 1 file for each table

CREATE VIEW SCOTT.EMP_V
AS
SELECT
EMPNO,
ENAME,
DEPTNO
FROM
SCOTT.EMP; --> This output is routed to EMP_V.sql file

CREATE VIEW SCOTT.DEPT_V
AS
SELECT
DEPTNO,
DNAME,
LOC
FROM
SCOTT.DEPT; --> This output is routed to DEPT_V.sql file

I am browsing through list and dictionaries and trying to do in parallel.

df = pd.read_csv("view.csv")
print(df)
df1=df.groupby(['DB','TAB'])['COL'].apply(','.join)
print(df1)
df2 = df1.reset_index(name = 'COLS')
print(df2)

Any better inputs are appreciated.
Please let me know

[Larz60+]

Take a look at my SQLAlchemy tutorial, which creates a database from CSV files here: https://python-forum.io/thread-33843.html
There is also a basic SQLAlchemy tutorial here: https://python-forum.io/thread-24127.html

[vkomarag]

I tried this and able to work it out except one thing. The columns I need to split into rows under select statement .This would achieve my task.

e.g. SELECT
EMPNO,
ENAME,
DEPTNO
FROM
EMP;

df = pd.read_csv("view.csv")
#print(df)
df1=df.groupby(['DB','SCH','TAB'])['COL'].apply(','.join)
# groupby_db = df.groupby("DB")
# print(groupby_db)
# grouped_lists = groupby_db["COL"].apply(list)
# print(grouped_lists)
#print(df1)
df2 = df1.reset_index(name = 'COLS')
print (df2)
for ind,row in df2.iterrows() :
    if ind > len(df2):
        break
    else:
        f=open(row["TAB"]+ '_V.txt','w')
        f.write("CREATE OR REPLACE VIEW " + row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+ "_V \nAS\nSELECT\n"+row["COLS"] + "\nFROM\n"+ row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+";")
        f.close()
        ind+=1
    print("CREATE OR REPLACE VIEW " + row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+ "_V \nAS\nSELECT\n"+row["COLS"] + "\nFROM\n"+ row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+";")

Larz60+ write Aug-19-2021, 01:16 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Fixed for you this time. Please use bbcode tags on future posts.

[vkomarag]

Take a look at my SQLAlchemy tutorial, which creates a database from CSV files here: https://python-forum.io/thread-33843.html
There is also a basic SQLAlchemy tutorial here: https://python-forum.io/thread-24127.html

That will be too heavy. I have written code and it is done except one thing. I have shared it below. Any help is appreciated.

I m expecting cols output like
SELECT
EMPNO,
ENAME,
DEPTNO
FROM
EMP;

But I am getting
SELECT
EMPNO,ENAME,DEPTNO
FROM
EMP;

[vkomarag]

Any response to the actual problem is appreciated.

[deanhystad]

I don't understand your question. Could you provide an example of real data? That may shed some light on what you are trying to do.

[vkomarag]

I don't understand your question. Could you provide an example of real data?

I have the data in the .csv file as below. I have attached the .csv file in the first post of this thread.
DB SCH TAB COL
DEV SCOTT EMP EMPNO
DEV SCOTT EMP ENAME
DEV SCOTT EMP DEPTNO
DEV SCOTT DEPT DEPTNO
DEV SCOTT DEPT DNAME
DEV SCOTT DEPT LOC

And I have wrote the below code and almost got the output except a minor one in the print

df = pd.read_csv("view.csv")
#print(df)
df1=df.groupby(['DB','SCH','TAB'])['COL'].apply(','.join)
# groupby_db = df.groupby("DB")
# print(groupby_db)
# grouped_lists = groupby_db["COL"].apply(list)
# print(grouped_lists)
#print(df1)
df2 = df1.reset_index(name = 'COLS')
print (df2)
for ind,row in df2.iterrows() :
    if ind > len(df2):
        break
    else:
        f=open(row["TAB"]+ '_V.txt','w')
        f.write("CREATE OR REPLACE VIEW " + row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+ "_V \nAS\nSELECT\n"+row["COLS"] + "\nFROM\n"+ row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+";")
        f.close()
        ind+=1
    print("CREATE OR REPLACE VIEW " + row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+ "_V \nAS\nSELECT\n"+row["COLS"] + "\nFROM\n"+ row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+";")

All I want to get is
CREATE OR REPLACE VIEW DEV.SCOTT.EMP_V
AS
SELECT
EMPNO,
ENAME,
DEPTNO
FROM
DEV.SCOTT.EMP;

What I m getting is

CREATE OR REPLACE VIEW DEV.SCOTT.EMP_V
AS
SELECT
EMPNO,ENAME,DEPTNO
FROM
DEV.SCOTT.EMP;

All I need is this minor change to my code or else any far better approach.

[deanhystad]

That is the same as before. No additional information was provided. That is not data.

[vkomarag]

That is the same as before. No additional information was provided. That is not data.

I m sorry that is very much clear on the input, what has been written and what has been the output I m getting and what I m expecting. Data is there in the thread as well as in the attachment. What else is required?

[Yoriz]

So you currently have the equivalent of

row = {
    "DB": "DEV",
    "SCH": "SCOTT",
    "TAB": "EMP",
    "COLS": "EMPNO,ENAME,DEPTNO",
}

print("CREATE OR REPLACE VIEW " + row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+ "_V \nAS\nSELECT\n"+row["COLS"] + "\nFROM\n"+ row["DB"] + "." + row["SCH"]+ "." + row["TAB"]+";")

Output:
CREATE OR REPLACE VIEW DEV.SCOTT.EMP_V 
AS
SELECT
EMPNO,ENAME,DEPTNO
FROM
DEV.SCOTT.EMP;

and you would like the output to be?

row = {
    "DB": "DEV",
    "SCH": "SCOTT",
    "TAB": "EMP",
    "COLS": "EMPNO,ENAME,DEPTNO",
}

appropriate_name = ".".join((row["DB"], row["SCH"], row["TAB"]))
columns = ",\n".join(row["COLS"].split(","))
print(
    f"CREATE OR REPLACE VIEW {appropriate_name}_V \nAS\nSELECT\n"
    f"{columns}\nFROM\n{appropriate_name};"
)

Output:
CREATE OR REPLACE VIEW DEV.SCOTT.EMP_V
AS
SELECT
EMPNO,
ENAME,
DEPTNO
FROM
DEV.SCOTT.EMP;

Be aware I'm not sure if what you are doing that SQL injection could be an issue