def test_db_retrieval(self):
import mysql.connector as mysql_conn
from database import login_info
"""Connect to a database, retrieve information from a table
and get the same information as when using classFactory's function."""
db = mysql_conn.Connect(**login_info)
cursor = db.cursor()
# Retrieve two rows from the table 'animal'
cursor.execute("""SELECT name, family, weight FROM animal
WHERE family='Hyena';""")
first = cursor.fetchone()
first_test = "animal_record({0!r}, {1!r}, {2!r})".format(
first[0], first[1], first[2])
second = cursor.fetchone()
second_test = "animal_record({0!r}, {1!r}, {2!r})".format(
second[0], second[1], second[2])
# Create generator instance for assertion tests
animal_table = build_row("animal", "name family weight")
animal = animal_table.retrieve(animal_table, cursor, "family='Hyena'")
self.assertEqual(first_test, repr(next(animal)))
self.assertEqual(second_test, repr(next(animal)))
def setUp(self):
S = build_row("animal", "id name family weight")
self.a = S([8, "Dennis", "Dragon", 10000])
self.e = S([1, "Ellie", "Elephant", 2350])
self.db = mysql.connector.Connect(**login_info)
self.cursor = self.db.cursor()
def setUp(self):
curs.execute("DROP TABLE IF EXISTS user")
conn.commit()
curs.execute(TBLDEF)
conn.commit()
curs.execute("INSERT INTO user (id, name, email) VALUES (1, 'John Doe', '*****@*****.**')")
conn.commit()
curs.execute("INSERT INTO user (id, name, email) VALUES (2, 'Jane Doe', '*****@*****.**')")
conn.commit()
C = build_row("user", "id name email")
self.c = C([1, "Steve Holden", "*****@*****.**"])
def setUp(self):
self.db = mysql.connector.Connect(**login_info)
self.cursor = self.db.cursor()
self.table = "test_animal"
self.cols = 'id name family weight'
self.data = (
("Ellie", "Elephant", 2350),
("Gerald", "Gnu", 1400),
("Gerald", "Giraffe", 940),
("Leonard", "Leopard", 280),
("Sam", "Snake", 24),
("Steve", "Snake", 35),
("Zorro", "Zebra", 340)
)
self.conditions = (
("WHERE name = 'Gerald'"),
("WHERE family = 'Snake'"),
("WHERE weight < 500"),
("") # No condition
)
#Set up test animal table
self.cursor.execute("""DROP TABLE IF EXISTS {0}""".format(self.table))
self.cursor.execute("""
CREATE TABLE {0}(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
family VARCHAR(50),
weight INTEGER) ENGINE = MYISAM;
""".format(self.table))
for t in self.data:
self.cursor.execute("""
INSERT INTO {0} ({1})
VALUES (%s, %s, %s)""".format(self.table, ", ".join(self.cols.split()[1:])), t)
# check that the table is created and with all the right columns
self.cursor.execute("""
SELECT column_name FROM information_schema.columns
WHERE table_name='{0}'""".format(self.table))
fetched_cols = self.cursor.fetchall()
cols = [(col,) for col in self.cols.split()]
assert(fetched_cols == cols)
self.db.commit()
self.C = build_row(self.table, self.cols)
def test_retrieve_data_row_objects(self):
db = mysql.connector.Connect(**login_info)
cursor = db.cursor()
table = 'animal'
cols = 'id name family weight'
D = build_row(table, cols)
sql = 'select * from animal;'
cursor.execute(sql)
expected_rows = set()
for row in cursor.fetchall():
expected_rows.add(repr(D(row)))
d = D([100, "Joe", "Python", 10])
observed_rows = set()
for row in d.retrieve(cursor):
observed_rows.add(repr(row))
self.assertEqual(observed_rows, expected_rows)
def test_retrieve_data_row_objects(self):
db = mysql.connector.Connect(**login_info)
cursor = db.cursor()
table = 'animal'
cols = 'id name family weight'
D = build_row(table, cols)
sql = 'select * from animal;'
cursor.execute(sql)
expected_rows = set()
for row in cursor.fetchall():
expected_rows.add(repr(D(row)))
d = D([100, "Joe", "Python", 10])
observed_rows = set()
for row in d.retrieve(cursor):
observed_rows.add(repr(row))
self.assertEqual(observed_rows, expected_rows)
def setUp(self):
# Create a test row
self.row_values = ((1, "Steve Holden", "*****@*****.**"),
(2, "Maggie Mae", "*****@*****.**"),
(3, "John Smith", "*****@*****.**"),
(4, "John Smith", "*****@*****.**"))
C = build_row("user", "id name email")
self.c = C(self.row_values[0])
self.db = mysql.connector.Connect(**login_info)
self.cursor = self.db.cursor()
self.cursor.execute('''DROP TABLE IF EXISTS user''')
self.cursor.execute('''CREATE TABLE user(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(50))''')
for sampledata in self.row_values:
self.cursor.execute('''INSERT INTO user (name, email)
VALUES (%s, %s)''', sampledata[1:])
def test_retrieve_data_row_objects(self):
db = mysql.connector.Connect(**login_info)
cursor = db.cursor()
table = 'animal'
cols = 'id name family weight'
D = build_row(table, cols)
cursor.execute("USE Zoo")
sql = 'SELECT * FROM animal;'
cursor.execute(sql)
expected_rows = set()
for row in cursor.fetchall():
expected_rows.add(repr(D(row)))
d = D([100, "Joe", "Python", 10]) # making up data
observed_rows = set()
for row in d.retrieve(cursor):
observed_rows.add(repr(row))
def setUp(self):
self.db = mysql.connector.Connect(**login_info)
self.cursor = self.db.cursor()
self.tmp_poc_t = randstring()
self.poc_seed = (
("jdoe", "John", "Doe", "*****@*****.**"),
("dpeters", "David", "Peters", "*****@*****.**"),
("asmith", "Alice", "Smith", "*****@*****.**"),
("brogers", "Bob", "Rogers", "*****@*****.**"),
("tlittle", "Tom", "Little", "*****@*****.**"),
("mwallace", "Moe", "Wallace", "*****@*****.**"),
)
# Probably don't need this because unlikely to drop a tmp table.
self.cursor.execute("DROP TABLE IF EXISTS %s" % self.tmp_poc_t)
# Create tmp poc table
self.cursor.execute(
"""
CREATE TABLE %s (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
username varchar(50),
firstname VARCHAR(50),
lastname VARCHAR(50),
email VARCHAR(50))
"""
% self.tmp_poc_t
)
# Then populate
for poc in self.poc_seed:
lrecord = ("INSERT INTO %s " % self.tmp_poc_t) + (
"(username, firstname, lastname, email) VALUES ('%s', '%s', '%s', '%s')" % poc
)
self.cursor.execute(lrecord)
self.db.commit()
# Create DataRow object an instantiate a blank instance of DataRow object
self.POC = build_row(self.tmp_poc_t, "id username firstname lastname email")
self.poc = self.POC([None, None, None, None, None])
def setUp(self):
self.db = mysql.connector.Connect(**login_info)
self.cursor = self.db.cursor()
self.tmp_poc_t = randstring()
self.poc_seed = (("jdoe", "John", "Doe",
"*****@*****.**"), ("dpeters", "David", "Peters",
"*****@*****.**"),
("asmith", "Alice", "Smith", "*****@*****.**"),
("brogers", "Bob", "Rogers", "*****@*****.**"),
("tlittle", "Tom", "Little", "*****@*****.**"),
("mwallace", "Moe", "Wallace",
"*****@*****.**"))
# Probably don't need this because unlikely to drop a tmp table.
self.cursor.execute("DROP TABLE IF EXISTS %s" % self.tmp_poc_t)
# Create tmp poc table
self.cursor.execute("""
CREATE TABLE %s (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
username varchar(50),
firstname VARCHAR(50),
lastname VARCHAR(50),
email VARCHAR(50))
""" % self.tmp_poc_t)
# Then populate
for poc in self.poc_seed:
lrecord=("INSERT INTO %s " % self.tmp_poc_t) + \
("(username, firstname, lastname, email) VALUES ('%s', '%s', '%s', '%s')" % poc)
self.cursor.execute(lrecord)
self.db.commit()
# Create DataRow object an instantiate a blank instance of DataRow object
self.POC = build_row(self.tmp_poc_t,
"id username firstname lastname email")
self.poc = self.POC([None, None, None, None, None])
def setUp(self):
C = build_row("animal", "id name family weight")
self.c = C([1, "Clifford", "Dog", 10000])
def test_retrieve_multple_records(self):
C = build_row("animal", "id", "name", "family", "weight", curs = self.cursor, condition="family='Snake'")
observed = [data for data in C.retrieve(C, curs = self.cursor, condition="family='Snake'")]
expected = "[animal_record(5, 'Sam', 'Snake', 24), animal_record(6, 'Steve', 'Snake', 35)]"
self.assertEqual(str(observed), expected)
def setUp(self):
C = build_row("user", "id name email")
self.c = C([1, "steve", "steve@"])
def test_retrieve_no_record(self):
C = build_row("animal", "id", "name", "family", "weight", curs = self.cursor, condition="name='None'")
observed = [data for data in C.retrieve(C, curs = self.cursor, condition="name='None'")]
expected = "[]"
self.assertEqual(str(observed), expected)
def setUp(self):
C = build_row("user", "id name email")
self.c = C([1, "Steve Holden", "*****@*****.**"])
def setUp(self):
C = build_row("animal", "id name family weight")
self.c = C([9999, "Pixie", "Unicorn", 1800])
def setUp(self):
C = build_row("user", "id name email")
self.c = C([1, "Steve Holden", "*****@*****.**"])
def test_retrieve_single_record(self):
C = build_row('animal', 'id', 'name', 'family', 'weight', curs=self.cursor, condition="name='Ellie'")
observed = [data for data in C.retrieve(C, curs=self.cursor, condition="name='Ellie'")]
expected = "[animal_record(1, 'Ellie', 'Elephant', 2350)]"
self.assertEqual(str(observed), expected)