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)
示例#2
0
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()
示例#4
0
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()
示例#5
0
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.')
示例#6
0
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()