Python Bytes - SQL to Dataframe

Published: 29 March 2023
on channel: AC

358

#Coded by Andrew C

import sqlite3

connect = sqlite3.connect(':memory:')

cursor = connect.cursor()

people_data = {
1:('John', 20, 'Office'),
2:('Lucy', 40, 'Alt'),
3:('Mary', 56, 'Office'),
4:('Zach', 23, 'Office'),
5:('Sam', 37, 'Home'),
6:('Linda', 31, 'Office'),
7:('Kevin', 39, 'Alt'),
8:('Juan', 21, 'Home')
}

def create():
sqlstr = 'CREATE TABLE PEOPLE(ID int AUTO_INCREMENT, Name varchar(255), Age int, Location varchar(255))'
cursor.execute(sqlstr)
connect.commit()

def insert(id, name, age, location):
sqlstr = 'INSERT INTO PEOPLE(ID, Name, Age, Location) VALUES ("' + str(id) + '", "' + str(name) + '", ' + str(age) + ', "' + str(location) + '")'
cursor.execute(sqlstr)
connect.commit()

import pandas as pd

def dfimport():
sql_query = pd.read_sql_query (''' SELECT * FROM PEOPLE''', connect)
df = pd.DataFrame(sql_query, columns = ['ID', 'Name', 'Age','Location'])
print (df)

create()

for k, v in people_data.items():
insert(k, v[0], v[1], v[2])

insert(9,'Luke',26, 'Alt')

dfimport()

cursor.close()

Watch video Python Bytes - SQL to Dataframe online without registration, duration hours minute second in high quality. This video was added by user AC 29 March 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 358 once and liked it 7 people.

814