Difference between revisions of "Insert Update Delete sqlite queries"

From AQA Computer Science
Jump to: navigation, search
(Created page with "You will need to use the sqlite connection code from the previous page. You should add this method: <syntaxhighlight lang=python> def db_execute(conn, create_table_sql):...")
 
 
Line 17: Line 17:
 
     database = "test.db"
 
     database = "test.db"
  
     sqlprojects = """ CREATE TABLE IF NOT EXISTS projects (
+
     insert = "insert into table values(1,2,3,4)"
                id integer PRIMARY KEY,
+
    delete = "delete from table where id=2"
                name text NOT NULL,
+
    update = "update table set name = 'test' where id = 1"
                begin_date text,
 
                end_date text
 
                ); """  
 
  
    sqltasks = """CREATE TABLE IF NOT EXISTS tasks (
 
              id integer PRIMARY KEY,
 
              name text NOT NULL,
 
              priority integer,
 
              status_id integer NOT NULL,
 
              project_id integer NOT NULL,
 
              begin_date text NOT NULL,
 
              end_date text NOT NULL,
 
              FOREIGN KEY (project_id) REFERENCES projects (id)
 
                ); """
 
                               
 
    # create a database connection
 
 
     conn = create_connection(database)
 
     conn = create_connection(database)
 
     if conn is not None:
 
     if conn is not None:
         create_table(conn, sqlprojects)
+
         db_execute(conn, insert)
         create_table(conn, sqltasks)
+
         db_exectute(conn, delete)
 +
        db_exectute(conn, update)
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
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.
 
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.

Latest revision as of 10:43, 6 October 2019

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.