How to setup serverless sql pool to use it with basic SQL Interfaces like pyodbc or RODBC?

Question

I'm trying to set up a serverless SQL pool in Azure Synapse Analytics, and I want to be able to use basic SQL interfaces like pyodbc or RODBC to interact with it. However, I'm not sure how to go about doing this.

Basically, I want to be able to use standard commands like create or insert. Moreover, the wrappers from RODBC or pyodbc like e.g. RODBC::sqlSave(con, iris, "IRIS") should work. So for, only selectworks. However, the line RODBC::sqlSave(channel = con, dat = iris, tablename = "IRIS") throws the following error.

Error in RODBC::sqlSave(channel = con, dat = iris, tablename = "IRIS") : 
  42000 15868 [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]CREATE TABLE IRIS is not supported.
[RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE "IRIS"  ("rownames" varchar(255), "SepalLength" float, "SepalWidth" float, "PetalLength" float, "PetalWidth" float, "Species" varchar(255))'

Can anyone provide guidance on how to configure the serverless SQL pool to work with these interfaces? Specifically, I'm looking for information on any necessary configurations or drivers that need to be installed, as well as any sample code or tutorials that demonstrate how to connect to and query the serverless SQL pool using pyodbc or RODBC.

Thanks in advance for your help!

Answer 1

How to setup serverless sql pool to use it with basic SQL Interfaces like pyodbc or RODBC?

I tried to reproduce the similar error and got the similar error:

The cause of error:

As per this Microsoft Document

Serverless SQL pool has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts isn't supported:

• Tables
• Triggers
• Materialized views
• DDL statements other than ones related to views and security
• DML statements

Create table is not supported in Serverless SQL Pool. you can create external tables.

You can use PYODBC or RODBC to connect to and query the serverless SQL pool by following the procedures listed below:

Get the required drivers:

• Installing the SQL Server ODBC driver is required for pyodbc. From the Microsoft website, you may download it.
• You must install the FreeTDS driver in order to use RODBC. From the FreeTDS website, you may get it.

Get the required libraries in Code:

• You will need to import the pyodbc library in order to use pyodbc.
• You will need to import the RODBC library in order to use RODBC.

below is the sample Python code to query the serverless SQL pool.

import  pyodbc

# Define the connection string

server = 'servername-ondemand.sql.azuresynapse.net'

database = 'db name'

username = 'demo'

password = 'password'

driver= '{ODBC Driver 17 for SQL Server}'

connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"

# Establish a connection to the SQL pool

conn = pyodbc.connect(connection_string)

  

# Create a cursor object

cursor = conn.cursor()

  

# Execute a SQL query

query = 'SELECT * FROM file2'

cursor.execute(query)

  

# Fetch the results

results = cursor.fetchall()

  

# Print the results

print(results)

Execution and output:-

Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-python?view=azuresql