notebook.community

Edit and run

Database

RDMS ( Relational Database Management Systems )

Opensource

  • MySQL
  • PostgreSQL
  • SQLITE

Proprietary

  • MSSQL
  • Oracle

Sqlite3 Python





In [1]:



    


import sqlite3 # psycopg2 # pymysql



    











In [2]:



    


conn = sqlite3.connect('example.sqlite3')



    











In [3]:



    


cur = conn.cursor()



    











In [4]:



    


cur.execute('CREATE TABLE countries(id integer, name text, iso3 text)')



    


















    
Out[4]:








<sqlite3.Cursor at 0x7f9764860180>

















In [5]:



    


cur.execute('SELECT * FROM countries')



    


















    
Out[5]:








<sqlite3.Cursor at 0x7f9764860180>

















In [6]:



    


cur.fetchall()



    


















    
Out[6]:








[]

















In [8]:



    


cur.execute('INSERT INTO countries(id, name, iso3) VALUES(1, "Nepal", "NEP")')



    


















    
Out[8]:








<sqlite3.Cursor at 0x7f9764860180>

















In [9]:



    


cur.execute('SELECT * FROM countries')



    


















    
Out[9]:








<sqlite3.Cursor at 0x7f9764860180>

















In [10]:



    


cur.fetchall()



    


















    
Out[10]:








[(1, 'Nepal', 'NEP')]

















In [15]:



    


sql = '''INSERT INTO countries(id, name, iso3) VALUES(?, ?, ?)'''
cur.executemany(sql, [(2, 'India', 'INA'), 
                      (3, 'Bhutan', 'BHU'), 
                      (4, 'Afganistan', 'AFG')])



    


















    
Out[15]:








<sqlite3.Cursor at 0x7f9764860180>

















In [16]:



    


cur.execute('SELECT * FROM countries')



    


















    
Out[16]:








<sqlite3.Cursor at 0x7f9764860180>

















In [17]:



    


cur.fetchall()



    


















    
Out[17]:








[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afganistan', 'AFG')]

















In [18]:



    


sql = 'INSERT INTO countries(id, name, iso3) VALUES(4, "Pakistan", "PAK")'
cur.execute(sql)



    


















    
Out[18]:








<sqlite3.Cursor at 0x7f9764860180>

















In [20]:



    


cur.execute('SELECT * FROM countries')
cur.fetchall()



    


















    
Out[20]:








[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afganistan', 'AFG'),
 (4, 'Pakistan', 'PAK')]

















In [21]:



    


sql = 'UPDATE countries SET id=5 WHERE iso3="PAK"'
cur.execute(sql)



    


















    
Out[21]:








<sqlite3.Cursor at 0x7f9764860180>

















In [22]:



    


cur.execute('SELECT * FROM countries')
cur.fetchall()



    


















    
Out[22]:








[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afganistan', 'AFG'),
 (5, 'Pakistan', 'PAK')]

















In [23]:



    


sql = 'UPDATE countries SET id=5'
cur.execute(sql)



    


















    
Out[23]:








<sqlite3.Cursor at 0x7f9764860180>

















In [24]:



    


cur.execute('SELECT * FROM countries')
cur.fetchall()



    


















    
Out[24]:








[(5, 'Nepal', 'NEP'),
 (5, 'India', 'INA'),
 (5, 'Bhutan', 'BHU'),
 (5, 'Afganistan', 'AFG'),
 (5, 'Pakistan', 'PAK')]

















In [26]:



    


conn.commit()



    











In [27]:



    


cur.execute('SELECT * FROM countries')
cur.fetchall()



    


















    
Out[27]:








[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afganistan', 'AFG'),
 (5, 'Pakistan', 'PAK')]

















In [28]:



    


cur.execute('SELECT * FROM countries WHERE id=4')
cur.fetchall()



    


















    
Out[28]:








[(4, 'Afganistan', 'AFG')]

















In [30]:



    


cur.execute('SELECT * FROM countries WHERE id>3')
cur.fetchall()



    


















    
Out[30]:








[(4, 'Afganistan', 'AFG'), (5, 'Pakistan', 'PAK')]

















In [31]:



    


cur.execute('SELECT * FROM countries WHERE name LIKE "%an"')
cur.fetchall()



    


















    
Out[31]:








[(3, 'Bhutan', 'BHU'), (4, 'Afganistan', 'AFG'), (5, 'Pakistan', 'PAK')]

















In [32]:



    


cur.execute('SELECT * FROM countries WHERE name LIKE "%an%"')
cur.fetchall()



    


















    
Out[32]:








[(3, 'Bhutan', 'BHU'), (4, 'Afganistan', 'AFG'), (5, 'Pakistan', 'PAK')]

















In [33]:



    


cur.execute('SELECT * FROM countries WHERE name LIKE "an%"')
cur.fetchall()



    


















    
Out[33]:








[]

















In [35]:



    


cur.execute('DELETE FROM countries')



    


















    
Out[35]:








<sqlite3.Cursor at 0x7f9764860180>

















In [36]:



    


cur.execute('SELECT * FROM countries')
cur.fetchall()



    


















    
Out[36]:








[]

















In [37]:



    


conn.commit()



    











In [34]:



    


import csv



    











In [43]:



    


sql = 'INSERT INTO countries(id, name, iso3) VALUES(?, ?, ?)'
_id = 1
with open('untitled.txt', 'r') as datafile:
    csvfile = csv.DictReader(datafile)
    for row in csvfile:
        if row['euname'] and row['iso3']:
            cur.execute(sql, (_id, row['euname'], row['iso3']))
            _id += 1
conn.commit()



    











In [44]:



    


cur.execute('SELECT * FROM countries')
cur.fetchall()



    


















    
Out[44]:








[(1, 'AFGHANISTAN', 'AFG'),
 (2, 'Albania', 'ALB'),
 (3, 'ALGERIA', 'DZA'),
 (4, 'ANDORRA', 'AND'),
 (5, 'ANGOLA', 'AGO'),
 (6, 'ANTIGUA AND BARBUDA', 'ATG'),
 (7, 'Argentina', 'ARG'),
 (8, 'Armenia', 'ARM'),
 (9, 'ARUBA', 'ABW'),
 (10, 'Australia', 'AUS'),
 (11, 'Austria', 'AUT'),
 (12, 'AZERBAIJAN', 'AZE'),
 (13, 'BAHAMAS', 'BHS'),
 (14, 'BAHRAIN', 'BHR'),
 (15, 'BANGLADESH', 'BGD'),
 (16, 'Barbados', 'BRB'),
 (17, 'BELARUS', 'BLR'),
 (18, 'Belgium', 'BEL'),
 (19, 'BELIZE', 'BLZ'),
 (20, 'Benin', 'BEN'),
 (21, 'BHUTAN', 'BTN'),
 (22, 'BOLIVIA', 'BOL'),
 (23, 'Bosnia-Herzegovina', 'BIH'),
 (24, 'BOTSWANA', 'BWA'),
 (25, 'Brazil', 'BRA'),
 (26, 'British Virgin Islands', 'VGB'),
 (27, 'BRUNEI', 'BRN'),
 (28, 'Bulgaria', 'BGR'),
 (29, 'BURKINA FASO', 'BFA'),
 (30, 'BURUNDI', 'BDI'),
 (31, 'Cambodia', 'KHM'),
 (32, 'Cameroon', 'CMR'),
 (33, 'Canada', 'CAN'),
 (34, 'CAPE VERDE', 'CPV'),
 (35, 'CENTRAL AFRICAN, REPUBLIC', 'CAF'),
 (36, 'Chad', 'TCD'),
 (37, 'CHILE', 'CHL'),
 (38, 'CHINA', 'CHN'),
 (39, 'Colombia', 'COL'),
 (40, 'COMOROS', 'COM'),
 (41, 'CONGO', 'COG'),
 (42, 'CONGO, DEMOCRATIC REPUBLIC OF', 'ZAR'),
 (43, 'COOK ISLANDS', 'COK'),
 (44, 'Costa Rica', 'CRI'),
 (45, 'Croatia', 'HRV'),
 (46, 'CUBA', 'CUB'),
 (1, 'AFGHANISTAN', 'AFG'),
 (2, 'Albania', 'ALB'),
 (3, 'ALGERIA', 'DZA'),
 (4, 'ANDORRA', 'AND'),
 (5, 'ANGOLA', 'AGO'),
 (6, 'ANTIGUA AND BARBUDA', 'ATG'),
 (7, 'Argentina', 'ARG'),
 (8, 'Armenia', 'ARM'),
 (9, 'ARUBA', 'ABW'),
 (10, 'Australia', 'AUS'),
 (11, 'Austria', 'AUT'),
 (12, 'AZERBAIJAN', 'AZE'),
 (13, 'BAHAMAS', 'BHS'),
 (14, 'BAHRAIN', 'BHR'),
 (15, 'BANGLADESH', 'BGD'),
 (16, 'Barbados', 'BRB'),
 (17, 'BELARUS', 'BLR'),
 (18, 'Belgium', 'BEL'),
 (19, 'BELIZE', 'BLZ'),
 (20, 'Benin', 'BEN'),
 (21, 'BHUTAN', 'BTN'),
 (22, 'BOLIVIA', 'BOL'),
 (23, 'Bosnia-Herzegovina', 'BIH'),
 (24, 'BOTSWANA', 'BWA'),
 (25, 'Brazil', 'BRA'),
 (26, 'British Virgin Islands', 'VGB'),
 (27, 'BRUNEI', 'BRN'),
 (28, 'Bulgaria', 'BGR'),
 (29, 'BURKINA FASO', 'BFA'),
 (30, 'BURUNDI', 'BDI'),
 (31, 'Cambodia', 'KHM'),
 (32, 'Cameroon', 'CMR'),
 (33, 'Canada', 'CAN'),
 (34, 'CAPE VERDE', 'CPV'),
 (35, 'CENTRAL AFRICAN, REPUBLIC', 'CAF'),
 (36, 'Chad', 'TCD'),
 (37, 'CHILE', 'CHL'),
 (38, 'CHINA', 'CHN'),
 (39, 'Colombia', 'COL'),
 (40, 'COMOROS', 'COM'),
 (41, 'CONGO', 'COG'),
 (42, 'CONGO, DEMOCRATIC REPUBLIC OF', 'ZAR'),
 (43, 'COOK ISLANDS', 'COK'),
 (44, 'Costa Rica', 'CRI'),
 (45, 'Croatia', 'HRV'),
 (46, 'CUBA', 'CUB')]

















In [42]:



    


sql = '''CREATE TABLE 
country_list(id integer primary key autoincrement,
country_name text not null,
iso3 text not null unique)'''
cur.execute(sql)



    


















    
Out[42]:








<sqlite3.Cursor at 0x7f9764860180>

















In [47]:



    


sql = 'INSERT INTO country_list(country_name, iso3) VALUES(?, ?)'
with open('untitled.txt', 'r') as datafile:
    csvfile = csv.DictReader(datafile)
    for row in csvfile:
        if row['euname'] and row['iso3']:
            cur.execute(sql, (row['euname'], row['iso3']))
conn.commit()



    











In [49]:



    


cur.execute('SELECT * FROM country_list')
cur.fetchall()



    


















    
Out[49]:








[(1, 'AFGHANISTAN', 'AFG'),
 (2, 'Albania', 'ALB'),
 (3, 'ALGERIA', 'DZA'),
 (4, 'ANDORRA', 'AND'),
 (5, 'ANGOLA', 'AGO'),
 (6, 'ANTIGUA AND BARBUDA', 'ATG'),
 (7, 'Argentina', 'ARG'),
 (8, 'Armenia', 'ARM'),
 (9, 'ARUBA', 'ABW'),
 (10, 'Australia', 'AUS'),
 (11, 'Austria', 'AUT'),
 (12, 'AZERBAIJAN', 'AZE'),
 (13, 'BAHAMAS', 'BHS'),
 (14, 'BAHRAIN', 'BHR'),
 (15, 'BANGLADESH', 'BGD'),
 (16, 'Barbados', 'BRB'),
 (17, 'BELARUS', 'BLR'),
 (18, 'Belgium', 'BEL'),
 (19, 'BELIZE', 'BLZ'),
 (20, 'Benin', 'BEN'),
 (21, 'BHUTAN', 'BTN'),
 (22, 'BOLIVIA', 'BOL'),
 (23, 'Bosnia-Herzegovina', 'BIH'),
 (24, 'BOTSWANA', 'BWA'),
 (25, 'Brazil', 'BRA'),
 (26, 'British Virgin Islands', 'VGB'),
 (27, 'BRUNEI', 'BRN'),
 (28, 'Bulgaria', 'BGR'),
 (29, 'BURKINA FASO', 'BFA'),
 (30, 'BURUNDI', 'BDI'),
 (31, 'Cambodia', 'KHM'),
 (32, 'Cameroon', 'CMR'),
 (33, 'Canada', 'CAN'),
 (34, 'CAPE VERDE', 'CPV'),
 (35, 'CENTRAL AFRICAN, REPUBLIC', 'CAF'),
 (36, 'Chad', 'TCD'),
 (37, 'CHILE', 'CHL'),
 (38, 'CHINA', 'CHN'),
 (39, 'Colombia', 'COL'),
 (40, 'COMOROS', 'COM'),
 (41, 'CONGO', 'COG'),
 (42, 'CONGO, DEMOCRATIC REPUBLIC OF', 'ZAR'),
 (43, 'COOK ISLANDS', 'COK'),
 (44, 'Costa Rica', 'CRI'),
 (45, 'Croatia', 'HRV'),
 (46, 'CUBA', 'CUB')]

















In [53]:



    


cur.execute('''INSERT INTO country_list(id, country_name, iso3)
VALUES(47, 'Cuba', 'CCB')''')



    


















    
Out[53]:








<sqlite3.Cursor at 0x7f9764860180>

















In [54]:



    


cur.execute('SELECT * FROM country_list')
cur.fetchall()



    


















    
Out[54]:








[(1, 'AFGHANISTAN', 'AFG'),
 (2, 'Albania', 'ALB'),
 (3, 'ALGERIA', 'DZA'),
 (4, 'ANDORRA', 'AND'),
 (5, 'ANGOLA', 'AGO'),
 (6, 'ANTIGUA AND BARBUDA', 'ATG'),
 (7, 'Argentina', 'ARG'),
 (8, 'Armenia', 'ARM'),
 (9, 'ARUBA', 'ABW'),
 (10, 'Australia', 'AUS'),
 (11, 'Austria', 'AUT'),
 (12, 'AZERBAIJAN', 'AZE'),
 (13, 'BAHAMAS', 'BHS'),
 (14, 'BAHRAIN', 'BHR'),
 (15, 'BANGLADESH', 'BGD'),
 (16, 'Barbados', 'BRB'),
 (17, 'BELARUS', 'BLR'),
 (18, 'Belgium', 'BEL'),
 (19, 'BELIZE', 'BLZ'),
 (20, 'Benin', 'BEN'),
 (21, 'BHUTAN', 'BTN'),
 (22, 'BOLIVIA', 'BOL'),
 (23, 'Bosnia-Herzegovina', 'BIH'),
 (24, 'BOTSWANA', 'BWA'),
 (25, 'Brazil', 'BRA'),
 (26, 'British Virgin Islands', 'VGB'),
 (27, 'BRUNEI', 'BRN'),
 (28, 'Bulgaria', 'BGR'),
 (29, 'BURKINA FASO', 'BFA'),
 (30, 'BURUNDI', 'BDI'),
 (31, 'Cambodia', 'KHM'),
 (32, 'Cameroon', 'CMR'),
 (33, 'Canada', 'CAN'),
 (34, 'CAPE VERDE', 'CPV'),
 (35, 'CENTRAL AFRICAN, REPUBLIC', 'CAF'),
 (36, 'Chad', 'TCD'),
 (37, 'CHILE', 'CHL'),
 (38, 'CHINA', 'CHN'),
 (39, 'Colombia', 'COL'),
 (40, 'COMOROS', 'COM'),
 (41, 'CONGO', 'COG'),
 (42, 'CONGO, DEMOCRATIC REPUBLIC OF', 'ZAR'),
 (43, 'COOK ISLANDS', 'COK'),
 (44, 'Costa Rica', 'CRI'),
 (45, 'Croatia', 'HRV'),
 (46, 'CUBA', 'CUB'),
 (47, 'Cuba', 'CCB')]

















In [ ]:



    


class Book:
    id = None
    name = None
    isbn = None
    
    def __init__(self, name, isbn):
        self.name = name
        self.isbn = isbn
    
    def save(self):
        if self.id:
            cur.execute('UPDATE books SET name=?,isbn=? WHERE id=?',
                        (self.name, self.isbn, self.id))
        else:
            cur.execute('INSERT INTO books(name, isbn) VALUES(?, ?)',
                        (self.name, self.isbn))
    
    @staticmethod
    def get_books_by_name(name):
        cur.execute('SELECT * FROM books WHERE name LIKE "%?%', 
                    (name,))
        return cur.fetchall()
    
    @staticmethod
    def get_all_books():
        pass
    
    @staticmethod
    def get_book_by_id(_id):
        cur.execute('SELECT * FROM books WHERE id=?', (_id,))
        result = cur.fetchone()
        if result:
            book = Book()
            book.id, book.name, book.isbn = result
            return book
        return None



    











In [ ]:



    


book1 = Book('Learn nepali', 'akshdkajjsdhk')
book1.save()



    











In [ ]:



    


Book.get_books_by_name('Learning')



    











In [ ]:



    


book2 = Book.get_book_by_id(4)
book2.name = 'New Name'
book2.save()



    







class Temperature:
    temp = 10

    def __init__(self, temp):
        self.temp = temp

    def get_temperature(self):
        return self.temp

    @classmethod
    def get_class_temperature(cls):
        return cls.temp

    @staticmethod
    def get_added_temp(inst):
        return inst.temp + Temperature.temp

t = Temperature(20)
# t = Temperature() --> Temperature.__init__(t, 20)
t.get_temperature() # 20
# Temperature.get_temperature(t)
Temperature.get_class_temperature() # 10
# Temperature.get_class_temperature(Temperature)
Temperature.get_added_temp(t)
# Temperature.get_added_temp(t)





In [ ]:



    






    











In [ ]:



    






    











In [ ]:



    






    











In [ ]:



    






    











In [ ]:

Content source: idwaker/git_python_session

Similar notebooks:

notebook.community | gallery | about