Tuesday, March 7, 2017

Python MySQL Database Connection using ODBC

I'm posting a sample python to Sql Server connection code. Here i will be using Python 2.7.

1. pip install pyodbc

2. Install the MySQL ODBC Connector from here.

3.  Setup ODBC connection details in Control Panel --> Admin Tools --> System DSN




4. Code :

from os import getenv
import pyodbc

conn = pyodbc.connect(r'Driver={MySQL ODBC 5.3 ANSI Driver};Server=127.0.0.1;Database=test;Trusted_Connection=yes;')

cur = conn.cursor()
string = "CREATE TABLE IF NOT EXISTS TestTable(empname varchar(15), sal double)"
cur.execute(string)

sqlStatement = "INSERT INTO TestTable(empname,sal)  VALUES (?, ?)"

cur.execute(sqlStatement, ('chandan', '1000'))

conn.commit()

4.Run python code.py in CMD



Monday, March 6, 2017

Python MSSQL Database Connection using ODBC

I'm posting a sample python to Sql Server connection code. Here i will be using Python 2.7.

1. pip install pyodbc

2. Code :

from os import getenv
import pyodbc


conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 11 for SQL Server};'
    r'SERVER=TEST;'
    r'DATABASE=SampleDB;'
    r'UID=sa;'
    r'PWD=***'
    )

cursor = conn.cursor()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
""")
cursor.executemany(
    "INSERT INTO persons VALUES (?, ?, ?)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike h', 'Sarah H')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

cursor.execute('SELECT * FROM persons WHERE salesrep=?', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()
k
conn.close()

3.Run python code.py in CMD