We will use SQL INSERT to add a record to our database student table.
We defined my_conn as connection object.
In all the codes below replace userid, password and database_name with your MySQL login and database details.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
We are using student table. Use the code to create your sample student table.
Add single record to student table.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
id=my_conn.execute("INSERT INTO `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) \
VALUES ('King1', 'Five', '45', 'male')")
print("Row Added = ",id.rowcount)
Output
Row Added = 1
In our student table we have used one auto incremented id column. We will collect this id after adding record to database.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
id=my_conn.execute("INSERT INTO `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) \
VALUES ('King1', 'Five', '45', 'male')")
print("ID of Row Added = ",id.lastrowid)
Output
ID of Row Added = 46
Using parameterized query to add record
Always use parameterized query when the data is coming from unknown sources. This is required to prevent injection attack.
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
query="INSERT INTO `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) VALUES(%s,%s,%s,%s)"
my_data=('King','Five',45,'male')
id=my_conn.execute(query,my_data)
print("ID of Row Added = ",id.lastrowid)
Output
ID of Row Added = 45
Using multiple records to add
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
try:
query="INSERT INTO `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) VALUES(%s,%s,%s,%s)"
my_data=[('King','Five',45,'male'),
('Queen','Four',44,'Female'),
('Jack','Three',42,'male')]
id=my_conn.execute(query,my_data)
print("Rows Added = ",id.rowcount)
except:
print("Database error ")
Output
Rows Added = 3
Capturing error message
If we want specific error message then can use like this.
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
try:
query="INSERT INTO `student9` (`name` ,`class` ,`mark` ,`sex`) VALUES(%s,%s,%s,%s)"
my_data=[('King','Five',45,'male'),
('Queen','Four',44,'Female'),
('Jack','Three',42,'male')]
id=my_conn.execute(query,my_data)
print("Rows Added = ",id.rowcount)
except SQLAlchemyError as e:
error = str(e.__dict__['orig'])
print(error)
Listing all tables of the database
r_set=my_conn.execute("SHOW TABLES");
for row in r_set:
print(row)
Listing all row of a table
r_set=my_conn.execute("SELECT * FROM student");
for row in r_set:
print(row)
By using Tkinter we can create one GUI interface to store user entered inputs in MySQL table
Tkinter window to add user entered data to MySQL table
« More on Error handling
← Subscribe to our YouTube Channel here