Explain the many ways Python can connect to a MySQL database

Previous articleshared windows download mysql5.7 zip package configuration installation mysql

Follow-up options are available

① Create a database locally and use the navicat tool to export the database of the remote test server to the local one for learning operations and without affecting the data of the test server.

② Connect the database account and password of the test server, operate on the test server, the database account and password of the internal test server in the allocation of the account will be given to different accounts to do permissions restrictions, such as different accounts allowed to log in the way, the scope of the open database, the account can be read and write operations of the permissions are not the same, if it appears that you have been using the code to log in to the remote database server, you should check the account has the If you can't log in to the remote database server with the code, you should check whether the account has the permissions. (I personally test different accounts with the same code, one can operate successfully and the other reports an error connecting to the database; on the other hand, when configuring the visualization database in the navicat tool or pycharm ide, the account login needs to use the ssh channel authentication, but the same account connects to the same account with the python code but it does not need the ssh remote connection code at all, and you can log in successfully by providing the account number and the password. (Database privilege restriction related is really deep!)

This code implements a connection to a remote server

Since the MySQL server runs as a separate process and is served externally over the network, a Python enabled MySQL driver is required to connect to the MySQL server.

Currently, there are several MySQL drivers:

mysql-connector-python: is the official pure Python driver for MySQL;

MySQL-python: is a Python driver that encapsulates the MySQL C driver.

Install the MySQL driver:

pip install mysql-connector-python

Test whether the installation is successful, test whether the python can be successfully imported (import )

pip install MySQL-python (python3 not supported)

Test whether the installation is successful, test python can successfully import MySQLdb can be (import MySQLdb)

pip install mysqlclient (mysqlclient is fully compatible with MySQLdb and supports python3)

Test whether the installation is successful, test python can successfully import MySQLdb can be (import MySQLdb)

pip install PyMySQL

Test whether the installation is successful, test python can successfully import pymysql can (import pymysql)

Multiple ways to connect to MySQL database in python (Way 1)

# Way one:
import 
 
# Open a database connection
db = (host='*.*.*.*',
        port=3306,
        user='*',  # Database IP, username and password
        passwd='*',
        charset = 'utf8')  
 
# Use the cursor() method to create a cursor object cursor
cursor = ()
 
# Execute SQL queries using the execute() method
("show databases;")
("use database_name;")
("show tables;")
 
# Use the fetchone() method to fetch a single piece of data; use the fetchall() method to fetch all the data.
data = ()
 
for item in data:
  print(item[0])
 
# Close the database connection
()

Multiple ways to connect to MySQL database in python (way 2)

# Way two:
import MySQLdb
 
# Open a database connection
conn = (host='*.*.*.*',
      port=3306,
      user='*',
      passwd='*',
      charset = 'utf8'
      )
      
# Use the cursor() method to create a cursor object cursor
cursor = ()
 
# Execute SQL queries using the execute() method
("show databases;")
("use database_name;")
("show tables;")
("select * from tables_name")
 
# Use the fetchone() method to fetch a single piece of data; use the fetchall() method to fetch all the data.
data = ()
for item in data:
 print(item)
 
# Close the database connection
()

Multiple ways to connect to MySQL database in python (way three)

# Way three:
import pymysql
 
# Open a database connection
conn = (host='*.*.*.*',
      port=3306,
      user='*',
      passwd='*',
      charset = 'utf8'
      )
    
# Use the cursor() method to create a cursor object cursor
cursor = ()
 
# Execute SQL queries using the execute() method
("show databases;")
("use database_name;")
("show tables;")
("select * from tables_name")
 
# Use the fetchone() method to fetch a single piece of data; use the fetchall() method to fetch all the data.
data = ()
for item in data:
 print(item[0])
  
# Close the database connection
()

The above is a small introduction to the Python connection to the MySQL database way detailed integration, I hope to help you, if you have any questions please leave me a message, I will reply to you in a timely manner. I would also like to thank you very much for your support of my website!