Python Data Persistence – MySQL

by

Python Data Persistence – MySQL

So far we have learned how some basic SQL operations are performed over a relational database using SQLite console. Similar console-driven interaction is possible with other RDBMS products. MySQL console works more or less similar (barring certain syntactical differences) to the SQLite console we’ve used in this chapter. Following piece of code shows a sample MySQL console session:

Welcome to the MySQL monitor. Commands end with; or \g. 
Your MySQL connection id is 17
Server version: 5.7.23 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 
Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;1 or '\h' for help. Type '\c' to clear
the current input statement. 
mysql> show databases;
+------------------------+
 | Database                     |
+------------------------+
 | information_schema   |
 |mydb                            | 
 |mysql                            |
 | performance_schema  |
 |I sys                              |
+------------------------+
5 rows in set (0.03 sec) 
mysql> use mydb;
Database changed 
mysql > CREATE TABLE products 
         -> ( productID varchar(5),
         -> productName varchar(20),
         -> price Numeric(7,2));
Query OK, 0 rows affected (0.17 sec)
mysql> insert into products values (1, 'TV', 40000);
Query OK, 1 row affected (0.06 sec) 
mysql> select * from products;
+-------------+---------------+---------+
 | productID    | productName | price    |
+-------------+---------------+---------+
|       1            |          TV         | 40000.00 |
+-------------+---------------+---------+
1 row in set (0.05 sec)

MS SQL Server also has a console-based frontend called SQLCMD which also works similarly. The command-line interface of Oracle is called SQL*Plus. As far as PostgreSQL is concerned, its primary command-line interface is psql program.

All the RDBMS products also provide GUI-based environments to perform various SQL-related operations instead of command-line actions. Oracle’s SQL Developer, Microsoft’s SQL Server management studio, pgAdmin for PostgreSQL, and Workbench for MySQL are respective examples. SQL Server client is integrated with Visual Studio which helps the user to perform database operations graphically. MySQL module is shipped with various web server software bundles (for example, LAMP, XAMPP, etc.), providing a web-based interface called PhpMyAdmin. (figure 7.2)

Python Data Presistence - MySQL chapter 7 img 1

Although SQLite doesn’t provide its own GUI tool for database management, many third-party tools are available. One such utility is SQLiteStudio that is very popularly used.