Difference between revisions of "Insert Update Delete sqlite queries"
(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):...") |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 6: | Line 6: | ||
c = conn.cursor() | c = conn.cursor() | ||
c.execute(create_table_sql) | c.execute(create_table_sql) | ||
| + | conn.commit() | ||
| + | conn.close() | ||
except Error as e: | except Error as e: | ||
print(e) | print(e) | ||
| Line 17: | Line 19: | ||
database = "test.db" | 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) | conn = create_connection(database) | ||
if conn is not None: | if conn is not None: | ||
| − | + | db_execute(conn, insert) | |
| − | + | db_execute(conn, delete) | |
| + | db_execute(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 13:10, 20 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)
conn.commit()
conn.close()
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_execute(conn, delete)
db_execute(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.
