sql_lite_db_update

program.py

# this is just to check db connection and sql commands from python.
# I know a better thing to do is to use django or SQLAlchemy to avoid sql injections. And that's what i do generally.

import sqlite3
conn = sqlite3.connect('example.db')

conn.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print("Table created successfully")

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()

print("Data created successfully")


cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")

for val in cursor:
    print(val)

# (1, 'Paul', 'California', 20000.0)
# (2, 'Allen', 'Texas', 15000.0)
# (3, 'Teddy', 'Norway', 20000.0)
# (4, 'Mark', 'Rich-Mond ', 65000.0)

tableName = 'COMPANY'
colName = 'NAME'
colVal = 'Viki'
name = 'ADDRESS'
val = 'Norway'
print("UPDATE " + tableName + " SET " +
                       colName + "='" + colVal +
                        "' WHERE " + name + "=('" + val + "')")
conn.execute("UPDATE " + tableName + " SET " +
                       colName + "='" + colVal +
                        "' WHERE " + name + "=('" + val + "')")

conn.commit()


cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")

for val in cursor:
    print(val)
    
# (1, 'Paul', 'California', 20000.0)
# (2, 'Allen', 'Texas', 15000.0)
# (3, 'Viki', 'Norway', 20000.0)
# (4, 'Mark', 'Rich-Mond ', 65000.0)