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

[Manprit Singh]

Dear all,
Learning is a lifelong process . At this point i still do not feel i am
absolutely ok with python or programming. In the past i was very active in
this mailing list and will appreciate a few - Alan sir, Dennis Lee Bieber,
dn and few others, they always answered my queries .
As i wrote in the starting , i have a simple text file named abcd.txt. (I
am not using a csv file or any other structured file) . The contents are
given below :

Time  Pieces
1       10
2       15
3       25
4       31
5       40
6       45
7       53
8       54
9       65
10      75

We can clearly see the first line of the file is

Time  Pieces

Now I have to read this text  into an sqlite3 database table , So it is
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.  Pls let me know if anything is possible .

Time  Pieces

On Mon, Jun 20, 2022 at 5:34 AM <avi.e.gross at gmail.com> wrote:

> Manprit,
>
> You may be approaching this topic differently than you could.
>
> What you keep calling a TEXT file is what others might call structured
> files
> like .CSV or .DAT types that contain values separated by some delimiter
> like
> a comma or a tab or arbitrary whitespace or other choices. Lots of python
> functionalities are available to read  these in to structures used in
> modules like numpy and pandas that many people use on top of or alongside
> regular built-in python commands. In these, there can be (and usually are)
> HEADER names for columns and sometimes also rows. The functionality that
> reads in such tables, Dataframes, or whatever you want to call it, will
> often optionally take the first line of the file to have headers specifying
> names. And, you can always add or change the names as well.
>
> So if passing the entire structure to another function, it can choose to
> access the name of any column (or often row) when it wants. Or, if you need
> to, you can ask for it and pass it your way as in interpolating the name
> into an SQL command.
>
> You may be asking a slightly different question here about just your data.
> Does your "text" file contain a first line saying ""Time        Pieces" or
> not?
>
> If it DOES, you may need to read it ONCE at the start before giving your
> iterator for the numbers to be read and store the results as your names to
> use as you wish. If the file has no header, then your code is the one
> attaching a name.
>
> To me the real issue is your design seems to be of a limited nature. You
> want to read in your data in a small batch (single line) and presumably are
> opening a connection to the database and doing login and so on, then
> sending
> a command to create a table, unless it already exists, then sending umpteen
> commands to insert yet another row into the table and then who knows? This
> seems like such a common operation that I have to guess there is a way to
> do
> it fairly trivially and my guess is way more efficiently.
>
>
>
> -----Original Message-----
> From: Tutor <tutor-bounces+avi.e.gross=gmail.com at python.org> On Behalf Of
> Manprit Singh
> Sent: Sunday, June 19, 2022 2:38 PM
> Cc: tutor at python.org
> Subject: Re: [Tutor] make a sqlite3 database from an ordinary text file
>
> Dear sir ,
>
> One more query ....
> How can i use column name in text file as colum names in sqlite3 database .
> I tried using python but failed.
>
> Can you put some light over it ?
> Regards
> Manprit Singh
>
> On Sun, 19 Jun, 2022, 23:36 Alan Gauld via Tutor, <tutor at python.org>
> wrote:
>
> > On 19/06/2022 18:11, Manprit Singh wrote:
> >
> > > cur.execute("create table workdata(Time INT, Pieces INT)")
> >
> > This will fail the second time it runs. You should check if the table
> > already exists and either drop it or use the existing table.
> >
> > However, I rarely create tables from Python, it's generally easier to
> > create a sql file and run that directly using the sqlite interpreter.
> > You only need Python if the format of the table has to be deduced from
> > the data. and that then leads to all sorts of follow-on issues over
> > accessing names of columns etc.
> >
> > Once you have the table you can then use Python to load the data,
> > although in your case you could do that directly from SQL too since it
> > looks like a tab separated file.
> >
> > As always use the most appropriate tool. If you can do it directly in
> > SQL there is no point in wrapping it in Python. That just adds
> > complexity and slows things down.
> >
> > --
> > 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
> >
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
>