import datetime
import mysql.connector
from mysql.connector import Error
# pip install mysql-connector
connection = MySQLdb.connect (host = "localhost",
user = "my_user",
passwd = "my_pass",
db = "company")
cursor = connection.cursor()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone()
print("server version:", row[0])
cursor.close()
connection.close()
Here we are connecting to Football database.
connection = mysql.connector.connect(host='localhost',
database='Football',
user='root',
password='hd123')
cursor = connection .cursor()
We could always create one using:
cursor.execute("CREATE DATABASE database_name")
To see all the databases we use SHOW DATABASES command
cursor.execute("SHOW DATABASES")
## printing all
for db in cursor.fetchall():
print(db)
For Example, we can create a table of the Premier League 2018/2019 goal scorers.
| Standing | Name | Club | Nationality | Goals |
|---|---|---|---|---|
| 1. | Pierre-Emerick Aubameyang | Arsenal | Gabon | 22 |
| 1. | Sadio Mané | Liverpool | Senegal | 22 |
| 1. | Mohamed Salah | Liverpool | Egypt | 22 |
| 4. | Sergio Agüero | Manchester City | Argentina | 21 |
| 5. | Jamie Vardy | Leicester City | England | 18 |
| 6. | Harry Kane | Tottenham Hotspur | England | 17 |
| 8. | Eden Hazard | Chelsea | Belgium | 16 |
| 9. | Callum Wilson | AFC Bournemouth | England | 14 |
| 10. | Richarlison | Everton | Brazil | 13 |
| 10. | Raúl Jiménez | Wolverhampton | Mexico | 13 |
| 10. | Alexandre Lacazette | Arsenal | France | 13 |
#cursor.execute("DROP TABLE PL_Goals_second;")
#cursor.execute("DROP TABLE PL_Goals;")
# creating a table (PL_Goals)
cm="""
CREATE TABLE PL_Goals
( Standing int NOT NULL,
Name char(50) NOT NULL,
Club char(50),
Nationality char(50),
Goals int
);
"""
cursor.execute(cm)
We can use INSERT INTO for entering data into the created table.
Players_data = [(1, "Pierre-Emerick Aubameyang","Arsenal", "Gabon", 22),
(1, "Sadio Mané", "Liverpool", "Senegal", 22),
(1, "Mohamed Salah", "Liverpool", "Egypt", 22),
(4, "Sergio Agüero", "Manchester City", "Argentina", 21),
(5, "Jamie Vardy", "Leicester City", "England", 18),
(6, "Harry Kane", "Tottenham Hotspur", "England", 17),
(8, "Eden Hazard", "Chelsea", "Belgium", 16),
(9, "Callum Wilson", "AFC Bournemouth", "England", 14),
(10, "Richarlison", "Everton", "Brazil", 13),
(10, "Raúl Jiménez", "Wolverhampton", "Mexico", 13),
(10, "Alexandre Lacazette", "Arsenal", "France", 13)]
# string format
format_str = """INSERT INTO PL_Goals (Standing, Name, Club, Nationality, Goals)
VALUES ({standing}, "{name}", "{club}", "{nationality}", "{goals}");"""
for p in Players_data:
sql_command = format_str.format(standing=p[0], name=p[1], club=p[2], nationality= p[3], goals = p[4])
cursor.execute(sql_command)
To select from a table in SQL, we can use the SELECT statement. Let's check what has been entered.
def Show_Table(Funname):
S = "SELECT * FROM %s" % (Funname)
cursor.execute(S)
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
Show_Table("PL_Goals")
We can also consider only one column. For exaxple,
cursor.execute("SELECT Name FROM PL_Goals")
for names in cursor.fetchall():
print(names)
This syntax is used to select data on some condition. For example, we can only select those rows in which Standing=1
cursor.execute("SELECT * FROM PL_Goals WHERE Standing = 1")
for row in cursor.fetchall():
print(row)
ORDER BY is used to sort the result in ascending or descending order. For example, we can sort the data in ascending order.
cursor.execute("SELECT * FROM PL_Goals ORDER BY Name")
for rows in cursor.fetchall():
print(rows)
Or in Sorting the data in descending order
cursor.execute("SELECT * FROM PL_Goals ORDER BY Name DESC")
for rows in cursor.fetchall():
print(rows)
cursor.execute("CREATE TABLE cp_PL_Goals AS SELECT * FROM PL_Goals;")
Show_Table("cp_PL_Goals")
We can DELETE to delete the records from the table. For example, let's delete all data where Standing=10.
cursor.execute("DELETE FROM cp_PL_Goals WHERE Standing = 10")
# saving
connection.commit()
Thus,
Show_Table("cp_PL_Goals")
Note that, we have two tables inside of Football database, i.e.
cursor.execute("SHOW TABLES FROM Football")
## printing all
for db in cursor.fetchall():
print(db)
In case that we are interested in deleting cp_PL_Goals table, we can try
cursor.execute("DROP TABLE cp_PL_Goals;")
It can be seen that
cursor.execute("SHOW TABLES FROM Football")
## printing all
for db in cursor.fetchall():
print(db)
First, let's add the following rows,
| Standing | Player | Club | Nationality | Goals |
|---|---|---|---|---|
| 10 | Glenn Murray | Brighton and Hove Albion | England | 13 |
| 10 | Paul Pogba | Manchester United | France | 13 |
| 10 | Gylfi Sigurdsson | Everton | Iceland | 13 |
Players_data = [(10, "Glenn Murray", "Brighton and Hove Albion", "England", 13),
(10, "Paul Pogba", "Manchester United", "France", 13),
(10, "Gylfi Sigurdsson", "Everton", "Iceland", 13)]
for p in Players_data:
sql_command = format_str.format(standing=p[0], name=p[1], club=p[2], nationality= p[3], goals = p[4])
cursor.execute(sql_command)
Show_Table("PL_Goals")
However, we can update any of rows! For example,
cursor.execute("""UPDATE PL_Goals SET Standing =11 WHERE Goals=13;""")
This gives,
Show_Table("PL_Goals")
To correct the information back, we can reverse the process by
cursor.execute("""UPDATE PL_Goals SET Standing =10 WHERE Goals=13;""")
Show_Table("PL_Goals")
We can limit the number of records returned from a query by using the Limit syntax:
cursor.execute("SELECT * FROM PL_Goals LIMIT 5")
for r in cursor.fetchall():
print(r)
# saving and closing the current connection
connection.commit()
connection.close()