Python- Mysql Connectivity
Introduction to database:
• A relational database is a collection of tables, each with a fixed number of columns
and a variable number of rows.
• Each column in a table has a name and contains values of the same data type, such
as integer or string.
• Each row, or record, contains values that are related to each other, such as a
particular patient’s name, age, and blood type.
• Superficially, each table looks like a spreadsheet or a file with one record per line, but
behind the scenes, the database does a lot of work to keep track of which values are
where and how the tables relate to one another.
• There are many different brands of databases to choose from, including commercial
systems like Oracle, IBM’s DB2, and Microsoft Access, and open-source databases like
MySQL and PostgreSQL.
• A database is usually stored in a file, or in a collection of files. These files are not
formatted as plain text—if you open them in an editor, they will look like garbage,
and any changes you make will probably corrupt the data and make the database
unusable. Instead, you must interact with the database in one of two ways:
o By typing commands into a GUI, just as you type commands into a Python
interpreter. This is good for simple tasks but not for writing applications of
your own.
o By writing programs in Python (or some other language). These programs
import a library that knows how to work with the kind of database you are
using and use that library to create tables, insert records, and fetch the data
you want.
• Your code can then form the results in a web page, calculate statistics, or do
whatever else you want.
The advantages of DBMS are as follows:
1. Improved data sharing: The DBMS helps create an environment in which end users have
better access to more and better-managed data. Such access makes it possible for end users
to respond quickly to changes in their environment.
2. Improved data security: The more users access the data, the greater the risks of data
security breaches. Corporations invest considerable amounts of time, effort, and money to
ensure that corporate data are used properly. A DBMS provides a framework for better
enforcement of data privacy and security policies.
3. Better data integration: Wider access to well-managed data promotes an integrated view
of the organization’s operations and a clearer view of the big picture. It becomes much
easier to see how actions in one segment of the company affect other segments.
4. Minimized data inconsistency: Data inconsistency exists when different versions of the
same data appear in different places. For example, data inconsistency exists when a
company’s sales department stores a sales representative’s name as “Bill Brown” and the
company’s personnel department stores that same person’s name as “William G. Brown,” or
when the company’s regional sales office shows the price of a product as $45.95 and its
national sales office shows the same product’s price as $43.95. The probability of data
inconsistency is greatly reduced in a properly designed database.
5. Improved data access: The DBMS makes it possible to produce quick answers to ad hoc
queries. From a database perspective, a query is a specific request issued to the DBMS for
data manipulation—for example, to read or update the data. Simply put, a query is a
question, and an ad hoc query is a spur-of-the-moment question. The DBMS sends back an
answer (called the query result set) to the application. For example, end users, when dealing
with large amounts of sales data, might want quick answers to questions (ad hoc queries)
such as: a) What was the dollar volume of sales by product during the past six months? b)
What is the sales bonus figure for each of our salespeople during the past three months? c)
How many of our customers have credit balances of $3,000 or more?
6. Improved decision making: Better-managed data and improved data access make it
possible to generate better-quality information, on which better decisions are based. The
quality of the information generated depends on the quality of the underlying data. Data
quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of
the data. While the DBMS does not guarantee data quality, it provides a framework to
facilitate data quality initiatives.
7. Increased end-user productivity: The availability of data, combined with the tools that
transform data into usable information, empowers end users to make quick, informed
decisions that can make the difference between success and failure in the global economy.
Program to check the version of mysql.
importmysql.connector
db=mysql.connector.connect(host='localhost',database='student',user='root',password='ro
ot')
cursor=db.cursor()
cursor.execute('SELECT VERSION()')
data=cursor.fetchone()
print('database version%s' %data)
db.close()
Output:
database version5.1.28-rc-community
LINE1 : import mysql.connector:
1. It creates connectivity between mysql and python shell.
2. Without importing this modulewe cannot use tables from mysql database.
3. Every dbms has its connector.
4. We need to install this connector in order to access the database.
LINE2 : db=mysql.connector.connect ()
1. Connect method creates the connection with the appropriate database.
2. This method takes one string as a parameter, which identifies the database we want to
connect to.
3. localhost: In computer networking localhost is the hostname given to the
computer(currently used).
LINE3: cursor=db.cursor()
1. Once the connection is created you cannot interact with the database until you create a
cursor object.
2. Cursor allows you to issue commands.
3. Cursor is used to execute the SQL queries.
4. Like the cursor in your editor, this keeps track of where we are in the database so that if
several programs are accessing the database at the same time, the database can keep track
of who is trying to do what.
5. Thus Once you have a Connection, you can create a Cursor object and call its execute()
method to perform SQL commands.
LINE4: Cursor.execute(sql query)
1. The executable query is placed in parethesis.
2. Execute allows to write and execute sql queries.
LINE4: Data=cursor.fetchone()
1. Once the database connection is established, you are ready to make a query in the
database.
2. Fetchone(): It fetches the next row of the query result set. A result is an object that is
returned when a cursor object is used to query a table.
3. Fetchall(): it fetches all the rows in the result set. If some rows have already been
extracted from the result set, then it retrieves the remaining rows from the result set.
We can now actually start working with the database.
The first step is to create a table. To do this, we have to describe the operation we want in
MYSQL, put that in a string, and tell the database to execute that string. The general form of
the MYSQL statement for table creation is as follows:
CREATE TABLE Table_name(Column_Name Type, Column_Name Type, ...)
Where the names of tables and columns are like the names of variablesin a program and the
types are chosen from the types the database supports.
Example:
>>>cur.execute('CREATE TABLE student(roll_no integer, name char(20),gender char(1))' )
The above example will create a table named “student” with 3 columns mainly roll_no,
name and gender.
Sample program:
import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database='TEST')
cursor = db.cursor()
cursor.execute("create table info( id integer,f_name char(15),l_name char(15),age
integer,gender char)")
db.close()
After creating a table, our next task is to insert data into it. We do this by inserting one
record at a time using the INSERT command, whose general form is as follows:
INSERT INTO TableName(column_name,..)VALUES(Value1, Value2, ...)
The values are matched left to right against the columns. For example, we insert data into
the table student.
>>>cur.execute('INSERT INTO student (id,name,gender )VALUES(1,’John’,’m’) )
o Notice that the number and type of values in the INSERT statements match the
number and type of columns in the database table.
o If the number of values being inserted does not match the number of columns in the
table, the database reports an error, and the data is not inserted.
Saving Changes
After we’ve inserted data into the database or made any changes, we must commit those
changes using the connection’s commit method:
>>>con.commit()
• Committing to a database is like saving the changes made to a file in a text editor.
• Until we do it, our changes are not stored and are not visible to anyone else who is using
the database at the same time.
• Requiring programs to commit is a form of insurance. If a program crashes partway
through a long sequence of database operations and the commit is never called, then the
database will appear as it did before any of those operations were executed.
Sample Program:
import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database='TEST')
cursor = db.cursor()
cursor.execute("create table stu_info1( roll integer,f_name char(15),age integer)")
cursor.execute("""insert into stu_info1(roll,f_name,age)values(1,'Raj',24),
(2,'Mohan',26),(3,'Vijay',25),(4,'Rohan',26)""")
db.commit()
cursor.execute('DELETE FROM stu_info1 WHERE f_name="Rohan"')
db.commit()
db.close()
Retrieving Data
Now that we have data in our database, we can start to run queries to search for data that
meets specified criteria. The general form of a query is as follows:
SELECT ColumnName, ColumnName, ... FROM Table_name
Where Table_name is the name of the table we want to get data from and the columnname
specify which values we want. For example,
>>>cur.execute('SELECT id,name,gender FROM student' )
Once the database has executed this query for us, we can access the results one record at a
time by calling the cursor’s fetchone method, just as we can read one line at a time from a
file using readline.
>>> print cur.fetchone()
>>1 John M
The fetchone method returns each record as a tuple whose elements are in the order
specifiedin the query. If there are no more records, fetchone returns none.
Query Conditions
Much of the time, we want only some of the data in the database. (Think about what would
happen if you asked Google for all of the web pages it had stored.) We can select a subset of
the data by using the keyword
WHERE to specify conditions that the rows we want must satisfy. For example, we can get
the name of the student having id=2
Operator Description
= Equal to
!= Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
As well as these relational operators, we can also use the Boolean operators AND, OR, and
NOT. To get a list of regions with population greater than 1 million that have names that
come before the letter L in thealphabet, we would use this:
>>>cur.execute cursor.execute("select f_name from stu_info where age<=24")
>>> print cur.fetchall()
Sample programs:
1. import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database='TEST')
cursor = db.cursor()
cursor.execute("CREATE TABLE stu_info(roll integer,f_name char(20),age integer)")
cursor.execute("""insert into stu_info(roll,f_name,age)values(1,'Raj',24),
(2,'Mohan',26),(3,'Vijay',25),(4,'Rohan',26)""")
db.commit()
print(cursor.fetchall())
db.close()
Output:
>>>
[(1, 'Raj', 24), (2, 'Mohan', 26), (3, 'Vijay', 25), (4, 'Rohan', 26)]
2. Using operator.
import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database='TEST')
cursor = db.cursor()
cursor.execute("select f_name from stu_info where age<=24")
print(cursor.fetchall())
db.close()
Output:
>>>
[('Raj',)]
3. import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database='TEST')
cursor = db.cursor()
cursor.execute "select f_name from stu_info where f_name='Raj'")
print(cursor.fetchall())
db.close()
Output:
>>>
[('Raj',)]
4. import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database='TEST')
cursor = db.cursor()
cursor.execute("select f_name from stu_info where f_name like 'R%'")
print(cursor.fetchall())
db.close()
Output:
>>>
[('Raj',), ('Rohan',)]
5. import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database=‘TEST’)
cursor = db.cursor()
cursor.execute("select f_name from stu_info where f_name like '_o%'")
print(cursor.fetchall())
db.close()
Output:
>>>
[('Mohan',), ('Rohan',)]
Update:
Data often changes over time, so we need to be able to change the information stored in
databases. To do that, we use the UPDATE command, as shown here:
cursor.execute("""UPDATE Table_name SET column_name=new_value WHERE
column_name = some_value """)
Query for update
import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database=‘TEST’)
#cursor.execute("CREATE TABLE stu_info(roll integer,f_name char(20),age integer)")
#cursor.execute("""insert into stu_info(roll,f_name,age)values(1,'Raj',24),
(2,'Mohan',26),(3,'Vijay',25),(4,'Rohan',26)""")
cursor = db.cursor()
cursor.execute("""UPDATE stu_info SET age=26 WHERE f_name = "Raj" """)
cursor.execute("SELECT * FROM stu_info" )
print(cursor.fetchall())
db.close()
Output:
>>> [(1, 'Raj', 26), (2, 'Mohan', 26), (3, 'Vijay', 25), (4, 'Rohan', 26)]
To delete a record:
We can also delete records from the database. To delete a record:
>>cursor.execute('DELETE FROM table_name WHERE column_name condition)
Example:
import mysql.connector
db = mysql.connector.connect(user='root', password='root', host='localhost',
database=‘TEST’)
cursor = db.cursor()
#cursor.execute("create table stu_info1( roll integer,f_name char(15),age integer)")
#cursor.execute("""insert into stu_info1(roll,f_name,age)values(1,'Raj',24),
# (2,'Mohan',26),(3,'Vijay',25),(4,'Rohan',26)""")
#db.commit()
cursor.execute('DELETE FROM stu_info1 WHERE f_name="Rohan"')
db.commit()
cursor.execute("SELECT * FROM stu_info1" )
print(cursor.fetchall())
db.close()
OUTPUT:
>>> [(2, 'Mohan', 26), (3, 'Vijay', 25)]
In both cases, all records that meet the WHERE condition are affected. If we do not include a
WHERE condition, then all rows in the database are updated or removed
Drop table from database:
To remove an entire table from the database, we can use the DROP command:
>>cursor.execute(‘DROP TABLE TableName’)
Example:
import mysql.connector
db=mysql.connector.connect(host='localhost',user='root',password='root',database=‘TEST’)
cursor=db.cursor()
cursor.execute('drop table stu_info1')
db.close()
When a table is dropped, all the data it contains is lost. You should be very, very sure you
want to do this (and even then, it’s probably a good idea to make a backup copy of the
database before deleting any sizable tables).
Alter and describe the table from database:
Users can add a new column to the existing table in the database using the alter command.
We can use alter command as follows:
cursor.execute("alter table table_name add new_column_name")
To describe the columns of the table created in the database we can use the describe
command. We can use the describe command as follows:
cursor.execute("desc table_name" )
Example:
import mysql.connector
db=mysql.connector.connect(host='localhost',user='root',password='root',database=‘TEST’)
cursor=db.cursor()
cursor.execute("alter table stu_info add addr char(20)")
cursor.execute("desc stu_info" )
print(cursor.fetchall())
db.close()
Output:
>>>
[('roll', 'int(11)', 'YES', '', None, ''), ('f_name', 'char(20)', 'YES', '', None, ''), ('age', 'int(11)',
'YES', '', None, ''), ('addr', 'char(20)', 'YES', '', None, '')]
Example:
import mysql.connector
db=mysql.connector.connect(host='localhost',user='root',password='root',database=‘TEST’)
cursor=db.cursor()
cursor.execute("alter table stu_info add addr char(20)")
cursor.execute("desc stu_info" )
print(cursor.fetchall())
db.close()
Output:
>>>
[('roll', 'int(11)', 'NO', 'PRI', '0', ''), ('f_name', 'char(20)', 'YES', '', None, ''), ('age', 'int(11)',
'YES', '', None, ''), ('addr', 'char(20)', 'YES', '', None, '')]
Primary key:
When a column is enforced with the primary key, it accepts only distinct and not null values
for that column. If we assign duplicate values to the column enforced with a primary key, it
will result into a violation of integrity constraint and will throw an error.
import mysql.connector
db=mysql.connector.connect(host='localhost',user='root',password='root',database='TEST')
cursor=db.cursor()
cursor.execute("create table t(id integer, primary key (id))")
cursor.execute("insert into t(id)values(1)")
cursor.execute("insert into t(id)values(1)")
db.close()
