Python pymysql.connect() Examples

The following are 30 code examples for showing how to use pymysql.connect(). These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example.

You may check out the related API usage on the sidebar.

You may also want to check out all available functions/classes of the module pymysql , or try the search function .

Example 1
Project: cutout   Author: jojoin   File: mysql.py    License: MIT License 7 votes vote down vote up
def cursor(cursor_type=None):
	global _connects, _curcon, _config
	if cursor_type:
		return _connects[_curcon]['connect'].cursor(cursor_type)
	else:
		return _connects[_curcon]['connect'].cursor(_config['cursor_type'])
	



# 以下查询接口 使用者必须调用
# cur.close()



## 执行sql语句 
Example 2
Project: aws-ops-automator   Author: awslabs   File: rds.py    License: Apache License 2.0 6 votes vote down vote up
def connect(cls, host_endpoint, user, password, db):
        return pymysql.connect(host=host_endpoint, user=user, password=password, db=db) 
Example 3
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 6 votes vote down vote up
def realTestDialogAuthThreeAttempts(self):
        TestAuthentication.Dialog.m = {b'Password, please:': b'stillnotverysecret'}
        TestAuthentication.Dialog.fail=True   # fail just once. We've got three attempts after all
        with TempUser(self.connections[0].cursor(), 'pymysql_3a@localhost',
                      self.databases[0]['db'], 'three_attempts', 'stillnotverysecret') as u:
            pymysql.connect(user='pymysql_3a', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db)
            pymysql.connect(user='pymysql_3a', auth_plugin_map={b'dialog': TestAuthentication.DialogHandler}, **self.db)
            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_3a', auth_plugin_map={b'dialog': object}, **self.db)

            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_3a', auth_plugin_map={b'dialog': TestAuthentication.DefectiveHandler}, **self.db)
            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_3a', auth_plugin_map={b'notdialogplugin': TestAuthentication.Dialog}, **self.db)
            TestAuthentication.Dialog.m = {b'Password, please:': b'I do not know'}
            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_3a', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db)
            TestAuthentication.Dialog.m = {b'Password, please:': None}
            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_3a', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db) 
Example 4
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 6 votes vote down vote up
def test_defer_connect(self):
        import socket
        for db in self.databases:
            d = db.copy()
            try:
                sock = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
                sock.connect(d['unix_socket'])
            except KeyError:
                sock = socket.create_connection(
                                (d.get('host', 'localhost'), d.get('port', 3306)))
            for k in ['unix_socket', 'host', 'port']:
                try:
                    del d[k]
                except KeyError:
                    pass

            c = pymysql.connect(defer_connect=True, **d)
            self.assertFalse(c.open)
            c.connect(sock)
            c.close()
            sock.close() 
Example 5
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_issues.py    License: MIT License 6 votes vote down vote up
def test_issue_17(self):
        """could not connect mysql use passwod"""
        conn = self.connections[0]
        host = self.databases[0]["host"]
        db = self.databases[0]["db"]
        c = conn.cursor()

        # grant access to a table to a user with a password
        try:
            with warnings.catch_warnings():
                warnings.filterwarnings("ignore")
                c.execute("drop table if exists issue17")
            c.execute("create table issue17 (x varchar(32) primary key)")
            c.execute("insert into issue17 (x) values ('hello, world!')")
            c.execute("grant all privileges on %s.issue17 to 'issue17user'@'%%' identified by '1234'" % db)
            conn.commit()

            conn2 = pymysql.connect(host=host, user="issue17user", passwd="1234", db=db)
            c2 = conn2.cursor()
            c2.execute("select x from issue17")
            self.assertEqual("hello, world!", c2.fetchone()[0])
        finally:
            c.execute("drop table issue17") 
Example 6
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_issues.py    License: MIT License 6 votes vote down vote up
def test_issue_114(self):
        """ autocommit is not set after reconnecting with ping() """
        conn = pymysql.connect(charset="utf8", **self.databases[0])
        conn.autocommit(False)
        c = conn.cursor()
        c.execute("""select @@autocommit;""")
        self.assertFalse(c.fetchone()[0])
        conn.close()
        conn.ping()
        c.execute("""select @@autocommit;""")
        self.assertFalse(c.fetchone()[0])
        conn.close()

        # Ensure autocommit() is still working
        conn = pymysql.connect(charset="utf8", **self.databases[0])
        c = conn.cursor()
        c.execute("""select @@autocommit;""")
        self.assertFalse(c.fetchone()[0])
        conn.close()
        conn.ping()
        conn.autocommit(True)
        c.execute("""select @@autocommit;""")
        self.assertTrue(c.fetchone()[0])
        conn.close() 
Example 7
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def setup_method(self, load_iris_data):
        super(_EngineToConnMixin, self).load_test_data_and_sql()
        engine = self.conn
        conn = engine.connect()
        self.__tx = conn.begin()
        self.pandasSQL = sql.SQLDatabase(conn)
        self.__engine = engine
        self.conn = conn

        yield

        self.__tx.rollback()
        self.conn.close()
        self.conn = self.__engine
        self.pandasSQL = sql.SQLDatabase(self.__engine)
        # XXX:
        # super(_EngineToConnMixin, self).teardown_method(method) 
Example 8
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def test_datetime_time(self):
        # test support for datetime.time
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql('test_time', self.conn, index=False)
        res = read_sql_table('test_time', self.conn)
        tm.assert_frame_equal(res, df)

        # GH8341
        # first, use the fallback to have the sqlite adapter put in place
        sqlite_conn = TestSQLiteFallback.connect()
        sql.to_sql(df, "test_time2", sqlite_conn, index=False)
        res = sql.read_sql_query("SELECT * FROM test_time2", sqlite_conn)
        ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
        tm.assert_frame_equal(ref, res)  # check if adapter is in place
        # then test if sqlalchemy is unaffected by the sqlite adapter
        sql.to_sql(df, "test_time3", self.conn, index=False)
        if self.flavor == 'sqlite':
            res = sql.read_sql_query("SELECT * FROM test_time3", self.conn)
            ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(ref, res)
        res = sql.read_sql_table("test_time3", self.conn)
        tm.assert_frame_equal(df, res) 
Example 9
Project: piicatcher   Author: tokern   File: test_databases.py    License: Apache License 2.0 6 votes vote down vote up
def create_tables(self, request):
        self.conn = pymysql.connect(host="127.0.0.1",
                                    user="piiuser",
                                    password="p11secret",
                                    database="piidb"
                                    )

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, char_data_types)
            cursor.execute("commit")
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as drop_cursor:
                self.execute_script(drop_cursor, self.char_db_drop)
                logging.info("Executed drop script")
                drop_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 
Example 10
Project: piicatcher   Author: tokern   File: test_databases.py    License: Apache License 2.0 6 votes vote down vote up
def create_tables(self, request):
        self.conn = psycopg2.connect(host="127.0.0.1",
                                     user="piiuser",
                                     password="p11secret",
                                     database="piidb")

        self.conn.autocommit = True

        with self.conn.cursor() as cursor:
            self.execute_script(cursor, char_data_types)
            cursor.close()

        def drop_tables():
            with self.conn.cursor() as d_cursor:
                d_cursor.execute(self.char_db_drop)
                logging.info("Executed drop script")
                d_cursor.close()
            self.conn.close()

        request.addfinalizer(drop_tables) 
Example 11
Project: cutout   Author: jojoin   File: mysql.py    License: MIT License 5 votes vote down vote up
def connect(dbconf=None, reset=None, name='default', timeout=3500):
	global _connects, _curcon, _config
	con = None
	# 新连接
	if dbconf:
		for (k,v) in _dbConfig.items():
			if not k in dbconf:
				dbconf[k] = v
		con = pymysql.connect(**dbconf)
	# 读取缓存的连接
	if not dbconf and name:
		conn = _connects[name]
		tiv = conn['timevalid']
		ti = int(time.time())
		if(ti>=tiv): # 重新激活连接
			dbconf = conn['config']
			conn['connect'].close()
			conn['connect'] = pymysql.connect(**dbconf)
			conn['timevalid'] = ti+timeout
		con = conn['connect']
	# 缓存新的连接
	if dbconf and name:
		ti = int(time.time())
		_connects[name] = {
			'name': name,
			'connect': con,
			'config': dbconf,
			'timevalid': ti+timeout
		}
	# 设置默认连接
	if reset or not _curcon:
		_curcon = name
	# 返回连接
	return con



## 断开连接 
Example 12
Project: cutout   Author: jojoin   File: mysql.py    License: MIT License 5 votes vote down vote up
def disconnect(name='default'):
	global _connects
	if name in _connects:
		_connects[name]['connect'].close()
		del _connects[name]



## 获得数据库操作游标 
Example 13
Project: cutout   Author: jojoin   File: mysql.py    License: MIT License 5 votes vote down vote up
def query(sql, cursor_type=None):
	con = connect()
	con.ping()
	cur = cursor(cursor_type)
	cur.execute(sql)
	con.commit() #提交事务
	cur.close()
	return cur



## 查询
# @param cursor=None 返回查询的list数组
#        cursor=True 直接返回cursor 
Example 14
Project: cutout   Author: jojoin   File: mysql.py    License: MIT License 5 votes vote down vote up
def main():
	conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='mysql')
	cur = conn.cursor(pymysql.cursors.DictCursor)
	#cur = conn.cursor()
	cur.execute("SELECT Host,User FROM user")
	print(cur.description)
	print(cur.rowcount)
	print(repr(cur))
	for row in cur:
	   print(row)
	cur.close()
	conn.close() 
Example 15
Project: PT-help   Author: Rhilip   File: analytics_gen.py    License: MIT License 5 votes vote down vote up
def __init__(self, host, port, user, password, db):
            self.db = pymysql.connect(host=host, port=port, user=user, password=password, db=db,
                                      charset='utf8', autocommit=True) 
Example 16
Project: PT-help   Author: Rhilip   File: scrapy_6v.py    License: MIT License 5 votes vote down vote up
def __init__(self, host, port, user, password, db):
        self._commit_lock = Lock()
        self.db = pymysql.connect(host=host, port=port, user=user, password=password, db=db, charset='utf8') 
Example 17
Project: PPython-again   Author: maiwang79   File: test.py    License: Apache License 2.0 5 votes vote down vote up
def go() :
	conn=pymysql.connect(host='127.0.0.1', user='demo', passwd='test', db='essay_quinary') ;
	cur=conn.cursor();
	sql="show databases;"
	cur.execute(sql);
	r=cur.fetchall();
	print("aaaa");
	print(r);
	return r 
Example 18
Project: PPython-again   Author: maiwang79   File: php_python.py    License: Apache License 2.0 5 votes vote down vote up
def getConn() :
	return pymysql.connect(
		host='localhost',
		user='demo',
		passwd='test',
		db='ppython',
		port=3306) 
Example 19
Project: Quiver-alfred   Author: danielecook   File: peewee.py    License: MIT License 5 votes vote down vote up
def connect(self):
        with self._conn_lock:
            if self.deferred:
                raise Exception('Error, database not properly initialized '
                                'before opening connection')
            with self.exception_wrapper():
                self.__local.conn = self._connect(
                    self.database,
                    **self.connect_kwargs)
                self.__local.closed = False
                self.initialize_connection(self.__local.conn) 
Example 20
Project: Quiver-alfred   Author: danielecook   File: peewee.py    License: MIT License 5 votes vote down vote up
def get_conn(self):
        if self.__local.context_stack:
            conn = self.__local.context_stack[-1].connection
            if conn is not None:
                return conn
        if self.__local.closed:
            self.connect()
        return self.__local.conn 
Example 21
Project: Quiver-alfred   Author: danielecook   File: peewee.py    License: MIT License 5 votes vote down vote up
def _connect(self, database, **kwargs):
        if not sqlite3:
            raise ImproperlyConfigured('pysqlite or sqlite3 must be installed.')
        conn = sqlite3.connect(database, **kwargs)
        conn.isolation_level = None
        try:
            self._add_conn_hooks(conn)
        except:
            conn.close()
            raise
        return conn 
Example 22
Project: Quiver-alfred   Author: danielecook   File: peewee.py    License: MIT License 5 votes vote down vote up
def _connect(self, database, encoding=None, **kwargs):
        if not psycopg2:
            raise ImproperlyConfigured('psycopg2 must be installed.')
        conn = psycopg2.connect(database=database, **kwargs)
        if self.register_unicode:
            pg_extensions.register_type(pg_extensions.UNICODE, conn)
            pg_extensions.register_type(pg_extensions.UNICODEARRAY, conn)
        if encoding:
            conn.set_client_encoding(encoding)
        return conn 
Example 23
Project: Quiver-alfred   Author: danielecook   File: peewee.py    License: MIT License 5 votes vote down vote up
def _connect(self, database, **kwargs):
        if not mysql:
            raise ImproperlyConfigured('MySQLdb or PyMySQL must be installed.')
        conn_kwargs = {
            'charset': 'utf8',
            'use_unicode': True,
        }
        conn_kwargs.update(kwargs)
        if 'password' in conn_kwargs:
            conn_kwargs['passwd'] = conn_kwargs.pop('password')
        return mysql.connect(db=database, **conn_kwargs) 
Example 24
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: base.py    License: MIT License 5 votes vote down vote up
def setUp(self):
        self.connections = []
        for params in self.databases:
            self.connections.append(pymysql.connect(**params))
        self.addCleanup(self._teardown_connections) 
Example 25
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 5 votes vote down vote up
def realtestSocketAuth(self):
        with TempUser(self.connections[0].cursor(), TestAuthentication.osuser + '@localhost',
                      self.databases[0]['db'], self.socket_plugin_name) as u:
            c = pymysql.connect(user=TestAuthentication.osuser, **self.db) 
Example 26
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 5 votes vote down vote up
def realTestDialogAuthTwoQuestions(self):
        TestAuthentication.Dialog.fail=False
        TestAuthentication.Dialog.m = {b'Password, please:': b'notverysecret',
                                       b'Are you sure ?': b'yes, of course'}
        with TempUser(self.connections[0].cursor(), 'pymysql_2q@localhost',
                      self.databases[0]['db'], 'two_questions', 'notverysecret') as u:
            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_2q', **self.db)
            pymysql.connect(user='pymysql_2q', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db) 
Example 27
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 5 votes vote down vote up
def testMySQLOldPasswordAuth(self):
        if self.mysql_server_is(self.connections[0], (5, 7, 0)):
            raise unittest2.SkipTest('Old passwords aren\'t supported in 5.7')
        # pymysql.err.OperationalError: (1045, "Access denied for user 'old_pass_user'@'localhost' (using password: YES)")
        # from login in MySQL-5.6
        if self.mysql_server_is(self.connections[0], (5, 6, 0)):
            raise unittest2.SkipTest('Old passwords don\'t authenticate in 5.6')
        db = self.db.copy()
        db['password'] = "crummy p\tassword"
        with self.connections[0] as c:
            # deprecated in 5.6
            if sys.version_info[0:2] >= (3,2) and self.mysql_server_is(self.connections[0], (5, 6, 0)):
                with self.assertWarns(pymysql.err.Warning) as cm:
                    c.execute("SELECT OLD_PASSWORD('%s')" % db['password'])
            else:
                c.execute("SELECT OLD_PASSWORD('%s')" % db['password'])
            v = c.fetchone()[0]
            self.assertEqual(v, '2a01785203b08770')
            # only works in MariaDB and MySQL-5.6 - can't separate out by version
            #if self.mysql_server_is(self.connections[0], (5, 5, 0)):
            #    with TempUser(c, 'old_pass_user@localhost',
            #                  self.databases[0]['db'], 'mysql_old_password', '2a01785203b08770') as u:
            #        cur = pymysql.connect(user='old_pass_user', **db).cursor()
            #        cur.execute("SELECT VERSION()")
            c.execute("SELECT @@secure_auth")
            secure_auth_setting = c.fetchone()[0]
            c.execute('set old_passwords=1')
            # pymysql.err.Warning: 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead
            if sys.version_info[0:2] >= (3,2) and self.mysql_server_is(self.connections[0], (5, 6, 0)):
                with self.assertWarns(pymysql.err.Warning) as cm:
                    c.execute('set global secure_auth=0')
            else:
                c.execute('set global secure_auth=0')
            with TempUser(c, 'old_pass_user@localhost',
                          self.databases[0]['db'], password=db['password']) as u:
                cur = pymysql.connect(user='old_pass_user', **db).cursor()
                cur.execute("SELECT VERSION()")
            c.execute('set global secure_auth=%r' % secure_auth_setting) 
Example 28
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 5 votes vote down vote up
def testAuthSHA256(self):
        c = self.connections[0].cursor()
        with TempUser(c, 'pymysql_sha256@localhost',
                      self.databases[0]['db'], 'sha256_password') as u:
            if self.mysql_server_is(self.connections[0], (5, 7, 0)):
                c.execute("SET PASSWORD FOR 'pymysql_sha256'@'localhost' ='Sh@256Pa33'")
            else:
                c.execute('SET old_passwords = 2')
                c.execute("SET PASSWORD FOR 'pymysql_sha256'@'localhost' = PASSWORD('Sh@256Pa33')")
            db = self.db.copy()
            db['password'] = "Sh@256Pa33"
            # not implemented yet so thows error
            with self.assertRaises(pymysql.err.OperationalError):
                pymysql.connect(user='pymysql_256', **db) 
Example 29
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 5 votes vote down vote up
def test_utf8mb4(self):
        """This test requires MySQL >= 5.5"""
        arg = self.databases[0].copy()
        arg['charset'] = 'utf8mb4'
        conn = pymysql.connect(**arg) 
Example 30
Project: ServerlessCrawler-VancouverRealState   Author: MarcelloLins   File: test_connection.py    License: MIT License 5 votes vote down vote up
def test_init_command(self):
        conn = pymysql.connect(
            init_command='SELECT "bar"; SELECT "baz"',
            **self.databases[0]
        )
        c = conn.cursor()
        c.execute('select "foobar";')
        self.assertEqual(('foobar',), c.fetchone())
        conn.close()
        with self.assertRaises(pymysql.err.Error):
            conn.ping(reconnect=False)