Installing a MySQL Database

 Installing a MySQL Database

Get a free MySQL database package for Windows OS from:

https://dev.mysql.com/downloads/installer/

and choose either the off-line or web enabled installer download. You will have the option to create a free Oracle account which I did.

Note the TCP/IP connection port.

Create a root user and DB Admin user, the latter run as a standard system account.

I created all 4 possible error logs.

I choose the Full installation when prompted. Along the way you may be prompted to install Microsoft C++ redistributables which are free and the MySQL installer takes care of it for you. The only thing you have to install manually and separately is the MS Visual Studio C++ (unless you already have it), which I don't need, so I skipped that.

The full installation has a lot of programs, so be patient and you may have to retry some of them a 2nd and 3rd time to get them to download properly, all within the installer  - there is a retry button. 

Next install the mysql-connector-python driver using PIP. Do NOT install the mysql-connector package as that is something else.

Here is how to check that everything is installed properly and that  the various components can talk to each other:

################ test 1 ######################

import mysql.connector

print(dir(mysql.connector.connect))

# If the two statements above run without errors, then the MySQL connector driver is working properly.

################ test 2 ######################

mydb = mysql.connector.connect(

  host="localhost",

  user="your standard system username",

  password="you fill in"

)

print(dir(mydb))

print(mydb)

# if the connect function and the two print statements run without errors, then you can properly make a connection from a python script to the MySQL database.

However, if you get: 

"mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported" 

then you probably installed both mysql-connector and mysql-connector-python. Uninstall mysql-connector and try again.

################ test 3 ######################

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

# if the 2 statements above run without error, then a new database is created.

################ test 4 ######################

# mycursor = mydb.cursor()

# mycursor.execute("CREATE DATABASE mydatabase")

mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)

Once you have made a database, check to make sure it really is there with the SHOW command and a loop; the MySQL installed with some default already created databases and you want to be able to see them all.

It is not enough to just issue a SHOW command, you still have to loop to see them. If you try to loop without first running SHOW, the loop will exit because mycursor will have length zero for the its internal database count.

################ test 5 ######################

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="standard system username",

  password="fill in",

database="mydatabase"

) # Once the database is first successfully created and checked for existence,  just put the database name in the connector call as above.

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

If the above runs without error, then you can create a table within a database.

################ test 6 ######################

##### mycursor.execute("CREATE TABLE customers (name

##### VARCHAR(255), address VARCHAR(255))")

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

to prove the table still exists without errors.

Once you've completed those 6 tests, it is time to assign a Primary Key to the already existing database.

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

Now show tables is still OK after adding the Primary key

mycursor.execute("SHOW TABLES")

for y in mycursor:

    print(y)