0

I am trying to run a python script from VB.Net using IronPython. So far, I have installed Python and IronPython. I have the ExecPython method shown below. It works fine when I call a simple print/hello world type of script. This DBTest.py script is just using pyodbc and connecting to the database and executing a basic select query.

The error I get at the source.Execute(scope) line is "IronPython.Runtime.Exceptions.ImportException: 'No module named 'pyodbc''"

I've installed pyodbc using pip install pyodbc. The DBTest.py script runs fine when I run it with IDLE. I'm not sure if this is a limitation or if there's something I'm missing in the setup.

Thanks in advance for your help!!

Sub ExecPython(ByVal argv As List(Of String))

    Dim engine As ScriptEngine = Python.CreateEngine

    Dim scriptPath As String = "C:\scripts\DBTest.py"
    Dim source As ScriptSource = engine.CreateScriptSourceFromFile(scriptPath)

    argv.Add("")

    engine.GetSysModule.SetVariable("argv", argv)

    engine.SetSearchPaths({"C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python39\Scripts",
                          "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python39\include",
                          "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python39\Lib",
                          "C:\Program Files\IronPython 3.4\Lib"})

    Dim eIO As ScriptIO = engine.Runtime.IO

    Dim errors As New MemoryStream
    eIO.SetErrorOutput(errors, Text.Encoding.Default)

    Dim results As New MemoryStream
    eIO.SetOutput(results, Text.Encoding.Default)

    Dim scope As ScriptScope = engine.CreateScope
    source.Execute(scope)

    Console.WriteLine("ERRORS:")
    Console.WriteLine(FormatResult(errors.ToArray))
    Console.WriteLine("")
    Console.WriteLine("RESULTS:")
    Console.WriteLine(FormatResult(results.ToArray))

End Sub

Here is the python script that I am calling. It runs when I run the module from IDLE.

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=MYSERVERNAME;'
                      'Database=MYDBNAME;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor() cursor.execute('SELECT * FROM dbo.TABLENAME')

for row in cursor:
    print(row)
Michael Matlosz
  • 91
  • 1
  • 8
  • possibly related: https://stackoverflow.com/q/39595327/2144390 – Gord Thompson Apr 28 '21 at 13:53
  • I tried to use ipy, but it didn't work for IronPython 3.4. It said "Process is terminated due to StackOverflowException." – Michael Matlosz Apr 28 '21 at 14:46
  • Is the answer to my question that it can't be done that way using pyodbc? https://stackoverflow.com/questions/25663405/trying-to-import-pyodbc-library-in-ironpython-in-c-sharp – Michael Matlosz Apr 28 '21 at 18:15
  • It looks like you will probably have to choose between (1) having your VB.NET app shell out to execute a CPython script that uses pyodbc, or (2) staying within the .NET ecosystem and have IronPython use `System.Data.SqlClient` or `System.Data.Odbc`. However, for the latter you could also hit the database directly from VB.NET using those same .NET libraries so you would need to evaluate what additional value IronPython offers. – Gord Thompson Apr 28 '21 at 20:22
  • Thanks @GordThompson I'm so new to this, I'm not sure which approach would be better. Sounds like that's my next thing I need to figure out. I'd love to hear if you have an opinion, even though we're venturing a little off the original topic of my question! Thanks again. – Michael Matlosz Apr 29 '21 at 22:05

1 Answers1

0

It's been a while, probably you already found a solution.

But in general, for being able to use the 'module' you need to import clr and use the addReference function. Then you must do an import of the namespace with the python style.

Example:

import clr
clr.AddReference('System')
from System import *
Furk4n
  • 21
  • 7