[Tutor] make a sqlite3 database from an ordinary text file

[Manprit Singh]

Dear Sir,

Also the column names in the text file, becomes the column name in sqlite3
table. I have verified it while opening the table in column mode from
sqlite3 terminal

On Mon, Jun 20, 2022 at 9:02 PM Manprit Singh <manpritsinghece at gmail.com>
wrote:

> Dear Sir,
>
> The most convenient way that i found to read that text file into an
> sqlite3 database is using pandas as given below:
>
> import sqlite3
> import pandas as pd
>
> df = pd.read_table("abcd.txt", sep="\s+")
>                                       # will read the entire file to a
> dataframe
> conn = sqlite3.connect("work.db")
>                                          #  will make a database  work.db
> cur = conn.cursor()
> df.to_sql(name="worktime", con=conn, if_exists="replace",
> index=False)                      # Entire text file data, which is into a
> dataframe is now written to sqlite table
> cur.execute("select Time from worktime where
> Pieces=40")                                          # select query
> print(cur.fetchall())
>
> gives output :
>
> [(5,)]
>
>
> On Mon, Jun 20, 2022 at 8:04 PM Manprit Singh <manpritsinghece at gmail.com>
> wrote:
>
>>  Dear Sir ,
>>
>>
>>
>>  cur.execute("create table DATA(? INT, ? INT)",heads)
>>
>> As in earlier mail I pointed out that the above command  will not work
>> because column names are not written inside the  single quotes in the
>> command create table.  Hence we can not use (?} place holders  .
>>
>> Regards
>> Manprit Singh
>>
>> On Mon, Jun 20, 2022 at 7:47 PM Manprit Singh <manpritsinghece at gmail.com>
>> wrote:
>>
>>> Dear Sir,
>>>
>>> Many thanks for this . This mail has a lot of things for me . I Will do
>>> all the exercises on my own and will revert you soon.
>>>
>>> Regards
>>> Manprit Singh
>>>
>>> On Mon, Jun 20, 2022 at 4:39 PM Alan Gauld via Tutor <tutor at python.org>
>>> wrote:
>>>
>>>> On 20/06/2022 01:59, Manprit Singh wrote:
>>>>
>>>> > clear that the database table will have 2 columns, Column names of the
>>>> > sqlite3 table are to be picked from the  first line of the text file
>>>> > abcd.txt. (First column name will be Time, second column name will be
>>>> > Pieces ).
>>>> > See i know this can be done very easily using numpy and Pandas. But I
>>>> am
>>>> > trying to do it with Core Python only.
>>>> >
>>>> > cur.execute("create table workdata(? INT, ? INT)", ("Time", "Pieces"))
>>>> >
>>>> > I just want to insert column names (where these column names are  read
>>>> > from text file abcd.txt 1st line)  using a question mark style
>>>> > placeholder.
>>>>
>>>> OK, Having shown how I would really do it, I'll now try to
>>>> answer the question for pure Python:
>>>>
>>>> def get_data(open_file):
>>>>     for line in open_file:
>>>>         yield line.split()
>>>>
>>>>
>>>> # open the database/cursor here....
>>>>
>>>> with open('data.txt') as inf:
>>>>     heads = inf.readline().split()
>>>>     cur.execute("create table DATA(? INT, ? INT)",heads)
>>>>     cur.executemany("insert into workdata values (?, ?)",get_data(inf))
>>>>
>>>> cur.execute("select * from DATA")
>>>> for row in cur.fetchall():
>>>>     print(row)
>>>>
>>>>
>>>> Note, I didn't actually run this but I think it should be close.
>>>>
>>>> --
>>>> Alan G
>>>> Author of the Learn to Program web site
>>>> http://www.alan-g.me.uk/
>>>> http://www.amazon.com/author/alan_gauld
>>>> Follow my photo-blog on Flickr at:
>>>> http://www.flickr.com/photos/alangauldphotos
>>>>
>>>>
>>>> _______________________________________________
>>>> Tutor maillist  -  Tutor at python.org
>>>> To unsubscribe or change subscription options:
>>>> https://mail.python.org/mailman/listinfo/tutor
>>>>
>>>