Python – SQL Server on Ubuntu 検索結果をファイルに保存 No.95

import time
import pyodbc
import random
import datetime

server = 'localhost'
database = 'testdb'
username = 'sa'
password = 'yourpassword'
driver= '{ODBC Driver 17 for SQL Server}'
success = False
retryc = 0
max_retries = 3

while not success and retryc < max_retries:
    try:
        # create random number
        num = random.randint(100, 10000000)

        cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+',1433;DATABASE='+database+';UID='+username+';PWD='+ password+';',autocommit=False,Timeout=1,Trusted_Connection='no',Encrypt='no')
        cursor0 = cnxn.cursor()
        cursor0.execute("insert into sampledb.dbo.sampletb values ("+ str(num) + ","+ str(num) + ")")
        cursor0.commit()

        cursor = cnxn.cursor()
        cursor.execute("SELECT * FROM sampledb.dbo.sampletb")
        row = cursor.fetchone()
        
        # save the results each line to a file
        with open('/home/xxx/results.txt', 'w') as f:
            while row:
                f.write(str(row[0]) + " " + str(row[1]) + "\n")
                row = cursor.fetchone()
  
        success = True
    except pyodbc.Error as ex:
        retryc += 1
        print("Current Timestamps:" + str(datetime.datetime.now()) + "retry count: " + str(retryc))
        print("==========================")
        print("Error: " + str(ex))
        print("==========================")
        time.sleep(3)

    finally:
        cursor.close()

# results.txt
1 1
2 2
3 3
10 10
3790247 3790247