Can python cursor.execute accept multiple queries in one go?-CodePudding

Can the cursor.execute call below execute multiple SQL queries in one go?

cursor.execute("use testdb;CREATE USER MyLogin")

I don't have python setup yet but want to know if above form is supported by cursor.execute?

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' server ';DATABASE=' database ';UID=' username ';PWD='  password)
cursor = cnxn.cursor()
#Sample select query
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

CodePudding user response：

Yes, it is possible.

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):

But it is not a comprehensive solution. For example, in queries with two selections, you have problems.

Consider that two types of answers must be fetch all in the cursor!

So the best solution is to break the query to sub queries and do your work step by step. for example :

s = "USE some_db; SELECT * FROM some_table;"
s = filter(None, s.split(';'))

for i in s:
    cur.execute(i.strip()   ';')

CodePudding user response：

in the pyodbc documentation should give you the example your looking for. more over in the GitHub wiki: https://github.com/mkleehammer/pyodbc/wiki/Objects#cursors

you can see an example here:

cnxn   = pyodbc.connect(...)
cursor = cnxn.cursor()

cursor.execute("""
               select user_id, last_logon
                 from users
                where last_logon > ?
                  and user_type <> 'admin'
               """, twoweeks)

rows = cursor.fetchall()

for row in rows:
    print('user %s logged on at %s' % (row.user_id, row.last_logon))

from this example and exploring the code, I would say your next step is testing a multi cursor.execute("<your_sql_Querie>").

if this test works, maybe try and create a CLASS then create instances of that class for each query you want to run.

This would be the basic evolution of a developers effort of reproducing documentation...hope this helps you :)