how to automate data parsing with version and primary key into mysql using python
from mysql import connector as mysql
# Details for our MySql connection
DATABASE = {
"host": "localhost",
"user": "airflow",
"password": "python2019",
"db": "airflowdb",
}
# ----------------------------------------------
# Database related functions
# ----------------------------------------------
def connect_db(my_database):
"""Connect to a given my_database
Args:
my_database(dict): dictionary with the my_database details
Returns:
dbconnect: MySql my_database connection object
"""
try:
dbconnect = mysql.connect(
host=my_database.get("host"),
user=my_database.get("user"),
password=my_database.get("password"),
db=my_database.get("db"),
)
print("connected")
return dbconnect
except mysql.Error as e:
print(e)
0
2
def create_table(my_database, new_table):
"""Create new table in a my_database
Args:
my_database (dict): details for the db
new_table (str): name of the table to create
"""
dbconnect = connect_db(my_database)
# create a cursor for the queries
cursor = dbconnect.cursor()
cursor.execute("USE airflowdb")
# here we delete the table, it can be kept or else
cursor.execute(f"DROP TABLE IF EXISTS {new_table}")
# these matches the Twitter data
query = (
f"CREATE TABLE `{new_table}` ("
" `id` INT(11) NOT NULL AUTO_INCREMENT,"
" `user` varchar(100) NOT NULL ,"
" `created_at` timestamp,"
" `tweet` varchar(255) NOT NULL,"
" `retweet_count` int(11) ,"
" `id_str` varchar(100),"
" PRIMARY KEY (`id`))"
)
cursor.execute(query)
dbconnect.close()
cursor.close()
return print(f"Created {new_table} table")
Thank you!
2
0
0
Are there any code examples left?
New code examples in category SQL
