import pandas.io.sql as sql
import pandas as pd
import pyodbc

class DatabaseConnection():

    def __init__(self, server, database, username, password, driver):
        self._server = server
        self._database = database
        self._username = username
        self._password = password
        self._driver = driver
        self._cursor, self._cnxn = self.connect()

    @property
    def server(self):
        return self._server

    @property
    def database(self):
        return self._database

    @property
    def username(self):
        return self._username

    @property
    def password(self):
        return self._password

    @property
    def driver(self):
        return self._driver

    @property
    def cursor(self):
        return self._cursor

    @property
    def cnxn(self):
        return self._cnxn

    def connect(self):
        cnxn = pyodbc.connect('DRIVER={0};SERVER={1};PORT=1443;DATABASE={2};UID={3};PWD={4}'.format(self.driver, self.server, self.database, self.username, self.password))
        return cnxn.cursor(), cnxn

    @staticmethod
    def create_data_frame(table):
        df = pd.read_sql_query("SELECT * FROM NeuedaTransportData.dbo.{0}".format(table), letsConnect.cnxn)
        print df.loc
    @staticmethod
    def display_all_tables():
        pd.read_sql_query("SELECT * FROM NeuedaTransportData.dbo.tables", letsConnect.cnxn)

if __name__ == "__main__":
        letsConnect = DatabaseConnection("neuedadata.database.windows.net",
                                         "NeuedaTransportData", "neuadmin", "Weavneueda123", pyodbc.drivers()[0])
        letsConnect.create_data_frame("Collision")
        letsConnect.display_all_tables()