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

[Manprit Singh]

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
>>>
>>