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

[avi.e.gross at gmail.com]

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