Databases with Odbc

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.

import clr import System clr.AddReference("System.Data")

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.