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

DH 교육용 위키
이동: 둘러보기, 검색
(새 문서: =파이썬 프로그램을 이용한 데이터베이스 접근= ==ODBC란?== * ODBC: Open Database Connectivity (ODBC) * A standard application programming interface (API) for acces...)
 
 
(같은 사용자에 의한 3개의 중간 편집이 숨겨짐)
8번째 줄: 8번째 줄:
  
  
 
+
[[파일: Odbc.jpg| 600px]]
  
 
==파이썬 ODBC 프로그램 설치==
 
==파이썬 ODBC 프로그램 설치==
 
+
<pre>
 
> pip install pyodbc
 
> pip install pyodbc
 +
</pre>
  
 
==ODBC 응용 프로그램 예시==
 
==ODBC 응용 프로그램 예시==
  
 +
===Python Code===
 
<pre>
 
<pre>
 
#!/usr/bin/python
 
#!/usr/bin/python
41번째 줄: 43번째 줄:
 
 
 
main()
 
main()
 +
</pre>
 +
 +
===Output===
 +
<pre>
 +
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