0

So I have data in text file like this:

TRMMQMI12903CE2A25<SEP>SOCBYZN12AB0189CFC<SEP>Arthur Adams<SEP>Callin' Heaven
TRMMQUA128F425761E<SEP>SOEDQXW12A8AE458B8<SEP>Wesley Willis<SEP>Jesus Christ
TRMMQXZ128F92D0A0E<SEP>SOQVOIM12A8C140DF9<SEP>Manjul<SEP>Hungry Belly
TRMMZRE128F42B799E<SEP>SOHLRUF12A8C13F0FF<SEP>Daniel Balavoine<SEP>Sauver L'Amour
TRMMZJO128F42434B2<SEP>SOTFIKQ12A6D4FB45F<SEP>Sandhy SonDoro<SEP>Don�t Let It Bring You Down
TRMMZSA128F4233C55<SEP>SOBXHIT12A8C130F59<SEP>Willie "Big Eyes" Smith<SEP>Tell Me Mama

I managed to load it into pandas data frame like this:

data = pd.read_table('unique_tracks.txt', header=None, sep='<SEP>', engine='python')

and the data that I get from this looks like this:

0       TRMMMYQ128F932D901  ...                         Silent Night
1       TRMMMKD128F425225D  ...                          Tanssi vaan
2       TRMMMRX128F93187D9  ...                    No One Could Ever
3       TRMMMCH128F425532C  ...                        Si Vos Quer�s
4       TRMMMWA128F426B589  ...                     Tangle Of Aspens

I also created my table like this:

import sqlite3
from sqlite3 import Error
import pandas as pd


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn

def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

def main():
    database = r"Data_db.db"

    sql_create_songs_table = """ CREATE TABLE IF NOT EXISTS songs (
                                        id integer PRIMARY KEY,
                                        execution_id text NOT NULL,
                                        song_id text NOT NULL,
                                        artist_id text NOT NULL,
                                        song_title text NOT NULL
                                    ); """

    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_songs_table)

        # create tasks table
        create_table(conn, sql_create_users_table)
    else:
        print("Error! cannot create the database connection.")

if __name__ == '__main__':
    main()

create_connection(r"Data_db.db")

My question is how I can load data from my data frame into table ? Is there a difference between pd.read_table and pd.read_csv when It comes to loading data into a db ?

1 Answer 1

1

You can use the to_sql method.

data.to_sql("songs", conn)

Have a look at the documentation https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html

read_table and read_csv should not make a difference since it is already in a pandas dataframe. Just make sure dataframe columns match the table columns.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.