Python pymysql.connect() Examples

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) 

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语句 

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() 

def connect(cls, host_endpoint, user, password, db):
        return pymysql.connect(host=host_endpoint, user=user, password=password, db=db) 

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") 

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) 

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() 

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) 

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) 

def _connect_sqlite(self, db_filename):
        if SQLITE_AVAILABLE:
            self.connection = sqlite.connect(db_filename)
            self.db_type = DataBase.SQLITE
            return True
        else:
            return False 

def __init__(self):
        self.cnx = None
        self.cursor = None
        self.connect() 

def _connect_mysql(self, url, db):
        if MYSQL_AVAILABLE:
            username, password, server, port = self._get_user_pass_host_port(url)
            if not port:
                port = 3306
            self.connection = mdb.connect(server, username, password, db, port)
            self.db_type = DataBase.MYSQL
            return True
        else:
            return False 

def _open_connection(self):
        return pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               password=self.password,
                               database=self._mysql_database) 

def ensure_connect(self):
        if not self._connect or self._max_idle < (time.time() - self._last_used):
            try:
                self._connect.ping()
            except:
                self.connect()
        self._last_used = time.time() 

def connect(self):
        self.close()
        self._connect = pymysql.connect(**self._db_options)
        self._connect.autocommit(True) 

def connect(self):
        self.cnx = pymysql.connect(user="root", host="127.0.0.1")
        self.cursor = self.cnx.cursor()
        return self.cursor 

def __init__(self, **kwargs):
        super(MySql, self).__init__(**kwargs)

        self.conn = pymysql.connect(**kwargs)
        self.cursor = self.conn.cursor()

        try:
            self.conn.select_db(config.database)
        except:
            self.create_database(config.database)
            self.conn.select_db(config.database) 

def _open_connection(self):
        return cx_Oracle.connect(self.user,
                                 self.password,
                                 "%s:%d/%s" % (self.host, self.port, self._oracle_database)) 

def setup_method(self, request, datapath):
        pymysql = pytest.importorskip('pymysql')
        pymysql.connect(host='localhost', user='root', passwd='',
                        db='pandas_nosetest')
        try:
            pymysql.connect(read_default_group='pandas')
        except pymysql.ProgrammingError:
            raise RuntimeError(
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf.")
        except pymysql.Error:
            raise RuntimeError(
                "Cannot connect to database. "
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf.")

        self.method = request.function 

def setup_class(cls):
        pymysql = pytest.importorskip('pymysql')
        pymysql.connect(host='localhost', user='root', passwd='',
                        db='pandas_nosetest')
        try:
            pymysql.connect(read_default_group='pandas')
        except pymysql.ProgrammingError:
            raise RuntimeError(
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf.")
        except pymysql.Error:
            raise RuntimeError(
                "Cannot connect to database. "
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf.") 

def setup_method(self, request, datapath):
        self.method = request.function
        self.conn = sqlite3.connect(':memory:')

        # In some test cases we may close db connection
        # Re-open conn here so we can perform cleanup in teardown
        yield
        self.method = request.function
        self.conn = sqlite3.connect(':memory:') 

def connect(cls):
        return sqlite3.connect(':memory:') 

def connect(cls):
        url = 'postgresql+{driver}://postgres@localhost/pandas_nosetest'
        return sqlalchemy.create_engine(url.format(driver=cls.driver)) 

def test_read_procedure(self):
        import pymysql
        # see GH7324. Although it is more an api test, it is added to the
        # mysql tests as sqlite does not have stored procedures
        df = DataFrame({'a': [1, 2, 3], 'b': [0.1, 0.2, 0.3]})
        df.to_sql('test_procedure', self.conn, index=False)

        proc = """DROP PROCEDURE IF EXISTS get_testdb;

        CREATE PROCEDURE get_testdb ()

        BEGIN
            SELECT * FROM test_procedure;
        END"""

        connection = self.conn.connect()
        trans = connection.begin()
        try:
            r1 = connection.execute(proc)  # noqa
            trans.commit()
        except pymysql.Error:
            trans.rollback()
            raise

        res1 = sql.read_sql_query("CALL get_testdb();", self.conn)
        tm.assert_frame_equal(df, res1)

        # test delegation to read_sql_query
        res2 = sql.read_sql("CALL get_testdb();", self.conn)
        tm.assert_frame_equal(df, res2) 

def connect(cls):
        url = 'mysql+{driver}://root@localhost/pandas_nosetest'
        return sqlalchemy.create_engine(url.format(driver=cls.driver),
                                        connect_args=cls.connect_args) 

def connect(cls):
        return sqlalchemy.create_engine('sqlite:///:memory:') 

def test_drop_table(self):
        temp_conn = self.connect()

        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame')

        pandasSQL.drop_table('temp_frame')

        assert not temp_conn.has_table('temp_frame') 

def test_create_table(self):
        temp_conn = self.connect()
        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame') 

def get_connection(cls):
        return psycopg2.connect(host="127.0.0.1",
                                user="piiuser",
                                password="p11secret",
                                database="piidb"
                                ) 

def connect(cls):
        raise NotImplementedError()