Spaces:
Runtime error
Runtime error
File size: 3,624 Bytes
31d6ed6 1bb8aca 31d6ed6 1bb8aca |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
### In this file we will store all the codes related to connection to my sql server.
import mysql.connector
import pandas as pd
###======================================================================database details-=======================================================
def database_details(host,user,password):
connection = mysql.connector.connect(
host = host,
user = user,
password = password,
buffered = True
)
cursor = connection.cursor()
databases = ("Show databases")
cursor.execute(databases)
db = []
for (databases) in cursor:
db.append(databases[0])
cursor.close()
connection.close()
return db, len(db)
#### =========================================================================retrieving the tables==========================================================
def tables_in_this_DB(host,user,password,db_name):
db_config = {
'host':host,
'user': user,
'password': password,
'database': db_name,
}
connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()
query1 = "SHOW TABLES"
cursor.execute(query1)
tables = cursor.fetchall()
cursor.close()
connection.close()
return tables, len(tables)
#### ==================================================Printing the tables=======================================================================
def printing_tables(host,user,password,db_name):
db_config = {
'host':host,
'user': user,
'password': password,
'database': db_name,
}
connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()
cursor.execute("SHOW TABLES")
table_names = [table[0] for table in cursor.fetchall()]
tables_data = {}
for table_name in table_names:
query = f"SELECT * FROM {table_name}"
cursor.execute(query)
rows = cursor.fetchall()
col_names = [desc[0] for desc in cursor.description]
df = pd.DataFrame(rows, columns=col_names)
tables_data[table_name] = df
cursor.close()
connection.close()
return tables_data
def create_table_command(host,user,password,db_name):
db_config = {
'host': host,
'user': user,
'password': password,
'database': db_name,
}
connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()
query = "SHOW TABLES"
cursor.execute(query)
table_names = [table[0] for table in cursor.fetchall()]
create_table_statements = {}
for table_name in table_names:
query = f"SHOW CREATE TABLE {table_name}"
cursor.execute(query)
create_table_data = cursor.fetchone()
if create_table_data:
# The CREATE TABLE statement is in the second element of the tuple
create_table_statement = create_table_data[1]
create_table_statement = create_table_statement.split("ENGINE=")[0].strip()
create_table_statements[table_name] = create_table_statement
cursor.close()
connection.close()
return create_table_statements
def retrieve_result(host,user,password,db_name,query):
db_config = {
'host': host,
'user': user,
'password': password,
'database': db_name,
}
connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()
query = query
cursor.execute(query)
res = cursor.fetchall()
cursor.close()
connection.close()
return res |