Python ODBC

파이썬 프로그램을 이용한 데이터베이스 접근

ODBC란?

• ODBC: Open Database Connectivity (ODBC)
• A standard application programming interface (API) for accessing database management systems (DBMS)
• 응용 프로그램과 데이터베이스 관리 시스템 사이의 인터페이스를 제공하는 프로그램

파이썬 ODBC 프로그램 설치

> pip install pyodbc

ODBC 응용 프로그램 예시

Python Code

#!/usr/bin/python

import pyodbc 

# required interface data values are
server = 'tcp:digerati.aks.ac.kr' 
database = 'common' 
username = 'guest' 
password = 'guest' 

def main():

	cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
	cursor = cnxn.cursor()

	cursor.execute("select id, category from ibaNodes order by category") #SQL 문 실행

	row = cursor.fetchone() # Select 문의 결과 세트 중에서 첫번째 데이터를 가져온다
	
	while row: #가져온 데이터가 있을 때까지 반복 수행
		print( '{0}\t{1}'.format(row[1], row[0]) ) 	#가져온 데이터를 출력한다
		row = cursor.fetchone() # 그 다음 데이터를 가져온다
		
main()

Output

C:\2020-1\workshop\odbc>python odbc01.py
Cocktail        Americano
Cocktail        AngelFace
Cocktail        Aviation
Cocktail        B-52
Cocktail        Bacardi
Cocktail        Barracuda
Cocktail        Bellini
Cocktail        BetweenTheSheets
Cocktail        BlackRussian
Cocktail        BloodyMary
Cocktail        Bramble
Cocktail        BrandyAlexander
Cocktail        Caipirinha
Cocktail        Casino
Cocktail        ChampagneCocktail
Cocktail        CloverClub
Cocktail        Cosmopolitan
Cocktail        CubaLibre
Cocktail        Daiquiri
Cocktail        DarkNStormy
Cocktail        Derby
Cocktail        DirtyMartini
Cocktail        French75
Cocktail        FrenchConnection
Cocktail        FrenchMartini
Cocktail        EspressoMartini
Cocktail        GodFather
Cocktail        GodMother
Cocktail        GoldenDream
Cocktail        GinFizz
Cocktail        Grasshopper
Cocktail        HarveyWallbanger
Cocktail        HemingwaySpecial
Cocktail        HorsesNeck
Cocktail        IrishCoffee
Cocktail        Kamikaze
Cocktail        Kir
Cocktail        LemonDropMartini
Cocktail        JohnCollins
Cocktail        LongIslandIcedTea
Cocktail        Mai-Tai
Cocktail        Manhattan
Cocktail        Margarita
Cocktail        MaryPickford
Cocktail        Mojito
Cocktail        MonkeyGland
Cocktail        MoscowMule
Cocktail        Negroni
Cocktail        Mimosa
Cocktail        MintJulep
Cocktail        OldFashioned
Cocktail        Paradise
Cocktail        PinaColada
Cocktail        PiscoSour
Cocktail        PlantersPunch
Cocktail        PortoFlip
Cocktail        RamosGinFizz
Cocktail        RussianSpringPunch
Cocktail        RustyNail
Cocktail        Screwdriver
Cocktail        SeaBreeze
Cocktail        SexOnTheBeach
Cocktail        Sazerac
Cocktail        Sidecar
Cocktail        SingaporeSling
Cocktail        SpritzVeneziano
Cocktail        Stinger
Cocktail        TequilaSunrise
Cocktail        TommysMargarita
Cocktail        Tuxedo
Cocktail        Vampiro
Cocktail        Vesper
Cocktail        WhiskeySour
Cocktail        WhiteLady
Cocktail        YellowBird
Cocktail        DryMartini
Garnish LemonSliceBlackberry
Garnish SaltRimming
Garnish PineappleWedgeCherry
Garnish OrangeSlice
Garnish OrangeSliceCherry
Garnish OrangeWedge
Garnish MintLeaf
Garnish Olive
Garnish NoGarnish
Garnish NutmegPowder
Garnish LimeSlice
Garnish LimeWedge
Garnish LemonPeel
Garnish LemonSlice
Garnish LemonSliceCherry
Garnish LemonWedge
Garnish Cherry
Garnish Blackberry
Glass   ChampagneFlute
Glass   CocktailGlass
Glass   CollinsGlass
Glass   IrishCoffeeGlass
Glass   HighballGlass
Glass   MargaritaGlass
Glass   OldFashionedGlass
Glass   PilsnerGlass
Glass   SherryGlass
Glass   ChampagneCoupe
Glass   WhiteWineGlass
Liqueur Vermouth
Liqueur TripleSec
Liqueur SweetVermouth
Liqueur PeychaudsBitters
Liqueur PeachSchnapps
Liqueur PeachBitters
Liqueur OrangeBitters
Liqueur GrandMarnier
Liqueur LilletBlanc
Liqueur Kahlua
Liqueur Maraschino
Liqueur Campari
Liqueur CremeDeCacao
Liqueur CremeDeCacaoWhite
Liqueur CremeDeCassis
Liqueur CremeDeMenthe
Liqueur CremeDeMentheWhite
Liqueur CherryBrandy
Liqueur CoffeeLiqueur
Liqueur Cointreau
Liqueur Galliano
Liqueur Drambuie
Liqueur BlackberryLiqueur
Liqueur Absinthe
Liqueur Amaretto
Liqueur Baileys
Liqueur Angostura
Liqueur Aperol
Liqueur ApricotBrandy
Method  Build
Method  Blend
Method  Float
Method  Stir
Method  Shake
SoftDrink       RaspberrySyrup
SoftDrink       SnSMix
SoftDrink       SodaWater
SoftDrink       SimpleSyrup
SoftDrink       Sugar
SoftDrink       TabascoSauce
SoftDrink       TomatoJuice
SoftDrink       VanillaExtract
SoftDrink       Water
SoftDrink       WorcestershireSauce
SoftDrink       FreshCream
SoftDrink       FreshLemonJuice
SoftDrink       FreshLimeJuice
SoftDrink       EggWhite
SoftDrink       EggYolk
SoftDrink       Espresso
SoftDrink       CubedSugar
SoftDrink       Cola
SoftDrink       ChoppedOnionSlice
SoftDrink       CoconutCream
SoftDrink       CrushedLimeWedge
SoftDrink       CrushedMintLeaves
SoftDrink       CranberryJuice
SoftDrink       BrownSugar
SoftDrink       CelerySalt
SoftDrink       AgaveNectar
SoftDrink       LimeJuice
SoftDrink       LemonJuice
SoftDrink       GrapefruitJuice
SoftDrink       GrenadineSyrup
SoftDrink       GingerAle
SoftDrink       GingerBeer
SoftDrink       Honey
SoftDrink       HotChiliPepperSlice
SoftDrink       HotCoffee
SoftDrink       OrangeFlowerWater
SoftDrink       OrangeJuice
SoftDrink       OliveJuice
SoftDrink       Milk
SoftDrink       PeachPuree
SoftDrink       Pepper
SoftDrink       PineappleJuice
Spirit  Pisco
Spirit  IrishWhiskey
Spirit  BacardiCartaBlanca
Spirit  Cachaca
Spirit  Calvados
Spirit  Brandy
Spirit  BourbonWhiskey
Spirit  Cognac
Spirit  DarkRum
Spirit  Gin
Spirit  WhiteRum
Spirit  OldTomGin
Spirit  GoldRum
Spirit  Vodka
Spirit  Tequila
Spirit  ScotchWhiskey
Spirit  RyeWhiskey
Wine    WhiteWine
Wine    Champagne
Wine    PortWine
Wine    Prosecco