sqltest | comprehensive suite of SQL tests | SQL Database library

You should use a compatible version on the client-side. While the 4.x client is mostly compatible with 5.x, the SQL feature was in BETA until 5.0. So you should use 5.0 or newer.

In Spring boot you can just add the following property to override the version, if you are using Maven:

It sometimes can be useful to handle the errors yourself. Add references to

1. Microsoft ActiveX Data Objects 6.1 Library
2. Microsoft ActiveX Data Objects RecordSet 6.0 Library

Your problem is that you do execute directly on the connection and not the cursor.

Docs explain how that shortcut works:

execute(sql[, parameters]) This is a nonstandard shortcut that creates a cursor object by calling the cursor() method, calls the cursor’s execute() method with the parameters given, and returns the cursor.

https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.execute

See at the end. "returns the cursor". This means we can still get the use the Cursor.lastrowsid which you tried!

So just... save the returned cursor and get lastrowid from it. :)

A derived table is specifically a subquery that is used in the from clause, that returns a result set with an arbitrary number of columns and rows.

A subquery is more generic and refers to any query-within-a-query. One type of subquery, for instance, is a scalar subquery. Such a subquery returns at most one row and one column. It can be used in select and where (and some other places) where a scalar value can be used. A scalar subquery can also be used in the from clause.

Consider:

Recall SQL's logical order of operations that differ from its lexical order (i.e., order in how it is written). Usually the first step in query processing is the FROM clause, then JOIN, ON, WHERE, GROUP BY, etc. and usually ending with ORDER BY and SELECT (ironically one of the last clauses processed though written first).

Technically, your queries do not involve correlated subqueries since there are no inner or outer levels. Specifically, the derived table t3 and base table t4 are at the same level. The query engine evaluates t3 in isolation by itself during FROM clause step. Then, it evaluates JOIN table, t4, in isolation by itself and finally applies the matching ON logic.

Because t4 is not defined in the universe of t3, MS Access via GUI prompts for that parameter value (where MS Access via ODBC will raise an error). To resolve you have to include all necessary data sources in each table scope:

ALTER TRACE is a Server Permission not a database permission. So try SqlServerPermission instead of SqlDatabasePermission if such a thing exists.

You might be able to work around this by creating a user in Master for mySQLUser. My guess is that DSC is checking for the existence of a database principal before running

GRANT ALTER trace TO [MyUser]

And that grant will succeed if the login exists, if you can persuade DSC to attempt it.

You have MariaDB (essentially MySQL) as your database and you are trying to use Microsoft SQLServer client libraries to access it. It'll never work - they are completely different databases.

Use MySQL library instead

There is a function in pandas that does exactly what you want, pd.read_sql()

You need to create a connection to the database first. Here's a brief example: