Create mysql table with python

Python with MySQL Connectivity: Database & Table [Examples]

MySQL is an Open-Source database and one of the best type of RDBMS (Relational Database Management System). Co-founder of MySQLdb is Michael Widenius’s, and also MySQL name derives from the daughter of Michael.

How to Install MySQL Connector Python on Windows, Linux/Unix

Install MySQL in Linux/Unix:

Download RPM package for Linux/Unix from Official site: https://www.mysql.com/downloads/

In terminal use following command

Example rpm -i MySQL-5.0.9.0.i386.rpm

To check in Linux

Install MySQL in Windows

Download MySQL database exe from official site and install as usual normal installation of software in Windows. Refer this tutorial, for a step by step guide

How to Install MySQL Connector Library for Python

Here is how to connect MySQL with Python:

For Python 2.7 or lower install using pip as:

pip install mysql-connector

For Python 3 or higher version install using pip3 as:

pip3 install mysql-connector

Test the MySQL Database connection with Python

To test MySQL database connectivity in Python here, we will use pre-installed MySQL connector and pass credentials into connect() function like host, username and password as shown in the below Python MySQL connector example.

Syntax to access MySQL with Python:

import mysql.connector db_connection = mysql.connector.connect( host="hostname", user="username", passwd="password" )

import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root" ) print(db_connection)

Here output shows the connection created successfully.

Creating Database in MySQL using Python

Syntax to Create new database in SQL is

CREATE DATABASE "database_name"

Now we create database using database programming in Python

import mysql.connector db_connection = mysql.connector.connect( host= "localhost", user= "root", passwd= "root" ) # creating database_cursor to perform SQL operation db_cursor = db_connection.cursor() # executing cursor with execute method and pass SQL query db_cursor.execute("CREATE DATABASE my_first_db") # get list of all databases db_cursor.execute("SHOW DATABASES") #print all databases for db in db_cursor: print(db)

Here above image shows the my_first_db database is created

Create a Table in MySQL with Python

Let’s create a simple table “student” which has two columns as shown in the below MySQL connector Python example.

CREATE TABLE student (id INT, name VARCHAR(255))

import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root", database="my_first_db" ) db_cursor = db_connection.cursor() #Here creating database table as student' db_cursor.execute("CREATE TABLE student (id INT, name VARCHAR(255))") #Get database table' db_cursor.execute("SHOW TABLES") for table in db_cursor: print(table)

Create a Table with Primary Key

Let’s create an Employee table with three different columns. We will add a primary key in id column with AUTO_INCREMENT constraint as shown in the below Python project with database connectivity.

CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))

import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root", database="my_first_db" ) db_cursor = db_connection.cursor() #Here creating database table as employee with primary key db_cursor.execute("CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))") #Get database table db_cursor.execute("SHOW TABLES") for table in db_cursor: print(table)

ALTER table in MySQL with Python

Alter command is used for modification of Table structure in SQL. Here we will alter Student table and add a primary key to the id field as shown in the below Python MySQL connector project.

ALTER TABLE student MODIFY id INT PRIMARY KEY

import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root", database="my_first_db" ) db_cursor = db_connection.cursor() #Here we modify existing column id db_cursor.execute("ALTER TABLE student MODIFY id INT PRIMARY KEY")

Here below you can see the id column is modified.

Insert Operation with MySQL in Python:

Let’s perform insertion operation in MySQL Database table which we already create. We will insert data oi STUDENT table and EMPLOYEE table.

INSERT INTO student (id, name) VALUES (01, "John") INSERT INTO employee (id, name, salary) VALUES(01, "John", 10000)

import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root", database="my_first_db" ) db_cursor = db_connection.cursor() student_sql_query = "INSERT INTO student(id,name) VALUES(01, 'John')" employee_sql_query = " INSERT INTO employee (id, name, salary) VALUES (01, 'John', 10000)" #Execute cursor and pass query as well as student data db_cursor.execute(student_sql_query) #Execute cursor and pass query of employee and data of employee db_cursor.execute(employee_sql_query) db_connection.commit() print(db_cursor.rowcount, "Record Inserted")

Источник

Python MySQL Create 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»:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

mycursor.execute(«CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))»)

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:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

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:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

mycursor.execute(«CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))»)

If the table already exists, use the ALTER TABLE keyword:

Example

Create primary key on an existing table:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

mycursor.execute(«ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY»)

Источник

Python MySQL — Create and List Table

Now we will learn how to create tables in any MySQL database in Python and we will also see how to check if a table already exists or not by listing down all the tables in a database using Python.

Python MySQL — Create Table

Basically, to store information in the MySQL database there is a need to create the tables. It is also required to select our database first and then create tables inside that database.

At the time of creating the connection, you can also specify the name of your database, like given below:

import mysql.connector db = mysql.connector.connect( host = "localhost", user = "yourusername", password = "yourpassword", database = "studytonight" )

If the above code is executed without any errors then it means you have successfully connected to the database named studytonight.

SQL Query to Create Table

To create a table in the selected database the following statement will be used. Let us see the syntax:

Let us create a table named students in the specified database, that is studytonight

In the table students we will have the following fields: name, rollno, branch, and address.

#for our convenience we will import mysql.connector as mysql import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database="studytonight" ) cursor = db.cursor() cursor.execute("CREATE TABLE students (name VARCHAR(255), rollno INTEGER(100), branch VARCHAR(255), address VARCHAR(255))")

If this code executes without any error then it means the table has been created successfully.

Now, If you want to check the existing tables in the database then you can use the SHOW TABLES SQL statement.

List existing Tables in a Database

Now as we have created a table in our database. Let us check the tables that exist in our database. Use the code given below:

#for our convenience we will import mysql.connector as mysql import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database="studytonight" ) cursor = db.cursor() ## getting all the tables which are present in 'datacamp' database cursor.execute("SHOW TABLES") tables = cursor.fetchall() ## it returns list of tables present in the database ## showing all the tables one by one for table in tables: print(table)

The output of the above code is as follows

Python MySQL — Table with Primary Key

As we had created a table named students in our database, in which we will store student data and fetch it whenever required. But while fetching data, we might find students with same name and that can lead to wrong data getting fetched, or cause some confusion.

So to uniquely identify each record in a table we can use Primary Key in our tables. Let us see first what is a Primary key?

What is Primary Key?

A primary key is an attribute to make a column or a set of columns accept only unique values. With the help of the primary key, one can find each row uniquely in the table.

Watch this video to learn about DBMS Keys — DBMS Keys Explained with Examples

Thus in order to identify each row uniquely with a number starting from 1. We will use the syntax as follows:

INT AUTO_INCREMENT PRIMARY KEY

Using the above code with any column, we can make its value as auto increment, which means the database will automatically add an incremented value even if you do not insert any value for that column while inserting a new row of data to your table.

Add Primary Key during Table creation

Let us see how to add a primary key at the time of table creation. The code snippet for the same is given below:

import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database = "studytonight" ) cursor = db.cursor() ## creating the 'students' table with the 'PRIMARY KEY' cursor.execute("CREATE TABLE students (name VARCHAR(255), rollno INTEGER(100) NOT NULL AUTO_INCREMENT PRIMARY KEY, branch VARCHAR(255), address VARCHAR(255))")

If the above code runs without an error then it means you have successfully created a table named «students» with the column rollno as primary key.

Python MySQL — Describe the Table

We can use the below python code to describe any table to see what all columns it has and all the meta information about the table and all its columns.

import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database = "studytonight" ) cursor = db.cursor() cursor.execute("DESC students") print(cursor.fetchall())

[(‘name’, ‘varchar(255)’, ‘YES’, », None, »), (‘rollno’, ‘int’, ‘NO’, ‘PRI’, None, ‘auto_increment’), (‘branch’, ‘varchar(255)’, ‘YES’, », None, »), (‘address’, ‘varchar(255)’, ‘YES’, », None, »)]

Add Primary Key to Existing Table

Now in this example, we will assume that rollno column does not exist in our student table. So we will learn how to add a column to be used as primary key in an existing table.

Let us see how to create a primary key on an existing table. The code snippet for the same is given below:

import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "root", passwd = "himaniroot@99", database = "studytonight" ) cursor = db.cursor() ## We are going to add rollno field with primary key in table students cursor.execute("ALTER TABLE students ADD COLUMN rollno INT AUTO_INCREMENT PRIMARY KEY") print(cursor.fetchall())

In the above code we have used the SQL Alter Query to add a new column to an existing table. You can use the describe table query to see the new added column.

[(‘name’, ‘varchar(255)’, ‘YES’, », None, »), (‘branch’, ‘varchar(255)’, ‘YES’, », None, »), (‘address’, ‘varchar(255)’, ‘YES’, », None, »), (‘rollno’, ‘int’, ‘NO’, ‘PRI’, None, ‘auto_increment’)]

So in this tutorial we have covered everything related to creating a new MySQL table in python. We covered how to alter a table, how to list down all the tables in a database, and how to define a column as a primary key.

Источник