DataBases Connection

Drivers for MySQL

MySQL Connector

The default driver to work with MySQL is "MySQL Connector", its installation is done by entering the following command in the console:

pip install mysql-connector-python

To verify that the driver is correctly installed, you can run a Python file with the following line:

import mysql.connector

If no error is generated, it indicates that the driver is working properly.

Then the connection object must be made as follows:

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

PyMySQL Library

As an alternative to the "MySQL Connector", there is the "PyMySQL" library, which is installed with the following command in the console:

pip install PyMySQL

Luego el objeto de conexión se debe hacer de la siguiente manera:

import pymysql
if __name__ == '__main__':
    try:
        mydb = pymysql.connect(
            host="localhost",
            user="root",
            password="",
            db="mydatabasepython")
        with mydb.cursor() as mycursor:
            mycursor.execute(
                """CREATE TABLE customers (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255),
                    address VARCHAR(255))
                    """)
            del mycursor
            del mydb
    except pymysql.err.OperationalError as err:
        print('Connection failed')
        print(err)

From now on, the examples will be made using the “MySQL Connector”, but the structure is the same using the “PyMySQL” library, only the import of the driver/library and the creation of the “connect” object change.

Connect to the database engine

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

Release resources from the database connection

mycursor.close()
mydb.close()
del mycursor
del mydb

Create database

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

List existing databases

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password=""
)
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
    print(x)

Connect to a database

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="my_data_base_python"
)
mycursor = mydb.cursor()

Show existing tables

mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

Create table

Repeating the structure of the connection variable, after the cursor definition, add:

mycursor.execute(
    """CREATE TABLE customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        address VARCHAR(255))
        """)

Edit the structure of a table

mycursor.execute(
    "ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Delete a table

sql = "DROP TABLE IF EXISTS customers"
mycursor.execute(sql)

INSERT INTO

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="my_data_base_python"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
print("1 record inserted, ID:", mycursor.lastrowid)

In the case of inserting multiple rows

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
    ('Peter', 'Lowstreet 4'),
    ('Amy', 'Apple st 652'),
    ('Hannah', 'Mountain 21'),
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "was inserted.")

In the commands that require the update of the database, the following line must necessarily be included for the changes to take effect in the database:

mydb.commit()

SELECT FROM

Making a selection of all the fields of the table

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

Selecting specific columns

mycursor.execute("SELECT name, address FROM customers")

You could indicate the number of records you want to obtain, in the example 5 records are requested

myresult = mycursor.fetchmany(5)

In cases where I need to retrieve the first record obtained by the SELECT

myresult = mycursor.fetchone()

WHERE

The following example also uses the procedure to escape the data coming from the user, to prevent SQL Injection

sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

ORDER BY

sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

For descending order:

sql = "SELECT * FROM customers ORDER BY name DESC"

DELETE

sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

UPDATE

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

LIMIT

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

Starting from a specific position:

mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

JOIN

INNER JOIN

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

LEFT JOIN

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"

RIGHT JOIN

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

Drivers for PostgreSQL

The default driver to work with PostgreSQL is "psycopg2-binary", its installation is done by entering the following command in the console:

pip install psycopg2-binary

To use this driver, the following import and definition of the connect object must be carried out:

import psycopg2
if __name__ == '__main__':
    try:
        mydb = psycopg2.connect("""
        dbname='mydatabasepython'
        user=' postgres'
        password=''
        host=localhost
        """)
        with mydb.cursor() as mycursor:
            mycursor.execute(
                """CREATE TABLE customers (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255),
                    address VARCHAR(255))
                    """)
            del mycursor
            del mydb
    except psycopg2.OperationalError as err:
        print('Connection failed')
        print(err)

Another option is to set the connect object as follows:

#mydb = psycopg2.connect("postgresql: // user:password @ url/database")
mydb = psycopg2.connect("postgresql: // root: @ localhost/mydatabasepython")

Thanks for reading :)
I invite you to continue reading other entries and visiting us again soon.