How to use PyMySQL - 10 common examples

def _t2():
            _uuid = str(uuid.uuid4())
            _lock_uuid = str(uuid.uuid4())
            try:
                with get_db_connection() as db:
                    c = db.cursor()
                    execute_with_retry(db, c, "UPDATE locks SET lock_owner = 'whatever'")
                    # wait for signal to continue
                    time.sleep(2)
                    execute_with_retry(db, c, "INSERT INTO locks ( uuid, lock_time ) VALUES ( %s, NOW() )", (_uuid,))
                    db.commit()
            except pymysql.err.OperationalError as e:
                if e.args[0] == 1213 or e.args[0] == 1205:
                    deadlock_event.set()

def setUp(self):
        super(DataCheckTest, self).setUp()
        self.conn = pymysql.Connect(**db_save.PLUGIN_PARAM)
        self.cursor = self.conn.cursor()
        self.cursor.execute("DROP TABLE IF EXISTS test_data_check")
        self.cursor.execute("""
CREATE TABLE test_data_check(
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(32),
  term_id VARCHAR(32),
  item_id VARCHAR(32),
  time DATETIME,
  value FLOAT,
  warn_msg VARCHAR(1000)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
""")
        self.conn.commit()
        self.redis_client = redis.StrictRedis(db=config.getint('REDIS', 'db', fallback=1), decode_responses=True)
        self.redis_client.flushdb()

def main(mysql):
    """Add testset flag to recordings in MySQL database."""
    connection = pymysql.connect(host=mysql['host'],
                                 user=mysql['user'],
                                 passwd=mysql['passwd'],
                                 db=mysql['db'],
                                 cursorclass=pymysql.cursors.DictCursor)
    cursor = connection.cursor()

    # Download all datasets
    sql = ("SELECT `id`, `formula_in_latex` FROM `wm_formula` "
           "WHERE `is_important` = 1 ORDER BY `id` ASC")
    cursor.execute(sql)
    datasets = cursor.fetchall()

    for i, data in enumerate(datasets):
        fid, formula_in_latex = data['id'], data['formula_in_latex']
        print("%i: Create testset for %s (id: %i)..." % (i,
                                                         formula_in_latex,

['--config', config_file, 'galera', 'server', 'set_offline', percona_xtradb_cluster_three_node[2]['ip'], '3306']
                           )
    assert result.exit_code == 0

    result = runner.invoke(main,
                           ['--config', config_file, 'galera', 'register']
                           )
    assert result.exit_code == 0

    connection = pymysql.connect(
        host=proxysql_instance.host,
        port=proxysql_instance.port,
        user=proxysql_instance.user,
        passwd=proxysql_instance.password,
        connect_timeout=20,
        cursorclass=DictCursor)

    try:
        with connection.cursor() as cursor:
            cursor.execute('SELECT `hostgroup_id`, `hostname`, '
                           '`port`, `status`, `weight`, `compression`, '
                           '`max_connections`, `max_replication_lag`, '
                           '`use_ssl`, `max_latency_ms`, `comment`'
                           ' FROM `mysql_servers`'
                           ' WHERE hostgroup_id = %s'
                           ' AND hostname = %s',
                           (
                               hostgroup_reader,
                               percona_xtradb_cluster_three_node[2]['ip']
                           )
                           )
            row = cursor.fetchall()[0]

result = runner.invoke(main,
                           ['--config', config_file, 'galera', 'server', 'set_online', percona_xtradb_cluster_three_node[2]['ip'], '3306']
                           )
    assert result.exit_code == 0
    result = runner.invoke(main,
                           ['--config', config_file, 'galera', 'register']
                           )
    assert result.exit_code == 0

    connection = pymysql.connect(
        host=proxysql_instance.host,
        port=proxysql_instance.port,
        user=proxysql_instance.user,
        passwd=proxysql_instance.password,
        connect_timeout=20,
        cursorclass=DictCursor)

    try:
        with connection.cursor() as cursor:
            cursor.execute('SELECT `hostgroup_id`, `hostname`, '
                           '`port`, `status`, `weight`, `compression`, '
                           '`max_connections`, `max_replication_lag`, '
                           '`use_ssl`, `max_latency_ms`, `comment`'
                           ' FROM `mysql_servers`'
                           ' WHERE hostgroup_id = %s'
                           ' AND hostname = %s',
                           (
                               hostgroup_reader,
                               percona_xtradb_cluster_three_node[2]['ip']
                           )
                           )
            row = cursor.fetchall()[0]

except ImportError:
    from io import StringIO

try:
    import unittest2 as unittest
except ImportError:
    import unittest

try:
    from unittest import mock
except ImportError:
    import mock

try:
    import pymysql
    pymysql.install_as_MySQLdb()
except ImportError:
    pass

from architect.commands import main


@contextlib.contextmanager
def capture():
    out, err, sys.stderr, sys.stdout = sys.stdout, sys.stderr, StringIO(), StringIO()

    try:
        main()
    except SystemExit:
        pass

    sys.stderr.seek(0)

def test_wsrep_local_state_if_can_not_execute(mock_execute, galera_node):
    """
    :param galera_node: GaleraNode instance
    :type galera_node: GaleraNode
    """
    mock_execute.side_effect = OperationalError
    assert galera_node.wsrep_local_state == None

def test_finish_type_request_with_error(self, mock_g, mock_result, mock_rt):
        """
        Test function finish_type_request with error in database commit.
        """
        from mod_ci.controllers import finish_type_request
        from pymysql.err import IntegrityError

        mock_log = MagicMock()
        mock_request = MagicMock()
        mock_request.form = {
            'test_id': 1,
            'runTime': 1,
            'exitCode': 0
        }
        mock_g.db.commit.side_effect = IntegrityError

        finish_type_request(mock_log, 1, MagicMock(), mock_request)

        mock_log.debug.assert_called_once()
        mock_rt.query.filter.assert_called_once_with(mock_rt.id == 1)
        mock_result.assert_called_once_with(mock.ANY, mock.ANY, 1, 0, mock.ANY)
        mock_g.db.add.assert_called_once_with(mock.ANY)
        mock_g.db.commit.assert_called_once_with()
        mock_log.error.assert_called_once()

def test_watch_query_bad_arguments():
    """Test different incorrect combinations of arguments for `watch`
    command."""
    watch_query = mycli.packages.special.iocommands.watch_query
    with db_connection().cursor() as cur:
        with pytest.raises(ProgrammingError):
            next(watch_query('a select 1;', cur=cur))
        with pytest.raises(ProgrammingError):
            next(watch_query('-a select 1;', cur=cur))
        with pytest.raises(ProgrammingError):
            next(watch_query('1 -a select 1;', cur=cur))
        with pytest.raises(ProgrammingError):
            next(watch_query('-c -a select 1;', cur=cur))

def test_watch_query_bad_arguments():
    """Test different incorrect combinations of arguments for `watch`
    command."""
    watch_query = mycli.packages.special.iocommands.watch_query
    with db_connection().cursor() as cur:
        with pytest.raises(ProgrammingError):
            next(watch_query('a select 1;', cur=cur))
        with pytest.raises(ProgrammingError):
            next(watch_query('-a select 1;', cur=cur))
        with pytest.raises(ProgrammingError):
            next(watch_query('1 -a select 1;', cur=cur))
        with pytest.raises(ProgrammingError):
            next(watch_query('-c -a select 1;', cur=cur))