Programming Languages war Python, PHP, Javascrip (Nodejs) Connect to MySQL

Hey my friends, a new post about this war. I don't know which is better, but I'll try to figure it out. I hope y'all enjoy this series. Here we go!

It's regular in the job environment to have to connect to databases in this case, we're going to connect with MySQL.

You only need to have to install MySQL server, in my case I had to create a database called onepiece and a table called crews, crews has 4 fields, id, name, ship, captain.

Python

First, you need to install a driver called "mysql-connector-python" this driver is to connect to the database and can find, insert, update and delete in this database. With this driver you can do a develop to save your data in MySQL.

python -m pip install mysql-connector-python

Here is the code of python, I'm going to add comments into language for explaining all methods or functions.

# Import driver for connecting to Mysql.
import mysql.connector

# It's not necessary, but It's a good way to show organized data.
from pprint import pprint

# Do you remember how to use the class? We have another example.
class CRUD():

  # Method constructor, with 4 parameters, host: "IP of server mysql", user: "Database's user", password: "Database's password", database: "Database's name"
  def __init__(self, host, user, password, database):
    # A method for connecting to database.
    self.mydb = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
  # Method to create a new row.
  def create(self, data):
    # This is the string of syntax in the SQL language, "INSERT INTO table (fields) VALUES (%typeofdata)" is to say you want to add a new row. This way is a good practice for avoiding SQL injection.
    sql = "INSERT INTO crews (name, ship, captain) VALUES (%s, %s, %s)"
    # The initialize method of executing queries.
    mycursor = self.mydb.cursor()
    # Execute query and put together data. Data has to be in the same order that you want to put into the SQL query.
    mycursor.execute(sql, data)
    # Save data in database.
    self.mydb.commit()
    # Return id inserted.
    return mycursor.lastrowid

  # Method to find and print all rows from the table.
  def find(self):
    # This is the string of syntax in the SQL language, find all data into crews.
    sql = "SELECT * FROM crews"
    # The initialize method of executing queries.
    mycursor = self.mydb.cursor()
    # Execute query.
    mycursor.execute(sql)
    for result in mycursor.fetchall():
      pprint(result)

  # Method to find One row from the table.
  def findOne(self, data):
    # This the string of syntax in SQL language, find all data into crews where captian is equal to data recived.
    sql = "SELECT * FROM crews WHERE captain = %s"
    # The initialize method of executing queries.
    mycursor = self.mydb.cursor()
    # Execute query and put together data. Data has to be in the same order that you want to put into the SQL query.
    mycursor.execute(sql, [data])
    # Get only one coincidence.
    myresult = mycursor.fetchone()
    print('Find One')
    return myresult

  # Method to update One registry by Id.
  def update(self, id, data):
    # This is the string of syntax in the SQL language, "UPDATE table SET field = %typeofdata WHERE field = %typeofdata" is to say you want to update a row. This way is a good practice for avoiding SQL injection.
    sql = "UPDATE crews SET name = %s, ship = %s, captain = %s WHERE id = %s"
    # The initialize method of executing queries.
    mycursor = self.mydb.cursor()
    # In this case I get a list with id, I delete this register.
    data.pop(0)
    # This is a way for join two arrays.
    val = data + [id]
    # Execute query and put together data. Data has to be in the same order that you want to put into the SQL query.
    mycursor.execute(sql, val)
    # Save data in database.
    self.mydb.commit()
    # Return row count updated.
    return mycursor.rowcount

  # Method to delete One registry by Id.
  def delete(self, id):
    # This is the string of syntax in the SQL language, "DELETE FROM table WHERE id = %typeofdata" is to say you want to delete a row. This way is a good practice for avoiding SQL injection.
    sql = "DELETE FROM crews WHERE id = %s"
    # The initialize method of executing queries.
    mycursor = self.mydb.cursor()
    # Execute query and put together data. Data has to be in the same order that you want to put into the SQL query.
    mycursor.execute(sql, [id])
    # Save data in database.
    self.mydb.commit()
    # Return row count deleted.
    return mycursor.rowcount

# Initialize class.
onepiece = CRUD("localhost", "root", "password", "onepiece")

# Find one register.
crew = onepiece.findOne("Luffy")

# Create one register.
id = onepiece.create(['Red-Haired Pirates',
        'Red Force',
        'Shanks'])

# Crew is a tuple, tuple is not editable, then I have to change to list.
crew = list(crew)
# Edit field that you want to update.
crew[2] = "Sunny"

# Update and print count of rows updated.
print(f"Updated documents: {onepiece.update(crew[0], crew)}")

# Print all registers from the table after the update.
onepiece.find()

# Delete and print count of rows deleted.
print(f"Deleted documents: {onepiece.delete(id)}")

# Print all registers from the table after deleting.
onepiece.find()

PHP

First, you need to install a driver called "php-mysql" this driver is to connect to the database and can find, insert, update and delete in this database. With this driver you can do a develop to save your data in MySQL.

apt install php-mysql

Here is the code of PHP, I'm going to add comments into language for explaining all methods or functions. Here, it's not necessary to import the driver because when you install php-mysql you can use the driver by default.

<?php
// Do you remember how to use the class? We have another example.
class CRUD
{
    // Method constructor, with 4 parameters, host: "IP of server mysql", user: "Database's user", password: "Database's password", database: "Database's name"
    public function __construct($host, $user, $password, $database)
    {
        // A method for connecting to database.
        $this->mysqli = new mysqli($host, $user, $password, $database);
    }

    // Method to create a new row.
    public function create($data)
    {
        // This method is to prepare the string of syntax in the SQL language, This is the string of syntax in the SQL language, "INSERT INTO table (fields) VALUES (?)" is to say you want to add a new row. This way is a good practice for avoiding SQL injection.
        $stmt = $this->mysqli->prepare("INSERT INTO crews (name, ship, captain) VALUES (?, ?, ?)");
        // Here, you pass all types of parameters and parameters. Data has to be in the same order that you want to put into the SQL query.
        $stmt->bind_param('sss', $data[0], $data[1], $data[2]);
        // Execute query.
        $stmt->execute();
        // Return id inserted.
        return $stmt->insert_id;
    }

    // Method to find and print all rows from the table.
    public function find()
    {
        echo "Find \n";
        // Execute query. This method is if you don't have to pass any data. Find all data into crews.
        $res = $this->mysqli->query("SELECT * FROM crews");
        // To validate if it has any rows.
        if ($res) {
            // Print rows.
            while ($row = $res->fetch_assoc()) {
                var_dump($row);
            }
        }
    }

    // Method to find One row from the table.
    public function findOne($data = '')
    {
        echo "Find One \n";
        // This method is to prepare the string of syntax in the SQL language, This is the string of syntax in the SQL language, "SELECT * FROM table WHERE field =  ?" to find all data into the table where field is equal to the data received. This way is a good practice for avoiding SQL injection.
        $stmt = $this->mysqli->prepare("SELECT * FROM crews WHERE captain =  ?");
        // Here, you pass all types of parameters and parameters. Data has to be in the same order that you want to put into the SQL query.
        $stmt->bind_param('s', $data);
        // Execute query.
        $stmt->execute();
        // To get results.
        $res = $stmt->get_result();
        // Return row.
        return $res->fetch_assoc();
    }

    // Method to update One registry by Id.
    public function update($id, $data)
    {
        // In this case I get an array with id, I delete this register.
        unset($data['id']);
        // This method is to prepare the string of syntax in the SQL language, This is the string of syntax in the SQL language, "UPDATE table SET field = ? WHERE id = ?" to update a row. This way is a good practice for avoiding SQL injection.
        $stmt = $this->mysqli->prepare("UPDATE crews SET name = ?, ship = ?, captain = ? WHERE id = ?");
        // Here, you pass all types of parameters and parameters. Data has to be in the same order that you want to put into the SQL query.
        $stmt->bind_param('sssi', $data['name'], $data['ship'], $data['captain'], $id);
        // Execute query.
        $stmt->execute();
        // Return row count updated.
        return $stmt->affected_rows;

    }

    // Method to delete One registry by Id.
    public function delete($id)
    {
        // This method is to prepare the string of syntax in the SQL language, This is the string of syntax in the SQL language, "DELETE FROM crews WHERE id =  ?" to delete a row. This way is a good practice for avoiding SQL injection.
        $stmt = $this->mysqli->prepare("DELETE FROM crews WHERE id =  ?");
        // Here, you pass all types of parameters and parameters. Data has to be in the same order that you want to put into the SQL query.
        $stmt->bind_param('i', $id);
        // Execute query.
        $stmt->execute();
        // Return row count deleted.
        return $stmt->affected_rows;

    }
}

// Initialize class.
$onepiece = new CRUD("localhost", "root", "password", "onepiece");

// Find one register.
$crew     = $onepiece->findOne("Luffy");

// Create one register.
$id = $onepiece->create([
    'Red-Haired Pirates',
    'Red Force',
    'Shanks',
]);

// Edit field that you want to update.
$crew['ship'] = 'Sunny';

// Update and print count of rows updated.
echo "Updated documents: " . $onepiece->update($crew['id'], $crew) . "\n";

// Print all registers from the table after the update.
$onepiece->find();

// Delete and print count of rows deleted.
echo "Deleted documents: " . $onepiece->delete($id) . "\n";

// Print all registers from the table after deleting.
$onepiece->find();
?>

Javascript

First, you need install a module called "mysql" this module is to connect to the database and can find, insert, update and delete in this database. With this module you can do a develop to save your data in MySQL.

For this example, I did all code with async/await method.

npm install mysql

Here is code of Javascript, I'm going to add comments into language for explaining all methods or functions.

// To import the module for connecting to MySQL.
const mysql = require('mysql');
// To import the module for executing the better way queries.
const util = require('util');

// Do you remember how to use the class? We have another example.
class CRUD {
  // Method constructor, with 4 parameters, host: "IP of server mysql", user: "Database's user", password: "Database's password", database: "Database's name"
  constructor(host, user, password, database) {
    // A method for connecting to database
    this.connection = mysql.createConnection({
      host,
      user,
      password,
      database
    });
    // Set query with a better way for its execution.
    this.query = util.promisify(this.connection.query).bind(this.connection);
  }

  // Method to create a new row.
  async create(data) {
    // This method gets two parameters, first SQL query 'INSERT INTO table (fields) VALUES (?)' is to say you want to add a new row, and second data in the same order.
    const result = await this.query(
      `INSERT INTO crews (name, ship, captain) VALUES (?,?,?)`,
      data
    );
    // Return id inserted.
    return result.insertId;
  }

  // Method to find and print all rows from the table.
  async find() {
    // This method gets two parameters, first SQL query 'SELECT * FROM table' to get all results, and second data in the same order, the second parameter is optional.
    const rows = await this.query(`SELECT * FROM crews`);
    console.log(rows);
  }

  // Method to find One row from the table.
  async findOne(data = {}) {
    // This method gets two parameters, first SQL query 'SELECT * FROM table WHERE field =  ?' To find all data into the table where field is equal to the data received, and second data in the same order.
    const rows = await this.query(`SELECT * FROM crews WHERE captain =  ?`, [
      data
    ]);
    // Return row.
    return rows[0];
  }

  // Method to update One registry by Id.
  async update(id, data) {
    // In this case I get an array with id, I delete this register.
    delete data.id;
    // This method gets two parameters, first SQL query 'UPDATE table SET field = ? WHERE id = ?' To update a row, and second data in the same order.
    const result = await this.query(
      `UPDATE crews SET name = ?, ship = ?, captain = ? WHERE id = ?`,
      [...Object.values(data), id]
    );
    // Return row count updated.
    return result.affectedRows;
  }

  // Method to delete One registry by Id.
  async delete(id) {
    // This method gets two parameters, first SQL query 'DELETE FROM table WHERE id = ?' To find all data into the table where field is equal to the data received, and second data in the same order.
    const result = await this.query(`DELETE FROM crews WHERE id = ?`, [id]);
    // Return row count deleted.
    return result.affectedRows;
  }
}

// I have to create a main function, because I developed with async/await.
const main = async () => {
  // Initialize class.
  const onepiece = new CRUD('localhost', 'root', 'password', 'onepiece');

  // Find one register.
  const crew = await onepiece.findOne('Luffy');

  // Create one register.
  const id = await onepiece.create([
    'Red-Haired Pirates',
    'Red Force',
    'Shanks'
  ]);


  // Edit field that you want to update.
  crew.ship = 'Sunny';

  // Update and print count of row updated.
  console.log(
    'Updated documents: ' + (await onepiece.update(crew.id, crew))
  );

  // Print all registers from the table after the update.
  await onepiece.find();

  // Delete and print count of row deleted.
  console.log('Deleted documents: ' + (await onepiece.delete(id)));

  // Print all registers from the table after deleting.
  await onepiece.find();
};

// Run main function.
main();

Conclusion

Structure: Change a lot, because every modules/drivers are different. Lines: Python: 68, PHP: 75, Javascript: 70 Easy to understand: For me It's more clear javascript, because you can add more fields without you have to make a big change.

Would you like that I write more info about mysql?

I hope you enjoy my post and remember that I am just a Dev like you!

Programming Languages war Python, PHP, Javascrip (Nodejs) Connect to MySQL

Comments

More from this blog

How to connect and query to Postgresql Golang vs Rust?

How to create a rest API client Golang vs Rust?

Programming Languages war Golang vs Rust Define Variables

Programming Languages war Golang vs Rust

Programming Games Python Hangman

Python

PHP

Javascript

Command Palette

Comments

More from this blog

Python

PHP

Javascript