Shulou(Shulou.com)06/02 Report--

The main content of this article is to explain "what is the method of Python accessing the database through the jumping board", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the method of Python accessing the database through the jumping board"?

What is a springboard?

So what exactly does it do?

Now some larger Internet enterprises often have a large number of servers, in order to be able to unify and facilitate management, operation and maintenance personnel through the jumping machine to manage the corresponding servers. When we access the server, we first log in to the jump machine, and then enter the corresponding server. To some extent, it not only improves the data security of the server, but also improves the maintainability of the server.

Sshtunnel connection fortress machine

The essence of the springboard machine is still ssh connection, and the theory of making wheels by paramiko can also be realized. Sshtunnel is recommended here to save your own writing.

Understand the principle of the springboard, the configuration here is easier to understand. Take this picture as an example. First, you need to access the jumping machine. In fact, it is the most common ssh connection, which can be an account password or a key login. If it is a key, you need to add your own public key to a license file in the .ssh directory of the jumping machine.

Finally, you need to add the ip and port of the target host. With this understanding, you will know how to configure the following parameters

Local_bind_address may not be configured. If it is not configured, the local port number will be obtained randomly. If specified, it will occupy the local fixed port number. It is recommended to configure this parameter.

Enter the local port because the port of the target server ip will be mapped to the local port, and then the target port can be accessed by accessing the local port.

From sshtunnel import SSHTunnelForwarderserver = SSHTunnelForwarder ((host_jump, int (port_jump)), # configuration ssh_pkey=ssh_pk_jump of jumper machine, # access method of key, if password ssh_password=??? Ssh_username=user_name_jump, remote_bind_address= (host_mysql, int (port_mysql)) # configuration of database server local_bind_address= (local_ip, local_port) # Open local forwarding port) server.start () server.close ()

Some people are used to using context managers and look more comfortable.

From sshtunnel import SSHTunnelForwarderwith SSHTunnelForwarder ((host_jump, int (port_jump), # configuration ssh_pkey=ssh_pk_jump of jumper machine, # key access method, if password ssh_password=??? Ssh_username=user_name_jump, remote_bind_address= (host_mysql, int (port_mysql)) # configuration of database server local_bind_address= (local_ip, local_port) # Open local forwarding port) as server server.start () connects to the database

Once the channel is established, the target database port can be accessed through the locally mapped port.

Common mysql database access such as mysqldb,pymysql,mysql.connector

Mysqldb is similar to pymysql, except that the former applies to the version of python2, the latter applies to the version of python3, and mysql.connector is officially recommended.

There are some differences between pymysql and mysql.connector

What I use here is mysql.connector, which is always unable to connect in practice, but pymysql is OK.

Finally found a solution.

The following is an example of my mysql.connector connection database.

With SSHTunnelForwarder ((host_jump, int (port_jump), # configuration ssh_pkey=ssh_pk_jump of jumper machine, # key access method, if password ssh_password=??? Ssh_username=user_name_jump, remote_bind_address= (host_mysql, int (port_mysql)) # configuration of database server local_bind_address= (local_ip, local_port) # Open local forwarding port) as server server.start () conn = mysql.connector.connect (* * {'host':' 127.0.0.1') 'port': server.local_bind_port, 'user': dbcfg ["user"],' password': dbcfg ["password"], 'db': dbcfg ["db"],' charset': dbcfg ["charset"] 'collation': dbcfg ["collation"],' use_unicode': dbcfg ["use_unicode"], 'use_pure': True}) cursor = conn.cursor (dictionary=True) cursor.execute (sql) Params) rows = cursor.fetchall () # conn.commit () cursor.close () conn.close ()

In this way, the database can be accessed normally.

Code refactoring and optimization

First of all, as far as the above code is concerned, a jump engine connection is required for each query, which is extremely inefficient for a sudden large number of requests in a short period of time. So it is best to be able to save the established pipeline.

Secondly, if multiple databases are accessed at the same time, there is a scenario with or without jumping machine, so it is best to connect all the databases first, save the conn in the dictionary, call the corresponding conn according to the machine to be accessed, and then release it uniformly.

Def get_slave_conn_byProxy (dbcfg): server = SSHTunnelForwarder (eval (dbcfg ["addr"]), ssh_username=dbcfg ["ssh_username"], ssh_pkey=os.getenv ("HOME") + "/ .ssh/id_rsa", remote_bind_address=eval (dbcfg ["remote_bind_address"]) Local_bind_address=eval (dbcfg ["local_bind_address"]) # Open local forwarding port) server.start () conn = get_db_connection ({'host':' 127.0.0.1, 'port': server.local_bind_port,' user': dbcfg ["user"] 'password': dbcfg ["password"], 'db': dbcfg ["db"],' charset': dbcfg ["charset"], 'collation': dbcfg ["collation"] 'use_unicode': dbcfg ["use_unicode"],' use_pure': True}) return server Conndef get_db_connection (dbcfg): # logger.info (f "= debug db config: {dbcfg}") conn = mysql.connector.connect (* * dbcfg) return conn# determines whether it is necessary to jump through the jumping machine by entering the connection parameters def get_slave_conn (dbcfg): if "addr" in dbcfg: return get_slave_conn_byProxy (dbcfg) else: conn = get_db_connection (dbcfg) return None Conn def read_slave_db_data (sql, conn, params=None, use_pdf=True): cursor = conn.cursor (dictionary=True) cursor.execute (sql, params) rows = cursor.fetchall () # conn.commit () cursor.close () if use_pdf: return pd.DataFrame (rows) else: return rows I believe that everyone has a deeper understanding of "what is the method of Python accessing the database through the jumping board". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.