· host name – is server name or ip address on which mysql is running. if you are running on...

2019

PYTHON MySQL 1.0

DEEPAK BHINDE

PGT COMPUTER SCIENCE

www.dbgyan.wordpress.com

DEEPAK BHINDE PGT COMPUTER SCIENCE PYTHON mysql 1.0

No. Task 1. Task: MySql Get started.

2. Task : Creating a Database.

3. Task : Creating a Table 4. Task: Insert Into Table

5. Task: Select From a Table 6. Task : Select With a Filter (Where clause)

7. Task: MySQL Order By 8. Task: Use of Delete Command .

9. Task : Use of Update Command

10. Task : Drop Table command in MySQL

Task points


Install MySQL Driver

Python needs a MySQL driver to access the MySQL database.

Use the driver "MySQL Connector".

Use PIP to install "MySQL Connector" Navigate your command line to the location of PIP, and type the following:

Download and install "MySQL Connector": C:\Python37\Scripts>python -m pip install mysql-connector

Test MySQL Connector To test if the installation was successful, or if you already have "MySQL Connector" installed, create

a Python page with the following content:

#mysql_test.py:

import mysql.connector

If the above code was executed with no errors, "MySQL Connector" is installed and ready to be

used.

Step: 01 Date:

Task: MySql Get started.

Download:

Download a free MySQL database : https://www.mysql.com/downloads/

Download the latest version of Python: https://www.python.org/downloads/

Download MySql and python latest version and installed on computer

https://www.mysql.com/downloads/

https://www.python.org/downloads/


Create Connection Start by creating a connection to the database.

Use the username and password from your MySQL database:

Output :

<mysql.connector.connection.MySQLConnection object at 0x021A7B90>

Arguments required to connect MySQL database from Python

Username – i.e., the username that you use to work with MySQL Server. The default username for the MySQL database is root

Password – Password is given by the user at the time of installing the mysql database. If you are using root then you won’t need the password.

Host Name – is server name or Ip address on which MySQL is running. if you are running on localhost, then you can use localhost, or it’s IP, i.e. 127.0.0.0

Database Name – Database name to which you want to connect here we are using Database named i.e. ‘python_DB’

Code :

#mysql_connection.py: # import mysq.connector package import mysql.connector #Use the mysql.connector.connect() method of MySQL Connector Python with required # parameters to connect MySQL.

mydb = mysql.connector.connect(

host="localhost",

user="root",

passwd=""

)

print(mydb)


Step: 02 Date :

Task : Creating a Database

Note: If the above code was executed with no errors, you have successfully created a database.



To create a database in MySQL, use the "CREATE DATABASE" statement

Code :

# import mysql.connector package


#Use the mysql.connector.connect() method of MySQL Connector Python with required

# parameters to connect MySQL.


host="localhost",

user="root",

passwd=""

)

# Use the connection object returned by a connect() method to create a cursor object to perform

#Database Operations

mycursor = mydb.cursor()

# The cursor.execute() to execute SQL queries from Python.

mycursor.execute("CREATE DATABASE mydatabase")

Note: If the above code was executed with no errors, you have successfully created a database.


Check if Database Exists

Output : ('information_schema',)

('mydatabase',)

('mydatabase1',)

('mysql',) ('test',)

Code :






host="localhost",

user="root",

passwd=""

)





mycursor.execute("SHOW DATABASES")

for x in mycursor:

print(x)


Note: If the above code was executed with no errors, you have successfully created a table customers.

Step: 03 Date :

ask : Creating a Database

To create a table in MySQL, use the "CREATE TABLE" statement

Task : Creating a Table






host="localhost",

user="root",

passwd="",

database="mydatabase"

)





# create table as Customers

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


Check if Table Exists

Output : ('customers',)






host="localhost",

user="root",

passwd="",


)





# show tables are display all exiting tables

mycursor.execute("SHOW TABLES")

for x in mycursor:

print(x)


Primary Key

Note: If the above code was executed with no errors, you have successfully created a table customers.






host="localhost",

user="root",

passwd="",


)





# create table as Customers with id as primary key

# here "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for

#each record. Starting at 1, and increased by one for each record.

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name

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


Note: If the above code was executed with no errors, you have successfully altered a table customers.

Create primary key on an existing table






host="localhost",

user="root",

passwd="",


)





# Alter table as Customers to add new coloum as id as primary key

# here "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for

#each record. Starting at 1, and increased by one for each record.

mycursor.execute( "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name

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


Step: 04 Date :

gse

Insert a record in the "customers" table

Output : 1 record inserted.

The statement: mydb.commit() .

It is required to make the changes;

otherwise no changes are made to

the table.

Task: Insert Into Table

Code :






host="localhost",

user="root",

passwd="",


)




#insert one record into the customers table

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"

val = ("Deepak", "B 1/1 Gail")


mycursor.execute(sql, val)

# save the changes into the table.

mydb.commit()

print(mycursor.rowcount, "record inserted.")


Insert Multiple row in the "customers" table

Output :

6 records inserted.

Code : # import mysql.connector package





host="localhost",

user="root",

passwd="",


)


#insert multiple records into the customers table

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"

val = [

('Raj', 'Lowstreet 4'),

('Amy', 'Apple st 652'),

('Heena', 'vikaspuri 21'),

('Mohan', 'Rajnagar 345'),

('Vicky', 'Yellow Garden 2'),

('William', 'Central st 954')

]

# The cursor. Executemany() to insert multiple rows.

mycursor. executemany (sql, val)


mydb.commit()

print(mycursor.rowcount, " records inserted.")


Step: 05 Date :

gse

Select all records from the "customers" table, and display the result

Output:

('Deepak', 'B 1/1 Gail') ('Raj', 'Lowstreet 4') ('Amy', 'Apple st 652') ('Heena', 'vikaspuri 21') ('Mohan', 'Rajnagar 345') ('Vicky', 'Yellow Garden 2') ('William', 'Central st 954')

Task: Select From a Table

Code :






host="localhost",

user="root",

passwd="",


)


# To display all the records of customers .

mycursor.execute("SELECT * FROM customers")

# OR uncomment for display colomn i.e. name & address

# mycursor.execute("SELECT name, address FROM customers")

# fetchall() method, which fetches all rows from the last executed statement

# The fetchone() method will return the first row of the result

myresult = mycursor.fetchall()

for x in myresult:

print(x)


Step: 06 Date :

gse

When selecting records from a table, filter the selection by using the "WHERE" statement

Output:

('Deepak', 'B 1/1 Gail') #OR #('Vicky', 'Yellow Garden 2')

Task : Select With a Filter (Where clause)

Code :






host="localhost",

user="root",

passwd="",


)


# Select record(s) where the address is 'B 1/1 Gail'.

mycursor.execute("SELECT * FROM customers WHERE address ='B 1/1 Gail'")

# OR uncomment for Use the % to represent wildcard characters

#Select records where the address contains the word "Garden"

# mycursor.execute("SELECT * FROM customers WHERE address LIKE '%Garden%'")

# fetchall() method, which fetches all rows from the last executed statement


for x in myresult:

print(x)


Step: 07 Date :

gse Use the ORDER BY statement to sort the result in ascending or descending order. The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.

Output:

('Amy', 'Apple st 652') ('Deepak', 'B 1/1 Gail') ('Heena', 'vikaspuri 21') ('Mohan', 'Rajnagar 345') ('Raj', 'Lowstreet 4') ('Vicky', 'Yellow Garden 2') ('William', 'Central st 954')

Task: MySQL Order By

Code :






host="localhost",

user="root",

passwd="",


)


# Sort the result alphabetically by name.

mycursor.execute("SELECT * FROM customers order by name")

# OR uncomment

# Sort the result reverse alphabetically by name.

#mycursor.execute("SELECT * FROM customers order by name desc")


for x in myresult:

print(x)


Step: 08 Date :

gse

Delete records from an existing table by using the "DELETE FROM" statement.

Output:

1 record(s) deleted

Task: Use of Delete Command .

Code :






host="localhost",

user="root",

passwd="",


)


# delete the record of given condition .

mycursor.execute("DELETE FROM customers WHERE address = 'Central st 954'")


mydb.commit()

print(mycursor.rowcount, "record(s) deleted")


Step: 09 Date :

gse

Update existing records in a table by using the "UPDATE" statement.

Output:

1 record(s) updated

Note :The WHERE clause specifies which

record or records that should be updated.

If you omit the WHERE clause, all records

will be updated!

Task : Use of Update Command

Code :






host="localhost",

user="root",

passwd="",


)


# update the record of table .

mycursor.execute("UPDATE customers SET address = ’36, Vijay Nagar’ WHERE address = 'Yellow Garden

2'")


mydb.commit()

print(mycursor.rowcount, "record(s) updated ")


Step: 10 Date :

Delete an existing table by using the "DROP TABLE" statement.

Note: If the table is already deleted, or for any other reason does not exist, you can use the IF

EXISTS keyword to avoid getting an error.

Task : Drop Table command in MySQL

Code :






host="localhost",

user="root",

passwd="",


)


# Delete the table .

mycursor.execute("DROP TABLE customers")

# OR Delete the table if exiting .

#mycursor.execute("DROP TABLE IF EXISTS customers")

· host name – is server name or ip address on which mysql is running. if you are running on...

Documents