Python has the ability to connect with many popular databases like MySQL, PostgreSQL, mongoDb, etc. Connecting with the database is an essential part of building an application using Python. When doing some crawling or scrapping task with your application, you need to save your scrapping data; it’s not very effective when you saved your scrapping data in csv or text file, especially when you do a scrapping job in a long time.
Python can connect with MySQL databases using MySQL driver which called MySQL Connector. You can download and install MySQL connector using pip like the following script.
pip install mysql-connector
or
python3 -m pip install mysql-connector
when managing the MySQL database, I prefer using PHPMyadmin to create, read, insert, update, delete database, or table. I create a new database called python_tutorial and make a user table like the following.
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` text NOT NULL,
`age` int(2) NOT NULL,
`sex` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
now we will try to insert a record into our user table
import mysql.connector
this line used for import MySQL driver so that we can connect to our MySQL database.
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="",
database="python_tutorial"
)
When connecting to MySQL, we must provide host, user, password, and database name of our database.
sql variable on line 11 used to hold SQL syntax, and val variable is a value that we want to insert.
Run the Python program, and it will insert a row in the user table if it succeeds.
We can retrieve data using select SQL syntax like the following.
Line 16 will print a user record in the form of tuple; every tuple holds one record of user data.
to access data inside tuple you can access it using index number, inside a square bracket
user[1]
that’s all for this tutorial, thank you…
