def queryWithFetchall():
""" In case the number of rows in the table is small, fetchall() method to fetch all rows from the database table is more efficient.
fetchall() needs to allocate enough memory to store the entire result set in the memory (sometimes inefficient)
"""
dbConfig = ReadMySQLConfig()
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
cursor.execute("""
SELECT * FROM user
""")
rows = cursor.fetchall()
print('Total rows: {}'.format(cursor.rowcount))
for row in rows:
print(row)
cursor.close()
conn.close()
except mdb.Error as e:
print(e)
def insertOneRow(title, isbn):
""" Insert a row to the database 'testing' table 'books' """
query = """INSERT INTO books(title, isbn)
VALUES(%s, %s)
"""
args = (title, isbn)
dbConfig = ReadMySQLConfig('MySQL_testing')
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
cursor.execute(query, args)
# In case a new row is inserted successfully, we can retrieve the lsat insertid of the AUTO_INCREMENT column by using the lastrowid property of the MySQLCursor object.
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found.')
conn.commit()
except mdb.Error as e:
print(e)
finally:
cursor.close()
conn.close()
def queryWithFetchone():
""" Initiate a MySQL query using .fetchone() method """
# Load MySQL config
dbConfig = ReadMySQLConfig()
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
# Execute a query
cursor.execute("""
SELECT * FROM user
""")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone() # pop out this item and fill in the next
except mdb.Error as e:
print(e)
finally:
# Close connection
cursor.close()
conn.close()
def resetTestDatabase():
""" To delete and create a MySQL database named Testing """
dbConfigRoot = ReadMySQLConfig("MySQL_root")
dbConfigTesting = ReadMySQLConfig("MySQL_testing")
try:
conn = mdb.Connection(**dbConfigRoot)
cursor = conn.cursor()
# Delete the user
cursor.execute("""
# Drop the user
DROP USER IF EXISTS {user}@{host};
FLUSH PRIVILEGES;
# Create a new database
DROP DATABASE IF EXISTS {db};
CREATE DATABASE {db};
USE {db};
CREATE USER {user}@{host} IDENTIFIED BY '{passwd}';
GRANT ALL PRIVILEGES on {db}.* TO {user}@{host};
FLUSH PRIVILEGES;
# Create a table
CREATE TABLE books (
id int NOT NULL AUTO_INCREMENT,
title varchar(1024) NOT NULL,
ISBN varchar(32) NOT NULL,
PRIMARY KEY (id)
)
ENGINE=InnoDB
AUTO_INCREMENT=1
DEFAULT
CHARSET=utf8;
""".format(**dbConfigTesting))
except mdb.Error as e:
print(e)
finally:
cursor.close()
conn.close()
def connect():
""" Connect to MySQL database by loading database setttings from config.ini. """
# Load configurations
db_config = ReadMySQLConfig()
try:
print("Connecting to MySQL database...")
conn = mdb.connection(**db_config)
print('connection established.')
except mdb.Error as e:
print(e)
finally:
conn.close()
print('connection closed.')
def insertManyRows(books):
""" Insert many rows using .executemany() method
:param books: a list of tuples
"""
query = """INSERT INTO books(title, isbn)
VALUES(%s, %s)
"""
dbConfig = ReadMySQLConfig('MySQL_testing')
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
cursor.executemany(query, books)
conn.commit()
except mdb.Error as e:
print(e)
finally:
cursor.close()
conn.close()
def queryWithFetchmany():
""" Useful for a relatively big table, it takes time to fetch all rows and return the result set.
fetchmany() method returns the next number of rows(n) of the result set, which allows us to balance between time and memory space.
"""
dbConfig = ReadMySQLConfig()
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
cursor.execute("""
SELECT * FROM user
""")
for row in chunkCursor(cursor, 3):
print(row)
cursor.close()
conn.close()
except mdb.Error as e:
print(e)
def deleteOneRow(bookid):
""" Update one row at the time. """
dbConfig = ReadMySQLConfig('MySQL_testing')
query = """ DELETE from books
WHERE id = %s """
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
cursor.execute(query, (bookid, )) # This comma - , - is magic
conn.commit()
except mdb.Error as e:
print(e)
finally:
cursor.close()
conn.close()
def updateOneRow(bookid, title):
""" Update one row at the time. """
dbConfig = ReadMySQLConfig('MySQL_testing')
query = """ UPDATE books
set title = %s
WHERE id = %s """
data = (title, bookid)
try:
conn = mdb.Connection(**dbConfig)
cursor = conn.cursor()
cursor.execute(query, data)
conn.commit()
except mdb.Error as e:
print(e)
finally:
cursor.close()
conn.close()