{"id":3247,"date":"2014-10-19T22:40:01","date_gmt":"2014-10-20T06:40:01","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3247"},"modified":"2024-01-06T07:02:40","modified_gmt":"2024-01-06T14:02:40","slug":"python-connecting-mysql-databases","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/python-mysql\/python-connecting-mysql-databases\/","title":{"rendered":"Python MySQL – Connect to a MySQL Database in Python"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to connect to MySQL databases from Python using MySQL Connector\/Python API.<\/p>\n\n\n\n

This tutorial picks up where the Getting Started with MySQL Python Connector tutorial<\/a> left off.<\/p>\n\n\n\n

Setting up a sample database<\/h2>\n\n\n\n

First, download the following\u00a0pub<\/code> database, uncompress the zip file, and copy it to a directory such as C:\\temp\\pub.sql<\/code> :<\/p>\n\n\n\n

Download the Pub Sample Database<\/a><\/p>\n\n\n\n

Second, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to a MySQL server using the mysql client tool:<\/p>\n\n\n

mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Enter the password for the root<\/code> user.<\/p>\n\n\n
Enter password: ********<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SOURCE c:\\temp\\pub.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The command will create a new database called pub<\/code> with some tables.<\/p>\n\n\n\n
Fourth, switch the current database to pub<\/code>:<\/p>\n\n\n
USE<\/span> pub<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
SHOW<\/span> TABLES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output will be:<\/p>\n\n\n
+---------------+\r\n| Tables_in_pub |\r\n+---------------+\r\n| authors       |\r\n| book_author   |\r\n| books         |\r\n+---------------+\r\n3<\/span> rows in<\/span> set<\/span> (0.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, exit the mysql client tool:<\/p>\n\n\n
exit<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Connecting to the MySQL database<\/h2>\n\n\n\n
Create the following connect.py<\/code> file in the project directory and add the following code to the file:<\/p>\n\n\n
import<\/span> mysql.connector\r\n\r\n# Initialize a variable to hold the database connection<\/span>\r\nconn = None<\/span>\r\n\r\ntry<\/span>:\r\n    # Attempt to establish a connection to the MySQL database<\/span>\r\n    conn = mysql.connector.connect(host='localhost'<\/span>, \r\n                                   port=3306<\/span>,\r\n                                   database='pub'<\/span>,\r\n                                   user='<user>'<\/span>,\r\n                                   password='<password>'<\/span>)\r\n    \r\n    # Check if the connection is successfully established<\/span>\r\n    if<\/span> conn.is_connected():\r\n        print('Connected to MySQL database'<\/span>)\r\n\r\nexcept<\/span> mysql.connector.Error as<\/span> e:\r\n    # Print an error message if a connection error occurs<\/span>\r\n    print(e)\r\n\r\nfinally<\/span>:\r\n    # Close the database connection in the 'finally' block to ensure it happens<\/span>\r\n    if<\/span> conn is<\/span> not<\/span> None<\/span> and<\/span> conn.is_connected():\r\n        conn.close()\r\n<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
First, import the mysql.connector<\/code> module:<\/p>\n\n\n
import<\/span> mysql.connector<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, initialize a conn<\/code> variable to None<\/code>:<\/p>\n\n\n
conn = None<\/code><\/span><\/pre>\n\n\n
Third, use the connect()<\/code> method to connect to the MySQL server:<\/p>\n\n\n
conn = mysql.connector.connect(host='localhost'<\/span>, \r\n                                port=3306<\/span>,\r\n                                database='pub'<\/span>,\r\n                                user='<root>'<\/span>,\r\n                                password='<password>'<\/span>)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The connect()<\/code> method accepts host, port, database name, user, and password. You should change the connection details to yours.<\/p>\n\n\n\n
Note that you need to replace the user\/password with the actual user\/password.<\/p>\n\n\n\n
Fourth, check if the connection is created and display a message if the connection is created successfully: <\/p>\n\n\n
if<\/span> conn.is_connected():\r\n    print<\/span>('Connected to MySQL database'<\/span>)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
If an error occurs during the connection process, display an error in the except<\/code> block.<\/p>\n\n\n
print<\/span>(e)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the database connection in the finally<\/code> block:<\/p>\n\n\n
if<\/span> conn<\/span> is<\/span> not<\/span> None<\/span> and<\/span> conn<\/span>.is_connected<\/span>():\r\n        conn<\/span>.close<\/span>()<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Executing the connect.py module<\/h3>\n\n\n\n
To execute the connect.py<\/code> module, you follow these steps:<\/p>\n\n\n\n
First, open the Command Prompt on Windows or a Terminal on Unix-like systems.<\/p>\n\n\n\n
Second, go to the project directory.<\/p>\n\n\n
cd pub<\/code><\/span><\/pre>\n\n\n
Third, activate the virtual environment:<\/p>\n\n\n
\/venv\/scripts\/activate <\/code><\/span><\/pre>\n\n\n
Third, execute the connect.py<\/code> module:<\/p>\n\n\n
python<\/span> connect<\/span>.py<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Connected to MySQL database<\/code><\/span><\/pre>\n\n\n
The output indicates that we have connected to the MySQL server successfully.<\/p>\n\n\n\n
In this example, we’ve embedded the database configuration, including the password, directly into the code. However, it is not considered a best practice. <\/p>\n\n\n\n
To address this issue, let’s enhance the code by incorporating a separate database configuration file.<\/p>\n\n\n\n
Using a configuration file<\/h2>\n\n\n\n
First, create a database configuration file named app.ini<\/code> in the project directory and define a section called mysql with the following parameters:<\/p>\n\n\n
[mysql]\r\nhost = localhost\r\nport = 3306\r\ndatabase = pub\r\nuser = <user<\/span>><\/span>\r\npassword = <password<\/span>><\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new file config.py<\/code> in the project directory that reads the app.ini configuration file and returns a dictionary that contains the database connection:<\/p>\n\n\n
from<\/span> configparser import<\/span> ConfigParser\r\n\r\ndef<\/span> read_config<\/span>(filename='app.ini'<\/span>, section='mysql'<\/span>)<\/span>:<\/span>    \r\n    # Create a ConfigParser object to handle INI file parsing<\/span>\r\n    config = ConfigParser()\r\n    \r\n    # Read the specified INI configuration file<\/span>\r\n    config.read(filename)\r\n\r\n    # Initialize an empty dictionary to store configuration data<\/span>\r\n    data = {}\r\n\r\n    # Check if the specified section exists in the INI file<\/span>\r\n    if<\/span> config.has_section(section):\r\n        # Retrieve all key-value pairs within the specified section<\/span>\r\n        items = config.items(section)\r\n\r\n        # Populate the data dictionary with the key-value pairs<\/span>\r\n        for<\/span> item in<\/span> items:\r\n            data[item[0<\/span>]] = item[1<\/span>]\r\n    else<\/span>:\r\n        # Raise an exception if the specified section is not found<\/span>\r\n        raise<\/span> Exception(f'{section}<\/span> section not found in the {filename}<\/span> file'<\/span>)\r\n\r\n    # Return the populated data dictionary<\/span>\r\n    return<\/span> data\r\n\r\nif<\/span> __name__ == '__main__'<\/span>:\r\n    # Read the configuration from the default section ('mysql') in the 'app.ini' file<\/span>\r\n    config = read_config()\r\n\r\n    # Display the obtained configuration<\/span>\r\n    print(config)\r\n<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, modify the connect.py module that uses the config.py module to connect to the MySQL database:<\/p>\n\n\n
from<\/span> mysql.connector import<\/span> MySQLConnection, Error\r\nfrom<\/span> config import<\/span> read_config\r\n\r\n\r\ndef<\/span> connect<\/span>(config)<\/span>:<\/span>\r\n    \"\"\" Connect to MySQL database \"\"\"<\/span>\r\n    conn = None<\/span>\r\n    try<\/span>:\r\n        print('Connecting to MySQL database...'<\/span>)\r\n        conn = MySQLConnection(**config)\r\n\r\n        if<\/span> conn.is_connected():\r\n            print('Connection is established.'<\/span>)\r\n        else<\/span>:\r\n            print('Connection is failed.'<\/span>)\r\n    except<\/span> Error as<\/span> error:\r\n        print(error)\r\n    finally<\/span>:\r\n        if<\/span> conn is<\/span> not<\/span> None<\/span> and<\/span> conn.is_connected():\r\n            conn.close()\r\n            print('Connection is closed.'<\/span>)\r\n\r\n\r\nif<\/span> __name__ == '__main__'<\/span>:\r\n    config = read_config()\r\n    connect(config)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, execute the connect.py module:<\/p>\n\n\n
python<\/span> connect<\/span>.py<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you need to activate the virtual environment before executing this command.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the connect()<\/code> function or MySQLConnection<\/code> class to connect to the MySQL Server.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Fifth, display all tables from the pub<\/code> database using the SHOW TABLES<\/a><\/code> command:<\/p>\n\n\n
Third, execute the SQL statements<\/a> in the pub.sql<\/code> file using the SOURCE<\/code> command:<\/p>\n\n\n