Azure Question posted in
The official documentation to get you started can be found here.

How to get `.rowcount` on Azure Database Python script?

I need to make a python script using a query and params formatted as a list of dicts to use the execute function on the engine object.

The script that I have been trying only returns -1:

db_type = "mssql+pyodbc"
username = "sa"
password = "abcABC123"
host = "localhost"
port = "1433"
database = "tempdb"
driver = "ODBC+Driver+17+for+SQL+Server"

query = """
    INSERT INTO test (val) VALUES (:val)
"""

params = [
    {'val': 1},
    {'val': 2},
    {'val': 3},
    {'val': 4},
]

from sqlalchemy import create_engine
from sqlalchemy import text

engine = create_engine(f"{db_type}://{username}:{password}@{host},{port}/{database}?driver={driver}")
engine.connect()

with engine.begin() as connection:
    result = connection.execute(text(query), params)
    print(result.rowcount)

docker-compose.yml (for database)

version: "3.9"

services:
  TestDatabase:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: TestDatabase
    restart: unless-stopped
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=abcABC123
      - MSSQL_PID=Developer
      - MSSQL_AGENT_ENABLED=True

It should print the number of rows inserted but nonmatter what I try I cannot get it to work. If anyone can point me in the right direction. I think its a problem with the dbapi?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After much research about the azure database I have come to the conclusion that it is impossible. I currently make the one request and if it succeeds I then return the number of items in dictionary. Less than ideal but works well enough.


  2. 0

    Add below lines to your code to get total count of inserted rows to the SQL table:

    total_inserted_rows = 0

with engine.connect() as connection:
    with connection.begin():
        for param in params:
            result = connection.execute(query, **param)
            total_inserted_rows += result.rowcount
print(f"Total inserted rows: {total_inserted_rows}")

    It will total count of inserted rows to the SQL table, here is the complete code for your reference:

    from sqlalchemy import create_engine, text
import urllib
import sqlalchemy

server = '<serverName>'
database = '<dbName>'
username = '<userName>'
password = '<password>'
driver = '{ODBC Driver 17 for SQL Server}'
odbc_str = 'DRIVER='+driver+';SERVER='+server+';PORT=1433;UID='+username+';DATABASE='+ database + ';PWD='+ password
connect_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(odbc_str)
engine = sqlalchemy.create_engine(connect_str)
query = text("INSERT INTO test (val) VALUES (:val)")
params = [{'val': 1}, {'val': 2}, {'val': 3}, {'val': 4}]
total_inserted_rows = 0
with engine.connect() as connection:
    with connection.begin():
        for param in params:
            result = connection.execute(query, **param)
            total_inserted_rows += result.rowcount
print(f"Total inserted rows: {total_inserted_rows}")

    You will get the output as shown below:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search