Skip to content

Instantly share code, notes, and snippets.

@akashsoni01

akashsoni01/database bulk insert and export using panda python.ipynb

Created January 27, 2024 08:01
Show Gist options
  • Save akashsoni01/d9ffb55001a68a86b330a74fecba9648 to your computer and use it in GitHub Desktop.
Save akashsoni01/d9ffb55001a68a86b330a74fecba9648 to your computer and use it in GitHub Desktop.
Download ZIP
database bulk insert and export using panda python.ipynb
Display the source blob
Display the rendered blob
Raw
database bulk insert and export using panda python.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 94,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Requirement already satisfied: pandas in /home/pro/anaconda3/lib/python3.11/site-packages (2.0.3)\n",
"Requirement already satisfied: python-dateutil>=2.8.2 in /home/pro/anaconda3/lib/python3.11/site-packages (from pandas) (2.8.2)\n",
"Requirement already satisfied: pytz>=2020.1 in /home/pro/anaconda3/lib/python3.11/site-packages (from pandas) (2023.3.post1)\n",
"Requirement already satisfied: tzdata>=2022.1 in /home/pro/anaconda3/lib/python3.11/site-packages (from pandas) (2023.3)\n",
"Requirement already satisfied: numpy>=1.21.0 in /home/pro/anaconda3/lib/python3.11/site-packages (from pandas) (1.24.3)\n",
"Requirement already satisfied: six>=1.5 in /home/pro/anaconda3/lib/python3.11/site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n",
"Requirement already satisfied: py-ulid in /home/pro/anaconda3/lib/python3.11/site-packages (1.0.3)\n",
"Requirement already satisfied: py-ulid in /home/pro/anaconda3/lib/python3.11/site-packages (1.0.3)\n"
]
}
],
"source": [
"!pip install pandas\n",
"!pip install py-ulid"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Imports "
]
},
{
"cell_type": "code",
"execution_count": 95,
"metadata": {},
"outputs": [],
"source": [
"import sqlite3\n",
"import pandas as pd\n",
"import sqlalchemy\n",
"import pyodbc, os\n",
"import psycopg2\n",
"from sqlalchemy import create_engine\n",
"from ulid import ULID\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Change directory to your workspace"
]
},
{
"cell_type": "code",
"execution_count": 135,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"['data.csv', 'amazon_dataset.csv']\n"
]
}
],
"source": [
"### Open dataset directory \n",
"os.chdir('/home/pro/Documents/GitHub/scalable_api/datasets')\n",
"os.getcwd()\n",
"print(os.listdir())\n"
]
},
{
"cell_type": "code",
"execution_count": 99,
"metadata": {},
"outputs": [],
"source": [
"dataset = pd.read_csv('amazon_dataset.csv')"
]
},
{
"cell_type": "code",
"execution_count": 100,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(10002, 28)"
]
},
"execution_count": 100,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dataset.shape"
]
},
{
"cell_type": "code",
"execution_count": 101,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Uniq Id</th>\n",
" <th>Product Name</th>\n",
" <th>Brand Name</th>\n",
" <th>Asin</th>\n",
" <th>Category</th>\n",
" <th>Upc Ean Code</th>\n",
" <th>List Price</th>\n",
" <th>Selling Price</th>\n",
" <th>Quantity</th>\n",
" <th>Model Number</th>\n",
" <th>...</th>\n",
" <th>Product Url</th>\n",
" <th>Stock</th>\n",
" <th>Product Details</th>\n",
" <th>Dimensions</th>\n",
" <th>Color</th>\n",
" <th>Ingredients</th>\n",
" <th>Direction To Use</th>\n",
" <th>Is Amazon Seller</th>\n",
" <th>Size Quantity Variant</th>\n",
" <th>Product Description</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>4c69b61db1fc16e7013b43fc926e502d</td>\n",
" <td>DB Longboards CoreFlex Crossbow 41\" Bamboo Fib...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Sports &amp; Outdoors | Outdoor Recreation | Skate...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>$237.68</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>https://www.amazon.com/DB-Longboards-CoreFlex-...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Y</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>66d49bbed043f5be260fa9f7fbff5957</td>\n",
" <td>Electronic Snap Circuits Mini Kits Classpack, ...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Toys &amp; Games | Learning &amp; Education | Science ...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>$99.95</td>\n",
" <td>NaN</td>\n",
" <td>55324</td>\n",
" <td>...</td>\n",
" <td>https://www.amazon.com/Electronic-Circuits-Cla...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Y</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2c55cae269aebf53838484b0d7dd931a</td>\n",
" <td>3Doodler Create Flexy 3D Printing Filament Ref...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Toys &amp; Games | Arts &amp; Crafts | Craft Kits</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>$34.99</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>...</td>\n",
" <td>https://www.amazon.com/3Doodler-Plastic-Innova...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Y</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>18018b6bc416dab347b1b7db79994afa</td>\n",
" <td>Guillow Airplane Design Studio with Travel Cas...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Toys &amp; Games | Hobbies | Models &amp; Model Kits |...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>$28.91</td>\n",
" <td>NaN</td>\n",
" <td>142</td>\n",
" <td>...</td>\n",
" <td>https://www.amazon.com/Guillow-Airplane-Design...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Y</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>e04b990e95bf73bbe6a3fa09785d7cd0</td>\n",
" <td>Woodstock- Collage 500 pc Puzzle</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Toys &amp; Games | Puzzles | Jigsaw Puzzles</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>$17.49</td>\n",
" <td>NaN</td>\n",
" <td>62151</td>\n",
" <td>...</td>\n",
" <td>https://www.amazon.com/Woodstock-Collage-500-p...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Y</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 28 columns</p>\n",
"</div>"
],
"text/plain": [
" Uniq Id \\\n",
"0 4c69b61db1fc16e7013b43fc926e502d \n",
"1 66d49bbed043f5be260fa9f7fbff5957 \n",
"2 2c55cae269aebf53838484b0d7dd931a \n",
"3 18018b6bc416dab347b1b7db79994afa \n",
"4 e04b990e95bf73bbe6a3fa09785d7cd0 \n",
"\n",
" Product Name Brand Name Asin \\\n",
"0 DB Longboards CoreFlex Crossbow 41\" Bamboo Fib... NaN NaN \n",
"1 Electronic Snap Circuits Mini Kits Classpack, ... NaN NaN \n",
"2 3Doodler Create Flexy 3D Printing Filament Ref... NaN NaN \n",
"3 Guillow Airplane Design Studio with Travel Cas... NaN NaN \n",
"4 Woodstock- Collage 500 pc Puzzle NaN NaN \n",
"\n",
" Category Upc Ean Code List Price \\\n",
"0 Sports & Outdoors | Outdoor Recreation | Skate... NaN NaN \n",
"1 Toys & Games | Learning & Education | Science ... NaN NaN \n",
"2 Toys & Games | Arts & Crafts | Craft Kits NaN NaN \n",
"3 Toys & Games | Hobbies | Models & Model Kits |... NaN NaN \n",
"4 Toys & Games | Puzzles | Jigsaw Puzzles NaN NaN \n",
"\n",
" Selling Price Quantity Model Number ... \\\n",
"0 $237.68 NaN NaN ... \n",
"1 $99.95 NaN 55324 ... \n",
"2 $34.99 NaN NaN ... \n",
"3 $28.91 NaN 142 ... \n",
"4 $17.49 NaN 62151 ... \n",
"\n",
" Product Url Stock Product Details \\\n",
"0 https://www.amazon.com/DB-Longboards-CoreFlex-... NaN NaN \n",
"1 https://www.amazon.com/Electronic-Circuits-Cla... NaN NaN \n",
"2 https://www.amazon.com/3Doodler-Plastic-Innova... NaN NaN \n",
"3 https://www.amazon.com/Guillow-Airplane-Design... NaN NaN \n",
"4 https://www.amazon.com/Woodstock-Collage-500-p... NaN NaN \n",
"\n",
" Dimensions Color Ingredients Direction To Use Is Amazon Seller \\\n",
"0 NaN NaN NaN NaN Y \n",
"1 NaN NaN NaN NaN Y \n",
"2 NaN NaN NaN NaN Y \n",
"3 NaN NaN NaN NaN Y \n",
"4 NaN NaN NaN NaN Y \n",
"\n",
" Size Quantity Variant Product Description \n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 NaN NaN \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
"[5 rows x 28 columns]"
]
},
"execution_count": 101,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dataset.head()"
]
},
{
"cell_type": "code",
"execution_count": 102,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['Uniq Id', 'Product Name', 'Brand Name', 'Asin', 'Category',\n",
" 'Upc Ean Code', 'List Price', 'Selling Price', 'Quantity',\n",
" 'Model Number', 'About Product', 'Product Specification',\n",
" 'Technical Details', 'Shipping Weight', 'Product Dimensions', 'Image',\n",
" 'Variants', 'Sku', 'Product Url', 'Stock', 'Product Details',\n",
" 'Dimensions', 'Color', 'Ingredients', 'Direction To Use',\n",
" 'Is Amazon Seller', 'Size Quantity Variant', 'Product Description'],\n",
" dtype='object')"
]
},
"execution_count": 102,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dataset.columns"
]
},
{
"cell_type": "code",
"execution_count": 103,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0 4c69b61db1fc16e7013b43fc926e502d\n",
"1 66d49bbed043f5be260fa9f7fbff5957\n",
"2 2c55cae269aebf53838484b0d7dd931a\n",
"3 18018b6bc416dab347b1b7db79994afa\n",
"4 e04b990e95bf73bbe6a3fa09785d7cd0\n",
"Name: Uniq Id, dtype: object"
]
},
"execution_count": 103,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dataset['Uniq Id'].head()"
]
},
{
"cell_type": "code",
"execution_count": 104,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"32"
]
},
"execution_count": 104,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"len(dataset['Uniq Id'][0])"
]
},
{
"cell_type": "code",
"execution_count": 105,
"metadata": {},
"outputs": [],
"source": [
"# Database credentials\n",
"db_credentials = {\n",
" 'host': 'localhost',\n",
" 'port': '5432',\n",
" 'database': 'postgres_db',\n",
" 'user': 'postgres_user',\n",
" 'password': 'postgres_pass'\n",
"}\n",
"\n",
"# Create a connection string\n",
"connection_string = f\"postgresql://{db_credentials['user']}:{db_credentials['password']}@{db_credentials['host']}:{db_credentials['port']}/{db_credentials['database']}\"\n",
"\n",
"# Create a SQLAlchemy engine\n",
"engine = create_engine(connection_string)\n"
]
},
{
"cell_type": "code",
"execution_count": 106,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>id</th>\n",
" <th>username</th>\n",
" <th>password</th>\n",
" <th>email</th>\n",
" <th>role_name</th>\n",
" <th>is_active</th>\n",
" <th>is_tfa</th>\n",
" <th>created_at</th>\n",
" <th>updated_at</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>a4c291cc-5009-440c-8db0-02f7f11ab964</td>\n",
" <td>test-user</td>\n",
" <td>$argon2id$v=19$m=4096,t=3,p=1$xj+gEfx2tF584ugW...</td>\n",
" <td>test-user@email.com</td>\n",
" <td>User</td>\n",
" <td>True</td>\n",
" <td>False</td>\n",
" <td>2024-01-25 16:51:34.864462+00:00</td>\n",
" <td>2024-01-25 16:51:34.864462+00:00</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" id username \\\n",
"0 a4c291cc-5009-440c-8db0-02f7f11ab964 test-user \n",
"\n",
" password email \\\n",
"0 $argon2id$v=19$m=4096,t=3,p=1$xj+gEfx2tF584ugW... test-user@email.com \n",
"\n",
" role_name is_active is_tfa created_at \\\n",
"0 User True False 2024-01-25 16:51:34.864462+00:00 \n",
"\n",
" updated_at \n",
"0 2024-01-25 16:51:34.864462+00:00 "
]
},
"execution_count": 106,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\n",
"# create a read command \n",
"\n",
"df = pd.read_sql_query('SELECT * FROM public.\"users\"', con=engine)\n",
"\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Ulid generate to insert as id for each row in table "
]
},
{
"cell_type": "code",
"execution_count": 107,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'01HN2EKWP6Y0X86G8RDZCPXYJF'"
]
},
"execution_count": 107,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\n",
"#Instantiate the ULID class\n",
"ulid = ULID()\n",
"ulid.generate() #01BX5ZZKBKACTAV9WEVGEMMVRZ\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Mono ulid incr by 1"
]
},
{
"cell_type": "code",
"execution_count": 108,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'01HN2EKWPTF18GEGYV5NBRWCQR'"
]
},
"execution_count": 108,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from ulid import Monotonic\n",
"\n",
"#Instantiate the Monotonic Class\n",
"mono_ulid = Monotonic()\n",
"\n",
"# Same timestamp when calls are made within the same\n",
"# millisecond and least-significant random bit is incremented by 1\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR11\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR12\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR13\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR14\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR15\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR16\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR17\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR18\n",
"mono_ulid.generate() #01DC8Y7RBV4RSXX0437Z1RQR19\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"01HN2EKWQAQRKYMAKAR25A1TMV\n",
"01HN2EKWQA72ENKQ1YRAP96D9K\n",
"01HN2EKWQA0HEDKK6Z6445DV1Y\n",
"01HN2EKWQA6GSXGX05RYVAVFHX\n",
"01HN2EKWQAC2N61GBP6VYWD8R3\n"
]
}
],
"source": [
"# python loo for 10 times \n",
"for i in range(5):\n",
" print(ulid.generate())"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Merchants dataframe "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Create some random names"
]
},
{
"cell_type": "code",
"execution_count": 110,
"metadata": {},
"outputs": [],
"source": [
"import random as rand"
]
},
{
"cell_type": "code",
"execution_count": 117,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Hindi Names:\n",
"['हरप्रीत कौर', 'हरप्रीत शर्मा', 'हरप्रीत सिंह', 'हरप्रीत कुमार', 'हरप्रीत सागर']\n",
"\n",
"English Names:\n",
"['harpreet kaur', 'harpreet sharma', 'harpreet singh', 'harpreet kumar', 'harpreet sagar']\n"
]
}
],
"source": [
"\n",
"def generate_names():\n",
" hindi_prefixes = ['हर', 'गुर', 'बल', 'मान', 'नव', 'सुख', 'कुश']\n",
" hindi_suffixes = ['प्रीत', 'मीत', 'गीत', 'जीत', 'विंदर', 'दीप']\n",
" hindi_lastnames = ['कौर', 'शर्मा', 'सिंह', 'कुमार', 'सागर', 'देसी', 'परदेशी', 'झोरी']\n",
"\n",
" eng_prefixes = ['har', 'gur', 'bal', 'man', 'nav', 'sukh', 'kush']\n",
" eng_suffixes = ['preet', 'meet', 'geet', 'jeet', 'vinder', 'deep']\n",
" eng_lastnames = ['kaur', 'sharma', 'singh', 'kumar', 'sagar', 'desi', 'pardeshi', 'jhori']\n",
"\n",
" hindi_names = []\n",
" eng_names = []\n",
"\n",
" for (hi, eng) in zip(hindi_prefixes, eng_prefixes):\n",
" for (hi2, eng2) in zip(hindi_suffixes, eng_suffixes):\n",
" for (hiln, enln) in zip(hindi_lastnames, eng_lastnames):\n",
" hindi_names.append(hi + hi2 + ' ' + hiln)\n",
" eng_names.append(eng + eng2 + ' ' + enln)\n",
"\n",
" return hindi_names, eng_names\n",
"\n",
"# create a list of names\n",
"hindi_names, eng_names = generate_names()\n",
"\n",
"# print the generated names\n",
"print(\"Hindi Names:\")\n",
"print(hindi_names[:5]) # print the first 5 names\n",
"print(\"\\nEnglish Names:\")\n",
"print(eng_names[:5]) # print the first 5 names\n"
]
},
{
"cell_type": "code",
"execution_count": 113,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"मानदीप सागर\n",
"kushpreet kaur\n"
]
}
],
"source": [
"hindi_name = rand.choice(hindi_names)\n",
"english_name = rand.choice(english_names)\n",
"\n",
"print(hindi_name)\n",
"print(english_name)\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Create a util function that take data and return json object"
]
},
{
"cell_type": "code",
"execution_count": 123,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"{\"column1\":[1,2,3],\"column2\":[\"A\",\"B\",\"C\"]}\n",
"\n"
]
},
{
"data": {
"text/plain": [
"'{\"column1\":[1,2,3],\"column2\":[\"A\",\"B\",\"C\"]}\\n'"
]
},
"execution_count": 123,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def convert_dataframe_to_json(df):\n",
" \"\"\"\n",
" Converts a Pandas DataFrame to JSON format.\n",
"\n",
" Parameters:\n",
" - df (pd.DataFrame): The input DataFrame.\n",
"\n",
" Returns:\n",
" - str: JSON representation of the DataFrame.\n",
" \"\"\"\n",
" try:\n",
" # Convert DataFrame to JSON\n",
" json_data = df.to_json(orient='records', lines=True)\n",
" return json_data\n",
"\n",
" except Exception as e:\n",
" return f\"Error: {e}\"\n",
"\n",
"# Sample Pandas DataFrame\n",
"some_random_data = {'column1': [1, 2, 3],\n",
" 'column2': ['A', 'B', 'C']}\n",
"\n",
"def take_data_and_return_json(data):\n",
" df = pd.DataFrame([data])\n",
"\n",
" # Convert DataFrame to JSON using the function\n",
" json_data = convert_dataframe_to_json(df)\n",
"\n",
" # Display the JSON object\n",
" print(json_data)\n",
" return json_data\n",
"\n",
"take_data_and_return_json(some_random_data)\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Create a function that take hindi english name and return json"
]
},
{
"cell_type": "code",
"execution_count": 125,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"{\"hi\":\"\\u0915\\u0941\\u0936\\u092a\\u094d\\u0930\\u0940\\u0924 \\u091d\\u094b\\u0930\\u0940\",\"en\":\"manjeet jhori\"}\n",
"\n"
]
},
{
"data": {
"text/plain": [
"'{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u091d\\\\u094b\\\\u0930\\\\u0940\",\"en\":\"manjeet jhori\"}\\n'"
]
},
"execution_count": 125,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def generate_random_name_json():\n",
" data = { 'hi': rand.choice(hindi_names), 'en': rand.choice(eng_names)}\n",
" return take_data_and_return_json(data)\n",
"\n",
"\n",
"\n",
"generate_random_name_json()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Create some random data for merchants "
]
},
{
"cell_type": "code",
"execution_count": 131,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"{\"hi\":\"\\u0939\\u0930\\u092a\\u094d\\u0930\\u0940\\u0924 \\u0926\\u0947\\u0938\\u0940\",\"en\":\"gurpreet desi\"}\n",
"\n",
"{\"hi\":\"\\u0917\\u0941\\u0930\\u091c\\u0940\\u0924 \\u0938\\u093e\\u0917\\u0930\",\"en\":\"manvinder kumar\"}\n",
"\n",
"{\"hi\":\"\\u0915\\u0941\\u0936\\u092a\\u094d\\u0930\\u0940\\u0924 \\u0915\\u0941\\u092e\\u093e\\u0930\",\"en\":\"harvinder kaur\"}\n",
"\n",
"{\"hi\":\"\\u092e\\u093e\\u0928\\u091c\\u0940\\u0924 \\u091d\\u094b\\u0930\\u0940\",\"en\":\"kushmeet jhori\"}\n",
"\n",
"{\"hi\":\"\\u0928\\u0935\\u092e\\u0940\\u0924 \\u0938\\u093f\\u0902\\u0939\",\"en\":\"sukhjeet sharma\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u091c\\u0940\\u0924 \\u0915\\u0941\\u092e\\u093e\\u0930\",\"en\":\"kushpreet sagar\"}\n",
"\n",
"{\"hi\":\"\\u0915\\u0941\\u0936\\u0926\\u0940\\u092a \\u0915\\u0941\\u092e\\u093e\\u0930\",\"en\":\"sukhpreet kaur\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u0935\\u093f\\u0902\\u0926\\u0930 \\u0915\\u0941\\u092e\\u093e\\u0930\",\"en\":\"sukhmeet pardeshi\"}\n",
"\n",
"{\"hi\":\"\\u092e\\u093e\\u0928\\u092a\\u094d\\u0930\\u0940\\u0924 \\u091d\\u094b\\u0930\\u0940\",\"en\":\"gurvinder singh\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u0926\\u0940\\u092a \\u0938\\u093f\\u0902\\u0939\",\"en\":\"balmeet pardeshi\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u091c\\u0940\\u0924 \\u0938\\u093f\\u0902\\u0939\",\"en\":\"sukhpreet sagar\"}\n",
"\n",
"{\"hi\":\"\\u092c\\u0932\\u092e\\u0940\\u0924 \\u0926\\u0947\\u0938\\u0940\",\"en\":\"mandeep sagar\"}\n",
"\n",
"{\"hi\":\"\\u092e\\u093e\\u0928\\u091c\\u0940\\u0924 \\u0938\\u093e\\u0917\\u0930\",\"en\":\"manmeet jhori\"}\n",
"\n",
"{\"hi\":\"\\u0915\\u0941\\u0936\\u092e\\u0940\\u0924 \\u092a\\u0930\\u0926\\u0947\\u0936\\u0940\",\"en\":\"mandeep jhori\"}\n",
"\n",
"{\"hi\":\"\\u0915\\u0941\\u0936\\u092a\\u094d\\u0930\\u0940\\u0924 \\u0938\\u093f\\u0902\\u0939\",\"en\":\"harpreet sagar\"}\n",
"\n",
"{\"hi\":\"\\u092e\\u093e\\u0928\\u0926\\u0940\\u092a \\u091d\\u094b\\u0930\\u0940\",\"en\":\"navgeet pardeshi\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u092a\\u094d\\u0930\\u0940\\u0924 \\u0915\\u094c\\u0930\",\"en\":\"kushjeet jhori\"}\n",
"\n",
"{\"hi\":\"\\u0938\\u0941\\u0916\\u092a\\u094d\\u0930\\u0940\\u0924 \\u0936\\u0930\\u094d\\u092e\\u093e\",\"en\":\"kushvinder kaur\"}\n",
"\n",
"{\"hi\":\"\\u0917\\u0941\\u0930\\u0917\\u0940\\u0924 \\u0938\\u093f\\u0902\\u0939\",\"en\":\"baldeep sharma\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u0917\\u0940\\u0924 \\u0936\\u0930\\u094d\\u092e\\u093e\",\"en\":\"sukhgeet pardeshi\"}\n",
"\n",
"{\"hi\":\"\\u0917\\u0941\\u0930\\u092e\\u0940\\u0924 \\u0938\\u093f\\u0902\\u0939\",\"en\":\"kushgeet kaur\"}\n",
"\n",
"{\"hi\":\"\\u092e\\u093e\\u0928\\u091c\\u0940\\u0924 \\u0938\\u093e\\u0917\\u0930\",\"en\":\"manjeet kaur\"}\n",
"\n",
"{\"hi\":\"\\u092e\\u093e\\u0928\\u0917\\u0940\\u0924 \\u0938\\u093e\\u0917\\u0930\",\"en\":\"manjeet desi\"}\n",
"\n",
"{\"hi\":\"\\u0928\\u0935\\u0926\\u0940\\u092a \\u0938\\u093e\\u0917\\u0930\",\"en\":\"kushjeet pardeshi\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u0917\\u0940\\u0924 \\u0938\\u093f\\u0902\\u0939\",\"en\":\"kushgeet sagar\"}\n",
"\n",
"{\"hi\":\"\\u0939\\u0930\\u0926\\u0940\\u092a \\u0936\\u0930\\u094d\\u092e\\u093e\",\"en\":\"gurmeet sagar\"}\n",
"\n",
"{\"hi\":\"\\u0915\\u0941\\u0936\\u091c\\u0940\\u0924 \\u092a\\u0930\\u0926\\u0947\\u0936\\u0940\",\"en\":\"hardeep kumar\"}\n",
"\n",
"{\"hi\":\"\\u0915\\u0941\\u0936\\u0926\\u0940\\u092a \\u0938\\u093f\\u0902\\u0939\",\"en\":\"balgeet kumar\"}\n",
"\n",
"{\"hi\":\"\\u0928\\u0935\\u091c\\u0940\\u0924 \\u0936\\u0930\\u094d\\u092e\\u093e\",\"en\":\"gurpreet kaur\"}\n",
"\n",
"{\"hi\":\"\\u0928\\u0935\\u0926\\u0940\\u092a \\u0938\\u093e\\u0917\\u0930\",\"en\":\"sukhmeet singh\"}\n",
"\n"
]
},
{
"data": {
"text/plain": [
"[{'id': '01HN2F7MH4W7PBS61Q1N9GJRR5',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u0926\\\\u0947\\\\u0938\\\\u0940\",\"en\":\"gurpreet desi\"}\\n'},\n",
" {'id': '01HN2F7MH52DY85ARNNW5AVMT6',\n",
" 'name': '{\"hi\":\"\\\\u0917\\\\u0941\\\\u0930\\\\u091c\\\\u0940\\\\u0924 \\\\u0938\\\\u093e\\\\u0917\\\\u0930\",\"en\":\"manvinder kumar\"}\\n'},\n",
" {'id': '01HN2F7MH6N7BJDHHVBSDK6S7X',\n",
" 'name': '{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u0915\\\\u0941\\\\u092e\\\\u093e\\\\u0930\",\"en\":\"harvinder kaur\"}\\n'},\n",
" {'id': '01HN2F7MH6W5VVYHJ6Q03SP723',\n",
" 'name': '{\"hi\":\"\\\\u092e\\\\u093e\\\\u0928\\\\u091c\\\\u0940\\\\u0924 \\\\u091d\\\\u094b\\\\u0930\\\\u0940\",\"en\":\"kushmeet jhori\"}\\n'},\n",
" {'id': '01HN2F7MH7VWK306PB1GWPN78F',\n",
" 'name': '{\"hi\":\"\\\\u0928\\\\u0935\\\\u092e\\\\u0940\\\\u0924 \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"sukhjeet sharma\"}\\n'},\n",
" {'id': '01HN2F7MH87A4H4TGE0CDCSDKY',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u091c\\\\u0940\\\\u0924 \\\\u0915\\\\u0941\\\\u092e\\\\u093e\\\\u0930\",\"en\":\"kushpreet sagar\"}\\n'},\n",
" {'id': '01HN2F7MH8KW1M7GKN71N5M1M3',\n",
" 'name': '{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u0926\\\\u0940\\\\u092a \\\\u0915\\\\u0941\\\\u092e\\\\u093e\\\\u0930\",\"en\":\"sukhpreet kaur\"}\\n'},\n",
" {'id': '01HN2F7MH96HCVYKFHE88GXQW5',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u0935\\\\u093f\\\\u0902\\\\u0926\\\\u0930 \\\\u0915\\\\u0941\\\\u092e\\\\u093e\\\\u0930\",\"en\":\"sukhmeet pardeshi\"}\\n'},\n",
" {'id': '01HN2F7MHABBKQZ3WCWPGBWJY4',\n",
" 'name': '{\"hi\":\"\\\\u092e\\\\u093e\\\\u0928\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u091d\\\\u094b\\\\u0930\\\\u0940\",\"en\":\"gurvinder singh\"}\\n'},\n",
" {'id': '01HN2F7MHAGSDE6GAZXH0SWA07',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u0926\\\\u0940\\\\u092a \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"balmeet pardeshi\"}\\n'},\n",
" {'id': '01HN2F7MHB3MTRAHS1PQG57B40',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u091c\\\\u0940\\\\u0924 \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"sukhpreet sagar\"}\\n'},\n",
" {'id': '01HN2F7MHDPAGC9WD1ZKZCWVSC',\n",
" 'name': '{\"hi\":\"\\\\u092c\\\\u0932\\\\u092e\\\\u0940\\\\u0924 \\\\u0926\\\\u0947\\\\u0938\\\\u0940\",\"en\":\"mandeep sagar\"}\\n'},\n",
" {'id': '01HN2F7MHEZEBJYGBE5ZE7W4MW',\n",
" 'name': '{\"hi\":\"\\\\u092e\\\\u093e\\\\u0928\\\\u091c\\\\u0940\\\\u0924 \\\\u0938\\\\u093e\\\\u0917\\\\u0930\",\"en\":\"manmeet jhori\"}\\n'},\n",
" {'id': '01HN2F7MHFJ1QAF78MW7Y7W954',\n",
" 'name': '{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u092e\\\\u0940\\\\u0924 \\\\u092a\\\\u0930\\\\u0926\\\\u0947\\\\u0936\\\\u0940\",\"en\":\"mandeep jhori\"}\\n'},\n",
" {'id': '01HN2F7MHG6JRSQ9P68KGW0HYM',\n",
" 'name': '{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"harpreet sagar\"}\\n'},\n",
" {'id': '01HN2F7MHHVA88T54DC9R9ATTM',\n",
" 'name': '{\"hi\":\"\\\\u092e\\\\u093e\\\\u0928\\\\u0926\\\\u0940\\\\u092a \\\\u091d\\\\u094b\\\\u0930\\\\u0940\",\"en\":\"navgeet pardeshi\"}\\n'},\n",
" {'id': '01HN2F7MHJ2BWPHVQGDQNJJYQM',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u0915\\\\u094c\\\\u0930\",\"en\":\"kushjeet jhori\"}\\n'},\n",
" {'id': '01HN2F7MHM93B46DES0675THXV',\n",
" 'name': '{\"hi\":\"\\\\u0938\\\\u0941\\\\u0916\\\\u092a\\\\u094d\\\\u0930\\\\u0940\\\\u0924 \\\\u0936\\\\u0930\\\\u094d\\\\u092e\\\\u093e\",\"en\":\"kushvinder kaur\"}\\n'},\n",
" {'id': '01HN2F7MHNA7F2YG2TNPN63P4P',\n",
" 'name': '{\"hi\":\"\\\\u0917\\\\u0941\\\\u0930\\\\u0917\\\\u0940\\\\u0924 \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"baldeep sharma\"}\\n'},\n",
" {'id': '01HN2F7MHPVW9W5N2D57MD9RTD',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u0917\\\\u0940\\\\u0924 \\\\u0936\\\\u0930\\\\u094d\\\\u092e\\\\u093e\",\"en\":\"sukhgeet pardeshi\"}\\n'},\n",
" {'id': '01HN2F7MHQJVZ79SGNFA0X1YTJ',\n",
" 'name': '{\"hi\":\"\\\\u0917\\\\u0941\\\\u0930\\\\u092e\\\\u0940\\\\u0924 \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"kushgeet kaur\"}\\n'},\n",
" {'id': '01HN2F7MHRFWNJ8C16DV4S6W97',\n",
" 'name': '{\"hi\":\"\\\\u092e\\\\u093e\\\\u0928\\\\u091c\\\\u0940\\\\u0924 \\\\u0938\\\\u093e\\\\u0917\\\\u0930\",\"en\":\"manjeet kaur\"}\\n'},\n",
" {'id': '01HN2F7MHSW0BBVN0ERFKQ823F',\n",
" 'name': '{\"hi\":\"\\\\u092e\\\\u093e\\\\u0928\\\\u0917\\\\u0940\\\\u0924 \\\\u0938\\\\u093e\\\\u0917\\\\u0930\",\"en\":\"manjeet desi\"}\\n'},\n",
" {'id': '01HN2F7MHSKWDDMN6MWHXMM6KS',\n",
" 'name': '{\"hi\":\"\\\\u0928\\\\u0935\\\\u0926\\\\u0940\\\\u092a \\\\u0938\\\\u093e\\\\u0917\\\\u0930\",\"en\":\"kushjeet pardeshi\"}\\n'},\n",
" {'id': '01HN2F7MHTQBHT2QNGM0TMA05G',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u0917\\\\u0940\\\\u0924 \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"kushgeet sagar\"}\\n'},\n",
" {'id': '01HN2F7MHV6DE4Q88NMC5K31EC',\n",
" 'name': '{\"hi\":\"\\\\u0939\\\\u0930\\\\u0926\\\\u0940\\\\u092a \\\\u0936\\\\u0930\\\\u094d\\\\u092e\\\\u093e\",\"en\":\"gurmeet sagar\"}\\n'},\n",
" {'id': '01HN2F7MHWMF5S31MFR0MD5YCK',\n",
" 'name': '{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u091c\\\\u0940\\\\u0924 \\\\u092a\\\\u0930\\\\u0926\\\\u0947\\\\u0936\\\\u0940\",\"en\":\"hardeep kumar\"}\\n'},\n",
" {'id': '01HN2F7MHXAHQGM8AB7XFNAGJX',\n",
" 'name': '{\"hi\":\"\\\\u0915\\\\u0941\\\\u0936\\\\u0926\\\\u0940\\\\u092a \\\\u0938\\\\u093f\\\\u0902\\\\u0939\",\"en\":\"balgeet kumar\"}\\n'},\n",
" {'id': '01HN2F7MHYPR52GESXWMJ3PBA6',\n",
" 'name': '{\"hi\":\"\\\\u0928\\\\u0935\\\\u091c\\\\u0940\\\\u0924 \\\\u0936\\\\u0930\\\\u094d\\\\u092e\\\\u093e\",\"en\":\"gurpreet kaur\"}\\n'},\n",
" {'id': '01HN2F7MHZ0TVW0PVXHKBJ5PR5',\n",
" 'name': '{\"hi\":\"\\\\u0928\\\\u0935\\\\u0926\\\\u0940\\\\u092a \\\\u0938\\\\u093e\\\\u0917\\\\u0930\",\"en\":\"sukhmeet singh\"}\\n'}]"
]
},
"execution_count": 131,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"merchants = []\n",
"\n",
"for i in range(30):\n",
" merchants.append( {\n",
" \"id\":ulid.generate(),\n",
" \"name\": generate_random_name_json(),\n",
" })\n",
"\n",
"\n",
"merchants"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### Insert the generated data "
]
},
{
"cell_type": "code",
"execution_count": 132,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Data inserted successfully!\n"
]
}
],
"source": [
"df = pd.DataFrame(merchants)\n",
"# Insert data into the PostgreSQL database\n",
"try:\n",
" # Establish a connection\n",
" connection = engine.connect()\n",
"\n",
" # Insert DataFrame into the database\n",
" df.to_sql('merchants', con=engine, if_exists='append', index=False)\n",
"\n",
" print(\"Data inserted successfully!\")\n",
"\n",
"except Exception as e:\n",
" print(f\"Error: {e}\")\n",
"\n",
"finally:\n",
" # Close the connection\n",
" if connection:\n",
" connection.close()\n"
]
},
{
"cell_type": "code",
"execution_count": 134,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>id</th>\n",
" <th>name</th>\n",
" <th>details</th>\n",
" <th>is_active</th>\n",
" <th>created_at</th>\n",
" <th>updated_at</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>01HN2F7MH4W7PBS61Q1N9GJRR5</td>\n",
" <td>{'en': 'gurpreet desi', 'hi': 'हरप्रीत देसी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>01HN2F7MH52DY85ARNNW5AVMT6</td>\n",
" <td>{'en': 'manvinder kumar', 'hi': 'गुरजीत सागर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>01HN2F7MH6N7BJDHHVBSDK6S7X</td>\n",
" <td>{'en': 'harvinder kaur', 'hi': 'कुशप्रीत कुमार'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>01HN2F7MH6W5VVYHJ6Q03SP723</td>\n",
" <td>{'en': 'kushmeet jhori', 'hi': 'मानजीत झोरी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>01HN2F7MH7VWK306PB1GWPN78F</td>\n",
" <td>{'en': 'sukhjeet sharma', 'hi': 'नवमीत सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>01HN2F7MH87A4H4TGE0CDCSDKY</td>\n",
" <td>{'en': 'kushpreet sagar', 'hi': 'हरजीत कुमार'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>01HN2F7MH8KW1M7GKN71N5M1M3</td>\n",
" <td>{'en': 'sukhpreet kaur', 'hi': 'कुशदीप कुमार'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>01HN2F7MH96HCVYKFHE88GXQW5</td>\n",
" <td>{'en': 'sukhmeet pardeshi', 'hi': 'हरविंदर कुम...</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>01HN2F7MHABBKQZ3WCWPGBWJY4</td>\n",
" <td>{'en': 'gurvinder singh', 'hi': 'मानप्रीत झोरी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>01HN2F7MHAGSDE6GAZXH0SWA07</td>\n",
" <td>{'en': 'balmeet pardeshi', 'hi': 'हरदीप सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>01HN2F7MHB3MTRAHS1PQG57B40</td>\n",
" <td>{'en': 'sukhpreet sagar', 'hi': 'हरजीत सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>01HN2F7MHDPAGC9WD1ZKZCWVSC</td>\n",
" <td>{'en': 'mandeep sagar', 'hi': 'बलमीत देसी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>01HN2F7MHEZEBJYGBE5ZE7W4MW</td>\n",
" <td>{'en': 'manmeet jhori', 'hi': 'मानजीत सागर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>01HN2F7MHFJ1QAF78MW7Y7W954</td>\n",
" <td>{'en': 'mandeep jhori', 'hi': 'कुशमीत परदेशी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>01HN2F7MHG6JRSQ9P68KGW0HYM</td>\n",
" <td>{'en': 'harpreet sagar', 'hi': 'कुशप्रीत सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>01HN2F7MHHVA88T54DC9R9ATTM</td>\n",
" <td>{'en': 'navgeet pardeshi', 'hi': 'मानदीप झोरी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>01HN2F7MHJ2BWPHVQGDQNJJYQM</td>\n",
" <td>{'en': 'kushjeet jhori', 'hi': 'हरप्रीत कौर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>01HN2F7MHM93B46DES0675THXV</td>\n",
" <td>{'en': 'kushvinder kaur', 'hi': 'सुखप्रीत शर्मा'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>01HN2F7MHNA7F2YG2TNPN63P4P</td>\n",
" <td>{'en': 'baldeep sharma', 'hi': 'गुरगीत सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>01HN2F7MHPVW9W5N2D57MD9RTD</td>\n",
" <td>{'en': 'sukhgeet pardeshi', 'hi': 'हरगीत शर्मा'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>01HN2F7MHQJVZ79SGNFA0X1YTJ</td>\n",
" <td>{'en': 'kushgeet kaur', 'hi': 'गुरमीत सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>01HN2F7MHRFWNJ8C16DV4S6W97</td>\n",
" <td>{'en': 'manjeet kaur', 'hi': 'मानजीत सागर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>01HN2F7MHSW0BBVN0ERFKQ823F</td>\n",
" <td>{'en': 'manjeet desi', 'hi': 'मानगीत सागर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>01HN2F7MHSKWDDMN6MWHXMM6KS</td>\n",
" <td>{'en': 'kushjeet pardeshi', 'hi': 'नवदीप सागर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>01HN2F7MHTQBHT2QNGM0TMA05G</td>\n",
" <td>{'en': 'kushgeet sagar', 'hi': 'हरगीत सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>01HN2F7MHV6DE4Q88NMC5K31EC</td>\n",
" <td>{'en': 'gurmeet sagar', 'hi': 'हरदीप शर्मा'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>01HN2F7MHWMF5S31MFR0MD5YCK</td>\n",
" <td>{'en': 'hardeep kumar', 'hi': 'कुशजीत परदेशी'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>01HN2F7MHXAHQGM8AB7XFNAGJX</td>\n",
" <td>{'en': 'balgeet kumar', 'hi': 'कुशदीप सिंह'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>01HN2F7MHYPR52GESXWMJ3PBA6</td>\n",
" <td>{'en': 'gurpreet kaur', 'hi': 'नवजीत शर्मा'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>01HN2F7MHZ0TVW0PVXHKBJ5PR5</td>\n",
" <td>{'en': 'sukhmeet singh', 'hi': 'नवदीप सागर'}</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" <td>2024-01-26 08:47:29.358283+00:00</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" id \\\n",
"0 01HN2F7MH4W7PBS61Q1N9GJRR5 \n",
"1 01HN2F7MH52DY85ARNNW5AVMT6 \n",
"2 01HN2F7MH6N7BJDHHVBSDK6S7X \n",
"3 01HN2F7MH6W5VVYHJ6Q03SP723 \n",
"4 01HN2F7MH7VWK306PB1GWPN78F \n",
"5 01HN2F7MH87A4H4TGE0CDCSDKY \n",
"6 01HN2F7MH8KW1M7GKN71N5M1M3 \n",
"7 01HN2F7MH96HCVYKFHE88GXQW5 \n",
"8 01HN2F7MHABBKQZ3WCWPGBWJY4 \n",
"9 01HN2F7MHAGSDE6GAZXH0SWA07 \n",
"10 01HN2F7MHB3MTRAHS1PQG57B40 \n",
"11 01HN2F7MHDPAGC9WD1ZKZCWVSC \n",
"12 01HN2F7MHEZEBJYGBE5ZE7W4MW \n",
"13 01HN2F7MHFJ1QAF78MW7Y7W954 \n",
"14 01HN2F7MHG6JRSQ9P68KGW0HYM \n",
"15 01HN2F7MHHVA88T54DC9R9ATTM \n",
"16 01HN2F7MHJ2BWPHVQGDQNJJYQM \n",
"17 01HN2F7MHM93B46DES0675THXV \n",
"18 01HN2F7MHNA7F2YG2TNPN63P4P \n",
"19 01HN2F7MHPVW9W5N2D57MD9RTD \n",
"20 01HN2F7MHQJVZ79SGNFA0X1YTJ \n",
"21 01HN2F7MHRFWNJ8C16DV4S6W97 \n",
"22 01HN2F7MHSW0BBVN0ERFKQ823F \n",
"23 01HN2F7MHSKWDDMN6MWHXMM6KS \n",
"24 01HN2F7MHTQBHT2QNGM0TMA05G \n",
"25 01HN2F7MHV6DE4Q88NMC5K31EC \n",
"26 01HN2F7MHWMF5S31MFR0MD5YCK \n",
"27 01HN2F7MHXAHQGM8AB7XFNAGJX \n",
"28 01HN2F7MHYPR52GESXWMJ3PBA6 \n",
"29 01HN2F7MHZ0TVW0PVXHKBJ5PR5 \n",
"\n",
" name details is_active \\\n",
"0 {'en': 'gurpreet desi', 'hi': 'हरप्रीत देसी'} None None \n",
"1 {'en': 'manvinder kumar', 'hi': 'गुरजीत सागर'} None None \n",
"2 {'en': 'harvinder kaur', 'hi': 'कुशप्रीत कुमार'} None None \n",
"3 {'en': 'kushmeet jhori', 'hi': 'मानजीत झोरी'} None None \n",
"4 {'en': 'sukhjeet sharma', 'hi': 'नवमीत सिंह'} None None \n",
"5 {'en': 'kushpreet sagar', 'hi': 'हरजीत कुमार'} None None \n",
"6 {'en': 'sukhpreet kaur', 'hi': 'कुशदीप कुमार'} None None \n",
"7 {'en': 'sukhmeet pardeshi', 'hi': 'हरविंदर कुम... None None \n",
"8 {'en': 'gurvinder singh', 'hi': 'मानप्रीत झोरी'} None None \n",
"9 {'en': 'balmeet pardeshi', 'hi': 'हरदीप सिंह'} None None \n",
"10 {'en': 'sukhpreet sagar', 'hi': 'हरजीत सिंह'} None None \n",
"11 {'en': 'mandeep sagar', 'hi': 'बलमीत देसी'} None None \n",
"12 {'en': 'manmeet jhori', 'hi': 'मानजीत सागर'} None None \n",
"13 {'en': 'mandeep jhori', 'hi': 'कुशमीत परदेशी'} None None \n",
"14 {'en': 'harpreet sagar', 'hi': 'कुशप्रीत सिंह'} None None \n",
"15 {'en': 'navgeet pardeshi', 'hi': 'मानदीप झोरी'} None None \n",
"16 {'en': 'kushjeet jhori', 'hi': 'हरप्रीत कौर'} None None \n",
"17 {'en': 'kushvinder kaur', 'hi': 'सुखप्रीत शर्मा'} None None \n",
"18 {'en': 'baldeep sharma', 'hi': 'गुरगीत सिंह'} None None \n",
"19 {'en': 'sukhgeet pardeshi', 'hi': 'हरगीत शर्मा'} None None \n",
"20 {'en': 'kushgeet kaur', 'hi': 'गुरमीत सिंह'} None None \n",
"21 {'en': 'manjeet kaur', 'hi': 'मानजीत सागर'} None None \n",
"22 {'en': 'manjeet desi', 'hi': 'मानगीत सागर'} None None \n",
"23 {'en': 'kushjeet pardeshi', 'hi': 'नवदीप सागर'} None None \n",
"24 {'en': 'kushgeet sagar', 'hi': 'हरगीत सिंह'} None None \n",
"25 {'en': 'gurmeet sagar', 'hi': 'हरदीप शर्मा'} None None \n",
"26 {'en': 'hardeep kumar', 'hi': 'कुशजीत परदेशी'} None None \n",
"27 {'en': 'balgeet kumar', 'hi': 'कुशदीप सिंह'} None None \n",
"28 {'en': 'gurpreet kaur', 'hi': 'नवजीत शर्मा'} None None \n",
"29 {'en': 'sukhmeet singh', 'hi': 'नवदीप सागर'} None None \n",
"\n",
" created_at updated_at \n",
"0 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"1 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"2 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"3 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"4 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"5 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"6 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"7 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"8 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"9 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"10 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"11 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"12 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"13 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"14 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"15 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"16 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"17 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"18 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"19 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"20 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"21 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"22 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"23 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"24 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"25 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"26 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"27 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"28 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 \n",
"29 2024-01-26 08:47:29.358283+00:00 2024-01-26 08:47:29.358283+00:00 "
]
},
"execution_count": 134,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# create a read command \n",
"\n",
"df = pd.read_sql_query('SELECT * FROM public.\"merchants\"', con=engine)\n",
"\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 141,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DataFrame exported to merchants.csv\n"
]
}
],
"source": [
"# As we already changed our directory using os.chdir('/home/pro/Documents/GitHub/scalable_api/datasets')\n",
"# we can directly export over data to csv or xls file \n",
"csv_file_path = 'merchants.csv'\n",
"\n",
"# Export DataFrame to CSV\n",
"df.to_csv(csv_file_path, index=False)\n",
"\n",
"print(f\"DataFrame exported to {csv_file_path}\")\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.5"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment