0

I don't know if what I am trying to do makes the most sense or if there is a better way to do this. However, I am learning how to build a website using django. I am wondering, can I use an external python script that runs daily to get stock information, and publish it to my django website database?

I have created a Stock class as follows:

class Stock(models.Model):
    def __str__(self):
        return self.name
    name = models.CharField(max_length=50)
    ticker = models.CharField(max_length=5)
    price = models.DecimalField(max_digits=100, decimal_places=2)
    date = models.DateField()

I then run a python script that pulls stock data down and tries to write to the database as follows:

dfDatabase = dfCurrentDay[['Ticker', 'Company', 'Close', 'Date']]
con = db.connect(r'C:\Users\shawn\Dev\stockshome\trying_django\src\db.sqlite3')
dfDatabase.to_sql('Stock', con=con, if_exists='replace')
data = con.execute("SELECT * FROM Stock").fetchall()
print(data)

When I print data, it returns the appropriate values. However, when I go to the webpage, it shows up blank. Is that because I have done something wrong with my view, or am I doing something wrong trying to write to the database?

Is there a different way I should be approaching this idea? I envision having various pages based on stock sector, or market cap sizes, etc and I'd like to have a page with a table of the stock information and then a hyperlink to another page that will show the chart, or more detailed financial data.

Shawn Schreier
  • 780
  • 2
  • 10
  • 20

2 Answers2

1

So, you want to pull in data from an external source routinely and dump that data into your Database. One approach would be to routine run a batch job like a celery scheduler that runs at a specific time or after a specific period. So, you will have some sort of task something like this

@app.task
def pull_data():
    data = requests.get(your URL)
    #now you can do a bulk insert to the Database
    #assuming data is json data
    data = json.loads(data) #will convert json to dict
    #parse the data either by looping or however your data is structured and then
    #insert the data into the stock table.
    from .models import Stock
    stock,_ = Stock.objects.update_or_create(name=data['name']......)

Also, consider the idea of indexing your Stock table if, at a later stage, you would like to do READ queries.

Something like this
 @app.task(name='pull_and_dump')
 def pull_data_and_insert():
          response = requests.get('https://jsonplaceholder.typicode.com/posts')
        content = json.loads(response.content)
        for i in range(len(content)):
            dump,_ = DumpData.objects.update_or_create(u_id=content[i]['userId'],title=content[i]['title'])
SDRJ
  • 532
  • 3
  • 11
  • I've tried to do something like the first code you suggested and I am getting an error that the Apps aren't ready. I am doing some searching to troubleshoot. – Shawn Schreier Feb 10 '21 at 23:05
  • I have resolved that issue and now get an error that says "RuntimeError: Model class models.Stock doesn't declare an explicit app_label and isn't in an application in INSTALLED_APPS." Now I am onto troubleshooting that error but I think I am on the right track, or at least hope I am! – Shawn Schreier Feb 10 '21 at 23:39
  • Stacy - I think I have gotten so close. I had to add in a django.setup() to get the apps ready. I then had to add trades.models rather than just using .models even though my external python script is in the same folder. However, I was having issues with the date so I have temporarily removed that for now. The issue I am currently trying to work through is this error message - django.core.exceptions.ValidationError: ['“0 189.63\n1 278.69\n2 242.82\nName: price, dtype: float64” value must be a decimal number.']. Any thoughts on how to resolve? It stems from the stock,_ line of code. – Shawn Schreier Feb 11 '21 at 02:28
0

The reason that this does not work is because the table created by Django is not called Stock. Django will have prefixed the name of the model with the name of the application. So if your application is named "testapp" for example, Django will have created the table as testapp_stock.

The reason that it apparently works is that .to_sql() will drop and recreate the table. You should be able to find a table named Stock in your sqlite3 db that contains the data from your last attempt.

There are some other problems. Firstly your code drops the existing table and recreates it before inserting the rows from the dataframe. However, the column names from the dataframe do not match those of the Django model, so Django will not be able to use that table any longer with out exploding.

Also Django creates an auto incrementing "id" column for the table. Other tables might reference this column if it is a foreign key or otherwise. Dropping and recreating the table might cause referential integrity problems.

Another issue is that sqlite is not particularly good at handling multiple concurrent writes from different clients. It's possible that Django could be writing to the table at the same time as the drop/reload from the .to_sql() occurs.

You can correct the table and column names, however, I'm not sure that you should proceed this way. To help you decide you could take a look at some of these older questions on the same topic. The second one looks somewhat familiar:

mhawke
  • 84,695
  • 9
  • 117
  • 138
  • Thank you so much for the comments and advice. I really appreciate that and the links to other questions. I know it currently replaces, but I was just doing that for a test. I will review the other questions for guidance, but maybe I need to use celery, or use that third question's technique. – Shawn Schreier Feb 10 '21 at 17:52