User:Alvonruff/mysql.connector

MySQL Connector Notes

To install on python3:

• pip install mysql-connector-python

The connector generates TLS errors with python2.7, as "Python 2.7 support was removed in Connector/Python 8.0.24".

Instantiating a Connection

The algorithm using MySQLdb to connect to the database is:

    db = MySQLdb.connect(DBASEHOST, USERNAME, PASSWORD, conv=_IsfdbConvSetup())
    db.select_db(DBASE)
    db.set_character_set("latin1")

The method using mysql.connector to connect is:

    db = mysql.connector.connect(user=USERNAME, password=PASSWORD, host=DBASEHOST, database=DBASE)

Queries That Return a Single Record

The algorithm using MySQLdb to fetch a single record is:

    query = "select author_birthplace from authors where author_id='%d'" % int(authorID)
    db.query(query)
    result = db.store_result()
    record = result.fetch_row()
    return record[0][0]

The method using mysql.connector to fetch a single record would be:

    query = "select author_birthplace from authors where author_id='%d'" % int(authorID)
    cursor = db.cursor()
    cursor.execute(query)
    record = cursor.fetchone()
    return record[0]

Queries That Return Multiple Record

The algorithm using MySQLdb to fetch multiple records is:

    query = "select pubs.* from pubs,pub_authors where pub_authors.author_id=%d and pubs.pub_id=pub_authors.pub_id;" % aurec
    db.query(query)
    result = db.store_result()
    pub = result.fetch_row()
    results = []
    while pub:
        results.append(pub[0])
        pub = result.fetch_row()
    return results

The method using mysql.connector to fetch a multiple records would be:

    query = "select pubs.* from pubs,pub_authors where pub_authors.author_id=%d and pubs.pub_id=pub_authors.pub_id;" % aurec
    cursor = db.cursor()
    cursor.execute(query)
    results = []
    record = cursor.fetchmany()
    while record:
        results.append(record[0])
        record = cursor.fetchmany()
    return results