Insert Update Delete sqlite queries

From TRCCompSci - AQA Computer Science
Revision as of 10:43, 6 October 2019 by Admin (talk | contribs)
Jump to: navigation, search

You will need to use the sqlite connection code from the previous page. You should add this method:

def db_execute(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

If you have completed the 'Create' tutorial you can just rename the method you added instead. The method to execute is the same for create, insert, delete, and update queries. The code above will create a cursor first, this is essentially an sql command. You can then execute the cursor with the sql passed into the method.

You can use this code by:

    database = "test.db"

    insert = "insert into table values(1,2,3,4)"
    delete = "delete from table where id=2"
    update = "update table set name = 'test' where id = 1"

    conn = create_connection(database)
    if conn is not None:
        db_execute(conn, insert)
        db_exectute(conn, delete)
        db_exectute(conn, update)

This uses a variable for the database file, and 2 examples of sql to create 2 different tables. conn is the connection, and it uses the connection method from the previous page. If the conn is successful it passes the sql and the connection to the create table method. The second sql statement also shows how to create a table with a foreign key.