Python Pandas Tutorial 14: Read Write Data From Database (read_sql, to_sql)




[ad_1]

Pandas’ read_sql, read_sql_table, read_sql_query methods provide a way to read records in database directly into a dataframe. to_sql on dataframe can be used to write dataframe records into sql table. Using sqlalchemy engine, one can interface easily with mysql, postgres, oracle databases.

Topics that are covered in this Python Pandas Video:
0:00 Introduction
0:53 Create sqlalchemy engine by using create_engine() method
2:16 Read table in data frame by using read_sql_table() method
4:18 Use read_sql_query() method to execute sql query
6:44 Rename the dataframe columns using rename()
8:00 Use to_sql() method write into sql table
10:30 Use read_sql() method

Code link: https://github.com/codebasics/py/blob/master/pandas/21_sql/pandas_sql.ipynb

To download csv and code for all tutorials: go to https://github.com/codebasics/py, click on a green button to clone or download the entire repository and then go to relevant folder to get access to that specific file.

Next Video:
Pandas Time Series Analysis Part 1: DatetimeIndex and Resample: https://www.youtube.com/watch?v=r0s4slGHwzE&list=PLeo1K3hjS3uuASpe-1LjfG5f14Bnozjwy&index=15

Popular Playlist:
Complete python course: https://www.youtube.com/playlist?list=PLeo1K3hjS3uv5U-Lmlnucd7gqF-3ehIh0

Data science course: https://www.youtube.com/playlist?list=PLeo1K3hjS3us_ELKYSj_Fth2tIEkdKXvV

Machine learning tutorials: https://www.youtube.com/playlist?list=PLeo1K3hjS3uvCeTYTeyfe0-rN5r8zn9rw

Pandas tutorials: https://www.youtube.com/playlist?list=PLeo1K3hjS3uuASpe-1LjfG5f14Bnozjwy

Git github tutorials: https://www.youtube.com/playlist?list=PLeo1K3hjS3usJuxZZUBdjAcilgfQHkRzW

Matplotlib course: https://www.youtube.com/playlist?list=PLeo1K3hjS3uu4Lr8_kro2AqaO6CFYgKOl

Data structures course: https://www.youtube.com/playlist?list=PLeo1K3hjS3uu_n_a__MI_KktGTLYopZ12

Website: http://codebasicshub.com/
Facebook: https://www.facebook.com/codebasicshub
Twitter: https://twitter.com/codebasicshub

Source


[ad_2]

Comment List

  • codebasics
    November 26, 2020

    Step by step guide on how to learn data science for free: https://www.youtube.com/watch?v=Vn_mmOuQkSA
    Machine learning tutorials with exercises:
    https://www.youtube.com/watch?v=gmvvaobm7eQ&list=PLeo1K3hjS3uvCeTYTeyfe0-rN5r8zn9rw

  • codebasics
    November 26, 2020

    i got :::::sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1115, "Unknown character set: 'utf8mb4'") this error

  • codebasics
    November 26, 2020

    Hello how are yoy? I just saw your videos and they are amazing Thank's. I have a question about 'to_sql' and 'pymysql'. I have all the time traying to pass a 'csv' file or 'xlsx' file into mysql using the required engine, but I have always the same error 'Background on this error at: http://sqlalche.me/e/2j85' It's about connections and I read all the page about Connection Pooling¶ and nothing works, maybe you a tip or an orientation I can flow.
    Tank you very much !!!!

  • codebasics
    November 26, 2020

    Does this tutorial work with MS SQL Server?

  • codebasics
    November 26, 2020

    Video quality for this video is Horrible

  • codebasics
    November 26, 2020

    please zoom in. its very small

  • codebasics
    November 26, 2020

    Great stuff !

  • codebasics
    November 26, 2020

    If we want to read records from rabbitmq, then what commands we should use…. Can you please suggest ???

  • codebasics
    November 26, 2020

    I don't have any dbms installed. Can I create a sql file online and then use that to create engine in jupyter?

  • codebasics
    November 26, 2020

    Hi Sir,
    Thank you for your excellent video. However when I want to upload my df I get the following error, please see the error on stackoverflow:
    https://stackoverflow.com/questions/62912357/i-get-typeerror-cannot-use-a-string-pattern-on-a-bytes-like-object-when-using-t

  • codebasics
    November 26, 2020

    Very helpful and easy to understand,Thanks a lot sir!

  • codebasics
    November 26, 2020

    Sir, what would be the connection string if ms access is SQL??

  • codebasics
    November 26, 2020

    on google colab i am facing this error "Can't connect to MySQL server on 'localhost" how can i resolve it?

  • codebasics
    November 26, 2020

    Hi Sir,

    i am getting table EMP not found in below code

    import cx_Oracle

    import sqlalchemy

    import pandas as pd

    from sqlalchemy import *

    # Specify database connection details and establish connection.
    DATABASE = "XE"

    SCHEMA = "shivendra"

    PASSWORD = "shivendra"

    connstr = "oracle://{}:{}@{}".format(SCHEMA, PASSWORD, DATABASE)

    engine = sqlalchemy.create_engine(connstr)

    df = pd.read_sql_table("EMP", con=conn)

    i am uisng oracle database,i have installed cx_oracle,pandas and sqlalchemy
    .
    could you please tell me whct i am doing as mistake

  • codebasics
    November 26, 2020

    the link for documentation is down

  • codebasics
    November 26, 2020

    Excellent.

  • codebasics
    November 26, 2020

    how do you popup that helper box at 8:13 ?

  • codebasics
    November 26, 2020

    good tutorial, really helps, thank you sir

  • codebasics
    November 26, 2020

    What do you do if you already have a primary key?

  • codebasics
    November 26, 2020

    Hello, after the insert query using df.to_sql() is there a way to retrieve the results of the query? Like some sort of cursor operation like cursor.fetchone()

  • codebasics
    November 26, 2020

    df.to_sql( ) inserts value without repeatition ? if not in that case i want insert values from table without repeatition.

  • codebasics
    November 26, 2020

    Sir, in most cases we read csv/text/excel….. Can we access folder, so that we can able to read all the file from the same folder???? secondly, is there a method of reading all files together in jupyter notebook and then consolidating accordingly. Lastly, I am using jupyter notebook but I don't have borderlines across dataframe in rows and column. Could you please advise on this?

  • codebasics
    November 26, 2020

    you named a customer..'RAHUL GANDHI" ha ha ha ha….lol

    WATched the whole series..
    . Thank you so much …….
    out of 4 channels i shortlisted…yours i chose as first.
    Totally worth the time…. <3 🙂
    Please make a series on R… your teaching style is simple and effective……..

  • codebasics
    November 26, 2020

    Do you suppose to close the engine after you are done using it? There is a "dispose()" method, but I'm not sure if it has to be used.

  • codebasics
    November 26, 2020

    Thank u sm… I understood how pandas will work, how to read and write data from csv, excel, database. I completed all videos. The way of explaining with examples it's clearly understanding to me…. I am very thankful to u…I reached next level of my knowledge by adding pandas…

  • codebasics
    November 26, 2020

    this is last video on pandas to learn datascience

  • codebasics
    November 26, 2020

    Thank you so much. Exactly what I was looking for!!! And it is explained so clearly🙏👍👋

  • codebasics
    November 26, 2020

    Somehow I get an error and don't really know why: InternalError: (pymysql.err.InternalError) (1241, 'Operand should contain 1 column(s)')

    I have a guess: It's about the datatypes of my dataframe. Do the datatypes of the dataframe have to match with the data types of the mysql table?

  • codebasics
    November 26, 2020

    Great tutorial! One question: Do I have the same order of columns in my dataframe as in the database table where I insert the data of my pandas dataframe?

  • codebasics
    November 26, 2020

    thanks. thanks very helpful

  • codebasics
    November 26, 2020

    what is that localhost code how can i find it

  • codebasics
    November 26, 2020

    What A Blessing In A Video Format!

  • codebasics
    November 26, 2020

    1193, "Unknown system variable 'tx_isolation'" hello sir I am getting this error how to solved it.

  • codebasics
    November 26, 2020

    Very nice tutorial… it’s gonna help me a lot… tks

  • codebasics
    November 26, 2020

    how i can call store procedure using pandas in python.

  • codebasics
    November 26, 2020

    OMG- Amazing.. Why pay for a MOC

  • codebasics
    November 26, 2020

    thank you so much.you saved my ass

  • codebasics
    November 26, 2020

    Hello, I'm using microsoft access database, I can insert a row, but I can't update values in this row, I can't find any information for resolving this problem. I really thank if you can help me in this case

  • codebasics
    November 26, 2020

    Nice !!

  • codebasics
    November 26, 2020

    this is really stupid, you did not show how to create database and insert records and start reading etc

  • codebasics
    November 26, 2020

    Sir,
    z_score aur outlier explain kar do plzzzzzz!!!!!!!!!!

  • codebasics
    November 26, 2020

    thank you for the video!
    how would i use sql on a csv document i have saved in a folder as opposed to the mysql workbench you have in this video?

  • codebasics
    November 26, 2020

    "NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mysql.pymsql"
    I am getting this error, can you give a solution..

  • codebasics
    November 26, 2020

    Machine learning tutorials with exercises are available at:
    https://www.youtube.com/watch?v=gmvvaobm7eQ&list=PLeo1K3hjS3uvCeTYTeyfe0-rN5r8zn9rw

  • codebasics
    November 26, 2020

    Need help, picking sql query from EXCEL and execute using python code

  • codebasics
    November 26, 2020

    Sir request to you please make one video how to read documentation effectively it will be very helpful for us.

  • codebasics
    November 26, 2020

    Getting Error :
    ModuleNotFoundError: No module named 'PyMySQL'
    even though I have installed the module pymysql
    Please Help

Write a comment