#!/usr/bin/env python
# coding: utf-8

# In[ ]:


################################################################
##  Introduction to Database #1 - Basics of RDBMS
##  Atul Singh
##  www.datagenx.net
################################################################


# In[8]:


# import
from sqlalchemy import create_engine, Table, MetaData


# ### Connecting SQLite

# In[5]:


# creating engine
engine = create_engine("sqlite:///chinook.db")

# reading table names from database
print(engine.table_names())


# In[10]:


### Importing metadata with Table and MetaData object
metadata = MetaData()

# getting albums metadata
albums = Table('albums', metadata, autoload=True, autoload_with=engine)


# In[11]:


print(repr(albums))


# In[12]:


# we can get the same metadata details from metadata.tables dictionaty
print(repr(metadata.tables['albums']))


# In[18]:


## Let's query on table albums
stmt = 'select * from albums'
connection = engine.connect()


# In[22]:


#results = connection.execute(stmt).fetchall()
result_proxy = connection.execute(stmt)
print(result_proxy)


# In[23]:


results = result_proxy.fetchall()
print(results)


# In[26]:


# get all the column names
print(result_proxy.keys())


# In[33]:


# get total no of rows in tables
print(results.size())


# In[34]:


# get first row
result1 = results[0]
print(result1)


# In[35]:


# print column names
print(result1.keys())


# In[36]:


# access particulat column data
print(result1.Title)


# ### Creating SQL statement

# In[40]:


# importing select
from sqlalchemy import select


# In[38]:


metadata = MetaData()
customers = Table('customers', metadata, autoload=True, autoload_with=engine)


# In[41]:


stmt = select([customers])


# In[42]:


results= connection.execute(stmt).fetchall()


# In[43]:


print(results)


# In[44]:


# fetching 2nd row
sec_row = results[1]
print(sec_row)


# In[45]:


# fetching the column names
print(sec_row.keys())


# In[47]:


# fetching column by index and name
print(sec_row[1])
print(sec_row['LastName'])
print(sec_row.City)


# In[ ]:





# In[ ]:





# In[ ]:


############################################################
## Atul Singh  | www.datagenx.net | lnked.in/atulsingh
############################################################