Search code examples
pythonazurerunbook

Azure runbook using python to connect to Sql databae

I am creating an azure runbook to connect to a sql database to make some queries .

I was trying to import one of two libraries : pymssql , pyodbc but the machine runs the runbook does not have that library.

Do you have any other idea to make some queries to sql database , or install these two libraries on that machine.

Thank you very much.

Solution

  • Do you have any other idea to make some queries to sql database , or install these two libraries on that machine.

    As a workaround, we can use PowerShell to run sql query, here is the example:

        $SqlServer = "jasontest321.database.windows.net"
    $SqlServerPort = "1433"
    $Database = "jasonsqltest"
    $Table = "dbc"
    $SqlCredentialAsset = "sql"
    $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset 
    if ($SqlCredential -eq $null) 
        { 
            throw "Could not retrieve '$SqlCredentialAsset' credential asset. Check that you created this first in the Automation service." 
        }   
    $SqlUsername = $SqlCredential.UserName 
    $SqlPass = $SqlCredential.GetNetworkCredential().Password 
    $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

    $Conn.Open() 
    $Cmd=new-object system.Data.SqlClient.SqlCommand("insert into dbc(name,age)values('jason2','ba')", $Conn) 
    $Cmd.CommandTimeout=120 
    $Ds=New-Object system.Data.DataSet 
    $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
    [void]$Da.fill($Ds)
    $Ds.Tables.Column1
    $Conn.Close()

    About the $SqlCredentialAsset = "sql", we should create credentials in Azure automation account, like this: enter image description here

    Here is the runbook result:

    enter image description here

    More information about, please refer to this link.