How To Install Mysql Connector In Jupyter Notebook

How to use Python with MySQL

What is Python? Well, it is a high-level and general-purpose programming language that can be used for web development, data science and scripting. According to TIOBE index, Python is ranked top ten near popular programming linguistic communication. In this commodity, I will exist sharing how we can employ Python to interact with MySQL database.

Python Setup

1. Install Anaconda — https://www.anaconda.com/distribution/
2. Select python version 3 (Version ii will stop receiving security updates by 2022)

NOTE: Anaconda is a python and R distribution that aims to provide everything you demand:

• core python language,
• python packages,
• IDE/editor — Jupyter and Spyder
• Package director — Conda, for managing your packages)

MySql Setup

1. Download and install MySql Community Server — https://dev.mysql.com/downloads/mysql/
2. During the installation setup, you will exist prompted for a "root" password in the server configuration footstep.
3. Download and install MySql Workbench — https://dev.mysql.com/downloads/workbench/
4. Workbench is GUI tool that allows u.s.a. to manage your MySql database.
5. Launch workbench, at the home page, setup a new connection contour with the configuration (Connectedness method: Standard (TCP/IP), Hostname: 127.0.0.1,Port:3306,Username: root, Countersign: yourpassword) and test your connexion.
6. Double click on your local instance and information technology should bring you the schemas view where you lot tin can encounter all your databases and tables.

MySql-Connector-Python Setup

1. This is the python commuter for connecting to MySql server.
2. Using terminal and conda to download

          conda install -c anaconda mysql-connector-python        

Connect to MySql

1. Launch Anaconda-Navigator to bring you a list of applications available to install. The application we are interested in is Jupyter Notebook which is a web-based python IDE. Install and launch it.
2. In the notebook, create a new python file. In the beginning prison cell, write the following code to test the mysql connectedness.

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="password"
)
            
print(mydb)
                  

3. If successful, you should become an object returned with its memory accost

          <mysql.connector.connection_cext.CMySQLConnection object at 0x10b4e1320>        

Create Database

1. Allow's create a database called mydatabase.

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="countersign"
)
            
mycursor = mydb.cursor()
            
mycursor.execute("CREATE              DATABASE              mydatabase")
                  

2. Next, nosotros will try to connect to this new database.

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="countersign",
                              database="mydatabase"              
)
                  

Create Table

1. In our new database, let's create a new table called customer to concord information related to your customers.
2. In the sql below, we are creating a tabular array with title customers with iii columns of championship id, proper noun and address. Column id stores data of type integer denoted by INT . Column id is meant to store unique key for each tape. This is done using the Principal KEY . It should be likewise incremented every bit new tape is added by AUTO_INCREMENT .
3. For column proper name and accost, it stores data of type character of maximum 255, denoted by VARCHAR(255) .

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="password",
              database="mydatabase"
)
            
mycursor = mydb.cursor()
            
mycursor.execute("CREATE Tabular array customers (id INT AUTO_INCREMENT Master KEY, name VARCHAR(255), accost VARCHAR(255))")
                  

Add record into table

1. Allow'southward add some data into our new table.

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="password",
              database="mydatabase"
)
            
mycursor = mydb.cursor()
            
sql = "INSERT              INTO customers (name, accost) VALUES (%southward, %s)"
                    val = ("David", "California")          mycursor.execute(sql, val)
                          
mydb.commit()
                        
impress(mycursor.rowcount, "record(s) inserted.")        

2. To insert multiple data, group the information within square brackets, separated past comma. Supercede execute() with executemany().

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "INSERT INTO customers (name, address) VALUES (%southward, %due south)"          val = [
            ("Lily", "California"),
            ("David", "San Francisco"),
            ("Micheal", "Las Vegas"),
            ("Sarah", "New York")
]          mycursor.executemany(sql, val)          mydb.commit()          print(mycursor.rowcount, "record(s) inserted.")        

Select record from table

1. To fetch all data from tabular array, use SELECT * , whereby asterisks means all.

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="password",
              database="mydatabase"
)
            
mycursor = mydb.cursor()
                    sql = "SELECT * FROM customers"          mycursor.execute(sql)
myresult = mycursor.fetchall()
            
for 10 in myresult:
              print(x)
                  

ii. To select specific column, use SELECT <column_title> instead of *. For case SELECT name FROM customers .

3. Instead of fetchall(), we can utilise fetchone() to call up first row of result.

4. Nosotros tin can apply filter to our search using WHERE keyword. NOTE: Notice how the query value is stored in separate variable. This is a technique called parameterized queries to foreclose SQL injection.

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "SELECT * FROM customers WHERE address = %s"
adr = ("California", )          mycursor.execute(sql, adr)          myresult = mycursor.fetchall()          for 10 in myresult:
            print(ten)        

five. Nosotros tin likewise limit the number of search result by adding LIMIT .

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="password",
              database="mydatabase"
)
            
mycursor = mydb.cursor()
            
mycursor.execute("SELECT * FROM customers              LIMIT              5")
            
myresult = mycursor.fetchall()
            
for x in myresult:
              impress(ten)
                  

Sort Records

1. We can think information from tabular array in ascending order. To arrive in descending order, add together DESC after proper name.

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "SELECT * FROM customers WHERE accost = %s            Gild past name"
adr = ("California", )          mycursor.execute(sql, adr)          myresult = mycursor.fetchall()          for x in myresult:
            impress(x)        

Delete a record

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "DELETE FROM customers WHERE address = %due south"
adr = ("California", )          mycursor.execute(sql, adr)
mydb.commit()
impress(mycursor.rowcount, "record(s) deleted.")        

Driblet a table

          import mysql.connector
mydb = mysql.connector.connect(
              host="localhost",
              user="root",
              passwd="password",
              database="mydatabase"
)
            
mycursor = mydb.cursor()
            
sql = "DROP Tabular array customers"
            
mycursor.execute(sql)
                  

Update Tape

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "UPDATE customers Set up address = %southward WHERE name = %southward"
val = ("California", "John", )
mycursor.execute(sql, val)          mydb.commit()
print(mycursor.rowcount, "record(s) updated.")        

Bring together Table

1. Allow's create 2 more tables, one is called products storing information such equally product_id, name of product and their prices. Another table would be orders , storing all orders with each containing order_id, product_id and customer_id. The reason why we do not leap order_id with product proper noun or customer proper noun is because these values might alter in future and to prevent updating value in multiple tables, we usually stick to value that is constant, in this example their alphabetize number.
2. Create products table

          import mysql.connector
mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)
mycursor = mydb.cursor()          mycursor = mydb.cursor()          sql = "CREATE            Table            products            (id            INT AUTO_INCREMENT Primary Central,            proper name            VARCHAR(255),            toll            VARCHAR(255))"          mycursor.execute(sql)          sql = "INSERT            INTO            products            (proper noun,            price) VALUES (%s, %due south)"
val = [
            ("macbook", "2000"),
            ("iphone", "1000"),
            ("apple watch", "500")
]
mycursor.executemany(sql, val)
mydb.commit()
impress(mycursor.rowcount, "tape(due south) inserted.")        

3. Create orders tabular array

          import mysql.connector
mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)
mycursor = mydb.cursor()          mycursor = mydb.cursor()          sql = "CREATE            TABLE            orders            (id            INT AUTO_INCREMENT Main KEY,            customer_id            INT,            product_id            INT)"          mycursor.execute(sql)          sql = "INSERT            INTO            orders            (customer_id,            product_id) VALUES (%southward, %s)"
val = [
            ("one", "one"),
            ("one", "2"),
            ("two", "3"),
            ("3", "3")
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(south) inserted.")        

4. Next we would like to join ii tables. In that location are ii types of JOIN query which are the INNER Bring together and OUTER JOIN. The former only shows results in which all criteria of joining are met meaning showing records with mutual column.

NOTE: JOIN keyword by default means INNER Bring together, hence you can employ JOIN or INNER Join.

5. Nosotros are going to prove a list of all customers with their respective order ids. The relationship between customer and their orders are stored in orders tabular array. To do this, we are to show client name and guild id from a joined tabular array of customers and orders with criteria on matching customer id.

Annotation: Y'all tin can add an allonym to each table and reference cavalcade name from alias.

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "SELECT \
            c.name, o.id\
            FROM customers c\
            Bring together orders o ON c.id = o.customer_id\
            "          mycursor.execute(sql)          myresult = mycursor.fetchall()          for x in myresult:
            print(x)        

6. Below shows the effect of using JOIN. Notice the tabular array does non evidence the full list of customers because some customers do not accept whatever order id.

          ('Lily', 1)
('Lily', ii)
('David', 3)
('Micheal', four)        

7. To demonstrate OUTER JOIN, this time nosotros are going to join orders to customers table merely we are going to SELECT FROM customers instead of orders.

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "SELECT \
            c.name, o.id\
            FROM customers c\
            LEFT Join orders o ON c.id = o.customer_id\
            "          mycursor.execute(sql)          myresult = mycursor.fetchall()          for ten in myresult:
            print(x)        

viii. Below shows the effect using LEFT Join. Notice now nosotros have "Sarah" with no social club id in the result. That is the office of LEFT Bring together, meaning to show the total records of the LEFT table (customer) irrespective if the criteria is met. Correct JOIN does the same affair except information technology shows the full records of the right table.

          ('Lily', 1)
('Lily', 2)
('David', 3)
('Micheal', 4)
('Sarah', None)        

9. Now, let's go back to INNER Bring together but with multiple tables. Since we would like to show a table of orders, containing social club id, customer name, production name and production toll, our SELECT query should be FROM orders table. Next we would like to join orders with products (by finding matching production id) and customers tabular array(by finding matching customer id).

          import mysql.connector          mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="password",
            database="mydatabase"
)          mycursor = mydb.cursor()          sql = "SELECT \
            o.id, c.name, p.name, p.price\
            FROM orders o\
            JOIN products p ON o.product_id = p.id\
            Join customers c ON o.customer_id = c.id"          mycursor.execute(sql)          myresult = mycursor.fetchall()          for x in myresult:
            print(10)        

10. Below are the results from the JOIN query with multiple tables.

          (1, 'Lily', 'macbook', '2000')
(2, 'Lily', 'iphone', 'one thousand')
(iii, 'David', 'apple tree picket', '500')
(4, 'Micheal', 'apple scout', '500')        

11. Other queries include

• NATURAL Bring together — automatic joining past the database
• Cross Join — listing of all possible combinations
• UNION — combination of 2 or more than queries.

References

• How SQL injection works — https://tableplus.io/weblog/2018/08/sql-injection-assail-explained-with-case.html
• SQL Injection Prevention Cheat Sail — https://github.com/OWASP/CheatSheetSeries/blob/master/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.md

Source: https://medium.com/@tattwei46/how-to-use-python-with-mysql-79304bee8753

Posted by: dixonhindat.blogspot.com

Share this post