pymysql_kits

pymysql_kits is a tool based on PyMySQL. It can quickly establish a database connection pool and conveniently execute SQL statements.

Contributing:

• The database connection pool module code comes from mysql/mysql-connector-python.
• The SQL statement operation code comes from hopehook/python-lab.

Requirements

• Python – one of the following:
  • CPython : 2.7 and >= 3.4
  • PyPy : Latest version
• MySQL Server – one of the following:
  • MySQL >= 5.5
  • MariaDB >= 5.5
• PyMySQL >= 0.8.1

Installation

pip install pymysql-kits

Example

The following examples make use of a simple table

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

PyMySQLConnectionPool Demo

from pymysql_kits.pooling import PyMySQLConnectionPool

db_conf = {
    "host": "localhost",
    "user": "user",
    "password": "passwd",
    "database": "db",
    "port": 3306,
    "autocommit": True,
    "charset": "utf8mb4",
}

# Create connection pool
db_pool = PyMySQLConnectionPool(pool_size=5, pool_name='local_pool', **db_conf)

# Get a connection
connection = db_pool.get_connection()
try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

Connections and Transaction Demo

from pymysql_kits import Connections

db_conf = {
    "host": "localhost",
    "user": "user",
    "password": "passwd",
    "database": "db",
    "port": 3306,
    "autocommit": True,
    "charset": "utf8mb4",
}

# Create a connection poll
conn = Connections(pool_size=5, **db_conf)

# Query
result = conn.fetchall("SELECT `id`, `password` FROM `users` WHERE `email`=%s", ('webmaster@python.org',))
print(result)

# Insert
conn.insert("INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)", ('webmaster@python.org', 'very-secret'))

# For transaction
transaction = conn.begin()
try:
    transaction.insert("INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)", ('webmaster@python.org', 'very-secret'))
except:
    transaction.rollback()
else:
    transaction.commit()
finally:
    transaction.close()

# or
with conn.begin() as transaction:
    transaction.insert("INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)", ('webmaster@python.org', 'very-secret'))

License

pymysql_kits is released under the MIT License. See LICENSE for more information.