MySQL

How to create a slug from the name of an item in MySQL/MariaDB. UPDATE my_table SET slug = LOWER(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(name, ' ', '-'), 'and', '-'), '[^a-zA-Z0-9-]', ''), '-+', '-'));

Step 1 — Moving the MariaDB Data Directory mysql -u root -p select @@datadir; Output: +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec) exit sudo systemctl stop mariadb sudo systemctl status mariadb Output: mysql systemd[1]: Stopped MariaDB database server. sudo rsync -av /var/lib/mysql /mnt/my-volume-01 sudo mv /var/lib/mysql /var/lib/mysql.bak Step 2 — Pointing to the New Data Location sudo vi /etc/my.cnf [mysqld] . . ....

If you need to find the fastest way to count the number of rows in a massive MariaDB, or MySQL table, then you can do the following instead of performing a select count() query: show table status like '<TABLE_NAME>' This will provide you with a table of information about the table statistics, including the amount of rows.

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. Presenting the data let’s take two (2) tables as a demonstration for the code below. Users – Table 1 { id: 1, name: 'Carl', fav: 254}, { id: 2, name: 'Emma', fav: 254}, { id: 3, name: 'John', fav: 255}, { id: 4, name: 'Hayley', fav:}, { id: 5, name: 'Andrew', fav:} Products – Table 2...

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. How to Limit the Result Returned from MySQL in Python import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "YoUrPaSsWoRd", database = "your_database" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM customers LIMIT 10") myresult = mycursor.fetchall() for x in myresult: print(x) How to Start From Another Position in MySQL from Python import mysql....

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. How to Update a MySQL Table in Python import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "YoUrPaSsWoRd", database = "your_database" ) mycursor = mydb.cursor() sql = "UPDATE customers SET address = 'Stoneleigh Place' WHERE address = 'Abbey Road'" mycursor.execute(sql) mydb.commit() print(mycursor....

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. How to Delete/Drop a MySQL Table in Python import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "YoUrPaSsWoRd", database = "your_database" ) mycursor = mydb.cursor() sql = "DROP TABLE customers" mycursor.execute(sql) How to Delete/Drop Only if MySQL Table Exists in Python import mysql....

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. How to Delete MySQL Records in Python import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "YoUrPaSsWoRd", database = "your_database" ) mycursor = mydb.cursor() sql = "DELETE FROM customers WHERE address = 'The Rockies'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) deleted") Prevent SQL Injection in MySQL queries through Python Specify the injected variable as the second argument to the execute command as below....

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. How to Sort the Result of a MySQL Query in Python import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "YoUrPaSsWoRd", database = "your_database" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers ORDER BY name" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) How to Change Direction of MySQL Order in Python You can change the order of the sort by simply setting the order direction....

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. How to Select from MySQL with a Filter in Python You simply specify the WHERE clause in your SQL statement as follows: import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "YoUrPaSsWoRd", database = "your_database" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE address ='London Road'" mycursor....