MySQL Python

1

• MySQL Python

2

• CHAPTER 4
• THE BASICS OF SEARCH ENGINE FRIENDLY DESIGN
  DEVELOPMENT

3

• MySQL Python About MySQL
• MySQL is a fast, easy to use relational database.
  It is currently the most popular open-source
  database
• MySQL is used for many small and big businesses.
  It is developed, marketed and supported by MySQL
  AB, a Swedish company. It is written in C and
  C.
• MySQL is an open-source database, so you dont
  have to pay a single penny to use it.

• MySQL Features
• MySQL is a fast, easy to use relational database.
• MySQL is used for many small and big businesses.
• MySQL is an open-source database, so you dont
  have to pay for it.

4
Download MySQL Follow these steps Go to MySQL
official website http//www.mysql.com/downloads/ C
hoose the version number for MySQL community
server which you want.
MySQL Python Connector MySQL Python Connector is
used to access the MySQL database from Python,
you need a database driver. MySQL
Connector/Python is a standardized database
driver provided by MySQL. To check it wether
mysql.connector is available or not we type
following command
gtgtgt import mysql.connector
5
(No Transcript)
6
After typing this we clearly say that No Module
Named as a MySQL is present. Then we have to
install MySQL. Connector for Python. Python needs
a MySQL driver to access the MySQL database. So,
in next we download the mysql-connector with use
of pip C\Users\Nitin Arvind Shelkegtpip install
mysql-connector
7
(No Transcript)
8
After installation we test it whether it work or
not, lets check with the following command
gtgtgt import mysql.connector
9
(No Transcript)
10
The above line imports the MySQL Connector Python
module in your program, so you can use this
modules API to connect MySQL. If the above code
was executed with no errors, the we can say that
MySQL Connector is installed properly and get
ready to use of it.
gtgtgtfrom mysql.connector import Error
MySQL connector Error object is used to show us
an error when we failed to connect Databases or
if any other database error occurred while
working with the database.
11
Creating a connection to the database. After
installing the MySQL Python connector, we need to
test it to make sure that it is working
correctly, and you can connect to the MySQL
database server without any problems. To verify
the installation, you use the following
steps Type the following line of code
gtgtgt import mysql.connector To establish a
connection to the database we should know the
following parameters, Host localhost (In general
it is same for all) Databasemysql (You can set
as per your wish) Userroot (It is a username)
Password root_at_123 (password set by me while
installation of MyQL) gtgtgt mysql.connector.connect
( host 'localhost', database 'mysql', user
'root', password 'root_at_123')
12
(No Transcript)
13
Show the available Database You can check if
a database exists on your system by listing all
databases in your system by using the SHOW
DATABASES statement
gtgt my_database mysql.connector.connect( host
'localhost', database 'mysql', user 'root',
password 'root_at_123) gtgtgt cursor
my_database.cursor(). gtgtgt cursor.execute( " show
databases " ) gtgtgt for db in cursor ... 
print(db)
14
Output
('bank,) ('information_schema,) ('mysql,)
('performance_schema,) ('sakila,) ('sys,)
('world,) gtgtgt
15
(No Transcript)
16
Creating a Database To create a database in
MySQL, we use the CREATE DATABASE statement to
create the database named as college
gtgtgt my_database mysql.connector.connect( host
'localhost', user 'root', password 'root_at_123
) gtgtgt cursor my_database.cursor() gtgtgt
cursor.execute( " CREATE DATABASE college "  )
gtgtgt for db in cursor ...  print(db) ... gtgtgt
cursor.execute( " show databases " ) gtgtgt for db
in cursor ...  print(db) ...
Copyright _at_ 2019 Learntek. All Rights Reserved.
17
(No Transcript)
18
Creating the Tables Next, we create the tables
for the college database. It is compulsory to
define the name of the database while creating
the tables for it. Syntax to create the table is
create table_name( column 1 datatype, column 2
datatype, column 3 datatype, ,
column n datatype )
19
Lets create the table students, department and
faculty for the database college.
gtgtgt my_database mysql.connector.connect ( host
'localhost', database 'college', user
'root', password 'root_at_123 ) gtgtgt cursor
my_database.cursor() gtgtgtcursor. execute( "
CREATE TABLE students ( stud_id varchar(200),
stud_name VARCHAR(215), address VARCHAR(215),
city char(100)) " ) gtgtgt cursor. execute( "
CREATE TABLE department ( dept_id varchar(200),
dept_name VARCHAR(215)) " ) gtgtgt cursor.execute(
"CREATE TABLE faculty (  faculty_id
varchar(200),faculty_name VARCHAR(215) )"  )
20
Show the tables To display the tables, we will
have to use the SHOW TABLES Following code
display the all the tables present in the
database college
gtgtgt cursor. execute ( " SHOW TABLES " ) gtgtgt for
x in cursor ...     print(x) ...
('department,) ('faculty,) ('students',)
21
(No Transcript)
22
Assign Primary key in table Primary key It is
a minimal set of attributes (columns) in a table
or relation that can uniquely identifies tuples
(rows) in that table. For example, Student
(Stud_Roll_No,  Stud_Name,  Addr) In the student
relation, attribute Stud_Roll_No alone is a
primary key as each student has a unique id that
can identify the student record in the table.
gtgtgt my_database mysql.connector.connect ( host
'localhost', database 'college', user
'root', password 'root_at_123 ) gtgtgt cursor
my_database.cursor() gtgtgtcursor. execute( "
CREATE TABLE students2 ( stud_id varchar(200)
PRIMARY KEY, stud_name VARCHAR(215), address
VARCHAR(215), city char(100)) " )
23
If the table already exists, use the ALTER TABLE
keyword
gtgtgt my_database mysql.connector.connect ( host
'localhost', database 'college', user
'root', password 'root_at_123 ) gtgtgt cursor
my_database.cursor() gtgtgtcursor.execute( " ALTER
TABLE student ADD COLUMN id INT AUTO_INCREMENT
PRIMARY KEY " )
24
Describe the created tables Desc keyword is used
to describe the table in MySQL. Following code
describe the students table from the database
college
gtgtgt cursor.execute("desc students") gtgtgt for x in
cursor ...     print(x) ... ('stud_id',
'varchar(200)', 'YES', '', None, )
('stud_name', 'varchar(215)', 'YES', '', None,
) ('address', 'varchar(215)', 'YES', '', None,
) ('city', 'char(100)', 'YES', '', None, )
gtgtgt
Example 2 Following code describe the students2
(where stud_id is mentioned as primary key) table
from the database college
25
gtgtgt cursor.execute("desc students2") gtgtgt for x
in cursor ...     print(x) ... ('stud_id',
'varchar(200)', 'NO', 'PRI', None, )
('stud_name', 'varchar(215)', 'YES', '', None,
) ('address', 'varchar(215)', 'YES', '', None,
) ('city', 'char(100)', 'YES', '', None, )
gtgtgt
26
(No Transcript)
27
Insert data into the Table To insert the data
into the table, insert into statement is
used, Lets insert the data into the table
students of college database,
gtgtgt my_database mysql.connector.connect ( host
'localhost', database 'college', user
'root', password 'root_at_123 ) gtgtgt stm "
INSERT INTO students ( stud_id, stud_name,
address, city ) VALUES ('101','Nitin Shelke',
'Congress Nagar', 'Amravati' ) gtgtgt cursor
my_database.cursor() gtgtgt cursor.execute(stm)
28
Display or select the inserted data from the Table
gtgtgt cursor.execute(" select from students")
gtgtgt for x in cursor ...     print(x) ...
('101', 'Nitin Shelke', 'Congress Nagar',
'Amravati')
29
Alternate way is to use the fetchall() method gtgtgt
cursor.fetchall() (101, Nitin Shelke,
Congress Nagar, Amravati)
30
For more Training Information , Contact
Us Email info_at_learntek.org USA 1734 418
2465 INDIA 40 4018 1306
7799713624