Python SQL Raw Query Issue

We had given a task and for which we written below code, while executing it showing an error. Hence issue in sqlalchemy need help for . Please let us know where we went wrong in coding-

from sqlalchemy import create_engine
db_string = "sqlite:///tests.db"
db = create_engine(db_string)

#create
db.execute("CREATE TABLE IF NOT EXISTS players (plyid text, plyname text, runs text)")
db.execute("INSERT INTO players (plyid, plyname, runs) VALUES ('10001', 'Ply1', '100')")
db.execute("INSERT INTO players (plyid, plyname, runs) VALUES ('10002', 'Ply2', '80')")
db.execute("INSERT INTO players (plyid, plyname, runs) VALUES ('10003', 'Ply3', '65')")
db.execute("INSERT INTO players (plyid, plyname, runs) VALUES ('10004', 'Ply4', '95')")
db.execute("INSERT INTO players (plyid, plyname, runs) VALUES ('10005', 'Ply5', '99')")
   

# Read
s=[]
result_set = db.execute("SELECT * FROM players")
for s1 in result_set:
    print(s)

#Update
q=[]
db.execute("UPDATE players SET runs='100' WHERE plyid='10005'")
result_set1 = db.execute("SELECT * FROM players")
for s2 in result_set1:
    print(q)

#Delete
e=[]
db.execute("DELETE FROM players WHERE plyid='10005'")
result_set2 = db.execute("SELECT * FROM players")
for s3 in result_set2:
    print(e)

s=str(s)
q=str(q)
e=str(e)
with open(".hidden.txt",'w') as f:
    f.write(s)

with open(".hidden1.txt",'w') as f:
    f.write(q)

with open(".hidden2.txt",'w') as outfile:
    outfile.write(e)

Here our issue is that we need to combine for example S[] with S1 so that output will be
('10001', 'Ply1', '100'), ('10002', 'Ply2', '80'), ('10003', 'Ply3', '65'), ('10004', 'Ply4', '95'), ('10005', 'Ply5', '99')

similar q[] with S2 so that output will be
('10001', 'Ply1', '100'), ('10002', 'Ply2', '80'), ('10003', 'Ply3', '65'), ('10004', 'Ply4', '95'), ('10005', 'Ply5', '100')

Finally e[] with S3 so that output will be
('10001', 'Ply1', '100'), ('10002', 'Ply2', '80'), ('10003', 'Ply3', '65'), ('10004', 'Ply4', '95')

As we not getting above output hence we getting error while executing. Hence please suggest how we can append both.

1 Like

resolved…

1 Like