"Python ODBC"의 두 판 사이의 차이

DH 교육용 위키
이동: 둘러보기, 검색
17번째 줄: 17번째 줄:
 
==ODBC 응용 프로그램 예시==
 
==ODBC 응용 프로그램 예시==
  
 +
===Python Code===
 
<pre>
 
<pre>
 
#!/usr/bin/python
 
#!/usr/bin/python
42번째 줄: 43번째 줄:
 
 
 
main()
 
main()
 +
</pre>
 +
 +
===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
 
</pre>
 
</pre>

2020년 4월 23일 (목) 12:56 판

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

ODBC란?

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


Odbc.jpg

파이썬 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 </pre>