- Machine Learning
- Machine Learning Tutorial
- Getting Started
- Mean Median Mode
- Standard Deviation
- Percentile
- Data Distribution
- Normal Data Distribution
- Scatter Plot
- Linear Regression
- Polynomial Regression
- Multiple Regression
- Scale
- Train-Test
- Decision Tree
- Confusion Matrix
- Hierarchical Clustering
- Logistic Regression
- Grid Search
- Categorical Data
- K-means
- Bootstrap Aggregation
- Cross Validation
- AUC - ROC Curve
- K-nearest neighbors
- Python MySQL
- Python - MySQL
- MySQL Get Started
- MySQL Create Database
- MySQL Create Table
- MySQL Insert
- MySQL Select
- MySQL Where
- MySQL Order By
- MySQL Delete
- MySQL Drop Table
- MySQL Update
- MySQL Limit
- MySQL Join
- Python MongoDB
- Python - MongoDB
- MongoDB Get Started
- MongoDB Create DB
- MongoDB Collection
- MongoDB Insert
- MongoDB Find
- MongoDB Query
- MongoDB Sort
- MongoDB Delete
- MongoDB Drop Collection
- MongoDB Update
- MongoDB Limit
- Selected Reading
- Q&A
Python Creating a Table:
Creating a Table
To create a table in MySQL, use the "CREATE TABLE" statement.
Make sure you define the name of the database when you create the connection
Example
Create a table named "customers":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255),
address VARCHAR(255))")
Run example »
If the above code was executed with no errors, you have now successfully created a table.
Check if Table Exists
You can check if a table exist by listing all tables in your database with the "SHOW TABLES" statement:
Example
Return a list of your system's databases:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
Run example »
Primary Key
When creating a table, you should also create a column with a unique key for each record.
This can be done by defining a PRIMARY KEY.
We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.
Example
Create primary key when creating the table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT
PRIMARY KEY, name VARCHAR(255),
address VARCHAR(255))")
Run example »
If the table already exists, use the ALTER TABLE keyword:
Example
Create primary key on an existing table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT
PRIMARY KEY")
Run example »