How to Connect Python with SQL Database?
Python is a high-level, general-purpose, and very popular programming language. Basically, it was designed with an emphasis on code readability, and programmers can express their concepts in fewer lines of code. We can also use Python with SQL. In this article, we will learn how to connect SQL with Python using the ‘MySQL Connector Python module. The diagram given below illustrates how a connection request is sent to MySQL connector Python, how it gets accepted from the database and how the cursor is executed with result data.
SQL connection with Python
Connecting MySQL with Python
To create a connection between the MySQL database and Python, the connect() method of mysql.connector module is used. We pass the database details like HostName, username, and the password in the method call, and then the method returns the connection object.
The following steps are required to connect SQL with Python:
Step 1: Download and Install the free MySQL database from here.
Step 2: After installing the MySQL database, open your Command prompt.
Step 3: Navigate your Command prompt to the location of PIP. Click here to see, How to install PIP?
Step 4: Now run the commands given below to download and install “MySQL Connector”. Here, mysql.connector statement will help you to communicate with the MySQL database.
Download and install “MySQL Connector”
pip install mysql-connector-python
Step 5: Test MySQL Connector
To check if the installation was successful, or if you already installed “MySQL Connector”, go to your IDE and run the given below code :
If the above code gets executed with no errors, “MySQL Connector” is ready to be used.
Step 6: Create Connection
Now to connect SQL with Python, run the code given below in your IDE.
2. MySQL with Python¶
The ‘mysql-connector’ is not supported by Django-framework. The good option is ‘mysqlclient’ which is supported by Django as well.
$ mysql -u root -p Enter password: mysql> CREATE DATABASE pythonSQL; 2.2. Connect and load data¶
Following code can be used to connect and load the data to database. Note that, the commands in the c.execute(…) statements are exactly same as the commands in the previous chapters.
# create_fill_database.py import mysql.connector as mc # connect to database conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() # cursor to perform operations def create_table(): """ Create table in the database """ # optional: drop table if exists c.execute('DROP TABLE IF EXISTS writer') c.execute('CREATE TABLE writer \ ( \ id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, \ name VARCHAR(30) NOT NULL UNIQUE, \ age int \ )' ) def insert_data(): """ Insert data to the table """ c.execute("INSERT INTO writer (name) VALUES ('Pearl Buck')") c.execute(" INSERT INTO writer VALUES \ (NULL, 'Rabindranath Tagore', 80), \ (NULL, 'Leo Tolstoy', 82)" \ ) c.execute(" INSERT INTO writer (age, name) VALUES \ (30, 'Meher Krishna Patel')" \ ) def commit_close(): """ commit changes to database and close connection """ conn.commit() c.close() conn.close() def main(): """ execute create and insert commands """ create_table() insert_data() commit_close() # required for save the changes # standard boilerplate to call main function if __name__ == '__main__': main()
$ python create_fill_database.py
2.3. Read data from table¶
Following code can be used to read data from the table,
# read_database.py import mysql.connector as mc conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() def read_data(): c.execute('SELECT * FROM writer') writers = c.fetchall() # data is read in the form of list for writer in writers: # print individual item in the list print(writer) # data at each row is saved as tuple def main(): read_data() if __name__ == '__main__': main()
$ python read_database.py (1, 'Pearl Buck', None) (2, 'Rabindranath Tagore', 80) (3, 'Leo Tolstoy', 82) (4, 'Meher Krishna Patel', 30)
- In this way, we can get the data from the table and perform various operations on the data.
- Also, we can use all those queries with python, as queries in the execute statements are same as queries in previous chapter.
2.4. Connection in try-except block¶
We can use following code to put the connection string in the try except block, so that we can get proper message for not connecting with the database,
# connect_try.py import mysql.connector as mq from mysql.connector import errorcode try: conn = mq.connect(host='localhost', user='root', password='d', db='pythonSQL') print("Connected") except mq.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) else: print("Connection closed") conn.close()
$ python connect_try.py Connected Connection closed
$ python connect_try.py Something is wrong with your user name or password
$ python connect_try.py Database does not exist
© Copyright 2017, Meher Krishna Patel. Revision 31d452b4 .
Versions latest Downloads pdf html epub On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.