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)
""")
Out[3]:
<sqlite3.Cursor at 0x10a309180>
In [4]:
c.execute("PRAGMA table_info(students)").fetchall()
Out[4]:
[(0, 'id', '', 0, None, 0),
 (1, 'first_name', '', 0, None, 0),
 (2, 'last_name', '', 0, None, 0)]
In [5]:
c.execute("""
INSERT INTO students (id, first_name, last_name)
VALUES (1, "Alice", "Smith")
""")
Out[5]:
<sqlite3.Cursor at 0x10a309180>
In [6]:
c.execute("""
INSERT INTO students (id, first_name, last_name)
VALUES (2, "Bob", "Smith")
""")
Out[6]:
<sqlite3.Cursor at 0x10a309180>
In [7]:
c.execute("""
SELECT id, first_name, last_name
FROM students
""").fetchall()
Out[7]:
[(1, 'Alice', 'Smith'), (2, 'Bob', 'Smith')]
In [8]:
c.execute("""
DELETE FROM students
WHERE first_name = "Alice"
""")
Out[8]:
<sqlite3.Cursor at 0x10a309180>
In [9]:
c.execute("""
SELECT id, first_name, last_name
FROM students
""").fetchall()
Out[9]:
[(2, 'Bob', 'Smith')]
In [10]:
c.execute("""
INSERT INTO students (id, first_name, last_name)
VALUES (1, "Alice", "Smith")
""")
Out[10]:
<sqlite3.Cursor at 0x10a309180>
In [11]:
c.execute("""
SELECT last_name, first_name FROM students
WHERE first_name = "Alice"
""").fetchall()
Out[11]:
[('Smith', 'Alice')]
In [14]:
conn.commit()
# сохранить данные
In [19]:
import pandas as pd
In [20]:
df = pd.read_csv(
    "http://math-info.hse.ru/f/2018-19/spb-python/movie_metadata.csv")
In [21]:
df.to_sql("movies", conn)
In [22]:
del df
In [23]:
c.execute("""
PRAGMA table_info(movies);
""").fetchall()
Out[23]:
[(0, 'index', 'INTEGER', 0, None, 0),
 (1, 'color', 'TEXT', 0, None, 0),
 (2, 'director_name', 'TEXT', 0, None, 0),
 (3, 'num_critic_for_reviews', 'REAL', 0, None, 0),
 (4, 'duration', 'REAL', 0, None, 0),
 (5, 'director_facebook_likes', 'REAL', 0, None, 0),
 (6, 'actor_3_facebook_likes', 'REAL', 0, None, 0),
 (7, 'actor_2_name', 'TEXT', 0, None, 0),
 (8, 'actor_1_facebook_likes', 'REAL', 0, None, 0),
 (9, 'gross', 'REAL', 0, None, 0),
 (10, 'genres', 'TEXT', 0, None, 0),
 (11, 'actor_1_name', 'TEXT', 0, None, 0),
 (12, 'movie_title', 'TEXT', 0, None, 0),
 (13, 'num_voted_users', 'INTEGER', 0, None, 0),
 (14, 'cast_total_facebook_likes', 'INTEGER', 0, None, 0),
 (15, 'actor_3_name', 'TEXT', 0, None, 0),
 (16, 'facenumber_in_poster', 'REAL', 0, None, 0),
 (17, 'plot_keywords', 'TEXT', 0, None, 0),
 (18, 'movie_imdb_link', 'TEXT', 0, None, 0),
 (19, 'num_user_for_reviews', 'REAL', 0, None, 0),
 (20, 'language', 'TEXT', 0, None, 0),
 (21, 'country', 'TEXT', 0, None, 0),
 (22, 'content_rating', 'TEXT', 0, None, 0),
 (23, 'budget', 'REAL', 0, None, 0),
 (24, 'title_year', 'REAL', 0, None, 0),
 (25, 'actor_2_facebook_likes', 'REAL', 0, None, 0),
 (26, 'imdb_score', 'REAL', 0, None, 0),
 (27, 'aspect_ratio', 'REAL', 0, None, 0),
 (28, 'movie_facebook_likes', 'INTEGER', 0, None, 0)]
In [27]:
c.execute("""
SELECT movie_title, director_name FROM movies
WHERE country = "Russia" AND imdb_score > 5.0
""").fetchall()
Out[27]:
[('Obitaemyy ostrov\xa0', 'Fedor Bondarchuk'),
 ('Viy\xa0', 'Oleg Stepchenko'),
 ('Space Dogs\xa0', 'Inna Evlannikova'),
 ('Machete Kills\xa0', 'Robert Rodriguez'),
 ('Mongol: The Rise of Genghis Khan\xa0', 'Sergey Bodrov'),
 ('The Return\xa0', 'Andrey Zvyagintsev'),
 ('Hard to Be a God\xa0', 'Aleksey German'),
 ('Snow Queen\xa0', 'Vladlen Barbe'),
 ('Night Watch\xa0', 'Timur Bekmambetov'),
 ('The Geographer Drank His Globe Away\xa0', 'Aleksandr Veledinskiy')]
In [31]:
c.execute("""
SELECT avg(imdb_score), avg(duration) FROM movies
WHERE country = "Russia"
""").fetchone()
Out[31]:
(6.081818181818182, 109.54545454545455)
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()
Out[47]:
[(8.7, 8.7, 'Kyrgyzstan'),
 (8.4, 8.4, 'Libya'),
 (8.2, 8.2, 'United Arab Emirates'),
 (8.1, 8.1, 'Soviet Union'),
 (8.1, 8.1, 'Egypt'),
 (7.725, 6.5, 'Iran'),
 (7.62, 5.1, 'Poland'),
 (7.6, 7.6, 'Indonesia'),
 (7.5249999999999995, 7.2, 'Israel'),
 (7.516666666666666, 6.6, 'Sweden'),
 (7.5, 7.5, 'Colombia'),
 (7.5, 7.5, 'Cameroon'),
 (7.5, 6.7, 'Argentina'),
 (7.4, 7.4, 'Kenya'),
 (7.4, 7.4, 'Afghanistan'),
 (7.333333333333333, 6.9, 'Iceland'),
 (7.279999999999999, 6.3, 'New Zealand'),
 (7.2749999999999995, 5.5, 'Brazil'),
 (7.266666666666667, 6.0, 'West Germany'),
 (7.2, 7.2, 'Panama'),
 (7.2, 7.2, 'Finland'),
 (7.172727272727272, 5.7, 'Denmark'),
 (7.15, 6.4, 'Taiwan'),
 (7.0, 7.0, 'Pakistan'),
 (7.0, 6.7, 'Greece'),
 (6.966666666666666, 6.4, 'Czech Republic'),
 (6.952173913043477, 3.8, 'Japan'),
 (6.94, 5.0, 'Netherlands'),
 (6.9, 6.9, 'Dominican Republic'),
 (6.9, 6.9, 'Chile'),
 (6.873913043478259, 2.6, 'Italy'),
 (6.8242424242424224, 4.8, 'Spain'),
 (6.8183035714285705, 3.0, 'UK'),
 (6.8, 4.8, None),
 (6.783333333333334, 5.7, 'Ireland'),
 (6.776470588235294, 4.4, 'Mexico'),
 (6.741176470588235, 5.2, 'Hong Kong'),
 (6.7375, 4.9, 'Norway'),
 (6.678571428571429, 2.0, 'France'),
 (6.623333333333332, 3.2, 'China'),
 (6.6, 4.9, 'Romania'),
 (6.532352941176472, 2.8, 'India'),
 (6.5145454545454555, 4.8, 'Australia'),
 (6.449999999999999, 5.8, 'Hungary'),
 (6.4375, 4.7, 'South Africa'),
 (6.4, 6.4, 'Slovenia'),
 (6.367428421329131, 1.6, 'USA'),
 (6.340206185567008, 1.9, 'Germany'),
 (6.3, 6.3, 'Philippines'),
 (6.3, 6.3, 'Official site'),
 (6.257142857142858, 2.7, 'South Korea'),
 (6.161904761904762, 2.3, 'Canada'),
 (6.1, 6.1, 'Bulgaria'),
 (6.081818181818182, 4.1, 'Russia'),
 (6.08, 4.8, 'Thailand'),
 (6.0, 6.0, 'Turkey'),
 (6.0, 6.0, 'Slovakia'),
 (5.8999999999999995, 5.7, 'Switzerland'),
 (5.6, 5.6, 'Nigeria'),
 (5.6, 5.6, 'Georgia'),
 (5.6, 5.6, 'Cambodia'),
 (5.6, 4.5, 'Belgium'),
 (5.4, 5.4, 'Peru'),
 (4.8, 4.8, 'Aruba'),
 (4.4, 4.4, 'New Line'),
 (4.4, 4.4, 'Bahamas')]
In [52]:
c.execute("""
DROP TABLE grades
""")
Out[52]:
<sqlite3.Cursor at 0x2475726cab0>
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)
Enter subject: Geometry'; DROP TABLE grades; SELECT * FROM grades WHERE '1'='1
---------------------------------------------------------------------------
Warning                                   Traceback (most recent call last)
<ipython-input-73-e902f26cca79> in <module>()
      4 SELECT student, subject, grade FROM grades
      5 WHERE student = '{user}' AND subject='{subject}'
----> 6 """.format(user=user, subject=subject)).fetchall()
      7 print(results)

Warning: You can only execute one statement at a time.
In [56]:
"""
SELECT grade FROM grades
WHERE student = '{user}' AND subject='{subject}'
""".format(user='Alice', subject='Algebra')
Out[56]:
'\nSELECT grade FROM grades\nWHERE student = Alice AND subject=Algebra\n'
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)
Enter subject: Algebra
[('Alice', 'Algebra', 5)]
In [15]:
conn.commit()
# сохранить данные
In [16]:
# https://xkcd.com/327/