PYTHON - MYSQL CONNECTIVITY

To download MYSQL V5.7 CLICK HERE

 To download connector via pip;

python -m pip install mysql-connector

Establishing connection :

To get username and password :

select current_user();

The output is of form--username@host

Import mysql.connector as m

Mydb=m.Connect(host="localhost",user="root",passwd="your_mqsql_password")

If print(Mydb) doesn't show any error then it means that the connection has been successfully made

All the below commands need to written after the above commands which create a connection 

Note:Alternate of localhost is 127.0.0.0

Alternate representation :

<<1>>-host, <<2>>-user, <<3>> password

Creating database:

mycursor=Mydb.cursor()
mycursor.execute("create database datbase_name") 

If database already exits then it create an error

To check if connected use : mydb.is_connected()

To access a database :

 Method 1 : Same connection with a command an additional command at top mycursor.execute("use database_name") 

Or

Method 2: The following modifications in connection 

Mydb=m.Connect(host="localhost",user="root",passwd="your_mqsql_password", database= "database_name")

Displaying tables:

mycursor.execute("show tables") 
for x in mycursor :
       print(x) 

In above command x be a single element tuple containing name of a table in selected database 

All mysql need to be written as : mycursor.execute("mysql_command")

Important points to keep in mind while using execute:

1.)The mysql_command should not contain " ie double inverted comma and can only contain ' ie single inverted commas without semicolon or use triple quotes outside with semicolon

2.)To print some result of command like show, desc use following loop after execute:

for x in mycursor :
       print(x)

Here x will be a tuple with its element contains result of 1 single row of the mysql_command

3.) With commands insert, delete and update after execute we need to write:

Mydb.commit() 

to save the changes in the table

4.) To display records using select we need to use the fetch command family after execute:

mycursor.execute("select...") 
myrecords=mycursor.fetchall()
for x in myrecords:
     print(x) 

Here x will be a tuple with its element contains result of 1 single row of the query

The various fetch commands are as follows and have the same function as their name:

fetchall() 
fetchone() 
fetchmany(n) 

Note: using select won’t print the field headers 

5.) mycursor.rowcount is a variable which stores the number of records affected by the most recent mycursor.execute command 

6.) To print error use the execute statement in the try block and in the except block use:
Mydb.rollback() 
To prevent any unwanted or incomplete changes in the database

7.) To print structure of table:

mycursor.execute(“desc table_name”)
for j in mycursor:
    print(j)

8.) To prevent writing mydb.commit() again and again set

mydb.autocommit =True

To fetch record and print it from my cursor execute statement ony by one:

myrecord=mycursur.fetchone()

while myrecord is not None:

    print(myrecord) 

    myrecord=mycursur.fetchone()