Python –MySQL database
connectivity
Relational Databases
• Relational databases are a powerful, flexible
way to store and retrieve many kinds of data.
• There are many relational database
implementations, which vary in scala bility and
richness of features.
• The standard libraries do not include relational
database support; however, Python modules
exist to access almost any relational database,
including Oracle, MySQL, DB/2, and Sybase.
• The Python Database API defines a standard
interface for Python modules that access a
relational database.
DB API
• The DB API provides a minimal standard
for working with databases, using Python
structures and syn tax wherever possible.
This API includes the following:
• Connections, which cover guidelines for how
to connect to databases
• Executing statements to query, update,
insert, and delete data with cursors
• Transactions, with support for committing or
rolling back a transaction
• Examining metadata on the database module
as well as on database and table structure
Connection Objects
• The connect method constructs a database
connection.
• The connection is used in constructing cursors.
When finished with a connection, call its close
method to free it.
• Databases generally provide a limited pool of
connections, so a program should not
needlessly use them up.
• The parameters of the connect method vary by
module, but typically include dsn (data source
name), user, password, host, and database.
Connection Object Parameters
Transactions
• A transaction is a collection of actions that
must execute atomically
• a bank transfer might debit one account and
credit another; this should be done within a
single transaction, as performing only one half
of the transfer would obviously be
unacceptable.
• Calling the commit connection method
completes the current transaction; calling
rollback cancels the current transaction.
• Not all databases support transactions— for
example, Oracle does, MySQL doesn’t
Cursor objects
• A cursor can execute SQL statements and retrieve
data.
• The connection method cursor creates and returns
a new cursor.
• The cursor method execute(command
[,parameters]) executes the specified SQL
statement command, passing any necessary
parameters.
• After executing a command that affects row data,
the cursor attribute rowcount indicates the number
of rows altered or returned;
• After executing a command that selects data, the
method fetchone returns the next row of data
• The method fetchmany([size]) returns a sequence
of rows—up to size of them.
• The method fetchall returns all the rows.
Connecting to a MYSQL database
• To connect to a mysql database we need
a connector which can be downloaded by
the following steps:
• Open the command prompt-> Type the
following command in the windows
command prompt:
• python -m pip install mysql-connector-python
• To check whether the python interface is
connected or not open the python IDLE & type
• import [Link]
• If there is not error after executing this
command then the mysql connector is installed
successfully.
Program to Connect and
print the Database
• import [Link]
• mydb = [Link](
• host="localhost",
• user="root",
• password="root",
• database="bcadb")
• print(mydb)
• mycursor = [Link]()
• [Link]("CREATE DATABASE mydatabase")
• [Link]("SHOW DATABASES")
• for x in mycursor:
print(x)
Output
Inserting Records in a
Table
Selecting Records
Output
The End!