Skip to main content

pymysql connection & pool manager for python3

Project description

pymysql connection & pool manager for python3

Refactor pymysql connection

New features

  1. Parameter ‘charset’ default is utf8
  2. Parameter ‘autocommit’ default is True
  3. Added parameter ‘timezone’, default is ‘+00:00’
  4. Use pymysql.cursors.DictCursor by default
  5. Reconnect after the database connection is lost
  6. Add logs for creating connections, mysql warnings, exceptions, database queries, etc.
  7. Using the with…as syntax for transaction operations
  8. Provide simplified query methods such as fetch_all/fetch_row/fetch_column/fetch_first
  9. Provide simplified methods such as insert/insert_many/update/delete

1. Create pymysql connection

import pymysql
from pymysql_manager import Connection

conn = Connection(host='192.0.0.1', database='foo', timezone='+8:00')

2. Transaction

Before code:

try:
  conn.begin()
  conn.execute(....)
catch Exception:
  conn.rollback()
else:
  conn.commit()

Now:

with conn.transaction():
  conn.execute(...)

3. Fetch rowsets

# executed: select * from foo where id between 5 and 10
all_rows = conn.fetch_all('select * from foo where id between %s and %s', 5, 10)

# executed: select * from foo limit 1
first_row = conn.fetch_row('select * from foo')

# executed: select * from foo limit 1
first_column_on_first_row = conn.fetch_first('select * from foo')

# executed: select * from foo limit 1
third_column_on_first_row = conn.fetch_column('select * from foo', column=3)

4. Fetch by Iterator

When a result is large, it may be used SSCursor. But sometimes using limit … offset … can reduce the pressure on the database

by SSCursor

cursor = conn.cursor(pymysql.cursors.SSCursor)
conn.execute(sql)
while True:
  row = cursor.fetchone()
  if not row:
    break

by fetch_iterator

for row in conn.fetch_iterator(sql, per=1000, max=100000):
  print(row)

5. Single/Bulk Insert or Replace | Update | Delete

# insert ignore into mytable (foo, bar) values (1, 2)
db.insert('insert ignore into mytable', foo=1, bar=2)

# insert ignore into mytable (foo, bar) values (1, 2) on duplicate key update ...
db.insert('insert ignore into mytable on duplicate key update ...', **dict(foo=1, bar=2))

# insert ignore into mytable (id, name) values (1, 'foo'), (2, 'bar') on duplicate key update ...
db.insert_many('insert ignore into mytable on duplicate key update ...', ['id', 'name'], [(1, 'foo'), (2, 'bar')])

# update mytable set foo=1, bar=2 where id between %s and %s
db.update('update mytable where id between %s and %s', 10, 5, foo=1, bar=2)
db.update('update mytable where id between %s and %s', [10, 5], foo=1, bar=2)
db.update('update mytable where id between %s and %s', *[10, 5], **dict(foo=1, bar=2))

# update from mytable where id between %s and %s
db.delete('delete from mytable id between %s and %s', 10, 5)
db.delete('delete from mytable id between %s and %s', [10, 5])

Connection Pool

1. Create connection pool

from pymysql_manager import ConnectionPooled
pooled = ConnectionPooled(host='192.0.0.1', database='foo',
                          pool_options=dict(max_size=10, max_usage=100000, idle=60, ttl=120))

2. Execute SQL without the connection pool

pooled.execute(sql)
pooled.connection.execute(sql)

3. Using connection pool to execute SQL

with pooled.pool() as connection:
  connection.execute(sql)

Connection Manager

1. Configuration

from pymysql_manager import ConnectionManager
m = ConnectionManager(default='foo',
                       foo=dict(host='192.0.0.1', database='foo', user='root', passwd=''),
                       bar=dict(host='192.0.0.1', database='bar', user='root', passwd=''))

2. Get a connection

m.execute(sql) # use default connection
m['foo].execute(sql)
m.connection('foo').exeucte(sql)

3. Get a connection from connection pool

with m.pool() as connection: pass  # use default connection
with m['foo'].pool() as connection: pass
with m.connection('foo').pool() as connection: pass

License

The MIT License (MIT). Please see License File for more information.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for pymysql-manager, version 0.0.2
Filename, size File type Python version Upload date Hashes
Filename, size pymysql_manager-0.0.2-py3-none-any.whl (7.8 kB) File type Wheel Python version py3 Upload date Hashes View
Filename, size pymysql_manager-0.0.2.tar.gz (8.3 kB) File type Source Python version None Upload date Hashes View

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page