import re
try:
import pymysql
except:
!pip install pymysql
import pymysql
def create_mysql_primary_key_table(table, columns):
db = pymysql.connect(host='ithome.uk.to',user='kerek',password='',database='colab',charset="utf8")
cursor = db.cursor()
sql_statement = f"CREATE TABLE {table}(ID INT AUTO_INCREMENT PRIMARY KEY, "
for column in columns:
sql_statement+=f"""`{column}` text,"""
sql_statement = re.sub("[,]$", "", sql_statement)
sql_statement+=");"
cursor.execute(sql_statement)
db.commit()
db.close()
def insert_mysql_primary_key_data(table, columns, allData):
db = pymysql.connect(host='ithome.uk.to',user='kerek',password='',database='colab')
cursor = db.cursor()
sql_statement = f"""insert into {table}("""
for column in columns:
sql_statement+=f"`{column}`,"
sql_statement = re.sub("[,]$", "", sql_statement)
sql_statement+=") values("
for value in allData[0]:
sql_statement+=f"""%s,"""
sql_statement = re.sub("[,]$", "", sql_statement)
sql_statement+=")"
for values in allData:
cursor.execute(sql_statement, values)
db.commit()
db.close()
def read_data_to_mysql(table, column_name, column_value):
db = pymysql.connect(host='ithome.uk.to',user='kerek',password='',database='colab')
cursor = db.cursor()
sql_statement = f"""SELECT * FROM {table} WHERE `{column_name}` = '{column_value}'""" #can be selected column name
cursor.execute(sql_statement)
rows = list(map(lambda x: list(x), cursor.fetchall()))
print(f'Total Results have: {len(rows)}')
for i, row in enumerate(rows):
print(row)
db.close()
def update_data_to_mysql(table, updated_column, updated_values, reference_column, reference_data):
db = pymysql.connect(host='ithome.uk.to',user='kerek',password='',database='colab')
cursor = db.cursor()
sql_statement = f"""UPDATE {table} SET `{updated_column}`='{updated_values}' WHERE `{reference_column}`='{reference_data}'"""
cursor.execute(sql_statement)
db.commit()
db.close()
def delete_data_to_mysql(table, column_name, column_value,):
db = pymysql.connect(host='ithome.uk.to',user='kerek',password='',database='colab')
cursor = db.cursor()
sql_statement = f"""Delete From {table} where `{column_name}` = '{column_value}'"""
cursor.execute(sql_statement)
db.commit()
db.close()
table = 'testing'
columns = ['title', 'text']
allData = [["python3","learning3"], ["KET", "Dear JANE"]]
create_mysql_primary_key_table(table, columns)
insert_mysql_primary_key_data(table, columns, allData)