Using SQL: INSERT, datetime and transactions

Anvil Q&A
#1

Here are 3 questions about using SQL with Anvil data tables (available on the dedicated plan). I put them in the same post because anyone using SQL will find them all useful.

INSERT
Is it possible to insert rows using SQL?

I successfully SELECTed, UPDATEd and DELETEd rows on Anvil data tables using psycopg2, but I wasn’t able to INSERT new rows.

This complains about the first column being an integer (my test table has only text columns):

INSERT INTO table_12345 
VALUES ('abc', 'def');

And this says permission denied:

INSERT INTO table_12345 (col1, col2)
VALUES ('abc', 'def');

datetime
How do I cast to datetime?

This works when the column date is of type Date:

UPDATE table_12345 
SET date = date('01/01/2001')
WHERE name = 'abc';
COMMIT;

This doesn’t when the column date is of type Date and Time:

UPDATE table_12345 
SET datetime = timestamp '2001-01-01 01:23:45'
WHERE name = 'abc';
COMMIT;

Transactions
Is it possible to use both SQL and anvil.tables transactions in the same function?

I guess it isn’t, because my psycopg2 works with its own transaction started by its own connection, but I thought I would ask.

#2

this doesn’t specify the columns to insert into and my suspicion is that it’s attempting to insert ‘abc’ into the _id column

#3

Thanks Owen, you’re quite right.

The other problems were an issue with Stefano’s user account, dealt with under a separate support ticket. These are now resolved, and the SQL statements are all working as expected.

1 Like