Databases with Odbc

From IronPython Cookbook

Odbc stands for 'Open DataBase Connectivity', and is a standardised way of executing queries independent of the database in use.

The following code uses a MySql connection string and executes a SQL query. It fills a DataSet with the results of the query.

from System.Data import DataSet
from System.Data.Odbc import OdbcConnection, OdbcDataAdapter

connectString = (
    "DRIVER={MySQL ODBC 3.51 Driver};"
    "SERVER=some_server;"
    "PORT=3306;"
    "DATABASE=my_database;"
    "USER=username;"
    "PASSWORD=password;"
    "OPTION=3;"
)

query = "SELECT name FROM products WHERE price > 2 ORDER BY name"


connection = OdbcConnection(connectString)
adaptor = OdbcDataAdapter(query, connection)
dataSet = DataSet()
connection.Open()
adaptor.Fill(dataSet)
connection.Close()

If you need to know the connection string format for a particular database, try ConnectionStrings.com.

You can access the column names in the dataset, like:

columnNames = [column.ColumnName for column in dataSet.Tables[0].Columns]

You can access the values in the dataset, like:

rows = []
for row in dataSet.Tables[0].Rows:
    rows.append(list(row))

If you want to execute a command that isn't a query (like a delete, insert or update), you can use code like the following:

from System.Data.Odbc import OdbcCommand, OdbcConnection

connectString = (
    "DRIVER={MySQL ODBC 3.51 Driver};"
    "SERVER=some_server;"
    "PORT=3306;"
    "DATABASE=my_database;"
    "USER=username;"
    "PASSWORD=password;"
    "OPTION=3;"
)

connection = OdbcConnection(connectString)

command = OdbcCommand("delete from tableName;", connection)
command.ExecuteNonQuery()

query = "insert into tableName (name, value) values ('Fred', '66');"
command = OdbcCommand(query, connection)
command.ExecuteNonQuery()

Back to Contents.