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

# In[1]:


import sqlite3


# In[2]:


conn = sqlite3.connect("my_database.sqlite")
c = conn.cursor()


# In[3]:


c.execute("""
CREATE TABLE students (id, first_name, last_name)
""")


# In[4]:


c.execute("PRAGMA table_info(students)").fetchall()


# In[5]:


c.execute("""
INSERT INTO students (id, first_name, last_name)
VALUES (1, "Alice", "Smith")
""")


# In[6]:


c.execute("""
INSERT INTO students (id, first_name, last_name)
VALUES (2, "Bob", "Smith")
""")


# In[7]:


c.execute("""
SELECT id, first_name, last_name
FROM students
""").fetchall()


# In[8]:


c.execute("""
DELETE FROM students
WHERE first_name = "Alice"
""")


# In[9]:


c.execute("""
SELECT id, first_name, last_name
FROM students
""").fetchall()


# In[10]:


c.execute("""
INSERT INTO students (id, first_name, last_name)
VALUES (1, "Alice", "Smith")
""")


# In[11]:


c.execute("""
SELECT last_name, first_name FROM students
WHERE first_name = "Alice"
""").fetchall()


# In[14]:


conn.commit()
# сохранить данные


# In[19]:


import pandas as pd


# In[1]:


df = pd.read_csv(
    "https://github.com/Godoy/imdb-5000-movie-dataset/raw/master/data/movie_metadata.csv")


# In[21]:


df.to_sql("movies", conn)


# In[22]:


del df


# In[23]:


c.execute("""
PRAGMA table_info(movies);
""").fetchall()


# In[27]:


c.execute("""
SELECT movie_title, director_name FROM movies
WHERE country = "Russia" AND imdb_score > 5.0
""").fetchall()


# In[31]:


c.execute("""
SELECT avg(imdb_score), avg(duration) FROM movies
WHERE country = "Russia"
""").fetchone()


# In[47]:


c.execute("""
SELECT avg(imdb_score), min(imdb_score), country 
FROM movies
GROUP BY country
ORDER BY avg(imdb_score) DESC, country DESC
""").fetchall()


# In[52]:


c.execute("""
DROP TABLE grades
""")


# In[ ]:







# In[54]:


pd.DataFrame([['Alice', 'Algebra', 5],
              ['Alice', 'Geometry', 3],
              ['Bob', 'Algebra', 4]],
             columns=['student', 'subject', 'grade']
            ).to_sql("grades", conn)


# In[73]:


user = "Alice"
subject = input("Enter subject: ")
results = c.execute("""
SELECT student, subject, grade FROM grades
WHERE student = '{user}' AND subject='{subject}'
""".format(user=user, subject=subject)).fetchall()
print(results)


# In[56]:


"""
SELECT grade FROM grades
WHERE student = '{user}' AND subject='{subject}'
""".format(user='Alice', subject='Algebra')


# In[ ]:


user = "Alice"
subject = input("Enter subject: ")
results = c.execute("""
SELECT student, subject, grade FROM grades
WHERE  student = '{user}' AND subject='{subject}'
""".format(user=user, subject=subject)).fetchall()
print(results)


# In[72]:


user = "Alice"
subject = input("Enter subject: ")
results = c.execute("""
SELECT student, subject, grade FROM grades
WHERE student = ? AND subject=?
""", (user, subject)).fetchall()
print(results)


# In[15]:


conn.commit()
# сохранить данные


# In[16]:


# https://xkcd.com/327/