![]() ![]() This diagram shows that one classroom consists of multiple students, and each student has multiple grades. ![]() We can visualize our database schema with an entity-relationship diagram like the one below. That’s more than a third less storage space just by rearranging the data!īut it’s not enough to just store the data in separate tables we still need to model their relationships. That’s only 30 cells compared to 42 in the main table $-$ a 28.5% improvement! For this particular set of fields and tables, as we increase the number of students, say to 100 or 1,000 or 1,000,000, the improvement actually stabilizes at 38%. Instead, it’d be far more efficient to break out this information into separate tables, then relate the info in the tables to one another. But as the amount of data grows, all those duplicated values end up costing storage space and making it harder to extract the data you actually want from your table. If you only have a handful of students, it’s no big deal. Because students have multiple exam scores, storing all the data in one table requires duplicating info we only need to list once, like Jerry’s hobby, classroom ID, and teacher. This is a pretty inefficient way to store data, though. We could have one giant table that looks like this: Say we’re a school and are organizing our data on students, grades, and classrooms. The relevant data can then be extracted from different tables, filtered, and rearranged with queries in SQL, or Structured Query Language. The main idea with a relational database is to avoid duplicating data by storing it only once, with different aspects of that data stored in tables with formal relationships. Let’s do it! A time series of databases SQL databasesĭatabases arrived shortly after businesses began adopting computers in the 1950s, but it wasn’t until 1970 that relational databases appeared. But memorizing abstract facts can only get you so far $-$ we’ll then actually create each type of database in Python to build an intuition for how they work. We’ll start with a brief primer on the history and theory behind SQL and NoSQL. Yesterday’s filing cabinets have become today’s computer databases, with two major paradigms for how to best organize data: the relational (SQL) versus non-relational (NoSQL) approach.ĭatabases are essential for any organization, so it’s useful to wrap your head around where each type is useful. ![]() Then the following one works total.From ancient government, library, and medical records to present-day video and IoT streams, we have always needed ways to efficiently store and retrieve data. Sql_engine = create_engine(db_connection_str) In order me to achieve task, I have to create engine link sqlalchemy and pymysql db_connection_str = 'mysql+pymysql://****' Index=True, index_label=None, chunksize=None, dtype=None) Schema= 'HEALTH_PLAN', if_exists='append', However, I failed when I tried to insert row into table. Sql.execute(create_table_query, etl_conn) Pd.read_sql(query, etl_conn, index_col = 'index')Īnd I can create a table by using pandas.io and pymysql from pandas.io import sqlĬreate_table_query ='CREATE TABLE WD_PNL_MONTHLY_05052020 AS SELECT * FROM ![]() Query = 'select * from HEALTH_PLAN.WD_PNL_MONTHLY' So i should have achieved all database action by combining those two.įor example, based on the following etl_conn = nnect(host = host, user = user,password = passwd,db = db_health_plan) Since pandas have read_sql method method, what is engine from sqlalechmy in thereĪnd difference/relationship between engine and connectionīased on the official document pymysql: This package contains a pure-Python MySQL client library.įor my understanding, basically, it is the driver which connect between python to mysql. however, some of the database action, I have to use sqlachemy to create engine. In order to achieve better automation task, I start getting to know pymysql.įor me, I found some of database action I can achieve only by pymysql+pandas action. I have used a lot of python pandas and MySQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |