8000 How to Convert MySQL Table to Pandas DataFrame / Python Dictionary · softhints/python@6ec4447 · GitHub
[go: up one dir, main page]
Skip to content

Commit 6ec4447

Browse files
softhintssofthints
committed
How to Convert MySQL Table to Pandas DataFrame / Python Dictionary
1 parent 6f2e85f commit 6ec4447

File tree

1 file changed

+222
-0
lines changed

1 file changed

+222
-0
lines changed

‎notebooks/python/JSON/42._Convert_MySQL_table_to_Pandas_DataFrame_Python_dictionary.ipynb

Lines changed: 222 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,222 @@
1+
{
2+
"cells": [
3+
{
4+
"cell_type": "markdown",
5+
"metadata": {},
6+
"source": [
7+
"# 42. Convert MySQL table to Pandas DataFrame(Python dictionary)\n",
8+
"\n",
9+
"\n",
10+
"[How to Convert MySQL Table to Pandas DataFrame / Python Dictionary](https://blog.softhints.com/convert-mysql-table-pandas-dataframe-python-dictionary/)\n",
11+
"\n",
12+
"* [PyMySQL](https://pypi.org/project/PyMySQL/) + [SQLAlchemy](https://pypi.org/project/SQLAlchemy/) - the shortest and easiest way to convert MySQL table to Python dict\n",
13+
"* [mysql.connector](https://pypi.org/project/mysql-connector-python/)\n",
14+
"* [pyodbc](https://pypi.org/project/pyodbc/) in order to connect to MySQL database, read table and convert it to DataFrame or Python dict."
15+
]
16+
},
17+
{
18+
"cell_type": "markdown",
19+
"metadata": {},
20+
"source": [
21+
"![](https://blog.softhints.com/content/images/2020/11/MySQL_table_to_Pandas_DataFrame_to_Python_dict.png)"
22+
]
23+
},
24+
{
25+
"cell_type": "code",
26+
"execution_count": 7,
27+
"metadata": {},
28+
"outputs": [],
29+
"source": [
30+
"password = ''"
31+
]
32+
},
33+
{
34+
"cell_type": "markdown",
35+
"metadata": {},
36+
"source": [
37+
"## 1: Convert MySQL Table to DataFrame with PyMySQL + SQLAlchemy "
38+
]
39+
},
40+
{
41+
"cell_type": "code",
42+
"execution_count": 2,
43+
"metadata": {},
44+
"outputs": [
45+
{
46+
"data": {
47+
"text/plain": [
48+
"{'id': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5},\n",
49+
" 'name': {0: 'Emma', 1: 'Ann', 2: 'Kim', 3: 'Olivia', 4: 'Victoria'}}"
50+
]
51+
},
52+
"execution_count": 2,
53+
"metadata": {},
54+
"output_type": "execute_result"
55+
}
56+
],
57+
"source": [
58+
"from sqlalchemy import create_engine\n",
59+
"import pymysql\n",
60+
"import pandas as pd\n",
61+
"\n",
62+
"db_connection_str = 'mysql+pymysql://root:' + password + '@localhost:3306/test'\n",
63+
"db_connection = create_engine(db_connection_str)\n",
64+
"\n",
65+
"df = pd.read_sql('SELECT * FROM girls', con=db_connection)\n",
66+
"df.to_dict()"
67+
]
68+
},
69+
{
70+
"cell_type": "code",
71+
"execution_count": 3,
72+
"metadata": {},
73+
"outputs": [
74+
{
75+
"data": {
76+
"text/plain": [
77+
"[{'id': 1, 'name': 'Emma'},\n",
78+
" {'id': 2, 'name': 'Ann'},\n",
79+
" {'id': 3, 'name': 'Kim'},\n",
80+
" {'id': 4, 'name': 'Olivia'},\n",
81+
" {'id': 5, 'name': 'Victoria'}]"
82+
]
83+
},
84+
"execution_count": 3,
85+
"metadata": {},
86+
"output_type": "execute_result"
87+
}
88+
],
89+
"source": [
90+
"df.to_dict('records')"
91+
]
92+
},
93+
{
94+
"cell_type": "code",
95+
"execution_count": 4,
96+
"metadata": {},
97+
"outputs": [
98+
{
99+
"data": {
100+
"text/plain": [
101+
"{'id': [1, 2, 3, 4, 5], 'name': ['Emma', 'Ann', 'Kim', 'Olivia', 'Victoria']}"
102+
]
103+
},
104+
"execution_count": 4,
105+
"metadata": {},
106+
"output_type": "execute_result"
107+
}
108+
],
109+
"source": [
110+
"df.to_dict('list')"
111+
]
112+
},
113+
{
114+
"cell_type": "code",
115+
"execution_count": 5,
116+
"metadata": {},
117+
"outputs": [
118+
{
119+
"data": {
120+
"text/plain": [
121+
"{0: {'id': 1, 'name': 'Emma'},\n",
122+
" 1: {'id': 2, 'name': 'Ann'},\n",
123+
" 2: {'id': 3, 'name': 'Kim'},\n",
124+
" 3: {'id': 4, 'name': 'Olivia'},\n",
125+
" 4: {'id': 5, 'name': 'Victoria'}}"
126+
]
127+
},
128+
"execution_count": 5,
129+
"metadata": {},
130+
"output_type": "execute_result"
131+
}
132+
],
133+
"source": [
134+
"df.to_dict('index')"
135+
]
136+
},
137+
{
138+
"cell_type": "markdown",
139+
"metadata": {},
140+
"source": [
141+
"## 2: Convert MySQL Table to DataFrame with mysql.connector"
142+
]
143+
},
144+
{
145+
"cell_type": "code",
146+
"execution_count": 6,
147+
"metadata": {},
148+
"outputs": [
149+
{
150+
"data": {
151+
"text/plain": [
152+
"{0: {0: 1, 1: 2, 2: 3, 3: 4, 4: 5},\n",
153+
" 1: {0: bytearray(b'Emma'),\n",
154+
" 1: bytearray(b'Ann'),\n",
155+
" 2: bytearray(b'Kim'),\n",
156+
" 3: bytearray(b'Olivia'),\n",
157+
" 4: bytearray(b'Victoria')}}"
158+
]
159+
},
160+
"execution_count": 6,
161+
"metadata": {},
162+
"output_type": "execute_result"
163+
}
164+
],
165+
"source": [
166+
"import pandas as pd\n",
167+
"import mysql.connector\n",
168+
"\n",
169+
"# Setup MySQL connection\n",
170+
"db = mysql.connector.connect(\n",
171+
" host=\"localhost\", # your host, usually localhost\n",
172+
" user=\"root\", # your username\n",
173+
" password=password, # your password\n",
174+
" database=\"test\" # name of the data base\n",
175+
") \n",
176+
"\n",
177+
"# You must create a Cursor object. It will let you execute all the queries you need\n",
178+
"cur = db.cursor()\n",
179+
"\n",
180+
"# Use all the SQL you like\n",
181+
"cur.execute(\"SELECT * FROM girls\")\n",
182+
"\n",
183+
"# Put it all to a data frame\n",
184+
"df_sql_data = pd.DataFrame(cur.fetchall())\n",
185+
"\n",
186+
"# Close the session\n",
187+
"db.close()\n",
188+
"\n",
189+
"# Show the data\n",
190+
"df_sql_data.to_dict()"
191+
]
192+
},
193+
{
194+
"cell_type": "code",
195+
"execution_count": null,
196+
"metadata": {},
197+
"outputs": [],
198+
"source": []
199+
}
200+
],
201+
"metadata": {
202+
"kernelspec": {
203+
"display_name": "Python 3",
204+
"language": "python",
205+
"name": "python3"
206+
},
207+
"language_info": {
208+
"codemirror_mode": {
209+
"name": "ipython",
210+
"version": 3
211+
},
212+
"file_extension": ".py",
213+
"mimetype": "text/x-python",
214+
"name": "python",
215+
"nbconvert_exporter": "python",
216+
"pygments_lexer": "ipython3",
217+
"version": "3.8.4"
218+
}
219+
},
220+
"nbformat": 4,
221+
"nbformat_minor": 2
222+
}

0 commit comments

Comments
 (0)
0