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

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

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 </pre>