Database Access (Python Database API Specification v2.0)

Hello All,
Start out with quite new and just completing Certification's in class now.. However, as i keep reading between (Python,Jython, Java) it seems that i can (but might not understand the relationships) connect python to odbc (pyodbc) or ado (pyadodb) in a traditional manner. i understand there are "named queries" and Sql connectors, but as i am through all of the videos and in class i see some opportunities for scripting i would just like to do my self.
i have downloaded and installed Python, PiP, Jython and followed what i could to copy my .py files from [Python install directory\ site-packages] to [Iginition install directory\site-packages]. Opened the script console and "Import pyodbc" as my first example. Error: no module named pyodbc and the same for "import pymssql" Error: no module named pymssql. So i think i am not fully understanding the relationship between the Jython wrapping and python?? maybe~~
Very simple script i would like to run
import pyodbc
driver="{Sql Server Native Client 11.0}"
cnxn = pyodbc.connect('DRIVER=driver;SERVER=server;DATABASE=database;UID=uid;PWD=pwd')
cursor = cnxn.cursor()
cursor.execute("SELECT @@version")
for row in cursor.fetchall():
print('row=%r' %(row,))

Any help or funneling of thought is greatly appreciated

Pymssql is written in Cython, So I'm going to guess there are a lot of C dependencies that are unusable in Jython. Same for ODBC drivers in general, as they are also usually written in C, ?????

JDBCs are written in Java. Not the same thing as ODBC, though the perform the same purpose.

apologies. i guess what i mean to ask is could i travel down that Java db connection route and get to what i am looking for?

This is what you have to do in other inferior SCADA products, I'm not sure why you would want to have to manage your own db connections if it's all nicely done for you? Unless for a learning experience?

predominately learning Yes. I enjoy encapsulating ideas into processes and making code.

You can make JDBC connections from Jython code, but you'll need to provide the appropriate JDBC driver(s) yourself; Ignition's DB connections are deliberately made in an isolated classloader so that different drivers don't stomp on each other's classpaths. I wouldn't call this is a fruitful learning avenue, though. If you want to learn about DB connections on the JVM, it's going to be much easier to do it in plain Java in a module. If you want to make business code in Python, it's going to be much easier to let Ignition do the work of connection to your DB.

1 Like

Concur. The CPython techniques and libraries you might try to learn from will likely fail to run in Jython in Ignition, for a variety of reasons. Kiss your ORMs goodbye.

Learn to use Ignition's platform tools first. Then, after you've mastered the platform, you can dig under the hood. Most important is to learn standard SQL.

Ok maybe some example code of where i have been living will lend some direction on how i can migrate?
ADO and SQL Stroed procedures.txt (7.9 KB)

Thanks for the information btw. grey matter filling is always the best.


my favorite book... :slight_smile: