In [4]:

    

import sqlite3
db=sqlite3.connect('test_db2.sqlite')


    

In [5]:

    

cursor=db.cursor()


    

In [26]:

    

cursor.execute("DROP TABLE IF EXISTS candidates")


    

    
Out[26]:





<sqlite3.Cursor at 0x110ac23b0>

In [7]:

    

cursor.execute("DROP TABLE IF EXISTS contributors")


    

    
Out[7]:





<sqlite3.Cursor at 0x110ac23b0>

In [8]:

    

cursor.execute("PRAGMA foreign_key=1")


    

    
Out[8]:





<sqlite3.Cursor at 0x110ac23b0>

In [27]:

    

cursor.execute('''CREATE TABLE candidates (
                id interger PRIMARY KEY NOT NULL,
                first_name TEXT,
                last_name TEXT,
                middle_init TEXT,
                party TEXT NOT NULL)''')
db.commit()


    

In [28]:

    

cursor.execute('''INSERT INTO candidates 
                (id,first_name,last_name,middle_init,party)
                VALUES(?,?,?,?,?)''',
              (16,"AA","BB","","DD"))
db.commit()


    

In [31]:

    

cursor.execute("SELECT * FROM candidates WHERE first_name = 'AA'")


    

    
Out[31]:





<sqlite3.Cursor at 0x110ac23b0>

In [32]:

    

all_rows=cursor.fetchall()


    

In [33]:

    

print(all_rows)


    

    




[(16, 'AA', 'BB', '', 'DD')]

In [34]:

    

cursor.execute("SELECT first_name FROM candidates")


    

    
Out[34]:





<sqlite3.Cursor at 0x110ac23b0>

In [35]:

    

all_rows=cursor.fetchall()


    

In [36]:

    

print(all_rows)


    

    




[('AA',)]

In [41]:

    

cursor.execute('''CREATE TABLE contributors(
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                last_name TEXT,
                first_name TEXT,
                middle_name TEXT,
                street_1 TEXT,
                street_2 TEXT,
                city TEXT,
                state TEXT,
                zip TEXT,
                amount INTEGER,
                date DATETIME,
                candidate_id INTEGER NOT NULL,
                FOREIGN KEY(candidate_id) REFERENCES candidates(id))''')


    

    




ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 68))







    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-41-71ae47511ec9> in <module>()
     12                 date DATETIME,
     13                 candidate_id INTEGER NOT NULL,
---> 14                 FOREIGN KEY(candidate_id) REFERENCES candidates(id))''')
     15 db.commit()

OperationalError: table contributors already exists

In [43]:

    

db.commit()


    

In [44]:

    

contributors=[("Agee", "Steven", "", "549 Laurel Branch Road", "", "Floyd", "VA", int(24091), int(500), 2007-6-30, 16),("Aaron", "Carole", "", "PO Box 1806", "", "Ogunqui", "ME", int(3907), int(70), 2008-2-7, 34)]


    

In [45]:

    

cursor.executemany('INSERT INTO contributors (last_name, first_name, middle_name, street_1, street_2, city, state, zip, amount, date, candidate_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', contributors)


    

    
Out[45]:





<sqlite3.Cursor at 0x110ac23b0>

In [46]:

    

db.commit()


    

In [47]:

    

cursor.execute("SELECT last_name FROM contributors where amount <100")


    

    
Out[47]:





<sqlite3.Cursor at 0x110ac23b0>

In [48]:

    

for c in cursor.fetchall():
    print(c)


    

    




('Aaron',)

In [49]:

    

cursor.execute('INSERT INTO contributors (last_name, first_name, middle_name, street_1, street_2, city, state, zip, amount, date, candidate_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', ("Buckler", "Steve", "", "24351 Armada Dr.", "", "Dana Point", "CA", int(926291), int(50), 2007-7-30, 20))


    

    
Out[49]:





<sqlite3.Cursor at 0x110ac23b0>

In [ ]: