Add column sql python

How to Add a Column to a MySQL Table in Python?

Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Connector-Python module is an API in python for communicating with a MySQL database.

ALTER statement of SQL

With the ALTER statement, one can add, drop, or modify a column of an existing table as well as modify table constraints.

The syntax for adding a column with ALTER statement:

ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER column_name];

Here, the table_name is the name of the table to which the column is being added, new_column_name is the name of the column to be added and column_definition is the datatype and definition of the column to be added. FIRST and AFTER are optional statements that tell MySQL the position for the new column in the table. If this parameter is not specified then the new column is added to the end of the table.

Implementation:

To add a column to a MySQL table in Python, first establish a connection with the database server. Then create a cursor object. This cursor object interacts with the MySQL server and can be used to perform operations such as execute SQL statements, fetch data and call procedures. So, using this cursor object, execute the ALTER statement to add a column either at the end or at a specific position. Let’s see some examples for better understanding.

Database in use:

We will use a database with a students table describing student details such as roll number and name. Before we learn to add a column to a table, create such a sample table (inserting values is optional).

This example shows the addition of a column at the end of the table. Steps are as follows:

• Use the connect() function to establish a connection with the database server. Pass the host, user (root or your username), password (if present), and database parameters to the connect() method.
• Then to create a cursor object, use the cursor() function.
• Execute the ALTER statement for adding the streaming column to the students table.
• To check if the column has been added execute and fetch the result of the DESC statement to describe the structure of the table.

Источник

Alter A SQLite Table Using Python

Example 1 – Changing the name of the SQLite table using Python:

# ——Example Python Program to alter an SQLite Table——

# import the sqlite3 module

# Create a connection object

renameTable = «ALTER TABLE stud RENAME TO student»

# Query the SQLite master table

tableQuery = «select * from sqlite_master»

# Print the updated listed of tables after renaming the stud table

print(«Database Object Type: %s»%(table[0]))

print(«Name of the database object: %s»%(table[1]))

print(«Name of the table: %s»%(table[2]))

print(«SQL Statement: %s»%(table[4]))

# close the database connection

Output:

Database Object Type: table

Name of the database object: teacher

Name of the table: teacher

SQL Statement: CREATE TABLE teacher(id int,firstname varchar(32),lastname varchar(32))

Database Object Type: table

Name of the database object: student

Name of the table: student

SQL Statement: CREATE TABLE «student»(id int,firstname varchar(32),lastname varchar(32))

Example 2 – Adding one or more new columns to an SQLite table using Python:

# ——Example Python Program to add new columns to an existing SQLite Table——

# import the module sqlite3

# Make a connection to the SQLite DB

# Obtain a Cursor object to execute SQL statements

# Add a new column to student table

addColumn = «ALTER TABLE student ADD COLUMN Address varchar(32)»

# Add a new column to teacher table

addColumn = «ALTER TABLE teacher ADD COLUMN Address varchar(32)»

# Retrieve the SQL statment for the tables and check the schema

masterQuery = «select * from sqlite_master»

print(«Database Object Type: %s»%(table[0]))

print(«Database Object Name: %s»%(table[1]))

print(«**SQL Statement**: %s»%(table[4]))

# close the database connection

Output:

Database Object Type: table

Database Object Name: teacher

**SQL Statement**: CREATE TABLE teacher(id int,firstname varchar(32),lastname varchar(32), Address varchar(32))

Database Object Type: table

Database Object Name: student

**SQL Statement**: CREATE TABLE «student»(id int,firstname varchar(32),lastname varchar(32), Address varchar(32))

Источник

How to add a column to a MySQL table in Python?

It may be sometimes required to add a new column in a existing table. Suppose we have a “Students” table with columns such as Name, Age, Roll no. We want to add a new column “Address” into our existing table.

This can be done by using the ALTER command. The ALTER command is used to modify, drop or update columns in the database. This can also be used to add a new column into the table using ADD clause.

Syntax

ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER exisiting_column]

Here, table_name refers to the name of the table, new_column_name refers to the name of the column to be added, column_definition refers to the datatype of the column.

The FIRST AND AFTER clause are optional.This are used to specify the particular position at which you want to add the new column. The FIRST will insert the new column at the first position. The AFTER existing_column will insert a new column after the existing_column.

By default, the new column is inserted at the end of the table.

Steps to add new column in a table using MySQL in python

• import MySQL connector
• establish connection with the connector using connect()
• create the cursor object using cursor() method
• create a query using the appropriate mysql statements
• execute the SQL query using execute() method
• close the connection

Example

Suppose, we have a table named “Students”. We want to add a new column named “Address” of type VARCHAR(100) in the table.

import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="ALTER TABLE Students ADD Address VARCHAR(100)" cursor.execute(query) db.commit() print("NEW COLUMN ADDED..") db.close()

The above code adds a new column named “Address” into the table. The column is inserted at the last of the existing columns.

Источник

How to Add a Column to a MySQL TAble In Python

In this article, we show how to add a column to a MySQL table in Python.

We show how to add a column at any point of the table, including at the beginning, the end, or after any specific column on the table.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

How to Add a Column to the End of a MySQL Table

So, the code to add a column to the end of a MySQL table in Python is shown below.

The full code to add a column to the end of a MySQL in Python is shown below.

So, this is all the code that is needed to add a column to the end of a MySQL table in Python.

So, the first thing we have to do is import the MySQLdb.

Once we have MySQLdb imported, then we create a variable named db. We set db equal to the MySQLdb.connect() function. This function will allow us to connect to a database. In side of this MySQLdb.connect() function are 4 parameters. These are, MySQLdb.connect(«hostname», «username», «password», «database_name»). This line establishes connection with the database that you want.

We then create a variable named cursor, which we set equal to db.cursor(). This establishes a cursor for the database.

Next, we execute our function to add a column to the table using the cursor.execute() function. Inside of this function, we place in the line, «ALTER TABLE table_name ADD email VARCHAR(100) NOT NULL». So the whole line of code, is, cursor.execute(«ALTER TABLE table_name ADD email VARCHAR(100) NOT NULL»).

If the function executes and the column is added to the table, a ‘0’ will be output. This alerts you that the column has been successfully added to the table.

How to Add a Column To the Beginning of a Table

The code to add a column to the beginning of a MySQL table in Python is shown below.

We won’t go through the entire code again, but this the cursor.execute() line is the only line that changes.

The code, above, will add the column, ID, to the table, Table_name. The ID column is an int of length 11, not null, auto increments (increments by 1 with each new entry), and is the primary key for the table.

The keyword FIRST is what places the new added column at the beginning.

How to Add a Column After a Specific Column of the Table

The code to add a column after a specific column in a table in shown below.

So, the code above adds a column, named email, of type of VARCHAR of length 50 that is not null after the column, lastname.

The keyword, AFTER, followed by the column name puts the new column after that specified column.

So, this is how you can add a column to MySQL table in Python, at any place in the table.

Источник