Permalink
Switch branches/tags
Nothing to show
Find file Copy path
dc5210a Jun 1, 2014
0 contributors

Users who have contributed to this file

52 lines (42 sloc) 1.73 KB
# Sebastian Raschka, 2014
# Selecting rows from an existing SQLite database
import sqlite3
sqlite_file = 'my_first_db.sqlite' # name of the sqlite database file
table_name = 'my_table_2' # name of the table to be queried
id_column = 'my_1st_column'
some_id = 123456
column_2 = 'my_2nd_column'
column_3 = 'my_3rd_column'
# Connecting to the database file
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()
# 1) Contents of all columns for row that match a certain value in 1 column
c.execute('SELECT * FROM {tn} WHERE {cn}="Hi World"'.\
format(tn=table_name, cn=column_2))
all_rows = c.fetchall()
print('1):', all_rows)
# 2) Value of a particular column for rows that match a certain value in column_1
c.execute('SELECT ({coi}) FROM {tn} WHERE {cn}="Hi World"'.\
format(coi=column_2, tn=table_name, cn=column_2))
all_rows = c.fetchall()
print('2):', all_rows)
# 3) Value of 2 particular columns for rows that match a certain value in 1 column
c.execute('SELECT {coi1},{coi2} FROM {tn} WHERE {coi1}="Hi World"'.\
format(coi1=column_2, coi2=column_3, tn=table_name, cn=column_2))
all_rows = c.fetchall()
print('3):', all_rows)
# 4) Selecting only up to 10 rows that match a certain value in 1 column
c.execute('SELECT * FROM {tn} WHERE {cn}="Hi World" LIMIT 10'.\
format(tn=table_name, cn=column_2))
ten_rows = c.fetchall()
print('4):', ten_rows)
# 5) Check if a certain ID exists and print its column contents
c.execute("SELECT * FROM {tn} WHERE {idf}=?".\
format(tn=table_name, cn=column_2, idf=id_column), (123456,))
id_exists = c.fetchone()
if id_exists:
print('5): {}'.format(id_exists))
else:
print('5): {} does not exist'.format(some_id))
# Closing the connection to the database file
conn.close()