Install the MySQL Server
In order to binding between Python with the databases , we need to install MySQL server. The command is:
sudo apt-get install libmariadbclient-dev
Then we the MySQL connector. The command is:
pip3 install mysql-connector
Connecting to your database through Python
In this tutorial, I will show on how to connect and control our database through Python. We will used the database that we created from previous tutorial which is ‘DB_temp’.
Connect to your database
Create a python file and enter the following contents into it:
#!/usr/bin/python
import mysql.connector as mysql
import time
db = mysql.connect(
host = "localhost",
user = "shaharil", #user/client that can access the databases
passwd = "123"
)
print(db) #show whether the connection is succesful?
Show Databases
We can write a script to shows the databases under user.
#!/usr/bin/python
import mysql.connector as mysql
import time
db = mysql.connect(
host = "localhost",
user = "shaharil", #user/client that can access the databases
passwd = "123"
)
print(db) # show whether the connection is succesful?
cursor = db.cursor() # assigned to a variable name cursor
cursor.execute("show databases") #command to list out all databases
databases = cursor.fetchall() # it will returns a list of databases
print(databases)
Show Tables
Probably, we just create the table using MySQL command on Terminal. In order to shows the table, we can this script:
import mysql.connector as mysql
import time
db = mysql.connect(
host = "localhost",
user = "shaharil", #user/client that can access the databases
passwd = "123",
database = "DB_temp" # our database
)
print(db) # show whether the connection is succesful?
cursor = db.cursor() # assigned to a variable name cursor
cursor.execute("show databases") #command to list out all databases
databases = cursor.fetchall() # it will returns a list of databases
print(databases)
cursor.execute("show tables") # command to show tables in databases
tables = cursor.fetchall() # it will return all data inside the table
print(tables)
query = "select * from temp_table" # get all data recorded in table
cursor.execute(query)
datas = cursor.fetchall()
for data in datas:
print(data)
query = "select date from temp_table" # get only date data from table
cursor.execute(query)
datas = cursor.fetchall()
for data in datas:
print(data)
print("Total data =", cursor.rowcount,) # to list out how many data
Read Data from Table
The script to read data can be done by using command “SELECT” as example script below:
import mysql.connector as mysql
import time
db = mysql.connect(
host = "localhost",
user = "shaharil", #user/client that can access the databases
passwd = "123",
database = "DB_temp" # our database
)
print(db) # show whether the connection is succesful?
cursor = db.cursor() # assigned to a variable name cursor
cursor.execute("show databases") #command to list out all databases
databases = cursor.fetchall() # it will returns a list of databases
print(databases)
cursor.execute("show tables") # command to show tables in databases
tables = cursor.fetchall() # it will return all data inside the table
print(tables)
cursor.execute("select * from temp_table") # get all data recorded in table
datas = cursor.fetchall()
print("Total row =",cursor.rowcount)
print("\n Date Time Value")
print("============================================")
for data in datas:
print(str(data[0]) + " ",str(data[1]) + " ",str(data[2]) + " ")
Read Data from Table According to Date
dateFind = "2020-07-10" query = "SELECT COUNT(name) as 'count' FROM attendance where (date = '%s')" %(dateFind) print(query) cursor.execute(query) # command to insert the data into tables datas = cursor.fetchall() print(datas) # to list out how many data
Insert Data to Table
The script to insert data can be done by using command “INSERT INTO” as example script below:
import mysql.connector as mysql
import time
db = mysql.connect(
host = "localhost",
user = "shaharil", #user/client that can access the databases
passwd = "123",
database = "DB_temp" # our database
)
print(db) # show whether the connection is succesful?
cursor = db.cursor() # assigned to a variable name cursor
cursor.execute("show databases") #command to list out all databases
databases = cursor.fetchall() # it will returns a list of databases
print(databases)
cursor.execute("show tables") # command to show tables in databases
tables = cursor.fetchall() # it will return all data inside the table
print(tables)
query = "select * from temp_table" # get all data recorded in table
cursor.execute(query)
datas = cursor.fetchall()
for data in datas:
print(data)
query = "insert into temp_table(date,time,value) values(%s,%s,%s)"
values = ("2020-11-03","11:30:00",26)
cursor.execute(query,values) # command to insert the data into tables
db.commit() # to make final output on database
print(cursor.rowcount,"record inserted") # to list out how many data
query = "select * from temp_table" # get all data recorded in table
cursor.execute(query)
datas = cursor.fetchall()
print(cursor.rowcount,"record inserted") # to list out how many data inserted after
MORE DETAIL can access to this website:
