Creating Connection

1
2
3
4
5
6
7
import pymysql

mydb = pymysql.connect(
  host="localhost",
  user="root",
  password="123456"
)

Creating a Database

We created a database named “mydatabase”

1
2
3
mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

Check if Database Exists

1
2
3
4
5
mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

access the database when making the connection

1
2
3
4
5
6
mydb = pymysql.connect(
  host="localhost",
  user="root",
  password="123456",
  db="mydatabase"
)

Creating Table

1
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

Check if Table Exists

1
2
3
mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

Primary key

Insert Into Table

Now, we insert some values into the “customers” table

1
2
3
4
5
6
7
8
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

# change the table
mydb.commit()

print(mycursor.rowcount, "record inserted.")

Insert Multiple Rows

To insert multiple rows into a table, use the executemany() method.

The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")